Microsoft Office Forums - View Single Post - [Solved] Can a function or sub be called using a variable name?

macropod · 12-05-2013, 03:24 PM

Quote:

Originally Posted by BobBridges

Is there a way in VBA to put the name of a subroutine into a variable and then call it that way, so to speak "indirectly"?

Sure there is! Try:

Code:

Dim MacroName As String, i As Long
 
Sub Demo()
i = CLng(InputBox("Which macro to run?"))
MacroName = "ThisWorkbook.Macro" & i
Application.Run MacroName
End Sub
 
Private Sub Macro1()
MsgBox "Success! You ran macro: " & i
End Sub
 
Private Sub Macro2()
MsgBox "Success! You ran: " & MacroName
End Sub
 
Private Sub Macro3()
MsgBox "Ho hum ... you ran macro 3"
End Sub

Simply input 1, 2 or 3 into the inputbox.