vba type mismatch after adding IsWorkBookOpen in if statement

Question

I had a bit of code which was working:

WbShortage = "name.xlsm"
For l = TotalDefectsPerPieces + 1 To TheLastRow
            For i = StartDate To EndDate
                    Date = Cells(DateRow, i).Value
                    [...]
                    With Workbooks(WbShortage).Worksheets("Report")[...]

Then I added this to check which file is opened (there are two options):

WbShortage1 = "name.xlsm"
WbShortage2 = "name2.xlsm"
If IsWorkBookOpen(WbShortage1) = True Then
   WbShortage = WbShortage1
   Else
   WbShortage = WbShortage2
End If
For l = TotalDefectsPerPieces + 1 To TheLastRow
            For i = StartDate To EndDate
                    Date = Cells(DateRow, i).Value
                    [...]
                    With Workbooks(WbShortage).Worksheets("Report")[...]

And now in the line Date = Cells(DateRow, i).Value there is type mismatch error. When I delete If statement everything is fine. I just don't get it, this line in in no way dependent on WbShortage as it corresponds to another Wb.

Of course I have this function:

Function IsWorkBookOpen(sWB)
On Error GoTo NotOpen
Workbooks(sWB).Activate
IsWorkBookOpen = True
Exit Function
NotOpen:
IsWorkBookOpen = False
End Function

@TimWilliams Thanks, I will look to that, however, I have almost the same loop before this one, where I also used the same if statement and there is no problem. That's what confused me the most, because the difference is that in this loop is in 2D and the first one is 1D. — Obisam, Sep 08 '16 at 23:52

score 2 · Accepted Answer · edited May 23 '17 at 12:01

This comes up here multiple times a day: in a non-worksheet code module you need to make sure you qualify all of your calls to Cells or Range with a worksheet reference, or they will all be executed in the scope of the active sheet (and your IsWorkBookOpen function changes that...)

See: What is the default scope of worksheets and cells and range?

Your function might be better written as:

Function IsWorkBookOpen(sWB) As Boolean
    Dim wb As Workbook

    On Error Resume Next
    Set wb = Workbooks(sWB)
    On Error Goto 0

    IsWorkBookOpen = Not wb Is Nothing

End Function

...which avoids the side-effect of changing the Active workbook, but the real solution is to qualify your Cells with a worksheet

@Mat'sMug - what was that about functions and side effects?

I'm not Mat, nor his Mug, but I agree that this is a great example! — Doug Glancy, Sep 09 '16 at 03:10

vba type mismatch after adding IsWorkBookOpen in if statement

1 Answers1