Call multiple macros using array and function in excel vba

Question

I cannot call macros whose names I saved in a String Array.

I am attaching a 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 c sub Mac_Sched.

Answer 1

Try using 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

Answer 2

TBBH, I really don’t know what you are trying to do, and the heavily edited code shows nothing to show specific methods other than trying to call a macro from a string derived from an array element.

The Application.OnTime method uses the 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

, , .

---

_{. Select Excel VBA macros , .}