The VBA programming platform that runs through almost all Microsoft Office products is one of the most powerful tools one can use to improve the use of those products.
This VBA beginner’s guide will show you how to add the Developer menu to your Office application, how to open the VBA editor window, and how basic VBA statements and loops work so that you can get started with VBA in Excel, Word, Powerpoint, Outlook and OneNote.
This VBA guide uses the latest version of Microsoft Office products. If you have an earlier version, you may see some minor differences from the screenshots.
How to enable and use the VBA editor
In any of the Office products used in this guide, you may notice that you do not have the referenced developer menu. The developer menu is only available in Excel, Word, Outlook, and Powerpoint. OneNote does not provide a tool to edit VBA code from within the application, but you can still refer to the OneNote API to interact with OneNote from other Office programs.
You’ll learn how to do this in our upcoming Advanced VBA guide.
- To enable the developer menu in an office product, select the File menu and select Options from the left navigation menu.
- You will see a pop-up menu with options. Select Customize ribbon from the left navigation menu.
The list on the left contains all the available menus and menu commands available in that Office application. The list on the right is the ones that are currently available or activated.
- You should see Developer in the list on the right, but it won’t activate. Just select the checkbox to activate the Developer menu.
- if you don’t see it Developer available on the right, then change the left Choose assignments from dropdown to All assignments† Find Developer from the list and select Add>> in the center to add that menu to the ribbon.
- Select Okay when you’re done.
- Once the developer menu is active, you can go back to your main application window and select: Developer from the main menu.
- Then select View code from the Controls group on the ribbon to open the VBA editor window.
- This will open the VBA editor window where you can type the code that you will learn in the following sections.
- Try adding the Developer menu to some of the Office applications you use every day. Once you are comfortable with opening the VBA editor window, move on to the next section of this guide.
General VBA Programming Tips for Beginners
You will notice that when the VBA editor opens, the navigation options in the left pane look different depending on the Office application.
This is because the available objects where you can place VBA code depend on what objects are in the application. For example, in Excel, you can add VBA code to workbook or worksheet objects. In Word, you can add VBA code to documents. In Powerpoint, for modules only.
So don’t be surprised by the different menus. The structure and syntax of the VBA code is the same in all applications. The only difference is the objects you can reference and the actions you can take on those objects through the VBA code.
Before we dive into the various objects and actions you can take on them via VBA code, let’s first look at the most common VBA structure and syntax that you can use when writing VBA code.
Where to put VBA code
When you are in the VBA editor, you have to use the two drop-down lists at the top of the editing window to choose which object you want the code to associate with and when you want the code to run.
For example in Excel, if you choose: worksheet and Activatethe code is executed when the worksheet is opened.
Other worksheet actions you can use to trigger your VBA code include when the worksheet changes, when it is closed (deactivated), when the worksheet calculation is performed, and more.
When adding VBA code in the editor, always make sure that you put your VBA code on the object and use the correct action you want to use to trigger that code.
VBA IF statements
An IF statement works in VBA just like it works in any other programming language.
The first part of the IF statement checks whether a condition or set of conditions is true. These conditions can be joined by an AND or OR operator to link them together.
An example would be to check whether a grade in a spreadsheet is higher or lower than a “pass” grade, and assign the pass or fail status to another cell.
If Cells (2, 2) > 75 Then Cells (2, 3) = “Success” Else Cells (2, 3) = “Failed”
If you don’t want the entire statement on one line, you can split it into multiple lines by adding a “_” symbol at the end of the lines.
If Cells (2, 2) > 75 Then _
Cells(2,3) = “Passed” Else _
Cells (2, 3) = “Failed”
Using this technique can often make code much easier to read and debug.
VBA for following loops
IF statements are great for some comparisons, like the example above of looking at a single cell. But what if you want to loop through a whole series of cells and do the same IF statement on every cell?
In this case you need a FOR loop.
To do this, you need to use the length of a range and iterate through that length by the number of rows that contain data.
To do this, you need to define and cycle through the range and cell variables. You also need to define a counter so that you can output the results to the correct row. So your VBA code would have this line first.
Dim rng As Range, cell As Range
Dim Row Integer Counter
Define the range size as follows.
Set rng = Range(“B2:B7”)
rowCounter = 2
Finally, you can create your FOR loop to step through each cell in that range and make the comparison.
For Each cell In rng If cell.Value > 75 Then _ Cells(rowCounter, 3) = "Pass" Else _ Cells(rowCounter, 3) = "Fail" rowCounter = rowCounter + 1 Next cell
Once this VBA script is executed, you will see the results in the actual spreadsheet.
VBA While loops
A While loop also loops through a series of statements, just like the FOR loop, but the condition that the loop must continue is a condition that stays true.
For example, you can write the same FOR loop above as a WHILE loop, by simply using the rowCounter variable as follows.
While rowCounter < rng.Count + 2 If Cells(rowCounter, 2) > 75 Then _ Cells(rowCounter, 3) = "Pass" Else _ Cells(rowCounter, 3) = "Fail" rowCounter = rowCounter + 1 Wend
Notice rng.tel + 2 termination limit is required because the row counter starts at 2 and must end at row 7 where the data ends. However, the count of the range (B2:B7) is only 6 and the While loop does not end until the counter is LARGER than the counter – so the last rowCounter value must be 8 (or rng.Count + 2).
You can also set the While loop as follows:
While rowCounter <= rng.Count + 1
You can only increase the range count (6) by 1, because once the rowCounter variable reaches the end of the data (row 7), the loop can end.
VBA Do While and Do Until Loops
Do While and Do Until loops are almost identical to While loops, but work slightly differently.
- The loop loop checks if a condition is true at the beginning of the loop.
- The do-while-loop checks if a condition is true after executing the statements in the loop.
- The Do-to-loop checks if a condition is still false after executing the loop.
In this case, you would rewrite the While loop above as a Do-While loop as follows.
Do If Cells(rowCounter, 2) > 75 Then _ Cells(rowCounter, 3) = "Pass" Else _ Cells(rowCounter, 3) = "Fail" rowCounter = rowCounter + 1 Loop While rowCounter < rng.Count + 2
In this case, the logic doesn’t change much, but if you want to make sure the logic comparison takes place after all the statements have been executed (allowing for all of them to be executed at least once, no matter what), then a Do-While or Do -Until loop is the right option.
VBA Select Case Statements
The last type of logical statement that you need to understand to structure your VBA code is Select Case statements.
Given the example above, let’s say you want to have a grading method that isn’t just passed. Instead, you want to assign a letter number from A through F.
You can do this with the following Select Case statement:
For Each cell In rng Select Case cell Case 95 To 100 Cells(rowCounter, 3) = "A" Case 85 To 94 Cells(rowCounter, 3) = "B" Case 75 To 84 Cells(rowCounter, 3) = "C" Case 65 To 74 Cells(rowCounter, 3) = "D" Case 0 To 64 Cells(rowCounter, 3) = "F" End Select rowCounter = rowCounter + 1 Next cell
The resulting spreadsheet after running this VBA script will look like this.
Now you know everything you need to know to start using VBA in your Microsoft Office applications.