Calling subroutine based on a file name or any other criteria

Question

I'm looking to call a subroutine based on the file name of the open workbooks. I have workbook A named "SubModel_OtherAdmin" where I am executing the subroutines and workbook B where I have the VBA logic(subroutines, functions, etc.) stored. I create a subroutine in workbook B called "Assumptions_OtherAdmin". Instead of using:

Call Assumptions_OtherAdmin

I would like to Call that particular subroutine based on the file name in workbook A. Big picture, Workbook A can be other workbooks such as "SubModel_Marketing" or "SubModel_Revenue", etc. Each of these will have their own Assumptions subroutine name built and stored in workbook B.

My initial thought was to define a String then use Call String but that resulted in an error. My second thought was to build a table/grid in a tab in workbook B where the subroutines are stored that links the possible file names I will be working with to its respective Assumptions Subroutine name. For example, in column B row2 I would put the file name "SubModel_OtherAdmin" and in column C row2 I would put "Assumptions_OtherAdmin" and so on. I'm trying to brainstorm if I should create a function to Call the subroutine by finding the file name in column B and using the name in column C on the same row. I'm finding myself back to the same error as using Call String as mentioned above. Another avenue I'm looking at is using select Case but I'm stuck in my planning process. Any thoughts or suggestions will be appreciated.

Answer 1

The Call keyword is just a deprecated/obsolete way of calling a procedure.

This:

Call Assumptions_OtherAdmin

Is identical to this:

Assumptions_OtherAdmin

There's really no reason to use Call, it's there only because [much] older versions of the language required it, and it's still there for backward compatibility reasons.

---

You have several options for calling a procedure by name, especially in excel-vba. How it's going to work depends on where your procedure is located.

---

Standard Module

If the code is a Public procedure in a standard code module, because Excel's Application object has a Run method, given a Workbook object and a String procedure name, you can do this:

Public Sub CallMacroByName(ByVal book As Workbook, ByVal procedure As String)
    Dim qualifiedName As String

    If InStr(1, procedure, "'!") = 0 Then
        qualifiedName = "'" & book.FullName & "'!" & procedure
    Else
        qualifiedName = procedure 'assume procedure is qualified already
    End If

    Application.Run qualifiedName

End Sub

You would do it like this:

Public Sub DoSomething()

    Dim book As Workbook
    Set book = ActiveWorkbook

    CallMacroByName  book, "MyProcedure"

End Sub

If all you have is the workbook's full name, you can tweak this code and make it work without a reference to the book in question.

---

Class Module

If the procedure you need to call is an object member located in a class module, you'll need to work a little bit harder and pass an instance of the object that exposes the member you want to call. The advantage is that it works whether or not your host application supports Application.Run, because you'll be using the VBA.Interaction.CallByName standard library function:

Public Sub CallObjectMemberByName(ByRef instance As Object, ByVal procedure As String)

    'assumes procedure isn't a property accessor:
    CallByName instance, procedure, VbMethod

End Sub

You would do it like this:

Public Sub DoSomething(ByRef owner As Class1)

    'assumes owner instance is provided by something else
    CallObjectMemberByName owner, "MyProcedure"

End Sub

---

If the procedure you want to call is Private, you're out of luck.