Python & VBA

Python is an object-oriented programming language that appeared in the 1970s and for the story took its name from the British comedy group Monthy Python.

The advantages of Python language are various, it is an accessible language to learn, readunderstand, and write. Python works under many operating systems and has a very active community. It is a known language for data analysis that is continually evolving. Python is used in many sectors of activity (finance, accounting, biology, marketing, startup, university…).

Logo Python

Python is a compiled language that requires an interpreter installed on your machine.


VBA vs Python

A topical question is the following: can Python replace VBA? 

Python is an independent language of operating systems which allows the creation of multiple applications of which only the imagination is the limit. VBA macros are used only inside the Microsoft Excel application. The only language used to handle Microsoft applications from the inside will, therefore, remain VBA. However, in the world of automation for data analysis, Python is a more optimized open-source language. This language has better documentation, containing multiples libraries (for machine learning, data structure, API, Pandas, Scikit-learn, NumPy, Flask, Django…), and having a large community.


Run Python Script using the Shell (for Windows)

Through an Excel VBA macro, we can execute a Python script

Nothing’s easier to do it! You have to go through the Shell of your machine. Once the Shell is created, you can use the Run method on the object and give as an argument the link to the Python executor already installed in your machine and the Python script in question. This solution works only for Windows due to the ActiveX component CreateObject(“Wscript. Shell”).

Sub RunPythonScript()
'Procedure to run a Python Script in a VBA Procedure

    'Declaration
    Dim objShell As Object 'For the Shell
    Dim Pythonexe, PythonScript As String
    
    'Create the Shell Object
    Set objShell = VBA.CreateObject("Wscript.Shell")
    
    Pythonexe = """C:\\...\Python\...\python.exe """ 'path of the python.exe
    PythonScript = "C:\\...\VBAPython.py" 'path of your Python script
    
    'Run your Python script
    objShell.Run Pythonexe & PythonScript 'two arguments

    'Free variables for memory
     Set objShell = Nothing
    
End Sub

Run Python Script using the Add-in xlwings in VBA

What is xlwings ?

It is a Python library that makes it easy to call Python from Excel and vice versa. It is useful to interact with Excel from Python using a syntax close to VBA in your Python file (.py). It replaces VBA macro with a Python code. You also can use Python libraries as Numpy arrays or Pandas. This add-in is easy to distribute and works on Windows and Mac.

In a Python script thanks to the imported xlwings package, you can manipulate many Excel objects (range, cells, workbook, sheet …).

To install xlwings add-in in your machine, the first step is to open your terminal and run the command “pip install xlwings“. To install the add-in directly in Excel you can write in the terminal the following command “xlwings add-in install“. Then, open Excel and select the Developer tab and click on the Excel Add-ins button. Using the browse, search the xlwings file installed. In the add-in, you need to check the box for xlwings.

Select an add-in

Finally, open the Visual Basic Editor (IDE) and click on Tools->References to check the box for xlwings.

Add xlwings add-in with VBA

Below an example of a Python script to write “Hello World” in a cell A1 :

import xlwings as xw

wb = xw.Book()  # create a new workbook
wb = xw.Book('Financial Data.xlsx') # connect to an existing file in the current working directory
sht = wb.sheets['Sheet1'] # use an Excel sheet
sht.range('A1').value = 'Hello World' # write Hello World in the range A1

This add-in will allow you to use the RunPython command to launch Python scripts via VBA modules. At the first use, you can launch “xlwings run python install” in the terminal.

After creating the following hello.py file below, you can call it using the code lines below with the RunPython command :

# hello.py
import numpy as np
import xlwings as xw

def WriteHelloWorld():
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = 'Hello World!'

The VBA module to write to call the Python script above is the following :

Sub HelloWorld()
'Procedure to use the RunPython command of xlwings add-in

    RunPython ("import hello; hello.world()")

End Sub

Run Excel through Python without Excel Add-in

Similarly, it is possible to run an Excel application and manipulate objects attached inside a Python code. For this method, different modules are available in Python, we will see openpyxl and win32com.client.

win32com.client

Importing the module openpyxl you can use Microsoft Excel directly in Python. It will create and save a workbook with several sheets without open as visible an Excel application on your computer.

# Import the package openpyxl after installing using 
# pip install openpyxl
from openpyxl import Workbook

# Create a new Workbook
wb = Workbook()

# Activate the new sheet
ShVbaSkills =  wb.active

# Change the name of the new sheet
ShVbaSkills.title = "VBA SKILLS"

# Change the tab color of the new sheet
ShVbaSkills.sheet_properties.tabColor = "1072BA"

# Set the variable c as the Range("A1")
c = ShVbaSkills['A1']
c.value = 'HELLO Python World'

# Set the variable Row1 as the row 1
Row1= ShVbaSkills[1]

# Set a range of cells
Rng = ShVbaSkills['A1':'C2']

# Create another sheet at the first position
ShData = wb.create_sheet("Data", 0)

# Save the workbook
wb.save(filename = 'Desktop/TestPythonVBASkills.xlsx')
Hello Python World

Win32.com.client

Importing the module win32com.client you can also use Excel as the following however it works only for Windows.

import win32com.client

# Create an Excel application
AppExcel = win32com.client.Dispatch("Excel.Application")
AppExcel.Visible = True

# Create a new workbook
WbkExcel = AppExcel.Workbooks.add()

# Create a sheet
Sh = WbkExcel.Worksheets.Add()

# Set a range
Rng = Sh.Range("A1:A10")
Rng.Value = 42

Rng2 = Sh.Range("B1:B10")
Rng2.Value = 42

There are several ways to launch a Python code in Excel. Different modules and add-ins exist to handle both languages easily.


The Python language and the Microsoft Excel tool can be complementary using different methods. It is possible to run Python scripts in VBA procedures. You can also attach a macro to an Excel button that allows you to launch a Python script. Conversely, it is possible to open and manipulate the Microsoft Excel application and its objects thanks to Python packages. For example, it can be useful, depending on your needs, to use the Python language for the data processing and the Excel tool to return the results thanks to its visual interface. It’s up to you to best judge the use of the two languages according to your needs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s