Excel VBA refers to Visual Basic for Applications programming language. This is a variant of the Visual Basic programming language designed specifically for Office Applications. VBA can be used in Excel, Word, Outlook, Powerpoint and pretty much all the Office suite.
What is VBA used for?
Excel VBA is the language used to record macros and sequences of code that Excel can execute to automate tasks. If you have a repetitive task that you want to automate, the code you would have to write is in VBA.
But don’t worry, you don’t have to become a programmer to use VBA. Excel developed tools that can help with writing the code for macros just by recording your actions. More on that in the next post dedicated to macros.
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.
