What are the benefits of Python scripting in Excel versus VBA?

Answer 1

What are the benefits of Python scripting in Excel versus VBA? Before I answer this question, I will state some facts that you may or may not be aware of:

1. You can use the Python module openpyxl to read and modify the cells in an Excel workbook, change the formatting in Excel workbooks, create or alter pivot tables, fiter and sort worksheets, work with charts, and other things.
2. The Python module xlsxwriter can also work with Excel files.
3. You can export the contents of Excel worksheets into csv files, and then read and write these csv files using the csv module, which is part of the Python Standard Library and so does not have to be installed using pip or conda.
4. I do not have information about the performance of Python versus VBA for very large workbooks. That said, I have worked with VBA, and in the beginning, it was very slow when working with very large workbooks. I modified the VBA to make it run about 100 times faster. The point I'm trying to make is that both Python and VBA can be written to be very slow or very fast.

So the benefits of Python versus VBA depends on your situation:

1. Are you more familiar and comfortable using Python, or using VBA? Why spend lots of time learning one if you are more familiar with the other?
2. Is this a work-related task where you are expected to use one language and not the other?
3. I have no experience with using Python on very large Excel workbooks, so I have nothing to add to my comments above about performance.
4. openpyxl and xlswriter are only for working with Excel files, they can not work with other Microsoft Office programs. But it is not hard to write VBA that can work with and move information between different Microsoft Office programs. I have written VBA that moves information between Excel workbooks and Outlook Calendars. I have also written VBA that ran in Access. There is definitely VBA for working with Word, Project, OneNote, Publisher, and the rest of Outlook. I have personally written Python that runs SQL statements on Access databases. Beyond that, I do not know about Python modules that run against the other Microsoft Office programs, but you can use your favorite search engine to research this, if you wish.