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, read, understand, 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…).
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.
Finally, open the Visual Basic Editor (IDE) and click on Tools->References to check the box for xlwings.
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.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.
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 # 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')
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.