Last Updated on February 7, 2022 – 7 min read
Python is an object-oriented programming language that appeared in the 1970s. The name of the language comes from the British comedy group Monthy Python.
The advantages of the Python language are various. This language is easy to learn, read, understand, and write. It works under many operating systems. The community of developers is very active.
Python language has seen substantial growth in popularity over the last few years.
It is a known language for data analysis, visualization, data science thanks to machine learning libraries that are continually evolving, web-scraping, or web developers.
This language is for scripting and automation. Python is present in many sectors: finance, accounting, biology, marketing, startup, university.
Python is a compiled language that requires an interpreter installed on your machine. It is a multi-platform language that integrates well with other programming languages.
Python has a large number of libraries and frameworks available.
VBA vs Python
Python instead of VBA?
Python is an independent language of operating systems for developing multiple applications of which only the imagination is the limit. You can create complex algorithms with a few lines of code.
VBA macros are used only inside the Microsoft Excel application. The only language used to handle Microsoft applications from the inside remain VBA.
However, Python is a more optimized open-source language. This language has better documentation, contains multiple libraries (for machine learning as Scikit-learn, data structure as Pandas, or others NumPy, Flask, Django, etc.), and has a large community.
An integrated development environment (IDE) like PyCharm gives you instantaneously the errors. I highly recommend Jupyter for Notebooks and to manage the package’s environment. I also used PyCharm as an IDE or VS Code.
Run Python Script using the Shell (only for Windows)
Through an Excel VBA macro, you can execute a Python script.
Nothing’s easier to do it! You have to go through the Shell of your machine. Then, you can use the Run method on the object by giving two arguments, the link to your Python executor already installed and your Python script. 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 using the shell
'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 'run takes 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 to call Python from Excel and vice versa. You can interact with Excel from Python using a syntax close to VBA in your Python file (.py). It replaces VBA macro with Python code. You can use Python libraries as Numpy arrays or Pandas.
This add-in is easy to distribute and works on Windows and Mac.
In your Python script, you can manipulate many Excel objects (range, cells, workbooks, sheets, etc.) using the package xlwings.
Open your terminal and run the command pip install xlwings to install the xlwings add-in on your machine.
To install xlwings in the Microsoft Excel tool, you can write this command in your terminal xlwings add-in install.
Then, open Excel, 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 is an example of a Python script to write “Hello World” in 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 allows you to use the RunPython command to launch Python scripts via VBA modules.
After creating the following hello.py file below, you can call it using these code lines 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 to a Python code.
For this method, different modules are available in Python. You will see below openpyxl and win32com.client. Personnaly, I prefer to use openpyxl.
Openpyxl
Import the module openpyxl to use Microsoft Excel directly in Python. You can create, modify and save a workbook with several sheets.
After processing your data under Python, you can display your results or create a report through Excel.
You can also display a graph, an image, or your Dataframe in an Excel sheet.
# import the package openpyxl after installing using
# pip install openpyxl (or in your python environment if you use anaconda for example)
from openpyxl import Workbook
# create a new Workbook
wb = Workbook()
# activate the new sheet
sheet_vbaskills = wb.active
# change the name of the new sheet
sheet_vbaskills.title = "VBA SKILLS"
# change the tab color of the new sheet (with the hex code)
sheet_vbaskills.sheet_properties.tabColor = "1072BA"
# set the variable a as the range("A1")
a = sheet_vbaskills['A1']
a.value = 'Hello World with Python'
# set the variable row_1 as the row 1
row_1 = sheet_vbaskills[1]
# set a range of cells
rng_cells = sheet_vbaskills['A1':'C2']
# create another sheet at the first position
sheet_data = wb.create_sheet("Data", 0)
# save the workbook
wb.save(filename = 'Desktop/Test_python_openpyxl_vbaskills.xlsx')
Win32.com.client
By importing the module win32com.client you can also use Excel with your Python code. 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
wb_excel = AppExcel.Workbooks.add()
# create a sheet
sh = wb_excel.Worksheets.Add()
# set a range
rng = sh.Range("A1:A10")
rng.Value = 42
rng_2 = sh.Range("B1:B10")
rng_2.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.
It is possible to run Python scripts in VBA procedures.
You can also attach a macro to an Excel button to launch a Python script. Conversely, it is possible to open and manipulate the Microsoft Excel application and its objects thanks to Python packages.
It is a good practice, depending on your needs, to use the Python language for processing data and the Microsoft Excel tool to report your results.
It’s up to you to best judge the use of the two languages according to your needs.
SKILLS 