vba 用字符串调用 Sub
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15969796/
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
Call a Sub with a String
提问by CallMeMunchy
I want to call a different sub depending on the value of i.
我想根据i.
For example, if i = 1call sale_call1and if i = 2call sale_call2.
例如，如果i = 1调用sale_call1和如果i = 2调用sale_call2。
Private Sub test_Click()
Dim i As String
Dim pro As String
i = Me.tb1.Value
pro = "sale_call" + i
If i = "1" Then
Call pro
Else
Call pro
End If
End Sub
Sub sale_call1()
MsgBox "Hello"
End Sub
Sub sale_call2()
MsgBox "goodbye"
End Sub
回答by Siddharth Rout
Try this
尝试这个
Replace Call prowith Application.Run pro
替换Call pro为Application.Run pro
Example
例子
Private Sub test_Click()
Dim i As String
Dim pro As String
i = 1
pro = "sale_call" + i
'~~> This will run sale_call1
Application.Run pro
i = 2
pro = "sale_call" + i
'~~> This will run sale_call2
Application.Run pro
End Sub
Sub sale_call1()
MsgBox "Hello"
End Sub
Sub sale_call2()
MsgBox "goodbye"
End Sub
FOLLOWUP
跟进
If your code is not in a module but in a Userform or Sheet Code area then Application.Runwill not work till the time sale_call1or sale_call2is not placed in a module. If you do not wish to move them to a module then you will have to use CallByName. Check Excel's inbuilt help on this function. Here is an example which assumes that the code is in Userform1
如果您的代码不在模块中，而是在用户表单或工作表代码区域中，那么在此之前Application.Run将无法工作sale_call1或未sale_call2放置在模块中。如果您不想将它们移动到模块中，则必须使用CallByName. 检查 Excel 的有关此功能的内置帮助。这是一个假设代码在Userform1
Private Sub CommandButton1_Click()
Dim i As String
Dim pro As String
i = 1
pro = "sale_call" + i
'~~> This will run sale_call1
CallByName UserForm1, pro, VbMethod
i = 2
pro = "sale_call" + i
'~~> This will run sale_call2
CallByName UserForm1, pro, VbMethod
End Sub
Sub sale_call1()
MsgBox "Hello"
End Sub
Sub sale_call2()
MsgBox "goodbye"
End Sub
回答by Maiqui
Just add as prefix the workbook name where the macro is hosted. Like when doing a formula in a cell:
只需添加托管宏的工作簿名称作为前缀。就像在单元格中执行公式时一样：
Application.Run "WorkbookNameAsString.app_ext!MacroName"
回答by Ankush Brar
VBA uses '&' for concatenation;
VBA 使用“&”进行连接；
Incorrect:
不正确：
pro = "sale_call" + i
Corrected:
更正：
pro = "sale_call" & i
I hope it helps.
我希望它有帮助。
