VBA: Calling Python from Excel¶
Import the xlwings VBA module into Excel¶
To get access to the
RunPython function in VBA, you need to import the VBA module
xlwings.bas into the VBA
- Open the VBA editor with
- Then go to
File > Import File...and import the
xlwings.basfile. It can be found in the directory of your
If you don’t know the location of your xlwings installation, you can find it as follows:
$ python >>> import xlwings >>> xlwings.__path__
An even easier way is to start from a template that already includes the xlwings VBA module and boilerplate code. Use the command line client like this (for details see: Command Line Client):
$ xlwings template open
While the defaults will often work out-of-the box, you can change the settings at the top of the xlwings VBA module
PYTHON_WIN = "" PYTHON_MAC = "" PYTHON_FROZEN = ThisWorkbook.Path & "\build\exe.win32-2.7" PYTHONPATH = ThisWorkbook.Path UDF_PATH = "" LOG_FILE = ThisWorkbook.Path & "\xlwings_log.txt" SHOW_LOG = True OPTIMIZED_CONNECTION = False
PYTHON_WIN: This is the directory of the Python interpreter on Windows.
""resolves to your default Python installation on the PATH, i.e. the one you can start by just typing
pythonat a command prompt.
PYTHON_MAC: This is the directory of the Python interpreter on Mac OSX.
""resolves to your default installation as per PATH on .bash_profile. To get special folders on Mac, type
Nameis one of the following:
PYTHON_FROZEN[Optional]: Currently only on Windows, indicates the directory of the exe file that has been frozen by either using
py2exe. Can be set to
PYTHONPATH[Optional]: If the source file of your code is not found, add the path here. Otherwise set it to
UDF_PATH[Optional, Windows only]: Full path to a Python file from which the User Defined Functions are being imported. Example:
UDF_PATH = ThisWorkbook.Path & "\functions.py"Default:
UDF_PATH = ""defaults to a file in the same directory of the Excel spreadsheet with the same name but ending in
LOG_FILE[Optional]: Leave empty for default location (see below) or provide directory including file name.
SHOW_LOG: If False, no pop-up with the Log messages (usually errors) will be shown. Use with care.
OPTIMIZED_CONNECTION: Currently only on Windows, use a COM Server for an efficient connection (experimental!)
LOG_FILE default locations¶
- Mac 2011:
- Mac 2016:
If the settings (especially
LOG_FILE) need to work on Windows on Mac, use backslashes
in relative file path, i.e.
ThisWorkbook.Path & "\mydirectory".
OPTIMIZED_CONNECTION = True works currently on Windows only and is still experimental! This will
use a COM server that will keep the connection to Python alive between different calls and is therefore much more
efficient. However, changes in the Python code are not being picked up until the
pythonw.exe process is restarted
by killing it manually in the Windows Task Manager. The suggested workflow is hence to set
OPTIMIZED_CONNECTION = False for development and to only set it to
True for production.
Subtle difference between the Windows and Mac Version¶
- Windows: After calling the Macro (e.g. by pressing a button), Excel waits until Python is done.
- Mac: After calling the Macro, the call returns instantly but Excel’s Status Bar turns into
Running...during the duration of the Python call.
Call Python with “RunPython”¶
After you have imported the xlwings VBA module and potentially adjusted the Settings, go to
Insert > Module (still
in the VBA-Editor). This will create a new Excel module where you can write your Python call as follows:
Sub MyMacro() RunPython ("import mymodule; mymodule.rand_numbers()") End Sub
This essentially hands over control to
import numpy as np from xlwings import Workbook, Range def rand_numbers(): """ produces std. normally distributed random numbers with shape (n,n)""" wb = Workbook.caller() # Creates a reference to the calling Excel file n = int(Range('Sheet1', 'B1').value) # Write desired dimensions into Cell B1 rand_num = np.random.randn(n, n) Range('Sheet1', 'C3').value = rand_num
You can then attach
MyMacro to a button or run it directly in the VBA Editor by hitting
Workbook.caller() within the function that is being called from Excel and not outside as
global variable. Otherwise it prevents Excel from shutting down properly upon exiting and
leaves you with a zombie process when you use
OPTIMIZED_CONNECTION = True.