具有变量名称的Excel VBA调用函数 [英] Excel VBA call function with variable name

问题描述

我正在尝试使用根据组合框值在运行时生成的变量名称调用函数。这在大多数语言中是直截了当的，但我似乎无法在Excel VBA中得出结论，我怀疑这是因为我不太了解编译器的工作原理。我已经找到了几个很贴近的帖子，但似乎并没有这样做。下面的代码是错误的，但应该提供我想要的想法。

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.

谢谢

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

推荐答案

CallByName 是完成任务所需要的

CallByName is what you'll need to accomplish the task.

示例：
Sheet1中的代码

example: Code in Sheet1

Option Explicit
Public Function Sum(ByVal x As Integer, ByVal y As Integer) As Long
    Sum = x + y
End Function

代码是Module1（bas module）

Code is Module1 (bas module)

Option Explicit

Sub testSum()
Dim methodToCall As String
methodToCall = "Sum"

MsgBox CallByName(Sheet1, methodToCall, VbMethod, 1, 2)
End Sub

运行方法 testSum 使用给定方法的名称调用方法 Sum 在一个字符串变量中，传递2个参数（1和2）。调用函数的返回值作为 CallByName 的输出返回。

Running the method testSum calls the method Sum using 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.

这篇关于具有变量名称的Excel VBA调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！