Excel VBA store functions or subroutines in an array

Question

In C/C++, when I have a bunch of functions (pointers), I can store them in an array or a vector and call some of them together in a certain order. Can something similar be done in VBA?

Thanks!

Answer 1

Yes, but I don't recommend it. VBA isn't really built for it. You've tagged this question with Excel, so I will describe how it is done for that Office Product. The general concept applies to most of the Office Suite, but each different product has a different syntax for the Application.Run method.

First, it's important to understand the two different methods of dynamically calling a procedure (sub/function) and when to use each.

Application.Run

Application.Run will either run a subroutine or call a function that is stored in a standard *.bas module.

The first parameter is the name of the procedure (passed in as a string). After that, you can pass up to 30 arguments. (If your procedure requires more than that, refactor for the love of code.)

There are two other important things to note about Application.Run.

1. You cannot use named arguments. Args must be passed by position.

2. Objects passed as arguments are converted to values. This means you could experience unexpected issues if you try to run a procedure that requires objects that have default properties as arguments.

   Public Sub Test1()
       Application.Run "VBAProject.Module1.SomeFunction"
   End Sub
   

The takeaway:

Use Application.Run when you're working with a standard module.

VBA.Interaction.CallByName

CallByName executes a method of an object, or sets/gets a property of an object.

It takes in the instance of the object you want to call the method on as an argument, as well as the method name (again as a string).

Public Sub Test2()
    Dim anObj As SomeObject
    Dim result As Boolean

    result = CallByName(anObj, "IsValid")
End Sub

The takeaway:

Use CallByName when you want to call a method of a class.

No pointers.

As you can see, neither of these methods use actual pointers (at least not externally). They take in strings that they then use to find the pointer to the procedure that you want to execute. So, you'll need to know the exact name of the procedure you want to execute. You'll also need to know which method you need to use. CallByName having the extra burden of requiring an instance of the object you want to invoke. Either way, you can stores these names as strings inside of an array or collection. (Heck, even a dictionary could make sense.)

So, you can either hard code these as strings, or attempt to extract the appropriate procedure names at runtime. In order to extract the procedure names, you'll need to interface with the VBIDE itself via the Microsoft Visual Basic for Applications Extensibility library. Explaining all of that here would require far too much code and effort, but I can point you to some good resources.

Articles & SE Questions:

1. Chip Pearson's Programming The VBA Editor
2. Extending the VBA Extensibility Library
3. Ugly workaround to get the vbext_ProcKind is breaking encapsulation
4. Automagic testing framework for VBA
5. How to get the procedure or function name at runtime
6. Import Lines of Code
7. Meta Programming in VBA: The VBIDE and Why Documentation is Important

The code from some of my Qs & As:

1. vbeCodeModule
2. vbeProcedure
3. vbeProcedures

Answer 2

A workaround is to enumerate and use a switch statement. You can store enumerated types (longs) in an array. E.g.:

Enum FType
    func1
    func2
    func3
End Enum

Sub CallEnumFunc(f As FType, arg As String)
    Select Case f
        Case func1: MyFunction1(arg)
        Case func2: MyFunction2(arg)
        Case func3: MyFunction3(arg)
    End Select
End Sub

Dim fArray(1) As FType
fArray(0) = func1
fArray(1) = func2
CallEnumFunc fArray(1), "blah"