Invoke VBA functions and procedures from strings

First let me start with explaining what does it mean to invoke VBA functions and procedures from strings. Then I will follow with an example. Lastly I will conclude this topic showing how you can add arguments.

Invoke VBA functions and procedures by string name

Normally you would invoke a VBA function or procedure like this:

Sub ShowMessage(msg as String)
   MsgBox msg
End Sub

Sub Main
   Call ShowMessage("Hello!")
End Sub

In the above example you are invoking the ShowMessage procedure directly from VBA. This is the usual and efficient approach. Let’s now say that you have 2 alternative VBA procedures you would like to call depending on a certain argument you receive like this:

Sub ShowMessageAlert
   MsgBox "Alert: Watch out!"
End Sub

Sub ShowMessageWarning
   MsgBox "Warning: Look around your back."
End Sub
You may want to you invoke both methods in different circumstances e.g

Sub Main(msgType as String)
   if msgType = "Alert" then ShowMessageAlert
   if msgType = "Warning" then ShowMessageWarning
End Sub

I know the examples above are too simple, but what if you have now much more of these similar methods which differ only in name e.g. suffix, prefix? Well you can invoke them by their name (using a string with the proc., func. name) like this:

Sub Main(msgType as String)
   Application.Run "ShowMessage" & msgType
End Sub

Neater right? Alternatively you could have achieved the same using multiple if statements or a select statement making the code much harder to read.

Invoke VBA functions and procedures with arguments

See the example below for invoking VBA functions and procedures with arguments:

Sub ShowMessageAlert(msg as String)
   MsgBox "Alert: " & msg
End Sub

Sub ShowMessageWarning(msg as String)
   MsgBox "Warning: " & msg
End Sub

Sub Main(msgType as String, msg as String)
   Application.Run "ShowMessage" & msgType , msg
End Sub

Invoke VBA functions and procedures from VBscript

Now this is where invoking VBA really comes in handy. Imagine you want to run an Excel macro without having to open the Excel workbook and run the macro manually. It might be more useful to create therefore a VBscript which would do this for you (file extension “vbs”). The code below will open (invisible in the background) the “Book1.xlsm” Excel workbook which is located in the same working directory as the VBscript file and execute macro “MyMacro”. Then it will close the workbook and quit.

dim xl, xlBook, path
path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
Set xl = CreateObject("Excel.application")
Set xlBook = xl.Workbooks.Open(path & "Book1.xlsm", 0, True)      
xl.Application.Visible = False
xl.DisplayAlerts = False     
xl.Application.run "Book1.xlsm!Module.MyMacro"
xl.ActiveWindow.close
Set xlBook = Nothing
xl.Quit 
Set xl = Nothing

Hope you find invoking VBA from strings useful!

Related posts: