calculated subroutine names

  • Hi All,


    To avoid using the worlds longest select case statement is there a way of using a variable name to call a subroutine?


    For example:


    Code
    1. myVar = cells(1,1).value
    2. call myVar()


    I was able to use calculated gosub / goto statements (e.g. gosub age * 20) in ancient versions of BASIC on micros years ago and wondered if there was anything similar for subroutines in VBA?


    Thanks,


    J

  • 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


    Add a module to contain the controlling procedure and copy

    Code
    1. Sub RunIt()
    2. If ActiveCell.Value <> "" Then
    3. CallByName ActiveCell.Parent, ActiveCell.Text, VbMethod
    4. End If
    5. 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: