Is it possible to use a string to dynamically call subs

March 3, 2022

I tried something like:

sub tester()
   'do something
End Sub

activesheet.cells(1,1) = 'tester'

sub foo()
     dim x as string
     x = activesheet.cells(1,1).value
     Call x

End Sub

The intent would be to manipulate a cell so that as the cell value changed, the sub called would also change.

>Solution :

Using Application.Run

Here’s an example with error handling to make it worth a while.

Option Explicit

Sub Test1()
    MsgBox "First"
End Sub

Sub Test2()
    MsgBox "Second"
End Sub

Sub Foo()
    On Error GoTo ClearError
    
    Dim S As String: S = CStr(Range("A1").Value)
    Application.Run S

ProcExit:
    Exit Sub
ClearError:
    MsgBox "Run-time error '" & Err.Number & ":" & vbLf & Err.Description
    Resume ProcExit
End Sub