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

 

[Fluff]

Re: Help with ISREF syntaxx

You can use it like

   If Evaluate("isref(pcode!a1)") Then MsgBox "ok"

For the activeworkbook

 

[Fluff]

Re: Help with ISREF syntaxx

Or like this for an inactive workbook

   If Evaluate("isref('[rwc 2019.xlsx]info'!a1)") Then MsgBox "ok"

 

[AlThePal78]

Re: Help with ISREF syntaxx

Or like this for an inactive workbook

   If Evaluate("isref('[rwc 2019.xlsx]info'!a1)") Then MsgBox "ok"

Why is this bad if all I am trying to do is find out if a certain worksheet is there or not there on that workbook? I can add this code if it is there and if it s not there I can do an else statement to add the sheet to the front and then add a table of contents. I am just trying to figure something out like I did this and got no error messages whether it was there or not.

 

[Fluff]

Re: Help with ISREF syntaxx

I'm afraid I don't understand what you are saying.

 

[AlThePal78]

Re: Help with ISREF syntaxx

I'm afraid I don't understand what you are saying.

I don't remember what post it was on stackoverflow.com it was but someone said that it was not a good idea to use that way to see if a sheet was available. However, I am trying to figure out why? I ran the sub procedure three different times. 1. to see if it was actually a worksheet, if it wasn't a worksheet and again if the worksheet already existed, still no issues using that version of the isref formula on the spreadsheet. Do you see or for see any reason that would not be the proper way to search for a worksheet.

 

[Fluff]

Re: Help with ISREF syntaxx

When checking if a sheet exists in the active workbook I use Isref, otherwise I use a function similar to post#9

 

[AlThePal78]

Re: Help with ISREF syntaxx

When checking if a sheet exists in the active workbook I use Isref, otherwise I use a function similar to post#9

Thank you very much I will use this information when creating my TOC and other personal macros.

 

[Fluff]

Re: Help with ISREF syntaxx

You're welcome & thanks for the feedback