Call multiple macro using array and function in excel vba

Question

I am unable to call macros whose names I have stored in a String Array.

I attach the code.

Option Explicit
Option Compare Text

Dim i, Ro As Integer

Public Sub Universal_Macro()

Dim Col(10) As Integer
Dim macro_name(10) As String

Ro = ActiveCell.Row

i = 1

For i = 1 To 10
    Call Mac_Sched(Col(), macro_name())
Next

End Sub

Sub Mac_Sched(Col() As Integer, Internal_Array() As String)
    Cells(Ro, Col(i)).Select
    Call Internal_Array(i)
End Sub

Getting error in the sub Mac_Sched.

getting error here Call Internal_Array(i) it says expected sub or function or property — siddharth taunk, Dec 15 '15 at 18:04
Can you please eloberate I am very novice with vba or excel vba — siddharth taunk, Dec 15 '15 at 18:10
@findwindow - `i` will work in this instance since it's Dimensioned outside the scope of any particular procedure. — Scott Holtzman, Dec 15 '15 at 18:11
Did you look up what Tim said? Don't make me regret my upvote XD — findwindow, Dec 15 '15 at 18:23
okey this is how we do it Application.Run (Internal_Array(i)) in the called sub and it worked — siddharth taunk, Dec 15 '15 at 18:30

score 2 · Answer 1 · answered Dec 15 '15 at 19:05

Try to use Application.Run:

Sub RunMc()

 Dim a(1 To 2) As String
 Dim MN As String

 For i = 1 To 2 'Fill the array
  a(i) = "m" & i
 Next
 MN = "Module1" 'the module name

 For i = LBound(a) To UBound(a)
  Application.Run MN & "." & a(i)
 Next

End Sub

Sub m1()
 Debug.Print "m1"
End Sub

Sub m2()
 Debug.Print "m2"
End Sub

score 0 · Answer 2 · edited May 23 '17 at 12:15

TBBH, I really don't know what you are trying to accomplish and the heavily redacted code does nothing to show specific methods other than the attempt at calling a macro from a string derived from an array element.

The Application.OnTime method uses a string as the name of the procedure to call.

Option Explicit
Option Compare Text
Dim i As Integer, Ro As Integer

Sub zero()
    Debug.Print "zero: " & i
End Sub

Sub first()
    Debug.Print "first: " & i
End Sub

Sub second()
    Debug.Print "second: " & i
End Sub

Sub third()
    Debug.Print "third: " & i
End Sub

Public Sub Universal_Macro()
    Dim Col() As Integer
    Dim macro_Name As Variant


    macro_Name = Array("zero", "first", "second", "third")
    ReDim Col(UBound(macro_Name))
    For i = LBound(macro_Name) To UBound(macro_Name)
        Col(i) = i
    Next

    Ro = ActiveCell.Row

    For i = LBound(macro_Name) To UBound(macro_Name)
        Call macro_Sched(Col(i), macro_Name)
    Next

End Sub

Sub macro_Sched(c As Integer, internal_Array As Variant)
    Cells(Ro, c + 1).Select '<~~Don't rely on Select! You dont' even reference a worksheet here.
    Application.OnTime Now, internal_Array(c)
End Sub

If parameters were to be passed to the children sub procedures, then some sort of string replacement might accommodate this but specifics on the child subs are not evident.

_{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Call multiple macro using array and function in excel vba

2 Answers2