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).

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
RunMacro

'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     
  xl.Application.run "Workbook.xlsm!Module.RunMacro"
  xl.ActiveWindow.close
  xl.Quit
  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 tasks – Create 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#