Excel VBA：为按钮.OnAction传递多变量时宏执行报错

问题描述 #

给Excel工作表按钮的.OnAction传递三个变量时触发报错：

Cannot Run the Macro "Deck v.1'!'Stop_SAP "10.x.x.x", sidadm, " ". The macro may not be available in this workbook or all macros has been disabled.

不传递变量时（.OnAction = "Stop_SAP"）宏可正常运行，当前代码如下：

With btn1
    .TextFrame.Characters.Font.Size = 8
    .TextFrame.Characters.Caption = "Stop SAP"
    .OnAction = "'Stop_SAP """ & hstname & """ ,""" & usrname & """,""" & pass & """'"
    .Line.visible = False
End With

解决方案 #

问题出在.OnAction的字符串格式错误，尤其是工作簿名称含空格时的引号嵌套逻辑。修正后的代码如下：

With btn1
    .TextFrame.Characters.Font.Size = 8
    .TextFrame.Characters.Caption = "Stop SAP"
    ' 修正引号嵌套：外层单引号包裹完整调用，参数双引号用两个双引号转义
    .OnAction = "'" & ThisWorkbook.Name & "'!Stop_SAP """ & hstname & """, """ & usrname & """, """ & pass & """"
    .Line.Visible = False
End With

关键说明 #

• 显式指定工作簿名称：因为你的工作簿名称包含空格（"Deck v.1"），必须用ThisWorkbook.Name限定宏所在工作簿，避免路径识别错误
• 正确转义参数引号：所有字符串参数的双引号需要用两个双引号转义，确保VBA能正确解析参数内容
• 外层单引号包裹：整个宏调用字符串用单引号包裹，避免工作簿名称中的空格干扰宏定位

额外验证项 #

• Stop_SAP必须是**公共（Public）**子程序，参数数量、类型需与传递变量匹配，示例结构：

Public Sub Stop_SAP(hstname As String, usrname As String, pass As String)
    ' 宏业务逻辑代码
End Sub

• 确保工作簿宏已启用，未被安全设置禁用

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