VBA or Visual Basic for Applications is a programming language that allows extra functionality for the Microsoft Office suite, including Excel, Word and PowerPoint. Some Examples of what you can do with VBA include adding an input form to capture data, customised formulas, and performing various activities when a user does something, such as a custom pop-up when a Word file opens.
This Tutorial aims to gently ease you into VBA, providing an overview of the editor, writing your first program and running it. It will also provide an overview of how to debug your code if you encounter errors. This tutorial will be using Microsoft Excel, however the code and process is pretty much identical across the entire Microsoft Suite.
Accessing the VBA Editor
The VBA Editor is where you write your code. If you have ever recorded a macro and then gone to edit it, you would have see the VBA Editor. There are two ways to access the VBA Editor:
- Via the Developer Toolbar – Click the Visual Basic button. If you don’t see the Developer Toolbar, you can display it via File -> Options
- Keyboard Shortcut – Press ALT + F11 and the VBA Editor will display.
Overview of the Editor
The VBA Editor is divided into three windows:
Creating your First Program
Step 1 in writing your first program is to give it a home. In VBA, we call this a module. To create a module, right click the Project Window and select Insert -> Module.
Now you can write your code. Copy the below into your newly created module and press F5. If all went well, you should have received a popup! Well Done.
Sub helloWorld() MsgBox "Hello World" End Sub
Some points about this program:
- All code that you want to run must be located between Sub / End Sub tags.
- Each Sub must have a unique name, in our case it is helloWorld().
- Each unique name must end in brackets. These brackets will sometimes have stuff inside them… but that is for a later tutorial.
- MsgBox is a VBA function that displays some text.
Debugging your Program
Sometimes, your code just won’t work 🙁 VBA Editor is great at real-time debugging, meaning you can step through each line of code to see what is going on. In order to do this, you need to add a breakpoint in your program so that VBA knows to stop at a certain point. To insert a breakpoint, select the where you want to halt your code and press F9.
When you are ready, execute your programme by pressing F7. Once the code hits the breakpoint, the line will go yellow. You can move to the next line by pressing F8.
Congratulations! You have written and debugged your first program in VBA. This tutorial has provided an overview of how you can get started in programming with VBA. Future tutorials will provide more functionality, such as adding track changes to Excel or automatically sending emails to everyone in a distribution list. If you have any questions, please leave them in the comments.