Excel VBA: UDF results in #NAME? error when called from cell-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?

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