VBA 通过 .onAction 传递参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25097756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Pass arguments with .onAction
提问by justry
this is how my sub looks like:
这是我的潜艇的样子：
Sub InsertRowWithContent(rowNo As Long)
This is my .onAction:
这是我的 .onAction：
.OnAction = "'InsertRowWithContent""" & C & """'"
C is a Longvariable declared earlier.
C 是之前声明的Long变量。
It says macro not found. It worked fine before adding an argument!
它说找不到宏。在添加参数之前它工作正常！
回答by peter_the_oak
I have sucessfully passed arguments with this syntax:
我已经成功地使用以下语法传递了参数：
.OnAction = "=InsertRowWithContent(" & C & ")"
Considerations:
注意事项：
- C is a long. So don't add quotas, just as you wouldn't if you would call the Sub in the code.
- OnAction evaluates an expression. Therefore according to my experience, the expression needs an equal sign, and as far as I now, it must be a function. Only automated local callbacks are subs.
- C是长。所以不要添加配额，就像在代码中调用 Sub 一样。
- OnAction 计算表达式。所以根据我的经验，表达式需要一个等号，就我现在而言，它必须是一个函数。只有自动本地回调是 subs。
EDIT
编辑
My answer above has Access as background. Djikay's answer works fine with Excel. Yet after some digging, I am quiet sure that simply Word doesn't understand either of those syntaxes. Word VBA cannot pass a parameter to a sub in an OnActionstatement. At least for the moment it's best to accept this.
我上面的回答以 Access 为背景。Djikay 的答案适用于 Excel。然而，经过一番挖掘，我确信 Word 无法理解这两种语法中的任何一种。Word VBA 无法将参数传递给OnAction语句中的子项。至少目前最好接受这一点。
But here is what definitively runs with Word (2010):
但这里是使用 Word (2010) 明确运行的内容：
Create your command bar and the button. For the OnAction, only pass the name of the Sub. But use the button's Parameterproperty.
创建命令栏和按钮。对于OnAction，只传递 的名称Sub。但是使用按钮的Parameter属性。
' Add bar
Set cdb = Application.CommandBars.Add("SomeTest", , False, False)
cdb.Visible = True
' Add button
Set cbb = cdb.Controls.Add
cbb.Caption = "PressMe"
cbb.OnAction = "TheCallback"
cbb.Parameter = 456
Then, access the parameter by the CommandBars.ActionControl.Parameterexpression:
然后，通过CommandBars.ActionControl.Parameter表达式访问参数：
Public Sub TheCallback()
MsgBox "The parameter passed is: " & CommandBars.ActionControl.Parameter
End Sub
ActionControlis very similar (if not the same) as ActiveControlunder Access: it is the control that was clicked last.
ActionControl与ActiveControlAccess 下的非常相似（如果不相同）：它是最后一次单击的控件。
Source: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_24982922.html
来源：http: //www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_24982922.html
*phuu* :-)
*噗* :-)
回答by djikay
You need to add a space after your Sub name, like this:
您需要在您的子名称后添加一个空格，如下所示：
.OnAction = "'InsertRowWithContent " & C & "'"
^^^^^^^^^^
EDIT
编辑
Also, since you're passing a Long parameter, you shouldn't enclose it in quotes.
此外，由于您传递的是 Long 参数，因此不应将其括在引号中。
EDIT 2
编辑 2
OK, this will require some special sauce (code). I've been experimenting and I got the following to work.
好的，这将需要一些特殊的酱汁（代码）。我一直在试验，我得到了以下工作。
In the Sheet1 Excel object:
在 Sheet1 Excel 对象中：
Option Explicit
Sub DestroyToolbar()
Application.CommandBars("DoomBar").Delete
End Sub
Sub MakeToolbar()
Dim C As Long
C = 100
With Application
.CommandBars.Add(Name:="DoomBar").Visible = True
With .CommandBars("DoomBar")
.Controls.Add Type:=msoControlButton, ID:=2950, Before:=1
With .Controls(1)
.OnAction = "'PressMe " & C & "'"
End With
End With
End With
End Sub
In a new standard code module, add this:
在新的标准代码模块中，添加以下内容：
Public Sub PressMe(C As Long)
With Application.CommandBars("DoomBar")
With .Controls(1)
MsgBox "The value of C that was passed to this macro is: " & C
End With
End With
End Sub
If you run MakeToolbar, it will create a new toolbar in the "Add-ins" ribbon. To remove it, you can run DestroyToolbar.
如果运行MakeToolbar，它将在“加载项”功能区中创建一个新工具栏。要删除它，您可以运行DestroyToolbar.
Once the toolbar is in place, then clicking the button should display a messagebox with the value of C(in my example, 100).
工具栏就位后，单击该按钮应显示值为C（在我的示例中为 100）的消息框。
I've tested the above in Excel 2010 and it worked, in a brand new .xlsm file.
我已经在 Excel 2010 中测试了上述内容，并且它在一个全新的 .xlsm 文件中工作。
