Dingrui(Frank) Zhang

A Simple Way to Call VBA Macro and Pass Arguments From R and Python

Frank / 2019-06-12

Sometimes you may encounter huge legacy VBA codes and you are so redundant to re-develop them in other languages (ie. R and Python). However, you really want to add running VBA codes in your workflow. Now there is a simple solution for R and Python. (Tested on Windows OS)

Sample VBA code

We have following macro.

Public Sub Test_Add(Arg1, Arg2)
Sheets(1).Range("a1").Value = Arg1 + Arg2
End Sub

We would like to pass the 2 numbers to this macro and write the value in the excel book.

For R

# Install package (Not avaliable on CRAN at 12 June 2019)

# Create Excel Application
xlApp <- COMCreate("Excel.Application")

# Open the Macro Excel book
xlWbk <- xlApp$Workbooks()$Open("E:/Test.xlsm")# Change to your directory

# its ok to run macro without visible excel application
# If you want to see your workbook, please set it to TRUE
xlApp[['Visible']] <- TRUE 

# Run the macro called "MyMacro": and Pass 10 and 30 as argument
# Successful return would be NULL
#> NULL 

# Close the workbook and quit the app:
xlWbk$Close(FALSE)# not save and close excel book
xlWbk$close(TRUE) # save and close excel book

For Python

# Import packages
import os
import win32com.client

# Create Excel Application

# In default, Python open excel book visibly.
#< <COMObject Open>

# Same as R code
xlApp.Application.Run("Test_add", 100,310)