Use VS Code to Debug Python Functions in Excel

Visual Studio Code (or VS Code) is a lightweight programming IDE with extensions for many languages, including Python. The Python extension for VS Code features a debugger than you can use to debug Python functions in Excel.

If you have used Excel’s VBA Editor then you will be used to stepping through VBA functions. With the VS Code Python debugger you can do the same, but with your Excel functions written in Python.

If you are new to writing Excel functions in Python then please see our Introduction to PyXLL.

Installing VS Code

If you don’t already have VS Code and the Python extension then you can download them from these links:

• https://code.visualstudio.com/download
• https://marketplace.visualstudio.com/items?itemName=ms-python.python

If you are new to developing Python code in VS Code, there is a tutorial available here:
https://code.visualstudio.com/docs/python/python-tutorial

See the following link for the VS Code Python debugger documentation:
https://code.visualstudio.com/docs/python/debugging

Once you have installed and familiarised yourself with VS Code you are ready to debug your Python code in Excel!

Calling a Python function from Excel

There are many different ways to call Python in Excel using PyXLL.

The most common way is to write an Excel worksheet function (or UDF) in Python. To make your Python function callable in Excel you only need to add the @xl_func decorator to it.

If you are new to calling Python in Excel with PyXLL then I would recommend this Introduction to Excel Worksheet Functions in Python page from the PyXLL documentation. Alternatively, take a look at our video tutorial Writing Excel Functions in Python.

PyXLL supports calling Python from Excel in many other ways too, for example:

• Excel Macros written in Python
• Ribbon toolbar actions
• Custom Python User Interfaces in Excel
• Right click context menus

Whichever method of calling Python code from Excel you choose, you can use VS Code to debug your Python functions in Excel!

Debugging Python in Excel with VS Code

Unlike running a Python script from VS Code, we will be attaching VS Code to our already running Excel process. The VS Code debugger is capable of attaching to the Python interpreter that PyXLL runs inside of Excel.

Run and Debug

To start debugging click the “Run and Debug” button in VS Code.

If you already have some Run Configurations set up for your project then instead click the down arrow to the right of the currently selected run configuration and select “Add Configuration…”.

If you do not have any Run Configurations yet you will instead see a blue button “Run and Debug”. Click that to create your first run configuration.

Attach using Process ID

When prompted to select a debugger select “Python”, and then for the debug configuration select “Attach using Process ID”.

Once you have selected “Attach using Process ID” you will next be shown a list of running processes. Type”EXCEL.EXE” into the drop down to find and select the Excel process. This will attach the debugger to the Python interpreter running in Excel. You should already have Excel running with the PyXLL add-in loaded at this stage.

Choosing the right process

Sometimes you may have multiple Excel processes running. In that case, it may not be obvious which Excel process is the one you want to attach to.

You can use a quick PyXLL function to tell you the process id. Calling that PyXLL function from the Excel instance you want to attach will make it easy for you to pick the right one in VS Code.

For example:

from pyxll import xl_func
import os 

@xl_func
def getpid():
    # This will return the Excel process ID so you know which
    # process to attach the debugger to
    return os.getpid()

Once the debugger is attached

After a short delay the status bar at the bottom of VS Code will change color to indicate that the debugger is attached. You will also see the debug icons at the top of the VS Code window.

Once the debugger is connected you can set break points and debug your code as it runs in Excel. When you have finished debugging use the disconnect button in the VS Code debug toolbar to disconnect from Excel.

Common Problems

If you get an error at this stage then you should check the PyXLL log file. One common error is that a conflicting version of the Python package “pydevd” has already been imported. If that is the case, remove that package from your Python environment so the VS Code package can be used instead and try again.

If you have any trouble in attaching the debugger (or if VS Code shows a connection failed error) check the following:

1. You have any of the packages “debugpy”, “pydevd” or “ptvsd” installed. These may prevent VS Code’s Attach to Process ID feature from working correctly.
   
   To uninstall these packages run:
   pip uninstall debugpy pydevd ptvsd
   
   Check they have been removed by looking in your “site-packages” folder.
    
2. Check the PyXLL log file. Any errors thrown when VS Code attempts to connect to Excel will be logged to the PyXLL log file.

For more information about debugging Python with VS Code please see:
https://code.visualstudio.com/Docs/editor/debugging