如何在工作表单元格中传入Workbook类型参数调用自定义VBA函数

问题根源 #

单元格公式只能传递文本、数字等值类型参数，无法直接传递Workbook这类对象类型参数，所以需要修改函数的参数类型，改用可从单元格输入的文本信息（工作簿名称或路径）来定位目标工作簿。

方案1：检查已打开的工作簿中的工作表 #

将第一个参数改为工作簿名称（字符串类型），通过Workbooks集合查找已打开的工作簿，再遍历工作表判断是否存在：

Function isWorkbookWorksheetExisting(workbookName As String, sheetName As String) As Boolean
    Dim targetWb As Workbook
    Dim ws As Worksheet
    
    isWorkbookWorksheetExisting = False
    
    ' 尝试获取已打开的目标工作簿
    On Error Resume Next
    Set targetWb = Workbooks(workbookName)
    On Error GoTo 0
    
    ' 找到工作簿后遍历工作表
    If Not targetWb Is Nothing Then
        For Each ws In targetWb.Worksheets
            ' 统一大小写避免匹配失败
            If UCase(sheetName) = UCase(ws.Name) Then
                isWorkbookWorksheetExisting = True
                Exit For
            End If
        Next ws
    End If
End Function

单元格公式用法 #

如果目标工作簿已打开，直接传入工作簿名称（带后缀或不带，取决于是否保存）：

=isWorkbookWorksheetExisting("Book1.xlsx", "Sheet1")
=isWorkbookWorksheetExisting("Book1", "Sheet1") ' 适用于未保存的工作簿

方案2：检查未打开的工作簿中的工作表 #

如果需要检查未打开的工作簿，将第一个参数改为工作簿的完整路径，函数内部会临时打开工作簿（只读）进行检查，完成后自动关闭：

Function isWorkbookWorksheetExisting(workbookFullPath As String, sheetName As String) As Boolean
    Dim targetWb As Workbook
    Dim ws As Worksheet
    
    isWorkbookWorksheetExisting = False
    
    ' 先检查文件是否存在
    If Dir(workbookFullPath) = "" Then Exit Function
    
    ' 只读打开工作簿，不更新外部链接
    On Error Resume Next
    Set targetWb = Workbooks.Open( _
        FileName:=workbookFullPath, _
        ReadOnly:=True, _
        UpdateLinks:=xlUpdateLinksNever _
    )
    On Error GoTo 0
    
    If Not targetWb Is Nothing Then
        For Each ws In targetWb.Worksheets
            If UCase(sheetName) = UCase(ws.Name) Then
                isWorkbookWorksheetExisting = True
                Exit For
            End If
        Next ws
        ' 关闭工作簿，不保存任何修改
        targetWb.Close SaveChanges:=False
    End If
End Function

单元格公式用法 #

传入完整的工作簿路径和工作表名称：

=isWorkbookWorksheetExisting("C:\Documents\财务报表.xlsx", "2024年3月")

额外说明 #

• 原函数可以保留在VBA模块中，供其他VBA代码内部调用（因为VBA代码里可以直接传递Workbook对象）；
• 加入UCase()是为了避免工作表名称大小写不匹配导致的判断错误，如果你需要严格区分大小写，可以去掉这个处理；
• 错误处理语句（On Error Resume Next）是为了防止工作簿不存在或无法打开时函数报错崩溃。

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