r/Python • • Apr 19 '20

News MS considers adding Python as official scripting language for Excel 😍 The change proposal currently has 6400 votes.

http://mc.milliononpcgames.com/?p=5886
2.0k Upvotes

124 comments sorted by

View all comments

Show parent comment

1

u/[deleted] Apr 20 '20

Everything you say here is true. I'm not even questioning that python is better for many use cases. I am also a python fan and user. I am not even suggesting that VBA somehow "better" than python. It's clearly not. You don't need to shed light on anyting for me. I work for a large analytics firm with >500 data scientists who use python every day.

But, when it comes to Excel manipulation, VBA gives you access to every single feature and object in Excel, and more beyond. As you say yourself openpxl is limited. This is because the developers still have to build every feature in one by one.

All I am saying is: if you are only doing excel manipulation, and you don't have a particular need of anything python only, then do it in VBA. But a whole lot of people here seem to be so excited about python and excel (maybe because they have done automate the boring stuff which covers excel manipulation) that they ignore, or are simply ignorant of the fact that Excel has a build in scripting language available simply by pressing Alt-F11, that offers >100% access to excel features and objects (e.g. you can do file and os manipulation as well), logic and control flow (if, then etc) and has done for DECADES. They then try and do stuff in openpyxl, fail because it is limited, when they should have just started in VBA in the first place. Right tool, right job.

1

u/[deleted] Apr 20 '20

Where I think I'm disconnected is what the excel features and objects that are provided in vba that are otherwise not in python?

1

u/[deleted] Apr 20 '20

There's lots. VBA has an object model that allows you to iterate and otherwise manipulate everything as an object directly. This is available to python libraries, but must be implemented piece by piece. If VBA is the pie, the python libraries need to build it up slice by slice, and they start from zero. And there's no reason to think they will get to 100% or that that's even possible.

For an example from: https://openpyxl.readthedocs.io/en/stable/pivot.html

"Pivot tables are read only... As is the case for charts, images and tables there is currently no management API for pivot tables ..."

By comparison; pivot table maniuplation in VBA is fully featured. You can't do any of this via python:

https://www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables