VBA跨工作簿调用工作表模块Public函数返回空串问题求解

问题场景 #

跨工作簿调用其他文件内的VBA过程时，工作表模块内的Sub子过程可通过Application.Run正常触发执行，但同模块下声明为Public的Function函数，调用后始终返回零长度空字符串，无法拿到预期返回值。

测试代码 #

Book1.xlsm文件Sheet1工作表模块内原有代码：

Sub sMyMsg()
    MsgBox "hey sub"
End Sub

Public Function fMyMsg() As String
    fMyMsg = "hey func"
End Function

同目录下Book2.xlsm标准模块内的调用代码：

Sub Test_Sub() '可正常执行
    Application.Run "Book1.xlsm!Sheet1.sMyMsg"
End Sub

Sub Test_Func() '执行异常，返回零长度空字符串
    Dim s As String
    s = "test"
    s = Application.Run("Book1.xlsm!Sheet1.fMyMsg")
    MsgBox s
End Sub

根本原因 #

VBA里的工作表模块、ThisWorkbook模块都属于类模块的特殊衍生类型，Application.Run方法对类模块成员的调用存在机制限制：

• 调用Sub过程时仅需要触发执行逻辑，不需要接收返回值，因此可以正常运行
• 调用Function过程时，按工作簿名!类模块名.函数名的字符串格式传参，无法正确绑定类实例的函数返回入口，最终只会返回对应类型的默认空值，不会执行函数内的返回赋值逻辑

修复方案 #

可根据实际编码需求二选一：

• 方案1（推荐）：将跨工作簿调用的函数迁移到标准模块
  把需要对外调用的fMyMsg函数从Sheet1工作表模块，移动到Book1.xlsm内的任意标准模块中，调用时直接写Application.Run("Book1.xlsm!fMyMsg")即可正常拿到返回值。这是VBA跨工程调用的通用规范，所有需要跨文件、跨模块对外暴露的通用过程、函数，都建议放在标准模块中，不要放在工作表、ThisWorkbook这类特殊类模块里，避免出现各类调用异常。
• 方案2：保留函数在工作表模块时，通过工作表对象直接调用
  如果业务逻辑要求函数必须留在Sheet1模块内，不要用字符串拼接宏路径的方式调用，先获取目标工作簿内的工作表实例，通过实例直接调用函数即可：

  Sub Test_Func_Fixed()
      Dim s As String
      Dim targetSheet As Object
      ' 先确认Book1.xlsm已打开，再绑定对应工作表对象
      Set targetSheet = Workbooks("Book1.xlsm").Worksheets("Sheet1")
      ' 通过对象实例直接调用函数
      s = targetSheet.fMyMsg()
      MsgBox s
  End Sub
  

  该方案需要提前确保被调用的Book1.xlsm处于打开状态，否则需要额外补充工作簿打开的逻辑。

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