The definitive guide

Visual Basic for Applications (VBA) has been voted the most dreaded programming language in the Stack Overflow Developer Survey in 2020. Four in five VBA developers are not interested in keep using it next year. While in my opinion VBA deserves to be despised, much of the dread is underserved. Its accessibility makes it easy to misuse it, causing frustration both in the developers and the final users.

Any tool choice should depend on its purpose. If you have been considering learning VBA, here is a guide to help you choose based on your motivation.

There is a guy at the office who does magic with VBA and I want to share the power.

Learn VBA. Understand what his code does and adapt it to your needs.

I want to automate my workflow in general.

If your work happens completely within Microsoft Office, learn VBA. If not (e.g. you are interested in web automation), learn Python.

I repeat the same steps every time to collect and aggregate data in Excel.

Learn Power Query. Consider moving your database to Access. If that is not enough, then learn VBA.

I am a power user in Excel. VBA feels like the natural next step for my career.

You are overestimating your knowledge. If you have a specific use case, ask Reddit for recommendations, which might not involve VBA. If not, consider learning other tools which are specific to your industry.

I develop in another language and I want to get rid of boring administrative tasks I have to do in Microsoft Office.

Hire a student in business administration as an intern to do the tasks for you. If you don’t have the budget, calculate how much your time on the tasks costs and show the figure to your boss.

I am copy-pasting a lot among different applications in predictable ways.

You are using the wrong tools. Organise a workshop with your colleagues and identify what you need to work well, then choose the tool which comes closest to the the ideal solution. Consider hiring an external consultant.

My customer refuses to use any modern technology and Microsoft Office is the only tool at my disposal.

Learn VBA. Don’t rely on copy-pasting from random corners of the internet, you want to buy a couple of good books from professional developers and invest the time to learn it well. Also, consider your career choices.

I have a legacy system that only accepts Excel documents formatted in a specific way.

Get rid of it, NOW. You have a ticking bomb which can explode at any software update and VBA won’t help you with that.

I want a quick and dirty solution for a critical process which influences public health policy.


tags: Excel   Python