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))
Thank you to all those participated in our survey. What an amazing number of responses, many of them with very deep content. We’re processing the over ten thousand responses(!), and already appreciate the time so many of you took to answer with passion and experience.
Please know this survey is used to help influence various topics – both on Python as well as other related topics that the comments started to bleed into. Given the passion, I want to be clear this remains an area of exploration for us, without any specific timeline.
We’ll provide updates as we progress on this feature request.
Lead Program Manager
Alex Marvin commented
@Matt, IronPython is a .NET language. Surprisingly, VBA is not .NET.
A lot of users who use Excel feel lost in using languages such as c#. Many of them have good understanding of scripting languages (R or Python) however.
I am not saying that we don’t support .net, if possible we do both.
ste chs commented
@Matt - what fragmentation? It's the first time that I hear about this; if you are referring to upgrading from Python 2.x to 3.x, this is just a natural process, not a different path.
Implementing VB.net in Excel is the 'old MS path' : platform lock in; the new MS path is to be open and Python in Excel will bring more people from open source to Office 365 and Azure, i.e. Microsoft apps everywhere
The fragmentation that exists in the Python community already does not lend itself to the long-term stability that is needed in the Office family.
At least if you're going to incorporate some newer technology into office, go with .net
This is a platform you own, and the transition from VBA->VB.net will be quite smooth (this was, after all, the path you created!)
This also allows for the transition from Ace/Jet -> SQL server in terms of database suppor
I work for one of the biggest 10 companies in the FTSE 100. In my department, we're gradually moving from a position where every possible task is done in Excel, to a position where a large portion of our tasks are being done in Python. In a few more years, I can see a situation where Excel is rarely used at all. Adding the ability to use Python within Excel will go a long way to reversing the trend!
support python as scripting language for workbooks deployed to Power BI Reporting Server + office online server
Yasuaki Tohyama commented
Good idea! and welcome!
Feature request is as follows.
If you bilt-in Python script in Excel alternative to VBA, please Do support Characters(start,len)
sheet.Cells(k,7).Characters(start+1, len(kwd)).Font.ColorIndex =3
sheet.Cells(k,7).Characters(start+1, len(kwd)).Font.Bold =True
This function is nessesary to colorizing keywords in cells for productivity.
At this moment, this function is not supported in XlsxWriter.
Only supported in win32com as GetCharacters.
Hans Avery commented
We have been moving out of Excel and into Python + databases + Jupyter. Adding Python to Excel is one of the best things MS could do to keep Office relevant in our workflow. Markdown support built in to Word would also be a big help.
It would be great if users from novice to pro data scientists could enjoy interactive REPL programming with Python libraries and non-official but very popular OSS visualization pip packages shipped with Excel (e.g., Swift Playgrounds on Xcode and matplotlib/seaborn on Jupyter Notebook).
Just upgrade VBA to VB.NET. Too many projects rely on VBA to be rewritten. But, whatever you decide to do, release information visible for everyone about your plans, so one can adapt and prepare in advance.
José Santiago commented
Within security compliant organizations, no matter how rich of features it is if at the end it does not reach end users who could make use of them. So to be more widely useful and open the door to productivity increases, it would need to come preinstalled and easy to configure, like VBA, even for the newbies that by getting into a pain free start could go further and become more proficient at it.
Security authorship should also be protected so the development of new tools is both worth the time and safe from exploit injections. That way, there would be also place for a market of scripted add-ins that could be managed within Office applications, even if some are free, as long as they pass some security checks themselves.
Managing scripts should also get version control treatment along modern language developing environments, like the Rubberduckvba add-in tries to bring to VBA with Unit Testing, Code Inspections or Refactoring.
I don't see why not turn the VBA IDE into an ECLIPSE alternative where multiple interpreters can be used (VBA, Python, Perl, etc).
The use of the worksheets in conjunction with ANY programming is a great advantage
Ward Williams commented
Yes! Yes! Yes! As a Python & Excel user, this would be amazing. The unfriendly VBA syntax should have a readable alternative like Python, which probably has the greatest overlap among programming languages with Excel use.
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.
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.
Chris Swain commented
Gustavo Freitas commented
It would be great we have python support