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

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?

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

7 个解决方案

#1

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)'
将宏名称括在单引号中，例如传递2个常量：'Button1_Click（“一个字符串！”，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()
如果要传递变量（如单元格的值），请将参数括在Evaluate（）中

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

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

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

'的button1_Click（评估（ “工作表Sheet1！$ A $ 1”））'

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.'.

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

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

如果我的第一篇文章允许，我会包含一张图片。

#2

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

#3

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

#4

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

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

#5

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（“测试测试”）'。单击包含宏的按钮时出错。

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“测试测试”'工作（注意testForXXXX和“Test ...”之间的空间）。

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

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

#6

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

在我意识到显示的所有潜在宏只是我的Personal.xlsb文件中没有参数的那些宏之前，我遇到了同样的问题，分配按钮不是很有用。然后我输入了Personal.xlsb！'macroNameWhichTakesArguments（“arg1”，...）'，然后工作表将其拾起。

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

I.E. Personal.xlsb！'testForXXXX（“测试测试”）'

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

宏testForXXXX将字符串作为输入，然后表单工作正常。

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

当您在excel 2007中为按钮使用assign宏路径时，如果它采用args，则至少testForXXXX不会显示在潜在宏列表中。

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

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

#7

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”'

#1