excel vba: Special Types - Functions as Arguments

问题:

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.

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.

回答1:

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:

Class Module: 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

Implements IStringFunction

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

Class Module: 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.

Download the simple demo or intermediate demo here

回答2:

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

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:

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

回答3:

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 myFunction containing your function

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

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:

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 sayHi as the default property of the class. Now you can call the function by the class object alone class(args), rather than class.function(args)

Save the amended file and import it into your VBA project

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 IStringFunction to get only the subset with the correct interface as per Gary McGill's answer