invoke vba

Running Excel VBA from VBscript

Today’s post is going to be very niche. But as I am often bored lately and playing around as a Data scientist in the Kaggle contests I occasionally run into these strange places where I need to make some peace of ultra-efficient code and needing to automate as many things possible.

Run VBA from VBscript

So I ran into the issue of having to run some Excel macros automatically from the Windows command line (cmd). I had some Excel file which was much easier to process in VBA than in R (this reminds me to have to learn Python soon). But then again I develop my machine learning models in R and not Excel. So I needed a way to execute Excel macros from R… to do some of the dirty work. How to do that? Fortunately R has the Shell command which invokes the Windows shell (cmd).

VBscripts are executable text files with the vbs file extension. They are supported natively by Windows and can be executed without MS Office. VBscript is almost identical to VBA hence most VBA scripts can be exported to simple VBscript files.

Want to learn how to make an App without Excel using VBscript? Read this post on HTA

The only thing left was to write a VBscript which would allow you to run an Excel macro (run VBA from VBscript). Here is an example:

'This runs the macro below

'The RunMacro procedure
Sub RunMacro() 
  Dim xl
  Dim xlBook      
  Dim sCurPath
  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open(path & "\Workbook.xlsm", 0, True)      
  xl.Application.Visible = False
  xl.DisplayAlerts = False "Workbook.xlsm!Module.RunMacro"
  Set xlBook = Nothing
  Set xl = Nothing
End Sub 

Cool right? I was thinking what use cases can be out there for using this code. These are a few I came up with:

  • Scheduled tasksCreate VBscript to run on system start-up or other similar events. This can be useful if e.g. you are an analyst which needs to refresh data on a daily basis. Running the Excel macro tasks automatically will save you a lot of time.
  • Running Excel macros from other applications – this is basically the use case I quoted above. However, keep in mind that running shell (cmd) Windows commands if possible virtually from any platform e.g. Java, C#, ASP.NET etc.
  • Parallelism – Excel does not allow you to utilize more than 1 thread when running Excel macros in a workbook. Ok. But what if I split my workbooks and run them from VBscript? Running all these workbooks in separate processes from VBscript will indeed be an elegant approach. Much cleaner than opening quadrillion workbooks… Although please do not do this. Excel is not meant to accomplish such tasks – use R or Python or even C#

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.