ISREF syntaxx

[dhancy]

In Excel VBA, I can run this code:

if evaluate("ISREF('Sheet1'!A1)") then

I now want to expand on that to refer to a sheet on another open workbook.

I found this syntax online:

if evaluate("ISREF('[MyFile.xlsm]Sheet1'!A1)") then

If Sheet1 exists, it returns true.

If Sheet1 does not exist, I get "Error 2015".

Shouldn't it return False in that case? Perhaps there is different syntax I should use? Can you help? Thanks!


Dennis

 

[StephenCrump]

Re: Help with ISREF syntaxx

You could do this:

Dim v As Variant


v = Evaluate("ISREF('[Timesheet.xlsm]Sheet1'!A1)")

and then test IsError(v).

The alternative is to write a short SheetExists function. One way (there are many possible variations) ...

Function SheetExists(sWs As String, Optional wb As Workbook) As Boolean

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    SheetExists = Len(wb.Worksheets(sWs).Name)

End Function

 

[dhancy]

Re: Help with ISREF syntaxx

Thank you !!

The "IsError" piece is just what I was missing. It works like a champ.


Dennis

 

[StephenCrump]

Re: Help with ISREF syntaxx

Great, you're welcome.

 

[AlThePal78]

Re: Help with ISREF syntaxx

Function SheetExists(sWs As String, Optional wb As Workbook) As Boolean

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    SheetExists = Len(wb.Worksheets(sWs).Name)

End Function

I see this code a lot. Would this look through a whole workbook vs having a loop to check to see if it exist.

 

[StephenCrump]

Re: Help with ISREF syntaxx

I see this code a lot. Would this look through a whole workbook vs having a loop to check to see if it exist.

Correct. Although I would describe the approach as check the existence of a particular sheet, rather than look through a whole workbook.

The only time I would loop though all worksheets in a workbook would be to do a fuzzy match, e.g. to pick up all worksheets with a certain keyword in the sheet name, or all worksheets with a sheet name in a particular format.

 

[AlThePal78]

Re: Help with ISREF syntaxx

Correct. Although I would describe the approach as check the existence of a particular sheet, rather than look through a whole workbook.

The only time I would loop though all worksheets in a workbook would be to do a fuzzy match, e.g. to pick up all worksheets with a certain keyword in the sheet name, or all worksheets with a sheet name in a particular format.

So somewhere in my code I can just put

if call [COLOR=#574123][I]Function SheetExists("SHEETNAME",thisworkbook)then
    do code
    end if
 [/I][/COLOR]

and this will come back to me either true or false, and if true do something? I am confused how to call this function

 

[Fluff]

Re: Help with ISREF syntaxx

You would call the function like

   If SheetExists("Pcode", ThisWorkbook) Then
      MsgBox "ok"
   End If

 

[Fluff]

Re: Help with ISREF syntaxx

I would also recommend changing the function slightly like

Function SheetExists(sWs As String, Optional wb As Workbook) As Boolean

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    SheetExists = Len(wb.Worksheets(sWs).Name)
[COLOR=#ff0000]    On Error Goto 0[/COLOR]
End Function

 

[AlThePal78]

Re: Help with ISREF syntaxx

I would also recommend changing the function slightly like

Function SheetExists(sWs As String, Optional wb As Workbook) As Boolean

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    SheetExists = Len(wb.Worksheets(sWs).Name)
[COLOR=#ff0000]    On Error Goto 0[/COLOR]
End Function

I thought there would be something simpler that would use isref in the VBA.

if isref(indirect("'sheetName'!A1") in wb then 
   etc.. 
end if

but that didn't work lol