DON'T use Python as an Excel scripting language
DO NOT let us do scripting with Python! Yay!
Dan Kernan commented
fluid interaction between excel and python should be supported. Python is the Excel of the coding world. putting them together from a user-adoption base makes sense. Enabling people to use a language that they know coming out of college or can pick up easily via a MOOC or on their own always makes sense.
A way for excel data structures to be made available as common python structures and to be written back out to Excel should be supported. The longer term solution is to incorporate references to non-data items (i.e. worksheets) and other office programs into python via a package that MS controls.
Kalle Arulaane commented
Totally disagree. Python would be wonderful compared to VBA or in addition to VBA.
VBA is outdated, clumsy and not used anywhere but Excel scripting.
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 support
Use PowerShell, it is built into Windows already. Build a Domain Specific Language in PowerShell Dedicated to Excel. Retain the core of PowerShell and add the needed cmdlets that are Excel specific.
Or provide excel with a way to call any language of your choice with a scripting api. Make the first two languages python and PowerShell. Other languages can then be added and you can use whatever good or bad language choice you wish depending what you consider to be good or bad.
Michael K. Hunter commented
Excel has got -
Power Query for advanced data manipulation
Power Pivot for BI data modeling
Power View for Visualization
R can be introduced first for advanced data analysis (which Python is also good at) and advanced visualization.
Where else can Python add value to Excel?
If at all Python is to be introduced into Excel, it can be only for its huge community.
I careful analysis is needed before introduction of Python because once introduced, it will have to be supported.
A simple example - today VBA still couldn't be got ridden off of Excel.
Do we need Python anymore?
Python doesn't need to be integrated into Excel like VBA, but it would be nice to be able to interface with Excel like a database for data crunching/parsing data outside of Excel. I have countless uses for pushing data into and out of Excel for automating report generation, database import/exports and many other things.
Python has tons of tools available to crunch data that are not available in VBA. How do I export to a Sqlite database from Excel using VBA? There are some hacky ways to do it, but nothing as simple as doing it with a language that has the tools already built in.
I typically use XLRD or others, but there are so many xls formats that I occasionally run into formats that can't be read by an existing Python library (.xlsb!!). Also Excel formatting get screwed up often when using these tools when trying to write to a document. Having a proper Python Library that isn't a hack to interface to all of the Excel document formats would be very valuable and expand Excel into many other uses where a database isn't quite justified/doable.
Zev Spitz commented
1,3) Python can access the same COM API as VBA can, via win32com.
2) Whatever version of Python is installed on the user's system, with an option in the Office options dialog to specify the path to the Python interpreter. Presumably the first time a user tries to open a file with embedded Python code, or execute a Python script, Excel (or whatever application) would prompt for the location of the interpreter.
4) I think the primary benefit of Python-as-an-embedded-scripting-language is not in performance, but rather in the higher-level abstractions. Specifically, because Python has a function-as-first-class-object mechanism, it enables a more succinct style of data processing (think LINQ).
Tomasz Truszczyński commented
I agree, on the reason it's waste of human resources. Python as a second programming language sounds good but as for now I don't see its critical advantages over VB. I'm pretty sure there are more interesting and/or important suggestions for many Excel users.
@Andrew Jackson, @Vogel612 - please add your comments to the "Allow Python as a scripting language" idea as well. When the team reviews that thread, it's good to see both sides of the conversation.
@Kenneth let's start with the problem that EXCEL is intertwined with COM. Any additional support is bound to be a "screwed on" type of thing, unless it involves substantial changes within large parts of EXCEL
Next up let's talk about python's problems. Which version of Python should be used?
Thirdly there's the problem of the extensive interop and COM API that already exists for vba (and not quite so extensively for) other .NET-based languages.
In addition python's inherent single-threading provides no real added benefit over VBA. Let's not forget the fact that VBA will have to still be supported for the next few decades, because millions of businesses rely on it. Then add in the madness that is localization. Do I actually have to remind you that EXCEL's functions are available in a few hundred languages natively?
Oh and let's please get started on how python is better because it's easier to learn... It isn't. It's just different. VBA has it's flaws, but that doesn't mean it has no place in Programming (or Business)
Do I need to continue?
Kenneth Barber commented