A while ago I wrote a blogpost that attracted some hostility. One commenter even called it “stupid.”

6 Reasons Not To Use VBA (Or Be Careful) was meant to be a fun warning and an invitation to BE CAREFUL. Ah! But a few folks took it as a swearing off of VBA coding, and that’s hardly the case.

VBA makes it possible to do anything with Excel, and there are lots of great uses for it. Let’s look at 3 beasts that VBA had tamed.

BEAST #1: 100K SUMIFS KILLS MEMORY

The Data:

• Two tall columns
• Column A: US States
• Column B: Names

Objectives:

• List specific states of interest in Column E
• Excel needs to list the associated names to the right of their state

The image below shows just 11 rows of data but the real problem was more than 100,000 rows.

PROBLEM: Formulas can work, but not too many

I created a beautiful formula-driven solution that worked on 100 rows. But when I scaled out to the full 100K-row data set, AW DAMN!

100K SUMIFS formulas made the spreadsheet drag, and one user couldn’t even get the file to open.

SOLUTION: Get rid of all formulas and write VBA code

Rather than have the formulas always sitting there sucking computer memory, the solution was to have no formulas at all and write a VBA script that would loop through the states in column E and retrieve the names in column B.

This does come at a cost, though:

• The code was fairly complex.
• We lose the real-time updating that we’d have with formulas.
• Users have to be ok with enabling macros.
• Users have to be trained NOT to do certain things like, insert rows or change column headers.

Nevertheless, with VBA we had a functioning solution that was easy on the computer memory.

BEAST #2: TOO BIG FOR THE FILTER

And … we’re back to dealing with the volume of data.

100,000 rows of data and maybe 30,000 unique IDs. Now, we want to filter by ID, copy the states and paste on another worksheet.

PROBLEM: The image below shows what happened upon trying to filter. Notice the yellow triangle.

Clicking the hyperlink for “Not all items showing” reveals a surprise:

 C’mon say it with me: AW DAMN!

Items 10,001 and later aren’t showing up as being available in the filter.

SOLUTION: Write VBA code that consolidates our steps and includes the filtering

I stepped back and looked at the work flow:

• List IDs of interest
• Filter for each one
• Copy the results of the filter
• Paste next to the appropriate ID
• Repeat until there are no more IDs of interest

In VBA, you’d write code that tells the dataset what to filter for. Neither do you have a menu that’s restricted to a certain size. Thus, I wrote code for the full workflow which includes the filtering.

BEAST #3: CRAP DATA & CORRUPT FORMULAS

The video below shows an inventory system that was created for a small nonprofit for tracking incoming and outgoing donations.

PROBLEM(S):

We could make an inventory system work with plain ol’ Excel by having a single worksheet where users would add new transactions to the bottom of a range of data. They could use pivot tables, sorting and filtering to dig out details. But here are the problems:

• There will be multiple users inputting data
• Users aren’t Excel heavyweights
• Lots of complex formulas drive the app

More users mean more opportunities for the input of crap data. Complex formulas are hard to fix if the users aren’t Excel savvy. In short: within 24 hours the app would be ruined.

SOLUTION: 

To reduce or eliminate the chance for crap data and broken formulas, the solution is to create a simple interface for the users. Protect the formals using Excel’s validation features. Use VBA to

1. Unprotect everything
2. Move data from the interface to the Inventory sheet where all data is stored
3. Re-protect everything
4. Give the user notification that the task was completed

FINAL WORDS ABOUT VBA

As you see, there’s plenty of use for Excel VBA. This makes Excel kinda scary because VBA can be or do almost anything. I’ve used VBA to break passwords and scrape web pages. Some people use Excel to automate enterprise-level tasks. As long as we’re on the side of good rather than evil, VBA is our friend.

There’s just a lot to be careful about with VBA. The previous blogpost, 6 Reasons Not To Use VBA (Or Be Careful) was written because VBA can be overused. When a document is to be shared, it’s unfair to go straight to VBA because it does create problems–more problems than the 6 I listed.

As a consultant, I have to explain to clients and potential clients that VBA solutions are expensive. In my second example, it’s wonderful to reduce 5 steps down to the press of a single button. But it costs money. Let’s take a simple example of dealing with errors.

In plain ol’ Excel we write a formula asking VLOOKUP to look for something that doesn’t exist. It returns #N/A. You can then wrap the VLOOKUP in IFERROR for smarter error-handling. But in the realm of VBA, error-handling is much more involved. Errors can cause the code to go into an infinite loop or, the code can crash and leave your user with a run-time error that they don’t understand. We then have to make a decision about everywhere an error can happen. Do we want the code to ignore it and keep going? Should it skip to the end of the script and stop? Should it call another script that’s designed to handle the error? What … other than crash?

Making those decisions, writing the code, testing, troubleshooting, revising the code, then testing and troubleshooting the revision … that all takes time and costs money. For the cost-conscious, they love the potential but hate the pricetag.

VBA is very much a tool for taming data beasts. But you can shoot your own eye out if you’re not careful.

Taming a Beast image courtesy of Aomori