在.xlam与.xlsm文件间传递公共变量的问题求助

通过功能区的.xlam文件（file1）运行查询功能时，流程为：file1打开远程宏文件file2，file2对远程数据库file3执行查询并生成字符串变量Confirm。原用MsgBox显示Confirm但会暂停代码，导致其他用户无法写入file3；改用file1中的无模态用户窗体替代后，Confirm变量无法从file2传递到file1的用户窗体标签。

核心问题是file2与file1的Confirm变量分属不同作用域，无法直接共享，以下是可靠的解决步骤：

1. 修改File2（Macros.xlsm）的代码 #

将原ViewSUB子过程改为函数，让它直接返回生成的Confirm字符串：

Public Function ViewSUB() As String
    Dim Confirm As String
    Dim i As Long, LastrowDBase As Long
    Dim DBase As Worksheet
    
    ' 补充获取数据库工作表和最后行号的逻辑（根据实际情况调整）
    Set DBase = ThisWorkbook.Worksheets("数据库表名")
    LastrowDBase = DBase.Cells(DBase.Rows.Count, 2).End(xlUp).Row
    
    For i = 3 To LastrowDBase
        If DBase.Cells(i, 2) = SUBPN And DBase.Cells(i, 5) = "SomeText" Then
            Confirm = Confirm & "  - " & DBase.Cells(i, 3) & " - Flag: " & DBase.Cells(i, 7) & " - " & DBase.Cells(i, 6) & vbCrLf
        End If
    Next i
    
    ' 移除原有的模态MsgBox
    ' MsgBox Confirm
    
    ViewSUB = Confirm ' 返回生成的结果字符串
End Function

2. 修改File1（.xlam）的代码 #

调用file2的函数时接收返回值，赋值给自身的Confirm变量：

Public Confirm As String

Sub ViewSUB(control As IRibbonControl)
    Dim Sub_Macros As String, strFileExistsA As String, strFileExistsB As String
    Dim currentWorkbook As Workbook
    
    strFileExistsA = Dir(Sub_Macros_L)
    strFileExistsB = Dir(Sub_Macros_R)
    If strFileExistsA <> "" Then
        Sub_Macros = Sub_Macros_L
    ElseIf strFileExistsB <> "" Then
        Sub_Macros = Sub_Macros_R
    Else
        MsgBox "Macro File is missing", vbCritical, "Macro File Missing"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Set currentWorkbook = Application.ActiveWorkbook
    
    Workbooks.Open Sub_Macros, ReadOnly:=True
    currentWorkbook.Activate
    
    ' 接收file2函数返回的结果
    Confirm = Application.Run("'Macros.xlsm'!ViewSUB")
    
    ViewSub_Details.Show vbModeless
    
    Workbooks("Macros.xlsm").Close SaveChanges:=False
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

3. 调整File1的用户窗体代码 #

确保窗体初始化时正确引用file1的公共Confirm变量（若模块名为Module1，需对应修改）：

Option Explicit

Private Sub UserForm_Initialize()
    ' 明确指向xlam模块的公共变量
    Label1.Caption = Module1.Confirm
End Sub

Private Sub CommandButton01_Click()
    Unload ViewSub_Details
End Sub

补充说明 #

若不想修改file2的过程类型，也可在file2中直接赋值给file1的公共变量（需确保xlam已加载，且模块名正确）：
在file2的循环结束后添加：

Application.AddIns("你的xlam文件名").Module.Confirm = Confirm

但函数返回值的方式更稳定，避免因加载状态或模块名变化导致的错误。

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