excel vba: functions as arguments of functions

Question

There is no special type for functions, therefore it is hard for me to see, how to add functions as arguments to functions in excel vba.

What I try to accomplish is something like this:

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

Concerning the motivation: I have a masse of little functions all repeating itself but one call to a specific function.

Thanks for taking the time to answer this question!

Answer 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.

Answer 2

In Mr. McGill's code, I replaced the word 'evaluate' with 'gibberjabber' in the code module and the class modules because 'evaluate' is already a method in VB and it was confusing me. And it still works. I thought maybe in a few places the word pointed to the new definition and in a few other places it was pointing to the built-in method. I thought wrong. I'm still trying to grok how this is useful. I suspect it's a major help but it hasn't dawned on me yet. That's a plea for help, by the way!