VBA自定义函数打开Excel时ActiveWorkbook指向错误问题解决

问题场景 #

需要通过VBA实现以下逻辑：读取主文件内存储的目标Excel文件名、打开对应文件、提取目标文件指定单元格的值，回写到主文件的指定单元格。
具体场景说明：

• 主文件为GetValues.xlsm，单元格内存储目标文件名Test1.xlsx
• 预期逻辑：打开Test1.xlsx，读取其A1单元格的值，写入GetValues.xlsm的对应单元格
  原调试代码如下：

Function Getvalue(myFile)
'Dim myPath As String
'Dim myExtension As String
myPath = Application.ActiveWorkbook.Path & "\"
myFile = myPath & myFile
Workbooks.Open myFile
Debug.Print "myFile: "; myFile
Debug.Print "ActiveWorkbook: "; ActiveWorkbook.Name
'Val = ActiveWorkbook.Worksheets(1).Range("A1").Value
GetValue = 1
End Function

单元格调用公式：

=GetValue(A2)

调试时立即窗口输出如下，可见执行打开操作后，ActiveWorkbook仍指向GetValues.xlsm，未切换到新打开的Test1.xlsx：

myFile: G:\Teaching-CAL\MAE343-CompressibleFlow\04_Codes\LVL\Test1.xlsx
ActiveWorkbook: GetValues.xlsm

问题原因 #

1. 核心限制：当自定义函数（UDF）被单元格公式触发执行时，Excel处于工作表计算锁定状态，该模式下禁止切换活动工作簿、活动工作表等界面元素，因此Workbooks.Open执行后不会自动将新文件设为活动工作簿，依赖ActiveWorkbook获取对象的逻辑必然失效。
2. 原代码存在隐性错误：
   • 函数名定义为Getvalue（小写v），返回值却赋值给GetValue（大写V），易引发变量未定义的赋值失效问题
   • 取主文件路径时使用ActiveWorkbook，若执行时代码所在工作簿不是活动状态，路径拼接会出错
   • 调用公式引用A2单元格，但描述中文件名存储在A1，属于单元格引用错误

修复方案 #

核心原则：所有工作簿、工作表操作都不要依赖ActiveWorkbook/ActiveSheet这类全局激活属性，Workbooks.Open方法本身会返回打开的工作簿对象，直接赋值给变量调用即可，完全不需要切换激活状态。

可用自定义函数（可直接作为单元格公式使用） #

' 统一函数名，明确返回值类型
Function GetValue(myFile As String) As Variant
    Dim myPath As String
    Dim targetWb As Workbook
    Dim sourceSht As Worksheet
    
    ' 用ThisWorkbook获取代码所在主文件的路径，不受激活状态影响
    myPath = ThisWorkbook.Path & "\"
    myFile = myPath & myFile
    
    ' 先检查目标文件是否存在，避免报错
    If Dir(myFile) = "" Then
        GetValue = "目标文件不存在"
        Exit Function
    End If
    
    ' 打开文件时直接接收返回的工作簿对象，完全不需要依赖ActiveWorkbook
    Set targetWb = Workbooks.Open(Filename:=myFile, ReadOnly:=True)
    Set sourceSht = targetWb.Sheets(1)
    ' 读取目标单元格值
    GetValue = sourceSht.Range("A1").Value
    
    ' 取值完成后关闭目标文件，不保存更改，避免残留打开窗口
    targetWb.Close SaveChanges:=False
    
    ' 释放对象内存
    Set sourceSht = Nothing
    Set targetWb = Nothing
End Function

调用方式：在主文件对应单元格输入公式=GetValue(A1)（括号内为存储目标文件名的单元格引用，根据实际位置调整）即可。

更稳定的宏过程方案（不受UDF计算模式限制） #

如果不需要在单元格实时用公式触发，推荐用普通Sub过程执行，兼容性更好，支持批量处理多行文件名：

Sub BatchExtractValues()
    Dim lastRow As Long, i As Long
    Dim myPath As String, myFile As String
    Dim targetWb As Workbook
    Dim mainSht As Worksheet
    
    Set mainSht = ThisWorkbook.Sheets(1) ' 修改为实际存储数据的工作表
    myPath = ThisWorkbook.Path & "\"
    ' 获取A列最后一行数据行号
    lastRow = mainSht.Cells(mainSht.Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To lastRow
        myFile = myPath & mainSht.Cells(i, "A").Value
        If Dir(myFile) <> "" Then
            Set targetWb = Workbooks.Open(Filename:=myFile, ReadOnly:=True)
            ' 读取目标文件A1值写入B列对应行
            mainSht.Cells(i, "B").Value = targetWb.Sheets(1).Range("A1").Value
            targetWb.Close SaveChanges:=False
        Else
            mainSht.Cells(i, "B").Value = "目标文件不存在"
        End If
    Next i
    
    Set targetWb = Nothing
    Set mainSht = Nothing
End Sub

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