excel vba: Special Types - Functions as Arguments of Functions

Question

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.

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.

Download the simple demo or intermediate demo here

Answer 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