Excel VBA UDF在单元格调用时返回#NAME?错误的排查求助

一、#NAME?错误的主要原因 #

• UDF存放位置错误：将函数放在工作表模块或ThisWorkbook模块中，这类模块的函数属于私有范围，单元格直接调用无法识别。必须将UDF放在标准模块（右键VBA编辑器→插入→模块）中。
• 宏未启用或文件未解锁：如果文件来自网络或被标记为“不安全”，需右键文件→属性→勾选“解除锁定”，并在Excel中启用宏（文件→选项→信任中心→宏设置，选择允许宏运行）。
• 调用格式不正确：若UDF在其他工作簿中，需用=工作簿全名.xlsm!IsExtWorkBookOpen("文件名")的格式调用；传入的文件名需与当前实例中打开的文件名完全匹配（不带路径，除非有重名文件）。
• 宏安全设置限制：信任中心设置为“禁用所有宏”或未勾选“信任对VBA工程对象模型的访问”，会阻止UDF执行。

二、实现本地所有Excel实例中检测工作簿是否打开的功能 #

原函数仅能检测当前Excel实例的工作簿，要覆盖所有实例，需借助Windows API枚举所有Excel窗口并验证文件状态。以下是完整代码（需放在标准模块中）：

Option Explicit

' Windows API用于枚举Excel窗口
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" _
    (ByVal hwnd As LongPtr, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long

Private Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
Private Const OBJID_NATIVEOM As Long = &HFFFFFFF0

' 检测目标工作簿是否在任意Excel实例中打开
Function IsWorkbookOpenAnyInstance(filePath As String) As Boolean
    Dim excelApp As Object
    Dim hwnd As LongPtr
    Dim fullFilePath As String
    
    ' 转换为标准完整路径
    fullFilePath = UCase(Application.GetOpenFilename( _
        fileFilter:="Excel Files (*.xlsx;*.xlsm;*.xls)", _
        Title:="Validate Path", FileName:=filePath))
    If fullFilePath = "FALSE" Then Exit Function ' 无效路径直接返回False
    
    ' 遍历所有Excel窗口
    hwnd = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
    Do While hwnd <> 0
        ' 获取窗口对应的Excel实例
        If GetExcelAppFromHWnd(hwnd, excelApp) Then
            On Error Resume Next
            ' 尝试以只读方式打开文件，失败则说明文件已被锁定（即已打开）
            Dim tempWb As Object
            Set tempWb = excelApp.Workbooks.Open(fullFilePath, ReadOnly:=True, Notify:=False)
            If Err.Number <> 0 Then
                IsWorkbookOpenAnyInstance = True
            Else
                tempWb.Close SaveChanges:=False
                IsWorkbookOpenAnyInstance = False
            End If
            On Error GoTo 0
            Exit Do ' 找到结果后终止遍历
        End If
        hwnd = FindWindowEx(0&, hwnd, "XLMAIN", vbNullString)
    Loop
End Function

' 通过窗口句柄获取Excel应用实例
Private Function GetExcelAppFromHWnd(hwnd As LongPtr, ByRef excelApp As Object) As Boolean
    Dim obj As Object
    Dim iid(3) As Long
    Dim hr As Long
    
    ' 初始化IID_IDispatch GUID
    iid(0) = &H20400
    iid(1) = &H0
    iid(2) = &H0
    iid(3) = &HC0000000 Or (&H46 * &H10000)
    
    hr = AccessibleObjectFromWindow(hwnd, OBJID_NATIVEOM, iid(0), obj)
    If hr = 0 Then
        Set excelApp = obj.Application
        GetExcelAppFromHWnd = True
    Else
        GetExcelAppFromHWnd = False
    End If
End Function

使用方法 #

在单元格中直接调用，传入目标文件的完整路径：

=IsWorkbookOpenAnyInstance("C:\Documents\TargetWorkbook.xlsm")

返回TRUE表示文件已在任意Excel实例中打开，FALSE表示未打开。

内容的提问来源于stack exchange，提问作者Agustin Ferreira