Python as an Excel scripting language
Let us do scripting with Python! Yay! Not only as an alternative to VBA, but also as an alternative to field functions (=SUM(A1:A2))
Hi folks –
Thanks for the continued passion around this topic.
We’d like to gather more information to help us better understand the needs around Excel and Python integration.
To help us with this, can you please complete the survey below?
Lead Program Manager
Stuart McCroden commented
Anyone following machine learning/data science knows that it is the reason Python is one of the fastest growing languages (this can be debated, but it's growing because of ML/DS field). Using Python for Excel scripting would help grow Python, but also increase the number of Excel users.
1) Allow any text editor or IDE to be used. But, Microsoft could install Visual Studio by default as the editor.
2) Give us the ability to use Git for version control, and not inhibit the use of Gitlab or Github, etc.
3) Let us use any external python library that we want just like we were creating our own python script from scratch. You could allow a GUI to assist newer python users, but don't make it more difficult than it needs to be to use libraries.
Minoru Mizutani commented
I would strongly hope that Microsoft chooses JetBrains' IDE platform as the code editor for the would-be Excel-builtin Python as Google customized the IDE platform as Android Studio for Android app development.
João Beno Schreiner Junior commented
I would rather support TypeScript...
Part2 the solution
So what to do?
- Integrate other dynamic languages directly into VBE editor (rename it ExcelCodeEditor or something)
- Allow any new module or class to be created in a new dynamic language. I'll accept the IRON versions (IronPython,IronRuby,IronPerl, R) as a bridge to real versions. Think Jupyter/BeakerX here.
- Let us use your own languages in excel!! C# and VB.NET and F# using a repl env.
- debugging VBA is 1000x faster than dotnet as you can change code on the fly function by function, not having to restart excel EVERY TIME so keep these tools dynamic via a REPL type interface so at best you get the "this action will reset your project" warning rather than needing to restart excel when you change a function.
- Think scripting REPL type linking to other libs. You could allow debugging via VS for library based debugging) but just returning results (no step in) from imported libs for using them as an easy first cut. Make adding a lib as easy as the project references dialog since you want to enable this from excel, without VS type project files (whcih are painful). Think scripting!
- class and module/function based interop between modules - Can call a VBA function that returns a string from Python module e.g. to assist migration. leaving out idiosyncratic language features in a first cut (tuples e.g.).
- fix the 32/64bit issues for memory space and allow these languages to run in out of process containers to prevent excel dying. This would be the biggest thing you could do to enable large scale excel apps on desktops.
- All this should be available by default for a system admin to just pick an option on install wizard (e.g. "include python code tools" option), so that a single sheet (xls[x/m]? could be passed around between teams without having to get an admin to install as an app- yes I know its a security risk from internet but you have trust options around that and I fundamentally trust the people in my team at work.
- Do not use this as an excuse to integrate onedrive and sharepoint. This is not what users want!
C'mon MS - show us why you're a multi billion dollar software company and do something truly innovative and useful to users for this and show us you still have some talent!
Part1 - the problem (this post was too long...)
MS made a total mess of excel with dotnet and VSTO.
This is your chance to correct this mistake.
There are 2 types of excel app essentially.
1. where its a spreadsheet with a few internal macros/functions written locally, all stored IN sheet. These can range from expanding inline functions, running a few simple repeated actions through to fulls apps further connecting to databases, microservices etc
2. where an external app controls excel via Object model.
With dotnet and VSTO, MS only gave the excel developer the second option and the first still had to be done with VBA or addins which require separate deployment. ExcelDNA was a great 3rd party tool to allow you to connect these but it is external to excel and turns an excel sheet into an app which requires deployment and interop is slow. This is a real pain in a corporate environment. It also meant you couldn't use this (easily) without Visual Studio which is more hassle than worth for small extensions. As a result VBA is still used everywhere.
An additional problem here is that once upon a time a VBA macro could be exported into VB6 dlls and shared between sheets (if installed once) and despite COM's bad rep and install hassles this was actually easy to do in VB. There is no option to do this anymore in Excel with the death of VB6 and you now have to convert VBA to a C# project using VSTO/ExcelDNA and Visual studio which is really painful if you want to reuse one function (without copy and paste). You took away half the tools and gave nothing back!
On another front, remote automation of the object model in excel from c# is slow. Since it uses COM-dotnet interop. It is often faster to do things directly in VBA. (MS also makes Excel slower in EVERY iteration (I've seen this ever since excel 4->5 before Excel 95 even) for some reason. This is a very bad way of addressing this perf imbalance - make all of Excel slow!...)
Given MS expertise in VS options for dynamic languages - ruby,python,perl etc, it should be REALLY easy for you to create a default native environment within excel (like the VBA editor) which would allow you to pick your language when you create a new module/class. You need to bridge the gaps between allowing one module to call code in another (and handling multiple runtime memory issues which could be a problem) but they could interop. Still most people would likely just use one language esp if you had performance disclaimers.
Its SO ironic that VB.NET is actually not a bad language (personally I don't get uptight about pedantic syntax issues like end blocks vs braces) and it solved lots of language problems in VB (dim and initialise in one line e.g.) and provided a bridge to dotnet libraries so WHY oh WHY was it not possible to use it in excel directly?!? There is a possible reason that when you add dotnet runtime into excel process you run out of memory quickly (on 32bit anyway) and MS still hasn't solved the office 32/64bit runtime issue. Its a reason I had to write a 64bit out of process com server a while back so that excel could communicate from VBA to a 64bit process (not running out of memory for a large portfolio). This is next to impossible to do in dotnet though and requires a lot of unlisted hacks to get the component running out of process.
The VBE editor also hasn't been updated since Excel 2000. It really just looks like you gave up and hoped it would go away. Excel2016 even looks no different with one minor diff in intellisense I saw. This is a severe lack of innovation on MS part.
Gustavo Freitas commented
It would be great we have python support
Stefan Lukacs commented
Python in Excel would make so much sense given the weight it has in the Data Science community as well as easy syntax and readability
Gustav Brock commented
You really should forget about this. MS has top-notch languages right at hand, like C# or TypeScript, that should have much higher preference.
This is where resources for implementing a replacement for VBA should be allocated - and it's about time.
Mats Rolfson commented
Ironpython would be the perfecr match!
C# If you really consider to replace VBA, or to add another language, then please use C# and promote your own language.
Python in Excel would be awesome!
Jacek Kotowski commented
I think Python is OK, but R would be so much easier for you and us.
Why don't you build a library so that R can read and write excel ranges and generate plots in the sheet.
Such solutions exist already, maybe you could contact and work together with the open source developers: google: R package Excel link (there is also a VBA demonstration excel file showing how it should be done.
Aka Khan commented
Please add Python for scripting in Excel. VBA is too confusion as it does not really follow VB.net or VBScript syntax. Thank you.
Neko Debu commented
Jason Blum commented
Absolutely! Python all the way.
Fabio Costa commented
Sweeeeeeeeeet!!! Two thumbs UP!!!
In japan, many companies use ms excel. But, those can not use usefull functions of this.
Python is so easy.
I want to use excel with python!!
+1 for Python
now is the time to exchange the legacy VBA for modern language. i'm tired to refactor or newly develop projects by VBA which is not flexible. if new language can upgrade the productivity of the Excel cutomization, i'll highly welcome to try mastering it !
Василь Коломієць commented
C# or Python have to kill VBA in office at all