5000+ Great Articles

The Best VBA Guide (For Beginners) You’ll Ever Need

bilal 1983

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.

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.

– /

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 75 Then _ Cells (rowCounter, 3) = “Pass” Else _ Cells (rowCounter, 3) = “Fail” rowCounter = rowCounter + 1 Wend

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.

Exit mobile version