Yes, but I do not recommend. VBA is not really built for this. You noted this question in Excel, so I will describe how this is done for this Office product. The general concept applies to most Office Suite, but every other product has a different syntax for the Application.Run method.
First, it is important to understand two different methods for dynamically calling a procedure (sub / function) and when to use them.
Application.Run
Application.Run either execute the subroutine or call a function that is stored in the standard *.bas module.
The first parameter is the name of the procedure (passed as a string). After that, you can pass up to 30 arguments. (If your procedure requires more than refactoring for code love.)
Application.Run has two other important things.
- You cannot use named arguments. Args should be positioned.
Objects passed as arguments are converted to values. This means that you may encounter unforeseen problems if you try to start a procedure that requires objects, the default properties as arguments.
Public Sub Test1() Application.Run "VBAProject.Module1.SomeFunction" End Sub
Stakeout:
Use Application.Run when you work with the standard module.
VBA.Interaction.CallByName
CallByName executes an object method or sets / gets an object property.
In the instance of the object you want to call the method, it is used as an argument, as well as the name of the method (again as a string).
Public Sub Test2() Dim anObj As SomeObject Dim result As Boolean result = CallByName(anObj, "IsValid") End Sub
Stakeout:
Use CallByName if you want to call a class method.
No pointers.
As you can see, none of these methods use actual pointers (at least not externally). They take the lines that they then use to find a pointer to the procedure that you want to execute. So, you need to know the exact name that you want to fulfill. You also need to know which method you need to use. CallByName with an extra burden requiring an instance of the object you want to call. In any case, you can save these names as strings inside an array or collection. (Heck, even a dictionary might make sense.)
Thus, you can either hardcode them as strings, or try to extract the appropriate procedure names at run time. To extract the names of the procedures, you will need to interact with VBIDE itself using the Microsoft Visual Basic Extensibility Application Library. Explaining all of this here will require too much code and effort, but I can point you to some good resources.
SE articles and questions:
Code from some of my Qs and As: