The VBA programming platform, which works in almost all Microsoft Office products, is one of the most powerful tools anyone can use to improve the use of these products.
This VBA Beginner’s Guide will show you how to add a developer menu to an Office application, how to enter the VBA editor window, and how basic VBA statements and loops work so you can get started using VBA in Excel, Word, Powerpoint, Outlook, and OneNote.
This VBA tutorial uses the latest version of Microsoft Office products. If you have an older version, you may see slight differences from the screenshots.
How to enable and use the VBA Editor
You may notice that in any of the Office products used in this tutorial, you do not have the specified developer menu. The developer menu is only available in Excel, Word, Outlook and Powerpoint. OneNote does not offer a tool for editing VBA code from within an application, but you can still reference the OneNote API to interact with OneNote from other Office programs.
You will learn how to do this in our upcoming advanced VBA tutorial.
- To enable the developer menu in any office product, select the File menu and select Options from the left navigation menu.
- You will see the Options pop-up menu. From the left navigation menu select Customize Ribbon.
The list on the left shows 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 the Developer listed on the right, but will not be activated. Just check the box to activate the developer menu.
- If you don’t see the developer available on the right, change the left option Select commands from the dropdown to All commands. Find Developer in the list and select Add >> Center to add this menu to the ribbon.
- Click OK when done.
- When the Developer menu is active, you can return to the main application window and select Developer from the top menu.
- Then select View Code from the Controls group on the Ribbon to open the VBA Editor window.
- This will open a VBA editor window, where you can enter the code that you will explore in the next few sections.
- Try adding a developer menu to a few Office apps you use every day. When you’re comfortable with opening the VBA editor window, skip to the next section of this tutorial.
General VBA Programming Tips for Beginners
You will notice that when you open the VBA editor, the navigation options in the left pane differ from one Office application to the next.
This is because the available objects that you can put VBA code in 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 at the different menus. The structure and syntax of VBA code is the same for all applications. The only difference is the objects you can reference and the actions you can take on those objects using VBA code.
Before we dive into the various objects and the actions you can perform on them with VBA code, let’s first take a look at the most common VBA structure and syntax that you can use when writing VBA code.
Where to put the VBA code.
When you are in the VBA editor, you need to use the two dropdowns at the top of the edit window to select which object you want to attach the code to and when you want the code to run.
For example, in Excel, if you select Worksheet and Activate, the code will run whenever the worksheet is opened.
Other worksheet actions you can use to run VBA code include: when the worksheet is modified, when it is closed (deactivated), when the worksheet is calculated, etc.
When you add VBA code to the editor, always place the VBA code on the object and use the correct action that you want to use to run that code.
IF VBA statements
The IF statement works in VBA just like it does in any other programming language.
The first part of the IF statement checks if a condition or set of conditions is true. These conditions can be combined with the AND or OR operator to link them together.
One example would be to check if a grade in a spreadsheet is above or below a “passing†grade and assign a pass or fail status to another cell.
If Cells (2, 2)> 75, then Cells (2, 3) = “Pass”, Else Cells (2, 3) = “Fail”
If you don’t want the entire statement to be on one line, you can split it over multiple lines by adding the “_” character at the end of the lines.
If Cells (2, 2)> 75, then _
Cells (2, 3) = “Pass”, otherwise _
Cells (2, 3) = “Fail”
Using this technique can often make your code much easier to read and debug.
VBA For Next Loops
IF statements are great for single comparisons, as in the single cell view example above. But what if you want to loop over the entire range of cells and execute the same IF statement for each one?
In this case, you need a FOR loop.
To do this, you will need to use the length of the range and iterate over that length by the number of lines containing the data.
To do this, you need to define the range variables and cells and loop through them. You also need to define a counter so that you can output the results to the appropriate line. So your VBA code will have this line first.
Dim rng As Range, cell As Range
Dim rowCounter as Integer
Determine the size of the range as follows.
Set rng = Range (“B2: B7”)
rowCounter = 2
Finally, you can create a FOR loop to loop through each cell in that range and perform a 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
After running this VBA script, you will see the results in a real spreadsheet.
Loops while in VBA
The While Loop also loops through a series of statements just like a FOR loop, but the condition to continue the loop is a condition that remains true.
For example, you can write the same FOR loop above as the WHILE loop by simply using the rowCounter variable as follows.
While rowCounter
Note. The completion constraint rng.Count + 2 is required because the row count starts at 2 and must end on line 7 where the data ends. However, the range counter (B2: B7) is only 6, and the While loop will only end when the counter becomes MORE than the counter, so the last rowCounter value must be 8 (or rng.Count + 2).
You can also customize the While Loop as follows:
While rowCounter <= rng.Count + 1
You can only increment the counter of range (6) by 1, because when the rowCounter variable reaches the end of the data (line 7), the loop might end.
VBA Do While and Do While Loops
Bye and Bye loops are almost identical to Bye loops, but they work a little differently.
- The Bye Loop checks if a condition is met at the beginning of the loop.
- The Do-While Loop checks if a condition is true after executing statements in the loop.
- The Do-until loop checks to see if the condition remains false after the loop has completed.
In this case, you could rewrite the above While Loop as follows as a Do-While Loop.
Do If Cells (rowCounter, 2)> 75 Then _ Cells (rowCounter, 3) = “Pass” Else _ Cells (rowCounter, 3) = “Fail” rowCounter = rowCounter + 1 Loop while rowCounter In this case, the logic doesn’t change much, but if you want to make sure that the boolean comparison happens after all statements have been run (allowing them all to run no matter what at least once), then Do-While or Do-until loop is correct option. The last type of logical operator you need to understand in order to start structuring your VBA code is the Select Case statements. In the example above, let’s say you want to have an assessment method that does not only allow passing an exam. Instead, you want to assign a letter grade from A to 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 table after running this VBA script looks like the following. Now you know everything you need to know to start using VBA in your Microsoft Office applications. – VBA Select Case Statements
