Introduction

Overview

In this guide we introduce the reader to VBA, and how it can be used to build user defined functions (UDFs) to achieve low-level data and/or task automation.

About VBA

Visual Basic for Applications, or VBA for short, is an implementation of Microsoft’s event-driven implementation Visual Basic 6. Ever since it was first introduced in 1993, it has been very popular for achieving automation in Excel, allowing business users to create millions of macros with it. This still holds true, even today!

VBA alternatives

Popularity in VBA, however, has been dropping, since its peak in 2004 - as other, more modern alternatives have emerged that can allow business users to create processes that are more secure and scalable.

For example:

• Importing data using Power Query (Get & Transform) instead, where code is more sustainable, and does not rely as much on the original developer.
• Automation in Excel Online with Office Scripts (TypeScript)
• Gathering data with Power Apps
• Automation of other Office 365 applications, such as Outlook, using Power Automate
• Introduction of new Excel Dynamic Arrays, and formulae such as LET, and LAMBDA

VBA is still alive!

The above said, VBA still has its place in the office! A major reason why VBA is being used so frequently is that it does not require resources from an IT department. Providing training or low-code resources (such as PowerApps) can be timely or costly, whereas business users can improve efficiency relatively easily with VBA.

Also, other technologies such as Office Script, as still relatively nascent, and do not record events; meaning that Office Script cannot determine which cell is currently selected. In VBA, event driven programming, where actions can be triggered from the selection of a particular cell, is very possible.

In the rest of this guide, we go through some practical examples of how VBA can be used to help with:

• Improving Data integration tasks between databases and local excel tables, e.g. with User forms
• Importing live or near real-time data from external online datasets
• Automating workbook tasks and calculations
• Creating advanced custom functions
• Achieving better workbook navigation

Click ahead to find out more!