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.

Solution 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

Solution 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

Solution 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

Comments

[Treb]

That does not really help him, because he still has to write a seperate evaluation function for each class.

[Gary McGill]

@Treb: maybe I don't understand the question, but I don't see what you mean. Each class represents a different function, so of course it has to have a separate evaluation function! Maybe if the problem could be re-stated it would be clearer.

[Roman Glass]

Hi, I think this is the right answer. This is the way to do it in oo-languages, I just didn't know, that interfaces exist in VBA -- my fault. I remember seeing this technic the 1st time in a little java. But I have to test the proposed solution. At the moment I got an error message about not implementing the interface. Public Function apply(ByVal tmp As Variant) As Boolean End Function --- Implements IBooleanFunction Public Function apply(ByVal tmp As Variant) As Boolean apply = Application.IsText(tmp) End Function

[Gary McGill]

Roman, you need to prefix the function name with the name of the interface it implements, so if the interface is called IFoo and the method is called Bar, then in your implementation class the method must be called IFoo_Bar.

[epeleg]

this is nice but I don't think that it is actually the same as passing a function as far as scopes go...

[william_grisaitis]

that is dope! i'll take it, with or without ideal scoping.

[J S]

In Excel VBA 2010, this syntax gives a compilation error "Expected: End of statement". The parameters need parentheses around them: f = Application.Run(g,x)

[David Leal]

@J S, I guess this solution works when g is a function, but not when it is a subroutine. I was trying with a subroutine and I get a compilation error. Do you know how to implement it for calling a subroutine?

[Greedo]

Note, you can avoid all the importing/exporting by adding Rubberduck's '@DefaultMember attribute above the function signature. And for pure/static style functions you can use the '@PredeclaredId attribute and pass the class' default instance around to avoid newing it up each time. Also see this post about first class functions in vba

[Greedo]

A better implementation of this: codereview.stackexchange.com/q/138557/146810, and another approach using real function pointers: codeproject.com/Articles/204509/Functors-in-VBA