Is Python a suitable replacement for VBA?

[Tom Nielson]

This question is for Albert. I actually have "Automate the Boring Stuff with Python" on my Amazon wish list. I'm a Java developer, but I may buy this for my brother and I may grab a copy for myself too. My brother is being tasked with learning VBA and automating things at his workplace. From the description of this book, Python sounds like a suitable modern replacement for VBA and other workplace scripting automation tools. Would you say that is correct?

[Joe Ess]

Tom Nielson wrote:Python sounds like a suitable modern replacement for VBA and other workplace scripting automation tools. Would you say that is correct?

Not Albert, but I think it depends on if interacting with Microsoft applications is a requirement. Python doesn't have any native functionality to do so, though there are some third-party tools available. Depending on how functional they are and how much functionality one requires, Python may or may not be a good choice.

[Al Sweigart]

Hi Tom. I would say yes. VBA as a language is... less than ideal. The syntax is a bit messy and it doesn't have a large package repo like Python (or Ruby or Perl) has. It's also limited to Windows.

The chief benefit of VBA is that it was the original language made for programmatically interacting with Excel spreadsheets. Chapter 12 of the book covers using the OpenPyXL module to let Python do this. While the module can do all of the basic functionality of reading and writing cells, VBA might have some features that OpenPyXL lacks.

Python has win32 modules (mostly written by a guy named Mark Hammond) which can interact with COM components and do PowerShell-like things, so you might also want to check that out.

But in general, I would recommend the Python route anyway unless everyone at the company already has VBA expertise and an existing codebase of VBA scripts.

[Tom Nielson]

Very cool. I went ahead and ordered a copy on Amazon for my brother. I wouldn't mind getting one for myself.

I was a VBA developer for a few years before coding in Java, and I would rather encourage him to start scripting in something relevant and defining the future, rather than something that exists because of legacy. Python definitely seems to be hot right now and I may pick it up too.