如何阻止Excel将跨工作簿宏函数公式链接转为绝对路径？

我之前处理过几乎一模一样的场景，Excel这种自动把跨工作簿宏引用转成绝对路径的行为确实烦人，给你几个经过验证的解决办法：

方法一：启动时动态注入公式 #

直接在foo.xlsm里写死=bar.xlsm!functionName(...)的话，Excel只要检测到工作簿关闭再重新打开，就会自动补全绝对路径。我们可以换个思路：每次启动foo.xlsm时，先确保bar.xlsm已经加载，再动态生成公式引用当前打开的bar.xlsm。

步骤如下：

1. 打开foo.xlsm，按Alt+F11打开VBA编辑器
2. 找到ThisWorkbook模块，粘贴以下代码（根据你的实际路径检查逻辑和单元格范围调整）：

Private Sub Workbook_Open()
    Dim wC As Workbook
    ' 先检查bar.xlsm是否已经打开
    On Error Resume Next
    Set wC = Workbooks("bar.xlsm")
    On Error GoTo 0
    
    ' 如果没打开，尝试加载（替换成你的多路径检查逻辑）
    If wC Is Nothing Then
        ' 这里可以加循环遍历多个可能的路径，找到存在的bar.xlsm
        Set wC = Workbooks.Open("path\bar.xlsm", ReadOnly:=True, Editable:=False, AddToMru:=False)
    End If
    
    ' 假设需要用公式的单元格是Sheet1的A1:A10，根据实际情况修改
    With ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
        .Formula = "='" & wC.Name & "'!functionName(parameter)"
    End With
End Sub

3. 保存并重启Excel测试

原理：动态生成的公式引用的是当前已打开的bar.xlsm的文件名（不带路径），Excel会识别为当前活跃工作簿的引用，不会自动补全绝对路径。

方法二：将bar.xlsm转为Excel加载项（推荐） #

如果bar.xlsm里的宏是通用工具类的代码，把它转为Excel加载项是最彻底的解决方案——加载项的宏会进入Excel的全局命名空间，不需要在公式里指定工作簿名称。

步骤如下：

1. 打开bar.xlsm，点击「文件」>「另存为」，保存类型选择「Excel加载项（*.xlam）」，可以保存到Excel默认加载项路径（比如C:\Users\[你的用户名]\AppData\Roaming\Microsoft\AddIns）
2. 打开Excel，点击「文件」>「选项」>「加载项」，在「管理」下拉框选择「Excel加载项」，点击「转到」，勾选你刚保存的bar.xlam
3. 现在在foo.xlsm的单元格里直接写=functionName(parameter)即可，不需要带bar.xlsm!前缀
4. 如果bar.xlam的路径会变化，可以在foo.xlsm的ThisWorkbook模块加启动检查代码：

Private Sub Workbook_Open()
    Dim addIn As AddIn
    Dim barAddInPath As String
    ' 替换成你的bar.xlam的路径（或多路径检查逻辑）
    barAddInPath = "path\bar.xlam"
    
    On Error Resume Next
    Set addIn = Application.AddIns("bar") ' 这里的"bar"是加载项的名称（保存时的文件名）
    On Error GoTo 0
    
    ' 如果加载项未注册，先注册；如果已注册但未加载，启用它
    If addIn Is Nothing Then
        Set addIn = Application.AddIns.Add(barAddInPath)
    End If
    If Not addIn.Installed Then
        addIn.Installed = True
    End If
End Sub

原理：Excel加载项的宏是全局可用的，公式直接调用函数名即可，完全避免了工作簿路径的问题。

方法三：用定义名称封装宏引用 #

如果不想修改太多现有公式，可以用Excel的「定义名称」作为中间层，把对bar.xlsm的引用封装起来，每次启动时更新这个名称的指向。

步骤如下：

1. 打开foo.xlsm，点击「公式」>「定义名称」，名称设为MyFunction，引用位置填写=bar.xlsm!functionName，点击确定
2. 把原来的公式=bar.xlsm!functionName(parameter)改成=MyFunction(parameter)
3. 在foo.xlsm的ThisWorkbook模块添加启动时更新名称的代码：

Private Sub Workbook_Open()
    Dim wC As Workbook
    ' 先确保bar.xlsm已加载
    On Error Resume Next
    Set wC = Workbooks("bar.xlsm")
    On Error GoTo 0
    
    If wC Is Nothing Then
        Set wC = Workbooks.Open("path\bar.xlsm", ReadOnly:=True, Editable:=False, AddToMru:=False)
    End If
    
    ' 更新定义名称的指向
    ThisWorkbook.Names("MyFunction").RefersTo = "='" & wC.Name & "'!functionName"
End Sub

原理：定义名称相当于一个别名，单元格公式只引用别名，每次启动时更新别名指向当前打开的bar.xlsm的函数，从而避免路径变化的影响。

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