Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA store functions or subroutines in an array

Tags:

excel

vba

vbide

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!

like image 789
user2961927 Avatar asked Mar 05 '15 05:03

user2961927


People also ask

What is difference between function and subroutine in VBA?

VBA Sub vs Function: Key DifferencesA sub performs a task but does not return a value. A function returns a value of the tasks performed. Subs can be recalled from anywhere in the program and in multiple types.

How do I create a dynamic array in VBA?

Create a Dynamic Array in VBAFirst, declare an array with its name. After that, the elements count left the parentheses empty. Now, use the ReDim statement. In the end, specify the count of elements you want to add to the array.

Can a VBA function return an array?

A function in a normal module (but not a Class module) can return an array by putting () after the data type. Note that what is returned is actually a copy of the array inside the function, not a reference. So if the function returns the contents of a Static array its data can't be changed by the calling procedure.

Can you call a subroutine from a function in VBA?

To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses. Use a Sub procedure to organize other procedures so they are easier to understand and debug.


2 Answers

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
like image 138
RubberDuck Avatar answered Nov 08 '22 07:11

RubberDuck


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"
like image 30
q335r49 Avatar answered Nov 08 '22 06:11

q335r49