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
Ben james Knudson commented
Please make this happen! Now that I think about it.. I'm surprised there isn't an API for Excel...
Definitely a fan of this idea. New generations of developers, data engineers, financial experts are learning Python all of the time. Not only for data science and deep learning, but automation of routine daily tasks and data processing. In my opinion, replacing VBA with Python for Excel would keep it relevant for years to come!
I don't know python but I believe it would solve some of the things that make VBA frustrating. For example I imagine there is only one way to pass variables, by reference, and there probably isn't a different way for calling code that only changes state(subs) vs functions which is annoying with the whole
I like that the idea behind python is there is one way of doing things.
Also without first class functions we end up writing loops over and over again or having to implement a strategy pattern which is so tedious in vba with it's tacked on interface sytem.
We Japanese welcome the implementation of python on Excel, so I hope.
I love Excel and the VBA everyday when I work with PC. And I want to try python if it's faster than VBA.
This would be an excellent addition and bring MS closer to the open source community. It helps that Python is one of the strongest growing languages out there, and this would revitalize the Excel community and perhaps keep people from exploring SaaS for something they can do in-house.
Pedro Ferrarezi commented
OMG: amazing news! Please MS do it as fast as possible! People at financial stuff will love it. As me.
I think the best was to go about this would be to integrate Visual Studio Code as the new developer environment for MS Office. It is fairly lightweight and has extensible support for all sorts of languages and runtimes. The biggest challenge would be to either write wrappers for the existing C and VBA API's or provide a new API for Python.
I have also seen lots of requests for .Net support which I know VS Code already has excellent support for. VB.Net is a natural move from VBA and F#, with some extension libraries, could provide a useful domain specific scripting language for excel. Once again the challenge is providing high quality API's that can be used for real time iterative development like VBA and without the versioning issues that meant VSTO never took off.
Gustavo Freitas commented
Awesome answer ashvini. I am waiting for this feature
@Ste - imagine the chaos that would create!
In my last role, every Excel report I created had to be in XLS format, as there were still (in 2017) people in the business who could not open XLSX files, having versions of Excel prior to 2007.
And you know what was great? With some careful coding, the VBA I wrote in Excel 2010 was perfectly happy to run in Excel XP (I'm not exaggerating for effect, there were people still using XP, and this was in a multi-national transport company)
If you don't see any problem with endlessly-varying interpreter versions then I am convinced that you are not producing reports for a wide audience, on a PC to which you do not have admin rights to install whatever-the-latest-cool-thing-is
ste chs commented
1. I think the python interpreter will be implemented in Excel in the same way as in Visual Studio: it will be a module that can be updated whenever is needed/wanted, so I don't see any problem;
2. You didn't understood me: MS will not steal people from open source; now MS platforms can be used WITH open source products (linux servers, containers, etc.), not instead of, so the user will can to integrate the open source products that already is using with the Microsoft platforms and tools and Python in Excel is exactly one of the ways that will speed up the adoption of Microsoft products in the open source world.
@ste - of COURSE that's the fragmentation I'm referring to! As soon as Python 3.7.2 comes out, people will be complaining that the version in Excel is out of date. And what Open Source do you imagine people will be abandoning for Office if Python were included?
@Alex - it surprises you that VBA is not part of .net??
Bill Tubbs commented
I think if Microsoft wants to maintain its position as a leading data manipulation tool used in business it needs to totally rethink or replace Excel. There is too much of a legacy of out-dated or poorly designed tools in Excel. From its lack of support for structured data (tables) to its poor syntax for references, formulas, text, combined with poor reliability (tendency to crash and loose data). Maybe its time a different organization came out with a completely new tool... My vision of a contemporary data tool would be a multi-functional workbook built on top of a common data representation / language framework (e.g. Python). Instead of an x-y spreadsheet being the basis of all data representation, each tab in the workbook would be either a visualization of a data object (e.g. table of structured data or a chart), a script module, a Jupyter notebook (interactive code and output), output green or report template (e.g. html), ... or a spreadsheet.
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.