Excel VBA: UDF to check if a 2nd Excel workbook, open in another instance/application of excel, with-CodePudding

I copied some UDF function code that serves my purpose from this guide which supposedly has worked for others. I didn't really alter the code much, but for transparency here is the exact code I have in my workbook as a module:

Function IsExtWorkBookOpen(Name As String) As Boolean
    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsExtWorkBookOpen = (Not xWb Is Nothing)
End Function

Now when I enter the function in a cell of the workbook, the function appears as an option in the dropdown list. I pressed tab to autofill the full name of the UDF, to ensure the function name is correctly typed. I then called the function several times in different cells with different filenames/paths as strings, such as:

=IsExtWorkBookOpen("C:\Computer\Fake\filepath\name\CORRECTLY SPELLED FULL FILENAME.xlsm")
=IsExtWorkBookOpen("C:\Computer\Fake\filepath\name\[CORRECTLY SPELLED FULL FILENAME].xlsm")
=IsExtWorkBookOpen("[CORRECTLY SPELLED FULL FILENAME]")

tried with or without the brackets around the filename. And ofc I've redacted the filepath/name here, its an actual filepath and filename in my code. Note that the filepath in my code directs to a drive that is a network directory, not a local directory in the PC's hardware.

Also tried adding the following as a second parameter: Optional VolatileParameter As Variant and calling , NOW() as the second parameter to see if it has something to do with the UDF needing to be volatile. To my dismay, nothing seems to work, as the resulting value in the cells is always #NAME.

It gets weird though, because I tested my code in the VBA editor by inserting a procedure, calling the UDF in the procedure, placing breakpoints in the UDF and running the procedure. Here's the code for that:

Public Sub Test1()
    Dim x
    x = IsExtWorkBookOpen("EXTERNAL WORKBOOK.xlsm")
    MsgBox (x)
End Sub

The procedure runs perfectly, and returns a FALSE value. Can't notice anything off in the registries during breakpoints. Although I did notice it seems to return FALSE even if the external workbook I am checking is actually open... so thats probably a bigger problem than trying to figure out why the function won't work when entered into a cell.

Even weirder, is the very first time I entered the function into a cell, it did return and display FALSE! but only the very first time, ever since just #NAME.

I tried crawling the web for similar programming bugs and the closest I found was this forum thread. It seems that maybe, as with this user's UDF, mine is not even executing because excel doesn't know how to execute it from the start?

Finally, I noticed the following comment by 'Joe' on the first webpage, from where I copied the code, which stated:

Just want to make sure everyone is aware, the "IsWorkBookOpen" function will only be able to tell if a workbook is open in the current instance of Excel. If you have multiple instances open you would need to run it on each instance to be sure the workbook is (not) open, using this code alone.

Reading this comment, is this code doomed to work for my purpose from the beginning? I still doubt this because the #NAME error happens even when I tried calling the UDF with the correctly spelled filename of the current workbook (not the external one)(the workbook within which the VBA module is) which leads me to believe the bug is elsewhere in the code functionality.

If so though, can anyone give me a hint as to what code is needed to successfully perform my function that checks if another workbook is open in the same local PC/desktop?

EDIT: After further debugging/investigating, I discovered that the reason I can't get a simple function like that @FrankProp recommended to function for my purpose is because the external workbook I am trying to check as active (open) or not is always open in a different instance of excel. Probably because the external workbook is also a Macro-enabled workbook. So when I loop through Application.workbooks it only looks at other workbooks active in the same instance/application as the original workbook, which the workbook I'm checking for will never be in.

Reading through some other SOF threads, it seems that what I am trying to do is possible but is considerably more complicated.

I just tried adapting code from this thread for my purpose, but at first I got a runtime error 438 - VBA object doesn't support this property or method on line 41, Col 16 If xl Is acc.Application Then which I deduce, meant that an excel.application object had not been correctly assigned to the acc object so it couldn't access a non-existing .application property. Somehow the error magically disappeared when I just tried running it again recently. I'll keep updating this post with my progress. Also will change post title to be more relevant.

CodePudding user response：

That UDF works fine for me, though note that Application.Workbooks.Item() wants the workbook name and not the full path, so

=IsExtWorkBookOpen("tempo3.xlsb")

returns TRUE but

=IsExtWorkBookOpen("C:\Temp\tempo3.xlsb")

gives FALSE.

Note you can take a different approach using GetObject if you want to find out if a workbook is open in the same instance or a different instance of Excel (note here you'd pass in the full path):

Function IsExtWorkBookOpen2(Name As String) As Boolean
    Dim xWb As Object
    On Error Resume Next
    Set xWb = GetObject(Name)
    If Not xWb Is Nothing Then Debug.Print xWb.Name
    IsExtWorkBookOpen2 = (Not xWb Is Nothing)
End Function

CodePudding user response：

I suggest you to rewrite that function as follow:

Option Explicit



Function IsWorkbookOpenByName(name As String) As Boolean

Dim wb As Workbook
For Each wb In Application.Workbooks ' Loop through all open workbooks
    If wb.Name = name Then ' If a workbook is found with specified name (name   extension)...
        IsWorkbookOpenByName = True ' ... return true...
        Exit For ' ... and exit loop.
    End If
Next

End Function



Function IsWorkbookOpenByFullName(fullName As String) As Boolean
    
    Dim wb As Workbook
    For Each wb In Application.Workbooks ' Loop through all open workbooks
        If wb.FullName = fullName Then ' If a workbook is found with specified fullName (path   name   extension)...
            IsWorkbookOpenByFullName = True ' ... return true...
            Exit For ' ... and exit loop.
        End If
    Next
    
End Function

The code above is tested and working.

If you want to combine these functions in only one, simply change the If statement into If wb.Name = name or wb.FullName = name in the first function:

Option Explicit



Function IsWorkbookOpen(name As String) As Boolean

Dim wb As Workbook
For Each wb In Application.Workbooks ' Loop through all open workbooks
    If wb.Name = name or wb.FullName = name Then ' If a workbook is found with specified name or fullname...
        IsWorkbookOpen = True ' ... return true...
        Exit For ' ... and exit loop.
    End If
Next

End Function

With this last function you can pass the name or the fullname (name path extension).

Note 1: I highly recommend you using Option Explicit for all your VBA code. This preprocessor command forces the VBA interpreter (remember that VBA is not really compiled) to require all variable declarations. This prevent typos and will save you hours of debugging in search of a missing char in some variable name. Also, it will make your code more reliable and it's a healthy habit.

Note 2: When possible, avoid the On Error Resume Next statement because it can be harmful. That statement "authorizes" the code execution to continue even if errors occurs, so you could get unexpected result from your code and it could be difficult to debug if it's a complex function.

I say this because VBA was my first programming language, and unfortunately no one told me about these aspects. I learned that changing bad habits is more difficult than learning good practices on time :)