excel - 使用字符串调用 Sub

Question

我想根据 i 的值调用不同的 sub .

例如，如果 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

Answer 1

尝试这个

替换 Call pro与 Application.Run pro
例子

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

跟进

如果您的代码不在模块中，而是在用户表单或工作表代码区域中，则 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