CRM Chronicles: Changing Lives and Workflows through VBA Automation

It had been around a year since The Consultancy had assigned Jane, a new business consultant, to this federal contract. The work was tolerable; it followed an early in/early out routine, leaving her with ample time for afternoon "activities." But of course, there were the occasional urgent demands from some agency suits, expecting the impossible with impossible deadlines. That's why they kept consultants like Jane around—to solve problems.

And solving problems was Jane's specialty. Sure, she was good at fixing other people's problems, but she had a knack for creating them herself. The folks on the contract were a decent bunch, and the client wasn't half-bad either. Their small office was well-lit, despite being divided up by cubes. It was in an old federal building in downtown Washington, DC. In the summer, it was too hot, and in the winter, it was too cold. But at least there was good coffee in the basement food court and some small talk to be had.

Initially, Jane was tasked with the mind-numbing job of grooming JIRA tickets in Excel. The client wanted to track data at a granular level that JIRA couldn't do, and everyone in the federal consulting space loved Excel. Everyone had their own personal Excel sheets to fiddle with, slicing and dicing data in their own personal ways. But all of them seemed to make sense of it in columns and rows. So every day, Jane would come in and spend the first two hours of her day downloading, copying, pasting, and updating Excel sheets. It was a soul-sucking task, and as repetitive as it was, she would find herself zoning out and making mistakes. Jane knew she needed to find a way to automate this. She knew a bit of Python, Automator, and a bit of everything. But there was no way she could get full-on automation to do what she needed it to do.

Not to mention, Jane was working on a government-issued brick of a laptop. Those things were never the most up-to-date or powerful machines out there. They were spec'd for the lowest need and cost and kept around for their maximum lifespan, and often longer. Her laptop sounded like a jet engine taking off when she tried to check her email, and the battery had a solid three-point-two-five-minute life span when fully charged. And the systems were locked down tight. She couldn't download software or use plugins, and getting new software approved could take longer than a standard career span. And forget about getting one-off programs approved. Not happening. So, a lot of people ended up doing things manually that could have been automated. Hence Jane's situation.

As someone who enjoyed solving problems, it wasn't long before Jane was gnawing on this one in her sleep. She remembered playing with the macro recorder in Excel before. It was pretty handy for simple things, and she thought she might give it a whirl. It wasn't long before Jane became a Google expert on Excel VBA. It all started with needing to adjust the recorded macro to use a different sheet based on the date. Before she knew it, she was using pieces of VBA she found on the web to further automate what she had recorded. Since VBA was so similar in structure to Python, it wasn't long before she was using variables, if/then/else statements, loops, and arrays to plug and chug data for her. When Jane didn't know how to do something, she'd use the macro recorder to record it and then hack it to bits.

In a couple of months, Jane had automated her two-hour task down to about 15 minutes. As she was doing the ROI calculations, she realized that her inner geek had reduced the LOE by a whopping 455 hours per year! Those hours at the estimated hourly rate of $120 comes to $54,600 in savings annually. It took her about 80 hours to learn VBA and build the solution, so roughly $10,000. Jane might not be a financial analyst, but she recognized that this was an excellent return on investment.

She started automating other things, like the team's detailed and time-consuming daily time reporting. Jane was terrible at making sure she completed this task every day, and she would inevitably receive a reminder and have to spend a lot of time catching up on the reports. No matter when she did it, whether at the time of the activity, at the end of the day, or at the end of the week, it took about 30-45 minutes of work to report on the work they were doing. Then another poor soul had to compile those reports to generate a daily roll-up of total hours. With a 20-30 person team, that amounted to a lot of lost productivity.

So Jane started using her Outlook calendar to track all of her activities; literally, everything she did during the day went in Outlook. Then she wrote some VBA to pull her meetings out of Outlook and enter them in the daily report Excel sheet, assign the correct billing code, make a copy, run monthly tallies, and use Outlook to email it to her supervisor. She reduced it to three button clicks, taking about five minutes a day. After a couple of months, she demoed it to her supervisor and pointed out how using this tool could save the client 31 hours a week, 1,612 hours a year. At an average hourly rate of $90/hour, that would save the client $145,000 that was being spent on reporting on work. That was a significant amount of savings and a major increase in productivity.

The client loved that they could save money and still receive the same granularity in reporting and analysis of work performance. It was important to the agency to know that they weren't being taken advantage of; they'd been burned too many times in the past. And having instantly actionable data when asked about what the team was doing could make the difference in funding decisions at higher levels where budgets were tight.

It wasn't long before Jane was sought out to leverage her VBA skills to solve everyday, repetitive work problems throughout the building. What made VBA so beautiful was that it was installed on nearly every federal computer. That meant no special requests to approve automation software on individual computers, no long acquisition timeline, and no one has to spend more budget than the hours required to discover, design, implement, and iterate a solution.

One request was to automate a daily, 30-plus page report in PowerPoint that was sent to the Deputy Director to report on the progress of one of the Agency's largest IT efforts. The report required multiple JIRA downloads and status reports to be compiled, analyzed, and the slide deck updated using that data. This process took five to six hours a day, every working day of the year. This single report was costing the client an estimated 1,200 hours a year per project, and three projects were submitting the report. That was a lot of money spent on reporting—too much, in Jane's opinion. This task would stretch her skills. How would she automate the population of the PowerPoint slide deck? That seemed like a bridge too far. But VBA was going to surprise her again when she realized she could access almost any Microsoft software installed on the local machines. Instead of trying to update the slide deck, she decided to build a new one for each run of the macro. It took two months of discovery, design, build, implementation, and iteration to get the tool to do what she wanted it to do. By the time they were done, the individual could run the entire report process in 20 minutes. Jane spent about 160 hours building the solution, which saved the agency a little north of 3,000 hours annually.

When Jane told people about her VBA skills, sometimes they scoffed. After all, it was an old language stuck in the Microsoft suite. What value could it really have? Her answer: a lot! As she worked on more and more projects, Jane realized that automation wasn't just a way to make people's jobs easier; it was a way to unlock their potential. By taking the tedious, repetitive tasks off their plates, she was giving them more time and energy to focus on the things they were truly passionate about. It was a small way of making the world a better place, one line of code at a time.

Disclaimer: "CRM Chronicles: Changing Lives and Workflows through VBA Automation" is a work of fiction created to demonstrate the benefits of a CRM system in a creative and entertaining manner. Any resemblance to actual persons, living or dead, or actual events is purely coincidental.

Dynamic Specialties Group is a boutique CRM consulting firm. We support small and medium-sized businesses, nonprofit associations, and the federal government by integrating, automating, and scaling their marketing and sales process using Salesforce, HubSpot, and other cloud-based platforms. We offer Advisory, New Installation, Org Revitalization, and Monthly Support Services.