Call Function Using a Variable as its name AND pass variables to the function

[lkpederson]

I have a subroutine with an string array of names. I'd like to use those as the names of functions to call i.e. loop through a string array until you get a match then execute the function. Additionally, the functions need to pass a string array to be able to do their work.

Note: One of Fnc(x) will have commonality with one or more of the arr1. The left 3-6? characters will be the same.

Pseudocode:
Sub GoDoStuff()

Dim Fnc(2) As String, arr1() As String, Result() As String

' array of names for functions
Fnc(0) = "A"
Fnc(1) = "B"
Fnc(2) = "C"

do stuff and create an array, arr1, by searching through some data.

' call the function by Fcn(x)

If arr1 Like Fnc(i) Then
Application.Run Fnc(i), arr1(), Result() <-- tried this
Application.Run "Fnc(i), arr1(), Result() <-- and this
Application.Run (Fnc(i), arr1(), Results()) <-- and this

Ideas? I can post the actual code as well but may be adequate. Perhaps it's not possible.

I know I can achieve what I want by writing "If Thens" and calling each function but wanted to see if there was a cleaner way.

[Paul_Hossler]

I don't think .Run will return results, but you can pass input to a .Run subroutine




Option Explicit

Sub drv()
Dim aFunc(1 To 3) As String
Dim a As Variant
aFunc(1) = "Hello1"
aFunc(2) = "Hello2"
aFunc(3) = "Hello3"
Application.Run aFunc(1)
Application.Run aFunc(2), "asdfasdfasdf"
Application.Run aFunc(2), Array(11, 22, 33, 44)
Application.Run aFunc(3), "asdfasdfasdf", "qqqwwweeerrrttt"
Application.Run aFunc(3), Array(11, 22, 33, 44), Array(55, 66, 77, 88)
End Sub


Sub Hello1()
MsgBox "Hello 1"
End Sub


Sub Hello2(v As Variant)
Dim i As Long
If IsArray(v) Then
For i = LBound(v) To UBound(v)
MsgBox "Hello 2 " & v(i)
Next I
Else
MsgBox "Hello 2 --- " & v
End If
End Sub


Sub Hello3(v As Variant, v1 As Variant)
Dim i As Long
If IsArray(v) Then
For i = LBound(v) To UBound(v)
MsgBox "Hello 3 --- " & v(i) * v1(i)
Next I
Else
MsgBox "Hello 3 --- " & v & " --- " & v1
End If
End Sub

[Jay Freedman]

You may be able to do what you want by using the CallByName function: https://msdn.microsoft.com/en-us/library/office/gg278760(v=office.14).aspx

[SamT]

Sub Call_my_routine(FunctionName As String)

Select Case FunctionName
Case "A": A
Case "B": B
Case "C": C
End Select
End Sub


Sub A()
End Sub


Sub B()
End Sub


Sub C()
End Sub

[Paul_Hossler]

You may be able to do what you want by using the CallByName function: https://msdn.microsoft.com/en-us/library/office/gg278760(v=office.14).aspx

CallByName "Executes a method of an object, or sets or returns a property of an object (https://msdn.microsoft.com/en-us/library/office/gg264568(v=office.14).aspx)" so it won't work in the "regular ol'" function' case