如何用VBA自定义函数实现跨工作簿引用及自动更新？

首先明确：Excel单元格内的自定义函数（UDF）无法直接打开外部工作簿——这是Excel的安全限制，UDF仅允许返回计算值，不能执行修改应用状态的操作（比如打开文件）。但可以通过「工作簿打开事件 + 自定义函数」的组合实现需求。

步骤1：用Workbook_Open事件自动打开目标工作簿 #

打开WB01的VBA编辑器（按Alt+F11），找到左侧「ThisWorkbook」模块，写入以下代码：

Private Sub Workbook_Open()
    Dim targetWB As Workbook
    Dim targetPath As String
    
    ' 替换为目标工作簿的完整网络路径
    targetPath = "\\000.000.0.000\Public\DOCS\Hualley\FLUXO CAIXA HINDY - 108.xlsm"
    
    ' 检查目标工作簿是否已打开，避免重复打开
    On Error Resume Next
    Set targetWB = Workbooks("FLUXO CAIXA HINDY - 108.xlsm")
    On Error GoTo 0
    
    If targetWB Is Nothing Then
        ' 以只读模式打开（避免文件锁定，如需修改可去掉ReadOnly:=True）
        Set targetWB = Workbooks.Open(Filename:=targetPath, ReadOnly:=True)
        ' 若不想显示目标工作簿，可添加Visible:=False，但需配合关闭事件处理
        ' Set targetWB = Workbooks.Open(Filename:=targetPath, ReadOnly:=True, Visible:=False)
    End If
    
    ' 强制刷新当前工作簿公式，确保值更新
    ThisWorkbook.RefreshAll
End Sub

步骤2：编写自定义函数读取目标单元格值 #

插入一个标准模块（右键左侧工程窗口 → 插入 → 模块），写入以下自定义函数：

Function GetRemoteCell() As Variant
    Dim targetWB As Workbook
    Dim targetSheet As Worksheet
    
    ' 检查目标工作簿是否已打开
    On Error Resume Next
    Set targetWB = Workbooks("FLUXO CAIXA HINDY - 108.xlsm")
    On Error GoTo 0
    
    If targetWB Is Nothing Then
        GetRemoteCell = "⚠️ 目标工作簿未打开"
        Exit Function
    End If
    
    ' 检查目标工作表是否存在
    Set targetSheet = targetWB.Worksheets("CONTAS MES")
    If targetSheet Is Nothing Then
        GetRemoteCell = "⚠️ 目标工作表不存在"
        Exit Function
    End If
    
    ' 返回目标单元格的值
    GetRemoteCell = targetSheet.Range("BA80").Value
End Function

步骤3：使用自定义函数 #

在WB01的目标单元格中输入：

=GetRemoteCell()

可选：添加关闭工作簿时的清理逻辑 #

如果隐藏打开了目标工作簿，可在「ThisWorkbook」模块中添加关闭事件，自动关闭目标工作簿：

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim targetWB As Workbook
    
    On Error Resume Next
    Set targetWB = Workbooks("FLUXO CAIXA HINDY - 108.xlsm")
    On Error GoTo 0
    
    If Not targetWB Is Nothing Then
        ' 不保存修改直接关闭（因为是只读打开）
        targetWB.Close SaveChanges:=False
    End If
End Sub

注意事项 #

• 确保当前用户有目标共享文件夹的访问权限，否则打开工作簿会失败。
• 如果目标工作簿被其他用户锁定，只读模式仍可正常打开；若需修改，需去掉ReadOnly:=True并确保文件未被占用。

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