Excel VBA 中的动态函数调用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19845944/
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
Dynamic Function Calls in Excel VBA
提问by sbanders
I was just curious if there might be a way to call functions dynamically. For example.
我只是好奇是否有一种方法可以动态调用函数。例如。
Sub foo1()
Debug.Print "in foo1"
End Sub
Sub foo2()
Debug.Print "in foo2"
End Sub
Is there a way that I can do something like:
有没有办法让我可以做这样的事情：
Sub callSomeFoo(i as Integer)
Call foo&i
End Sub
Or is something like this necessary:
或者这样的事情是必要的：
Sub callSomeFoo(i as Integer)
Select Case i
Case 1
Call foo1
Case Else
Call foo2
End Select
End Sub
Not a pressing matter... just curious. Any other creative things to do function call wise are welcome as well.
不是紧迫的问题……只是好奇。也欢迎任何其他创造性的函数调用明智的事情。
Thanks!
谢谢！
edit1: Here's the code I have and the error listed below:
edit1：这是我的代码和下面列出的错误：
Sub foo1()
Debug.Print "in foo1"
End Sub
Sub foo2()
Debug.Print "in foo2"
End Sub
Sub callSomeFoo()
Dim i%
'using a cell on the worksheet to determine the function. Happens to be "1"
i = Sheets("Sheet1").Range("A1").Value
'Line below works
Call foo1
'Line below gives me an error
Application.Run "foo"&i
End Sub
Error is:
错误是：
Run-time error '1004' Cannot run the macro 'foo1'. The macro may not be available in this workbook or all macros may be disabled.
运行时错误“1004”无法运行宏“foo1”。宏在此工作簿中可能不可用，或者可能禁用了所有宏。
回答by AndASM
You want the run method!
你想要运行方法！
Sub callSomeFoo(i as Integer)
Application.Run "foo" & i
End Sub
But that wont work, VBA doesn't like the name foo1and so it won't work.
但这行不通，VBA 不喜欢这个名字foo1，所以它行不通。
It's because FOO1 could also be a cell reference. The first arg of Application.Run can be a Range object, so it evaluates FOO1, thinks it's a cell, and since that cell is empty, doesn't know what to do. – Dick Kusleika
这是因为 FOO1 也可以是单元格引用。Application.Run 的第一个 arg 可以是 Range 对象，因此它评估 FOO1，认为它是一个单元格，并且由于该单元格为空，因此不知道该怎么做。— 迪克·库斯雷卡
This can easily be solved by choosing a longer nicer method name.
这可以通过选择更长更好的方法名称来轻松解决。
Tested working example
测试工作示例
Option Explicit
Public Sub TestDynamic1()
Debug.Print "TestDynamic 1"
End Sub
Sub TestDynamic2()
Debug.Print "TestDynamic 2"
End Sub
Private Sub TestDynamic3()
Debug.Print "TestDynamic 3"
End Sub
Sub callTestDynamic(i As Integer)
On Error GoTo DynamicCallError
Application.Run "TestDynamic" & i
Exit Sub
DynamicCallError:
Debug.Print "Failed dynamic call: " & Err.Description
End Sub
Public Sub TestMe()
callTestDynamic 1
callTestDynamic 2
callTestDynamic 3
callTestDynamic 4
End Sub
