ISREF syntaxx

dhancy

Board Regular
Joined
Jul 10, 2013
Messages
120
Office Version
  1. 2016
Platform
  1. Windows
In Excel VBA, I can run this code:

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:

Code:
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Re: Help with ISREF syntaxx

You could do this:

Code:
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) ...

Code:
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
 
Upvote 0
Re: Help with ISREF syntaxx

Thank you !!

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


Dennis
 
Upvote 0
Re: Help with ISREF syntaxx

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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

Code:
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
 
Upvote 0
Re: Help with ISREF syntaxx

You would call the function like
Rich (BB code):
   If SheetExists("Pcode", ThisWorkbook) Then
      MsgBox "ok"
   End If
 
Upvote 0
Re: Help with ISREF syntaxx

I would also recommend changing the function slightly like
Code:
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
 
Upvote 0
Re: Help with ISREF syntaxx

I would also recommend changing the function slightly like
Code:
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.

Code:
if isref(indirect("'sheetName'!A1") in wb then 
   etc.. 
end if
but that didn't work lol
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top