Automating Processes in Excel: Part 1 Why RPA is not VBA

by Ryan Mac | Oct 9, 2019 | Automotive Industry, Business Process Management, Business Process Outcomes (BPO), Continuous Process Improvement, Intelligent Process Automation, Robotic Process Automation (RPA)

This is part 1 of a series focusing on using RPA to automate processes in Excel – we’ll cover everything from when and why to use RPA, to details around how to most effectively design the solutions and get the most bang for your buck.

Part 1: Why RPA is not VBA

If you’ve been around the Microsoft Office suite of products for any length of time in a professional setting, then you’ve likely come across “VBA” code. And if you are keeping up even tangentially with business technology news, you can’t ignore the rise of “RPA” and software robots. So, let’s start with some simple definitions.

VBA, or Visual Basic for Applications, is essentially a programming language created specifically for Microsoft Office applications.
RPA, or Robotic Process Automation, is technology that deploys software robots or digital workers to execute high volume repeatable tasks.

You might be accustomed to the typical usage of VBA – macros in Excel that execute a series of steps or functions at the click of a button. You could say that VBA has been allowing users to automate manual tasks within Excel and the MS Office applications for many years. Well, what’s new and different about automation using RPA? The primary answer is versatility. Where VBA is limited in scope to an application like Excel, you can almost think of RPA as a macro that is able to run across any applications with which a human user interacts in a business process. VBA does shine within MS Office products, but RPA can shine across any and all applications.

So, when does it make sense to use each solution to assist in a business process?

VBA might be a good fit if…

The data gathering or input steps need to be manual
Excel is the only application used
Few users are collaborating

You might think of a business process that fits this mold. At Ashling Partners, we have a process to help clients prioritize use cases and potential automation projects based on their ROI. Our template, which we deliver to clients for use in their CoE, contains VBA macros that will sort, filter, and arrange data based on pre-defined criteria. It then adds the data into graphs that can quickly be presented in an executive summary. This is an efficient usage of VBA because all steps of the process are contained within Excel, and it requires manual input of data. We expose the macro via a clickable button for easy use in the workbook.

While RPA might be a better fit if…

The data gathering or input steps could be automated
Excel is only a part of the process and other applications need to be used
Many users are collaborating

You might also think of a business process that fits this mold. A recent example that we’ve encountered involved a supply chain team in a manufacturing plant, which needs to run a series of daily reports from their ERP system, perform some analysis to determine the highest priority inventory items on which to focus, then distribute the report to a broader team who will collaborate on the data. This is right in the wheelhouse for RPA, which is able to gather the data from the source system, add to a template in Excel, perform sorting/filter/analysis as needed, post the report to a central repository, and notify all of the recipients on the team.

Now, the next logical question to address is whether or not RPA and VBA can be used together in a business process. The answer is YES! RPA is intended to perform the same steps as a human actor, so just as a human may click a button in Excel to run a macro, a Bot running an RPA script can also execute VBA code. For example, RPA can be used to gather data from a source system and stage it into an Excel template, then VBA can be used for sorting/filtering/manipulation, and finally RPA takes over to complete the process by publishing the workbook and notifying the relevant parties over email.

One key consideration to mention here, is that RPA is an emerging technology of the future, while VBA has been around for decades and is notoriously brittle. Many organizations have a desire to maintain a single code-base in technology projects, so it follows that they would choose to move as much of the process toward RPA as possible. In fact, we’ve seen countless examples where VBA might execute steps in Excel faster than RPA, but that speed is outweighed by the efficiencies gained from maintaining a single code-base and the process ends up being built exclusively in RPA.

There’re no right or wrong answers, but hopefully we’ve helped clarify the differences between RPA and VBA, when each might be the right solution, and how it might be possible to use the two technologies together to automate business processes.