My thoughts on Python integration in Excel I submitted in the survey listed on https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10549005-python-as-an-excel-scripting-language
- 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
@e.UDF
def my_udf(a: e.String, b: e.Number) -> e.String
return a[:b].upper()
@e.Macro
...- 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]])
em.calculate()
assert em.getSheet(name="Sheet 1").values[1][0] == "AB"- 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
¯\_(ツ)_/¯