带有变量名的 Excel VBA 调用函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7406523/
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
Excel VBA call function with variable name
提问by BWG
I'm trying to call a function with a variable name that is generated at run time based upon a combo box value. This is straightforward in most languages but I can't seem to figure it out in Excel VBA, I suspect this is because I don't really understand how the compiler works. I've found several posts that are close but don't quite seem to do the trick. The code below is wrong but should give an idea of what I want.
我正在尝试使用在运行时基于组合框值生成的变量名调用函数。这在大多数语言中都很简单，但我似乎无法在 Excel VBA 中弄清楚，我怀疑这是因为我不太了解编译器的工作原理。我发现了几篇很接近但似乎不太合适的帖子。下面的代码是错误的，但应该让我知道我想要什么。
Thanks
谢谢
Sub main()
'run formatting macros for each institution on format button click
Dim fn As String
Dim x As Boolean
'create format function name from CB value
fn = "format_" & CBinst.Value
'run function that returns bool
x = Eval(fn)
...
End Sub
回答by shahkalpesh
CallByNameis what you'll need to accomplish the task.
CallByName是您完成任务所需要的。
example: Code in Sheet1
示例：Sheet1 中的代码
Option Explicit
Public Function Sum(ByVal x As Integer, ByVal y As Integer) As Long
Sum = x + y
End Function
Code is Module1 (bas module)
代码是 Module1（基本模块）
Option Explicit
Sub testSum()
Dim methodToCall As String
methodToCall = "Sum"
MsgBox CallByName(Sheet1, methodToCall, VbMethod, 1, 2)
End Sub
Running the method testSumcalls the method Sumusing the name of the method given in a string variable, passing 2 parameters (1 and 2). The return value of the call to function is returned as output of CallByName.
运行该方法使用字符串变量中给出的方法名称testSum调用该方法Sum，并传递 2 个参数（1 和 2）。函数调用的返回值作为 的输出返回 CallByName。
回答by Adam Law
The above will work but not with a large number of names
以上将起作用，但不适用于大量名称
Use Application.Run(MacroName, Parameters)
使用 Application.Run(MacroName, Parameters)
You have to may sure that there is a macro but it is better than the above as there is no select statement.
您必须确定有一个宏，但它比上面的要好，因为没有选择语句。
回答by Adam Law
With respect to my answer above you might also find this useful to check whether the macro exists
关于我上面的回答，您可能还会发现这对于检查宏是否存在很有用
'=================================================================================
'- CHECK IF A MODULE & SUBROUTINE EXISTS
'- VBA constant : vbext_pk_Proc = All procedures other than property procedures.
'- An error is generated if the Module or Sub() does not exist - so we trap them.
'---------------------------------------------------------------------------------
'- VB Editor : Tools/References - add reference TO ......
'- .... "Microsoft Visual Basic For Applications Extensibility"
'----------------------------------------------------------------------------------
'- Brian Baulsom October 2007
'==================================================================================
Sub MacroExists()
Dim MyModule As Object
Dim MyModuleName As String
Dim MySub As String
Dim MyLine As Long
'---------------------------------------------------------------------------
'- test data
MyModuleName = "TestModule"
MySub = "Number2"
'----------------------------------------------------------------------------
On Error Resume Next
'- MODULE
Set MyModule = ActiveWorkbook.VBProject.vbComponents(MyModuleName).CodeModule
If Err.Number <> 0 Then
MsgBox ("Module : " & MyModuleName & vbCr & "does not exist.")
Exit Sub
End If
'-----------------------------------------------------------------------------
'- SUBROUTINE
'- find first line of subroutine (or error)
MyLine = MyModule.ProcStartLine(MySub, vbext_pk_Proc)
If Err.Number <> 0 Then
MsgBox ("Module exists : " & MyModuleName & vbCr _
& "Sub " & MySub & "( ) : does not exist.")
Else
MsgBox ("Module : " & MyModuleName & vbCr _
& "Subroutine : " & MySub & vbCr _
& "Line Number : " & MyLine)
End If
End Sub
'-----------------------------------------------------------------------------------
回答by Pepe
You should write a function that accepts the CB value as a parameter and then uses a select case to call the appropriate formatting function.
您应该编写一个接受 CB 值作为参数的函数，然后使用 select case 调用适当的格式化函数。
Something similar to this
类似的东西
Function SelectFormatting(Name as String) As Boolean
Select Case CBinst.Value
Case "Text1":
SelectFormatting = Text1FormattingFunction()
Case "Text2":
.
.
.
End Select
End Function
