What is Excel VBA? A Beginner’s Guide to Macros and Automation

Excel VBA refers to Visual Basic for Applications, a programming language that helps automate tasks in Microsoft Excel. VBA is a powerful tool for creating macros and customizing Excel functionalities. Not only does it work in Excel, but it’s also compatible with other Office applications like Word, Outlook, and PowerPoint.

What is VBA used for?

Excel VBA is commonly used to automate repetitive tasks through macros. These macros can simplify tasks like data analysis, generating reports, or modifying large sets of data. If you’re new to VBA, don’t worry—you don’t need to be a programmer to get started, as Excel offers tools to help you record macros without writing code.

How do you access VBA

Before you can access VBA you need to enable one extra tab on your ribbon. This tab is hidden by default because it contains more advanced functions for developers. In order to enable this extra Developer TAB, click on the small down button on the top green bar and select “More Commands”

Go to the “Customize Ribbon” section and click the select button next to the Developer Tab in the Main Tabs list

Once the Developer Tab is added, to access the VBA code in an Excel Worksheet, go to the Developer Tab and click on Visual Basic

This will open an entirely different window with 3 sections called “Microsoft Visual Basic for Applications”

The top left area is reserved for the Project Explorer, the bottom left area is reserved for Properties and the blank area is reserved for code. To start writing code, you have to decide if you write a Macro or a worksheet/workbook code.

Macros

A macro is a function that can be executed anywhere in your worksheet and will be stored in Modules. To create a module, right-click on the Sheet1 or ThisWorksheet and click on Insert – Module. This is not actually required because once you start recording a macro, a module will automatically be created. You do this only if you write code from scratch.

A macro will execute when it is triggered by a button, keyboard shortcut or called in another VBA code.

Worksheet / Workbook VBA code

If you want to create code that is triggered by worksheet or workbook events, you double click on the Sheet 1 or ThisWorkbook and a black window opens. Open the General dorpdown and you’ll see the events that you can write VBA code for.

These are some of the Worksheet events

and these are some of the Workbook events

So you can see that you can write code that triggers when a workbook is opened, closed, before or after save, when a certain worksheet gets activated etc.

For the worksheet you can write VBA code for events like Selection Change, Deactivate, Pivot Table Update etc.

To see an example of a very simple VBA code that refreshes a pivot table automatically when external data is brought in through a connection data refresh. In other words, if you have your Excel connected to an external data source and you refresh the data, your pivot table would refresh using this simple code.

Simple Excel VBA Example: Automatically Refresh Pivot Table

If your worksheet contains a pivot table linked to an external data source, you can automate refreshing it with a simple VBA code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.PivotTables.Count > 0 Then
Sh.PivotTables(1).RefreshTable
End If
End Sub

This code will refresh the pivot table each time data in the worksheet is updated.