vba 如何从按钮调用宏并传递参数

How to call a macro from a button and pass arguments

提问by user3470049

I want to add a button to my excel worksheet which should call a macro that can handle one agument (an integer value). Sadly when creating the button, I cannot link any macro that has arguments. Also just typing the macro and the argument does not work.

我想在我的 excel 工作表中添加一个按钮，该按钮应该调用一个可以处理一个参数（整数值）的宏。遗憾的是，在创建按钮时，我无法链接任何具有参数的宏。也只是输入宏，参数不起作用。

Is there any simple solution to pass an argument to a macro when a button is pressed?

是否有任何简单的解决方案可以在按下按钮时将参数传递给宏？

回答by QA Collective

Yes, you can assign a macro to a button (or other excel controls/menu actions) and pass constant OR variable arguments to it.

是的，您可以将宏分配给按钮（或其他 excel 控件/菜单操作）并将常量 OR 变量参数传递给它。

In the 'Assign Macro' window (right-click on object and select 'Assign Macro'):

在“分配宏”窗口中（右键单击对象并选择“分配宏”）：

• Enclose the macro name in single quotes e.g. to pass 2 constants: 'Button1_Click("A string!", 7)'
• Select 'This Workbook' for the 'Macros in' field
• If you wish to pass a variable (like the value of a cell), enclose the parameter in Evaluate()

• 将宏名称括在单引号中，例如传递 2 个常量： 'Button1_Click("A string!", 7)'
• 为“宏位于”字段选择“此工作簿”
• 如果您希望传递变量（如单元格的值），请将参数括在 Evaluate() 中

For example, to pass the value of Sheet1!$A$1to a button function, you would have the following text in the 'Macro name:' field:

例如，要将 的值传递Sheet1!$A$1给按钮函数，您可以在“宏名称：”字段中输入以下文本：

Button1_Click(Evaluate("Sheet1!$A"))

If you don't enclose your variable argument with an 'Evaluate' function, excel returns the error 'Formula is too complex to be assigned to an object.'.

如果您不使用“评估”函数将变量参数括起来，excel 将返回错误“公式太复杂，无法分配给对象。”。

I would have included an image if this were allowed on my first post.

如果在我的第一篇文章中允许这样做，我会包含一张图片。

回答by user3819867

Sub ert()
Call ert2(Cells(1,1).Value)
End Sub

回答by 314UnreadEmails

Use an activeX control command button and in the button click method, call the sub and pass the argument:

使用 activeX 控件命令按钮并在按钮单击方法中，调用 sub 并传递参数：

Private Sub CommandButton_Click()

  Dim x as Integer
  x = 1
  Call SomeSub(x)

End Sub

回答by AntiqueWhale

Suppose you have a public sub take 1 argument like below (just for explanation purposes.)

假设您有一个公共 sub take 1 参数，如下所示（仅用于解释目的。）

And you insert a button on Worksheet like below, and you can not find the macro name when you want to assign your sub to this button.

并且您在工作表上插入一个按钮，如下所示，当您想将子分配给此按钮时，您找不到宏名称。

Now, you can type in your sub name + space + argument manually in single quotes, like below, click ok.

现在，您可以在单引号中手动输入子名称 + 空格 + 参数，如下所示，单击确定。

Then you see, problem solved.

然后你看，问题解决了。

回答by Tim Williams

Called from a regular "forms" button on a worksheet you can do something like this:

从工作表上的常规“表单”按钮调用，您可以执行以下操作：

Sub TestMe()
    Dim c, arr
    c = Application.Caller
    arr = Split(c, "_")
    If UBound(arr) > 0 Then MsgBox "Got " & arr(1)
End Sub

Where the calling button is named (eg) "Button_3"

调用按钮的名称（例如）“Button_3”

Or (simpler) right-click the button and enter 'TestMe2 5'(Including the single-quotes)

或者（更简单）右键单击按钮并输入'TestMe2 5'（包括单引号）

Sub TestMe2(i)
    MsgBox "Got " & i
End Sub

See also: Excel 2010 - Error: Cannot run the macro SelectCell using .onAction

另请参阅：Excel 2010 - 错误：无法使用 .onAction 运行宏 SelectCell

回答by AjV Jsy

To call this Sub from a button :

要从按钮调用此 Sub：

Public Sub TestButton(strMessage As String)
    MsgBox strMessage
End Sub

... be aware that the Sub won't be listed in the available macros, because it has a parameter. Just type in the call in single quotes : 'TestButton "Hello"'

...请注意 Sub 不会在可用的宏中列出，因为它有一个参数。只需在单引号中输入调用：'TestButton "Hello"'

回答by Michael

I had trouble with my version of Personal.xlsb!'testForXXXX("Test Test")'. I got an error when clicking the button containing the Macro.

我的 Personal.xlsb 版本有问题！'testForXXXX("Test Test")'。单击包含宏的按钮时出现错误。

However, I was able to fix it by removing the "(" and ")". So, Personal.xlsb!'testForXXXX "Test Test"' worked (notice the space between testForXXXX and "Test...").

但是，我能够通过删除“(”和“)”来修复它。因此， Personal.xlsb!'testForXXXX "Test Test"' 起作用了（注意 testForXXXX 和“Test...”之间的空格）。

In fact, I didn't need Personal.xlsb! and was just able to use 'testForXXXX "Test Test"'.

事实上，我不需要 Personal.xlsb！并且只能使用'testForXXXX“测试测试”'。

回答by ffskitty

I hit the same issue with the assign buttonnot being very useful until I realised that all the potential macrosdisplayed were only the ones in my Personal.xlsbfile that took no arguments. Then I typed Personal.xlsb!'macroNameWhichTakesArguments("arg1", ...)'and the sheet picked it up.

我遇到了同样的问题，因为它assign button不是很有用，直到我意识到显示的所有潜力macros只是我的Personal.xlsb文件中没有参数的那些。然后我打字Personal.xlsb!'macroNameWhichTakesArguments("arg1", ...)'，工作表把它捡起来。

I.E. Personal.xlsb!'testForXXXX("Test Test")'

IE Personal.xlsb!'testForXXXX("Test Test")'

Where the macro testForXXXXtake a string as input, then the sheet worked ok.

如果将macro testForXXXX字符串作为输入，则工作表可以正常工作。

When you use the assign macro route for a button in excel 2007at least testForXXXXwill not show up in the list of potential macros if it takes args.

当您使用分配宏路由时，如果需要 args button in excel 2007，则至少testForXXXX不会出现在潜在宏列表中。

I suspect Aaron C got tripped up by this too. Maybe its best not to use a Personal.xlsbfile?

我怀疑 Aaron C 也被这个绊倒了。也许最好不要使用Personal.xlsb文件？

回答by Pierre Massé

I would have liked to comment the answer by QA Collective but I do not have enough reputation point yet. Previous solutions did not work for me, as my macros are in named modules in my VBA project.

我本来想评论 QA Collective 的答案，但我还没有足够的声望点。以前的解决方案对我不起作用，因为我的宏位于 VBA 项目的命名模块中。

With Excel 2013, the way to pass a parameter to a macro using a button that worked in my context is :

使用 Excel 2013，使用在我的上下文中工作的按钮将参数传递给宏的方法是：

'<module name>.<macro name> <parameter>'

e.g

例如

'DataProcessor.disle "myText"'

or

或者

'DataProcessor.editWSProcess True'

(boolean do not need double quotes when passed as parameters) Please note that single quotes are around the whole expression.

(boolean 作为参数传递时不需要双引号) 请注意单引号是整个表达式。