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!