Excel Automation for Data Analysts

Should you learn VBA?

Note: A very simple sample file and code can be downloaded just above the video.

A lot of “real” programmers dismiss VBA and Visual-basic in general. This is short-sighted for a number of reasons.

Building solutions should be about effective solutions, not a myopic focus on the “most powerful tool.” When it comes to building in the office/daily use tools for MOST professionals, VBA is the most accessible and effective tool available. I can hear groans of “but Shadow IT….” – don’t get me started about the myth of Shadow IT’s problems. If, in general,IT was better and faster at delivering operational tools, this discussion wouldn’t happen.

But I digress.

From time to time, we work with business and data analysts, helping them better understand the tools at their disposal. This came from one of those instances.

It was a simple data cleanup application I created for a client. They received VERY large data files once or twice a week. The typically remove duplicates procedure for Excel could not be used because there were cells (fields) in the underlying data that needed to be preserved by merging the fields, not removing the rows. The client needed to merge data from two to five rows before performing their analysis.

Later, we created a custom filtered pivot table to give them the “visibility” and analysis they needed.

As part of the procedure, we also helped direct an offshore group of developers how to better use SQL queries to provide cleaner data from the outset. While this is desirable, it is not always possible. That is why teaching your financial, data, or business analysts VBA and other tools is powerful.

If you work in Excel, learning VBA (Visual-basic for Applications) is a HUGE benefit. Especially if you find yourself repeating tasks over and over.

Download Excel File


Posted in Consulting, Video and tagged , , .


Leave a Reply

Your email address will not be published.