So why (the heck) is VBA still being used in Excel?

Here’s a common scenario I witness frequently, particularly in finance:

A business manager approaches their IT Department in need of a solution.

Maybe there’s been a regulatory change, or they have a manual process which has inherent operational risk, or there’s a new product they want to trade.

The IT Manager says “Sure, we can have something for you in about 18 months.”

Aghast, the business manager replies “but we need it next week / month / quarter”.

IT Departments in many companies are under-resourced in terms of staff, money and skills, and typically have a (very long) backlog of work. Each year they’re being asked to do more with an ever shrinking budget.

This is where Excel solutions automated with VBA come in.

These “tactical” solutions are intended to get the business across the line until a more “strategic” solution is made available.

They're not the solution to all business problems, but in the right scenario they can be a timely and cost effective tactical solution to immediate business problems.

Here's a great example. I worked with an administrator at an asset manager. She spent 3 to 4 days a month, every month, preparing a series of 120 client reports. That's 36 to 48 days a year copying and pasting values from spreadsheets to PowerPoint, saving them as PDF and emailing the report to the respective clients.

The workflow looked like this:

The solution was an Excel dashboard which managed the whole process for her, all driven by VBA.

This extracted data from two business spreadsheets:

1. One with a list of clients and their investments
2. The other with a list of the monthly portfolio performance data for each client.

The dashboard created a new report for each client in-turn, populating the PowerPoint template with the details specific and relevant to each client.

Each report was saved as a PDF document in a folder accessible by the compliance department so they may review them before sending to the clients.

Once compliance had given their approval, the PDF reports were automatically emailed to each clients’ list of approved recipients.

The report generation time was reduced from 4 days a month to 3 minutes per month. Less time than it took for her to make a cup of tea.