Variable in macro name

[ChristineJ]

There are four macros named Macro1, Macro2, Macro3, and Macro4. Any of these may be called within another macro.

I'd like the number in those four macro names to be a variable and dependent on the value in cell A3.

For example, if the value in A3 is 3, Macro3 would be called. If that value in A3 is 1, Macro1 would be called.

Can this be done? Thanks!

 

[Michael M]

Try it this way

Sub MM1()
Select Case Range("A3").Value
Case Is = 1
    Call Macro1
Case Is = 2
    Call Macro2
Case Is = 3
    Call Macro3
Case Is = 4
    Call Macro4
 End Select
End Sub

 

[ChristineJ]

The Select Case is what I am currently using, as you have shown.

However, since ultimately I will have over 100 possible macros to call based on the value in A3, I was hoping to be able to consolidate significantly by making the number in the macro name a variable.

Thanks for your reply.

 

[Jaafar Tribak]

How about this :

Sub Test()

    On Error GoTo errHandler
        Application.Run "Macro" & [a3]
    On Error GoTo 0
    
errHandler:
        If Err.Number = 1004 Then
            MsgBox Err.Description, vbCritical, "Error!"
        End If
    
End Sub

 

[ChristineJ]

Awesome, Jaafar Tribak! It works perfectly and is exactly what I needed. Thanks so much for you help.