calculated subroutine names

Re: calculated subroutine names

Ancient versions of Basic were interpreted - VBA, while not exactly compiled to a standalone executable, is compiled.

Compiling code means the target of calls (or at least the offset from a known point in memory) must be fixed at the time of compiling - so the idea of what is called Macro Substitution in other 'languages' does not really exist.

However, VBA does have the CallByName method. This can call a function contained in a Class (Worksheet class module, Workbook Class module or a user created class...

Add a procedure to the class module for sheet1

Sub TestIt1()
   
   MsgBox "Testit1 in sheet1"
   
End Sub


Sub TestIt2()
   
   MsgBox "Testit2 in sheet1"
   
End Sub

Add a module to contain the controlling procedure and copy

Sub RunIt()




   If ActiveCell.Value <> "" Then
      CallByName ActiveCell.Parent, ActiveCell.Text, VbMethod
   End If
   
End Sub

Add 'TestIt1" & 'TestIt2' (no quotes) to any 2 cells. Select 1 of them and step through (cursor in the procedure and press F8) the procedure RunIt

You can probably also use a simple 'Application.Run' but this seems, to me at least, to fail occasionally.

Code in a module:

Sub TestRun()




   x = "TestItRun"
   Application.Run (x)




End Sub




Sub testItRun()
   
   MsgBox "TestIt"




End Sub