Call Excel VBA macros from Python using xlwings

I will explain how to call an Excel VBA macro from Python as a practical use of UDF in xlwings. For those who are asking "What is xlwings?", Please see the post below from the installation of Python to the basic usage of UDF of xlwings. Introduction to Python for VBA users-Calling Python from Excel with xlwings-

1. Background

Even if you start making in-house EUC tools with Python aiming for Excel VBA, I think that there are a lot of Excel VBA tools made by our predecessors in the company and you have to use them as well. I will. I can't just throw it away just because I hate VBA, and even if I try to rewrite it in Python, I'm too busy to do that. In the end, I wonder if I can escape from VBA.

And when using multiple Excel VBA tools in a series of work, I usually open a file, execute a macro, and when finished, the next file. Then, it may be better to create a program in VBA that automatically executes VBA macros one after another.

No, let's stop VBA. You can do it with Python.

This article describes how to run an Excel VBA macro from Python. This way you can comfortably code in Python while leveraging your existing VBA assets.

2. Environment

I am trying in the following environment.

• OS: Windows 10 version 1909
• Python: version 3.7.5
• xlwings: version 0.18.0 --Editor: VSCode version 1.45.1 & Python extension version 2020.5.80290

3. Basic

Try calling an Excel VBA macro from Python by referring to Official document (Python API) Let's do it.

In a folder of your choice, create a file called VBA_example.xlsm and open the VBA editor with ʻAlt + F11`. Add an appropriate module (let's call it Module1 for the time being) and write the following.

VBA_example.xlsm!Module1

Sub hello_VBA()
    MsgBox "Hello VBA from Python."
End Sub

Then open the same folder in Visual Studio Code and create a file called call_VBA.ipynb. Do the following (preferably line by line) with call_VBA.ipynb:

call_VBA.ipynb

import xlwings as xw             # 1.Import xlwings
wb = xw.Book('VBA_example.xlsm') # 2.Open the book
macro = wb.macro('hello_VBA')    # 3.Get macro
macro()                          # 4.Run macro

To explain the code

1. Import xlwings. Since it is ʻas xw, call xlwings with xw` below.
2. If the book is in the same folder, you can open it with xw.Book.
3. Store the macro hello_VBA in the workbook in the variable macro. If you're a VBA user, you might get a ?? here, but Python can also store functions in variables.
4. Run macro. In VBA, it may be Call macro, but Call is unnecessary. For macros that require arguments, put the arguments in parentheses.

It will be.

Here's a screen running this in VS Code:

It stops when I run the last cell, but when I activate the Excel window, I get the following screen:

The VBA macro is being executed properly.

4. Application (continuous execution of VBA macro)

This is a practical example using UDF. Let's try to execute VBA macros of other Excel files continuously from the button of one Excel file.

Create VBA_caller.xlsm in a folder of your choice. The contents of the worksheet should look like this:

--The sheet name is VBA_caller --Create table T.VBA_caller (from formatting as home tab table. Location is OK.) --The table should contain columns folders, files, and macros.

Open the VBA editor screen with ʻAlt + F11` and check xlwing in the reference settings. Create Module1 and paste the following:

VBA_caller.xlsm!Module1

Sub main()
  Dim rg_target As Range
  Set rg_target = Worksheets("VBA_caller").ListObjects("T.VBA_caller").Range
  
  Application.DisplayAlerts = False
  var_log = xlwings_udfs.call_vba(rg_target)
  Application.DisplayAlerts = True
End Sub

Prepare a Python script. Create a file VBA_caller.py with the following contents in the same folder.

VBA_caller.py

import os
from pathlib import Path

import numpy as np
import pandas as pd
import xlwings as xw


os.chdir(Path(__file__).parent.absolute().resolve())

@xw.sub
@xw.arg('df_target', pd.DataFrame, index=False, header=True)
def call_vba(df_target: pd.DataFrame):

    def call_vba_single(row):
        #Read settings
        wb_path, wb_name, macro_name = row['folder'], row['File'], row['macro']

        #Create an Excel instance for macro execution and open the workbook
        app = xw.apps.add()
        xl_path  = (Path() / wb_path / wb_name).resolve()
        wb = app.books.open(str(xl_path))

        #Macro execution
        app.display_alerts = False #During macro execution, Excel displays the message "Wait for another program to complete the OLE operation." And the execution stops.
        wb.macro(macro_name)()
        app.display_alerts = False #Set to False again as it may be returned to True at the callee

        #Save the workbook and kill the Excel instance for macro execution
        wb.save() 
        wb.close()
        app.kill()

    for _, row in df_target.iterrows():
        call_vba_single(row)

@xw.sub
def kill_excel():
    os.system('taskkill /f /im excel.exe')

#For debugging
if __name__ == '__main__':
    xw.serve()

After creating VBA_caller.py, go back to VBA_caller.xlsm and press the ʻImport Functions button on the xlwings tab to import the contents of VBA_caller.py` as UDF. This completes the caller's preparation.

Let's also prepare the called Excel file for the experiment.

Folder structure

┌example1
│　└example1.xlsm
├example2
│　├example2.xlsm
│　└example3
│　　　└example3.xlsm
├VBA_caller.py
└VBA_caller.xlsm

For ʻexample1.xlsm to ʻexample3.xlsm, save the following in the standard module:

VB:example1.slsm～example3.xlsm!Module1

'Change procedure-name and MsgBox string accordingly
Sub example1()
    MsgBox "example1"
End Sub

You are now ready.

I will try it. At VBA_caller.xlsm, press ʻAlt + F8, select main and press the Run button. If ʻexample1.xlsm opens in the new Excel instance and ʻexample1` is displayed in the message box, it is successful. When you press the OK button, the macros of example2.xlsm and example3.xlsm will be executed one after another.

5. Further application (simultaneous execution of VBA macros)

With the contents so far, you can do almost the same with VBA. However, if you rewrite VBA_caller.py a little, you can execute multiple macros at the same time.

Fix 1: Add the following to the end of the imoprt statement.

VBA_caller.py

import joblib

Fix # 2: Remove the following for loop and

VBA_caller.py

    for _, row in df_target.iterrows():
        call_vba_single(row)

Change to a call using joblib.

VBA_caller.py

    joblib.Parallel(n_jobs=-1, verbose=10)([
        joblib.delayed(call_vba_single)(row) for _, row in df_target.iterrows()
    ])

Fix (?) Part 3: (Try it if it doesn't work up to 2) Enter the path of python.exe in ʻInterpreteron the xlwings tab. If you have Anaconda installed with default settings, it will beC: \ ProgramData \ Anaconda3 \ python.exe`.

Go back to VBA_caller.xlsm and try running main again from ʻAlt + F8`. This time, multiple Excels will be launched at the same time and the macros will be executed at the same time. This is (probably) not possible with VBA, and you can reduce the overall processing time by running multiple macros at the same time.

joblib is a library that allows you to easily perform parallel processing in Python. For a detailed explanation, see here.

in conclusion

Thank you for reading to the end. This time, I didn't write about calling VBA macros that require arguments because it is a basic usage, but I would appreciate it if you could comment to that effect.

reference

-Official Document -Introduction to Python for VBA users-Calling Python from Excel with xlwings-