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:

    myVar = cells(1,1).value
    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?



  • 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

    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:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!