RPA and VBA Dilemma: Why Do I Need Robotics When I Have Macros?

/ ~ 8 minutes read

“Why do I need RPA when I have macros?”. If you ever thought about implementing Robotics Process Automation and you have basic coding or technical skills, this question should probably arise in your mind quite frequently. Let’s figure out if the answer to it is that simple as it may seem.

In this article, we will draw the line between Excel VBA (namely macros as an integral part of it) and Robotic Process Automation, find the critical differentiating features of them, observe use cases of each, and finally would try to resolve this dilemma.

BA and Macros: Definition and 5 use cases

VBA and Macros: Definition and 5 use cases

Before we start diving into the specifics of 2 terms, let’s address definitions.

What is VBA?

VBA stands for Visual Basic for Applications, the programming language used for Microsoft Office applications. One of the typical uses of VBA code is Excel Macros, whose primary function is the execution of sequences of steps within your Excel spreadsheet with a click of a button. In this post, we will talk about macros, since most of the time RPA functionality is compared to one of the macros. In the core of macros are scripts that are sequences of programming codes used to execute some scenarios or tasks within Excel. Whenever you do some repetitive tasks in Excel, for instance, apply specific formatting to export data, you can write a macro for this task, and run it every time you need to execute it.

Macros: 5 use areas

It is quite challenging to talk about macros without practical examples. Below we provide you with 5 use areas where Excel Macros can be executed:

1. Data processing

Data processing may include daily repetitive tasks of data cleaning and formatting, i.e.:

  • Creation of a list of unique values by deleting data duplicates
  • Selection and cleaning of blank lines
  • Creation of widely used formulas
  • Highlighting the most recently used column, row or cell

2. Tasks related to pivot tables

One of the commonly used functions of Excel is pivot tables. The creation, updating, and maintenance of pivot tables can take hours. Fortunately, macros can automate almost all actions related to pivot tables, i.e., for instance:

  • Formatting data within pivot tables
  • Expansion and collapsing table rows
  • Amendments to date formatting for grouped pivot tables fields
  • Macros for showing details in pivot tables

3. Workbook related tasks

Macros allow the automation of tasks related to workbooks and worksheets. It may include:

  • Sheets listing
  • Creation of table of contents
  • Opening and closing of workbooks
  • Hiding worksheets
  • Saving all workbooks

4. Add-ins and user forms

When you need to simplify your colleagues’ experience using Excel you can apply user forms, i.e., windows with interactive controls, that can open over Excel. Using VB Editor, you can create and amend user forms. 

Add-ins are installed on a user PC allowing him/her to open and run other users’ macros on their workbooks.

5. More complex tasks

More complicated tasks within Excel may also be automated with the use of macros and VBA. It may include building systems for creation, changing, and updating sets of Excel files. 

As you can see, Excel macros can automate a vast number of processes. 

RPA compared with VBA

Robotic Process Automation (RPA) is a technology that allows people to implement programmed robots to emulate humans’ actions within computer systems. RPA is a non-invasive technology, which means that a bot sits on a surface of the system and can execute various actions within multiple applications. Technology enthusiasts call RPA advanced macros since it is similar to macros in its primary purpose of mimicking and automating repetitive mundane tasks usually done by humans; however, it has a significant volume of added functionality. Below we will reveal this functionality in more detail.

So what is the difference between RPA and VBA?

One of the most crucial features distinguishing RPA from VBA is that bots can be used within multiple applications while VBA is used within Microsoft Office Suite. Another thing that makes using VBA challenging within complex processes is that macros cannot automate the gathering and input of data into Excel workbooks. Let’s imagine you need to collect this data from various sources, not from one single list or source. Then the process becomes even more labor intensive.

To have a better vision of all the key distinctive features of RPA and VBA we have prepared a simple comparison table:

comparison table with features of RPA and VBA

Arguably RPA has a number of advantages over VBA. Mostly these advantages are applicable when we deal with processes that stand out of Excel spreadsheets. Nevertheless, it does not mean that using RPA excludes Excel workflow and vice versa.  

RPA and VBA: 3 possible scenarios 

Depending on various business situations, you can choose what technology is the best fit for it.

Let’s picture 3 business processes that illustrate the perfect scenario for the use of RPA, VBA or 2 of them combined.

1. VBA: Sorting, filtering and arranging data within an Excel spreadsheet

Scenario: A marketing department of a mid-size food retail company keeps the marketing data in an Excel file that contains macros allowing to sort, filter, and distribute marketing metrics based on specific criteria. After processing data, the macros put data into diagrams that can easily reflect current metrics and dynamics over time.

Why VBA: in this scenario, macros is used, since all initial data are manually collected and updated in a single Excel workbook. 

2. RPA: Processing data from various sources

Scenario: A supply department of a large-size company needs to create a bunch of daily reports which involves analysis of data kept in their ERP system and determining the set of inventory items based on specific criteria, then communicating it to the central office to process the data and make strategic planning on these inventory items.

Why RPA: RPA bots are used as the data input and output is executed from various sources: RPA bot collects all the data related to inventory items, inputs the data into excel spreadsheet, sort and analyzes it, creates and sends a report to the central office and notifies all the stakeholders by email. 

3. RPA and VBA: what about the mix?

In the example above where a bot represents all the actions, we can assign the steps related to the Excel spreadsheet to macros. Then the process will look like this: 

The bot collects the data from the Enterprise Resource Planning system, puts it into the excel spreadsheet, then the VBA code is called on, and the data is manipulated with the use of macros. The final stages (publication and notification) would be executed by the bot again. The 2 scenarios slightly vary, but it should be noted that instead of a human calling on Excel macros, the bot will be used. It means the process can still be perceived as RPA, although there is partial use of VBA within it. 

RPA is simply not VBA: Why the fuss?

RPA is simply not VBA: Why the fuss?

In this article we have explained VBA and RPA terms, detected differences between the 2 tools, and have given you practical examples of their use.

There is no denying that RPA has much in common with VBA. However, in a nutshell, Robotic Process Automation has gone much ahead of macros. 

The advancement of RPA over VBA can be exemplified on various levels, beginning from functionality, when by using, for instance, Optical Character Recognition you can automate all your paper workflow, and ending with such tools as Orchestration or Studio, which allows you to meticulously orchestrate all automation steps with no IT involvement.

For that reason, the choice is not that tough. All you need to do is evaluate your current business processes and decide whether you keep using Excel for all your operations, or you are ready to walk the path of the promising technology that has already proven its efficiency to thousands of businesses.

If you are leaning towards the second option, then hurry up to book a demonstration package today and start innovating right now with Electroneek!

Other News

Get our weekly newsletter for the latest insights