Excel VBA：从单元格调用UDF时导致#NAME？错误

0ejtzxu1 于 2023-03-13 发布在其他

关注(0)|答案(2)|浏览(139)

我从this guide中复制了一些UDF函数代码来满足我的需求，据说它已经为其他人工作过了。我并没有真正修改代码，但是为了透明起见，下面是我在工作簿中作为模块使用的确切代码：

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

现在，当我在工作簿的单元格中输入函数时，该函数将作为一个选项出现在下拉列表中。我按Tab键自动填充UDF的全名，以确保函数名称键入正确。然后，我使用不同的文件名/路径作为字符串在不同的单元格中调用该函数几次，例如：

=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]")

我试过在文件名周围加上或不加上括号。ofc我在这里编辑了文件路径/名称，它是我代码中的实际文件路径和文件名。注意，我代码中的文件路径指向的驱动器是网络目录，而不是PC硬件中的本地目录。
还尝试将以下内容添加为第二个参数：Optional VolatileParameter As Variant，并调用NOW（）作为第二个参数，看看它是否与需要成为volatile的UDF有关。令我沮丧的是，似乎什么都不起作用，因为单元格中的结果值总是#NAME。
不过，这有点奇怪，因为我在VBA编辑器中测试了代码，插入了一个过程，在过程中调用UDF，在UDF中放置断点，然后运行该过程。

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

这个过程运行得很好，返回了一个FALSE值。在断点期间没有注意到注册表中有任何异常。虽然我注意到它似乎返回了FALSE，即使我正在检查的外部工作簿实际上是打开的...所以这可能比试图找出为什么函数在输入到单元格时不起作用更大的问题。
更奇怪的是，当我第一次在单元格中输入这个函数时，它确实返回并显示FALSE！但这只是第一次，从#NAME开始。
我试着在网上搜索类似的程序错误，我发现最接近的是this论坛线程。看起来，也许，就像这个用户的UDF一样，我的UDF甚至没有执行，因为excel从一开始就不知道如何执行它？
最后，我注意到下面的评论'乔'在第一个网页，从那里我复制的代码，其中指出：
只是为了确保每个人都知道，“IsWorkBookOpen”函数只能判断工作簿是否在Excel的当前示例中打开。如果您打开了多个示例，则需要在每个示例上运行该函数，以确保工作簿打开（未打开），仅使用此代码。
阅读这篇评论，这段代码从一开始就注定要为我的目的工作吗？我仍然怀疑这一点，因为即使我试图用当前工作簿（不是外部工作簿）（VBA模块所在的工作簿）的正确拼写的文件名调用UDF时，#NAME错误也会发生，这使我相信错误在代码功能的其他地方。
如果是这样的话，谁能给予我一个提示，告诉我需要什么代码才能成功地执行我的功能，检查另一个工作簿是否在同一台本地PC/桌面上打开？

excel

来源：https://stackoverflow.com/questions/73408448/excel-vba-udf-results-in-name-error-when-called-from-cell

2条答案

按热度按时间

luaexgnf1#

这个UDF对我来说很好用，但是注意Application.Workbooks.Item()需要工作簿 name 而不是完整路径，因此

=IsExtWorkBookOpen("tempo3.xlsb")

返回TRUE，但

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

给出FALSE。
注意，如果您想知道工作簿是在同一个Excel示例中打开还是在不同的Excel示例中打开，则可以使用GetObject采取不同的方法（注意，此处您将传入完整路径）：

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

赞(0）回复(0）举报 2023-03-13

zaqlnxep2#

我建议您将函数重写如下：

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

上面的代码已经过测试，可以正常工作。
如果您只想将这些函数合并到一个函数中，只需将第一个函数中的If语句更改为If wb.Name = name or wb.FullName = name：

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

使用最后一个函数，您可以传递名称或全名（名称+路径+扩展名）。

**注1：**我强烈建议您对所有VBA代码使用Option Explicit。此预处理器命令强制VBA解释器（请记住，VBA并没有真正编译）要求所有变量声明。这可以防止输入错误，并将保存您在某些变量名中查找丢失字符的调试时间。此外，它还将使您的代码更可靠，这是一个健康的习惯。
**注2：**尽可能避免使用On Error Resume Next语句，因为它可能有害。该语句“授权”代码在发生错误时继续执行，因此您可能会从代码中获得意外结果，如果它是一个复杂函数，则可能难以调试。

我这么说是因为VBA是我的第一门编程语言，不幸的是没有人告诉我这些方面，我学到了改变坏习惯比按时学习好的实践更难：）

赞(0）回复(0）举报 2023-03-13

我来回答

Excel VBA：从单元格调用UDF时导致#NAME？错误

2条答案

相关问题

热门标签

最新问答