excel vba：特殊类型 - 作为函数参数的函数

excel vba: Special Types - Functions as Arguments of Functions

提问by Roman Glass

There is no special type for functions in VBA. It is hard for me to see how to add functions as arguments to functions in Excel VBA.

VBA 中的函数没有特殊类型。我很难看到如何将函数作为参数添加到 Excel VBA 中的函数。

What I am trying to accomplish is something like this:

我想要完成的是这样的：

function f(g as function, x as string) as string
        f = g(x)
end function

Currently, I have a group of little functions all repeating themselves but with one call to a specific function.

目前，我有一组小函数，它们都在重复自己，但只调用一个特定的函数。

采纳答案by Gary McGill

From your code, function g takes a string parameter and returns a string. I suggest you create a class module called IStringFunction to act as the definition of an interface that all functions will support, thus:

从您的代码中，函数 g 接受一个字符串参数并返回一个字符串。我建议您创建一个名为 IStringFunction 的类模块来充当所有函数都支持的接口的定义，因此：

Class Module: IStringFunction

类模块：IStringFunction

Public Function Evaluate(ByVal s As String) As String
End Function

Then, create a couple of example functions implementing this interface:

然后，创建几个实现此接口的示例函数：

Class Module: HelloStringFunction

类模块：HelloStringFunction

Implements IStringFunction

Public Function IStringFunction_Evaluate(ByVal s As String) As String
    IStringFunction_Evaluate = "hello " & s
End Function

Class Module: GoodbyeStringFunction

类模块：GoodbyeStringFunction

Implements IStringFunction

Public Function IStringFunction_Evaluate(ByVal s As String) As String
    IStringFunction_Evaluate = "goodbye " & s
End Function

...and finally, some test code to exercise the functions:

...最后，一些测试代码来练习这些功能：

(Standard) Module: Test

（标准）模块：测试

Sub Test()

    Dim oHello As New HelloStringFunction
    Dim oGoodbye As New GoodbyeStringFunction

    MsgBox Evaluate(oHello, "gary")
    MsgBox Evaluate(oGoodbye, "gary")

End Sub

Private Function Evaluate(ByVal f As IStringFunction, ByVal arg As String) As String
    Evaluate = f.Evaluate(arg)
End Function

Note that the class implementing the interface must have methods named <Interface>_<Method>as in the example above, not just <Method>as you'd expect.

请注意，实现接口的类必须具有<Interface>_<Method>如上例中命名的方法，而不仅仅是<Method>您期望的那样。

Download the simple demoor intermediate demohere

在此处下载简单演示或中级演示

回答by david

Since VBA has it's roots in an interactive language, it has always had the ability to execute text:

由于 VBA 源于交互式语言，因此它始终具有执行文本的能力：

function f(g as string, x as string) as string
        f = application.run(g,x)
end function

MyStringA = f("functionA",string1)
MyStringB = f("functionB",string1)

Edit to Add: I think that in current versions of Excel, the application (Excel) can 'run' only things you can show in a spreadsheet cell. So that means functions, not subroutines. To execute a subroutine, wrap it up in a function wrapper:

编辑添加：我认为在当前版本的 Excel 中，应用程序 (Excel) 只能“运行”您可以在电子表格单元格中显示的内容。所以这意味着函数，而不是子程序。要执行子例程，请将其包装在函数包装器中：

function functionA(x as string)
    Call MySubA(x)
end function

回答by Greedo

It is possible to pass a function as an argument, and get exactly the behaviour you're after, but it's a bit hacky. This is achieved by exploiting the default property of a class,

可以将函数作为参数传递，并准确地获得您所追求的行为，但这有点笨拙。这是通过利用类的默认属性来实现的，

Create a class myFunctioncontaining your function

创建一个myFunction包含您的函数的类

Public Function sayHi(ByVal s As String) As String
    sayHi = "Hi " & s
End Function

Export it, and open the .cls file in a text editor

导出它，并在文本编辑器中打开 .cls 文件

You should see somewhere in the file, a line that defines your function

您应该在文件中的某处看到定义您的函数的行

Public Function sayHi(ByVal s As String) As String

Beneath it, add the line Attribute Value.VB_UserMemId = 0, so that now it looks like this:

在它下面，添加 line Attribute Value.VB_UserMemId = 0，现在它看起来像这样：

Public Function sayHi(ByVal s As String) As String
Attribute Value.VB_UserMemId = 0
    sayHi = "Hi " & s
End Function

What you've done here is to mark sayHias the default propertyof the class. Now you can call the function by the class object alone class(args), rather than class.function(args)

您在这里所做的是标记sayHi为类的默认属性。现在您可以单独通过类对象调用该函数class(args)，而不是class.function(args)

Save the amended file and import it into your VBA project

保存修改后的文件并将其导入到您的 VBA 项目中

Test module

测试模块

Sub test()
    Dim parameterFunction As Object         'this is what we'll be passing to our routine
    Set parameterFunction = New myFunction  'create instance of the function object
    MsgBox f(parameterFunction, "Greedo")
End Sub

Function f(ByVal g As Object, ByVal x As String) As String
        f = g(x)
End Function

*NB, this way you can pass any function; but you may instead specify ByVal g As IStringFunctionto get only the subset with the correct interface as per Gary McGill's answer

*注意，这样你可以传递任何函数；但您可以改为ByVal g As IStringFunction根据Gary McGill 的回答指定仅获取具有正确接口的子集