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
the folk at https://datanitro.com/ are doing something like that, when i saw their presentation years ago i was mind blown.
Vote for Python !
Plus, please add a modern bulit-in code editor in Excel for code editing, vs code may be the right one. VBE, the not smart dev env , really hurts.
Harvey Ellis commented
Some form of this is a very good idea. Building health economic models for clients in the pharmaceutical industry, it is nigh-on-impossible to get them to agree to anything but basic excel. Even R scripts, with code that is fully contained within a single script is difficult for them.
The main problem is versioning, and being able to provide a model in a single package - with no external dependencies. Therefore, I do wonder whether some of the more esoteric, but still rather useful packages would be supported in the Excel Python Interpreter. Perhaps there might be some kind "compartment" within the excel file for packages to be shipped within the file? (I am unsure how this might work.)
Or, perhaps as others have said, integrating VS code, with F#, C# or VB.NET support would be the way forward - allowing further extensibility, automation and performance whilst still within the Microsoft ecosystem.
In my opinion, adding the ability to writing Python scripts in excel in macros or as worksheet functions, is precisely same as connecting two closed oceans and get the advantage of boating on the vast open waters.
It is fantastic and I am ready to jump to that ocean.
This must happen please!!!
Dr. Pip commented
Python makes it easy for mere humans to do amazing things. Let them do it with Excel!
Ihor Ivliev commented
I really hope that the Python becomes the official macro language in Excel!
Excel User commented
Hi, I am almost finished with a master in finance. While interning 2 years ago I started VBA. One thing that I don't like is that i wonder if i am investing a lot of time in learning a language that will be dead in a few years. PLEASE, give information on what is going to happen in the next 5 years. I don't mind learning Python or whatever needed, but since most of the Excel users are not with IT degrees e.g. business, finance etc. we need some time to learn! I don't want to be fired because a skill that i have is obsolete. Btw Thank you! Excel is awesome :)
Well, it’s a bit of a double edged sword. I quite like the vba object structure as it’s intuitive, fits well with the interface and exposes pretty much everything available. The excel user interface to data inspection and data entry are hard to beat. And using the data with vba is good, but over decades I have had to build up vba objects to do most of the work on lists, file and automating writing to cells. Simplification of these tasks are definitely possible. and some api calls are needed. Python is a scripting language designed on a number of simple data structures and a rather pervasive object model where a newby does never quite know what’s going to happen where (eg typical calculator functions are ‘hidden’ in math; regex, list comprehension and magic methods all require a bit of a mindset ) There is a lot of confusion out there on the web, and the answers to simple questions sometimes have to go into some quite considerable depth. In many ways though python and vba are not so different, both being strongly object driven, however when teaching students to program, better(well more aesthetically pleasing) outputs are generated much quicker though I must admit I do give the a readcell and writecell function. I like the messy data calculate and display and the creativity it’s allows, but accept that this is not so good for business critical stuff. The vba editor is a bit overdue a overhaul as well. All in all, I can see the fit, but I fear the knowledge overhead required compared to learn python is significantly higher and that of vba and may put people with No computing experience. But a need to automate some tasks. I think a two level approach with a ‘vpa’ In which several core modules and the excel api are exposed, but users can start immediately, and can use standard functions like on, sum etc and the output is integrated in the excel sheets (seamless integration of the vba editor in excel as VPA tabs). And alongside that a more business process formal python api/integration with its own pip to load modules in the xl file.
Two key things should not be lost: I) the ability to send the spreadsheet any where to any company and they can run the code straight away without installing packages, python etc. You don’t won’t to have users needeing to engage w it just to open your code
2) please keep the explicit keyword, and leave it case insensitive. In python every typo is a pain ( I know that’s 3 really)...
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??