Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Python in Excel

My thoughts on Python integration in Excel I submitted in the survey listed on

  1. Git & code reviews & external editors => Open + Save As dialogs MUST support a plain text option, e.g.:

a) the un-zipped directory that currently makes the .xlsm file, or

b) have 2 files .xslx + .xslpy where:

  • the excel file would contain a link to the python file similar to "Data > Connections"
  • the python file would be plain text:
    • enabling static analysis of object model, e.g.:
import Excel as e

def my_udf(a: e.String, b: e.Number) -> e.String
    return a[:b].upper()

  • in the future, someone would be able create open source testing library, e.g.:
import ExcelMocks as em
from my_functions.xslpy import my_udf123

em.addSheet(name="Sheet 1", formulas=[["abc", "def"], ["=my_udf123(A1, 2)", None]])

assert em.getSheet(name="Sheet 1").values[1][0] == "AB"
  1. VBA Object Model is probably not 1:1 fit for Pythonic way of thinking - e.g. runtime exceptions from VBA are very cryptic and Python developers expect better than random error code numbers ¯\_(ツ)_/¯
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment