Calling a subroutine based on file name or any other criteria
I want to call a subroutine based on the file name of the open books. I have book A called "SubModel_OtherAdmin" where I execute subroutines and book B where VBA logic (subroutines, functions, etc.) is stored. I am creating a subroutine in Book B called "Assumptions_OtherAdmin". Instead of using:
Call Assumptions_OtherAdmin
I would like to name this particular subroutine based on the filename in book A. Big picture, book A could be other books like "SubModel_Marketing" or "SubModel_Revenue" etc. Each of these will have its own "Guess" subroutine name built and stored in Book B.
My initial thought was to define a String and then use it Call String
, but this resulted in an error. My second thought was to build a table / grid on a tab in Book B that stores the subroutines that associate the possible filenames of the files I will be working with with the corresponding subroutine name Assumptions. 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 have to create a function to call a subroutine by finding the filename in column B and using the name in column C on the same row. I am falling back to the same error as when usingCall String
as mentioned above. Another way I am looking is to use Chance, but I am stuck in the planning process. Any thoughts or suggestions would be appreciated.
source to share
The keyword Call
is a legacy / deprecated way of calling a procedure.
It:
Call Assumptions_OtherAdmin
Equally:
Assumptions_OtherAdmin
There's really no reason to use it Call
, it's only there because [many] older versions of the language required it, and it still exists for backward compatibility reasons.
You have several options for calling a procedure by name, especially in excel-vba... How this will work depends on where your procedure is located.
Standard module
If the code is a Public
procedure in a standard code unit, because the Excel object Application
has a method Run
specified as an object Workbook
and a procedure name String
, 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 you have the full name of the workbook, you can tweak this code and make it work without reference to the workbook in question.
Class module
If the procedure you need to call is a member of an object that is in a class module, you will need to work a little more complexly and pass an instance of the object that provides the member you want to call. The advantage is that it works regardless of whether your host application supports Application.Run
, because you will be using a standard library function VBA.Interaction.CallByName
:
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 Private
is out of luck.
source to share