Excel VBA For: A Step-by-step Tutorial on How to Use the For Loop

Excel VBA is one of the reasons why Excel is one of the most powerful spreadsheet applications available today. This tutorial will show you how to use the Excel VBA “For” loop to calculate sales commissions for a fictitious sales company, depending on whether each salesperson has hit their sales target. This tutorial assumes that you have some basic Excel knowledge. To learn how to create a worksheet, work with the various toolbars available in Excel, and to learn the other basics of Excel, sign up for the Microsoft Excel 2013 Training Tutorial for Udemy.

Excel VBA is Visual Basic for Applications and you can access VBA functions via the VBA editor.. This versatile editor allows you to use visual basic functions within Excel to create powerful macros. To access the VBA editor, you need to first ensure that your worksheet is saved as a macro-enabled workbook.

Here is the sample data we will use in this step-by-step tutorial. To follow this tutorial, create your own workbook and then save the workbook as a macro-enabled workbook.

We will create a macro to calculate the commission due for each salesperson using the VBA “For” loop as well as an “IF” function to ensure that the salesperson only receives commission if they reach their target sales amount.

How to Access the VBA Editor

You can either access the VBA editor directly using the Developer function from the menu and then you need to click on Visual Basic.

The visual basic editor will open up in a new window so that you can write your VBA application for your worksheet.

You can also access the Visual Basic Editor by selecting an ActiveX control to insert into your worksheet. For this tutorial, we will add a button that will allow us to calculate the commissions due.

To add the button to your worksheet, select the Developer tab and then select Insert. Insert an ActiveX command button and then draw the button onto your worksheet.

Double click the button to access the VBA code for that button and the VBA editor will open:

First we are going to rename the button to “Calculate Commissions.” To access a VBA or ActiveX control’s properties, in the VBA editor select caption and add “Calculate Commissions”

Now we need to add the “For” loop to our application. To add a “For” loop, you need to know how the for loop works. The syntax for the “For” loop is as follows:

For counter = start_counter To end_counter

    ‘Do something here

Next counter

The “For” loop uses a counter to track how many times it has performed a function. The loop starts and tests a condition. If the condition is false, the loop performs the functions specified within the loop. The counter is then increased by one. The program test the condition again, if the condition is false it repeats these steps. If the condition is true the program exits the for loop and moves onto the next set of instructions.

Here is a visual representation of how the “For” loop works:

 

For our spreadsheet, we need VBA to check each salespersons sales and for each salesperson, VBA must calculate commission. The “For” loop uses a counter so we need to first declare that counter as a variable in our VBA code.

To learn how to create your own VBA applications in Excel and how to declare variables for your applications, sign up for the Excel VBA and Macros with MrExcel from Udemy.

To declare the variable in our application, we will use “j” as our counter. We need the counter to count so we declare “j” as an integer.

You need to type the following into your editor:

Dim j As Integer

Now we need to add the “For” loop to our program. Using the syntax of the “For” loop given above, type the following into your editor:

For j = 3 To 12

The counter counts from 3 to 12, because our sales totals start from the 3rd row and there are ten salespeople involved in the sales so this “For” loop will perform the instructions we give it for all of our salespeople.

Next we have to tell Excel what steps it needs to repeat for each salesperson. We want to calculate the commission due for each sales person. To calculate the commission we need to get the value of their sales from our worksheet. Then we need to use that value to calculate the commission due. And finally, we need to place that value in the commission column for each salesperson.

Take a look at the formula we will use:

salesTotal = Cells(j, 3).Value

commi = salesTotal * 10 / 100

Cells(j, 5) = commi

Finally, we need Excel to repeat this for each salesperson so we need to increment the counter each time the step is performed. So we type in:

Next j

The output of the above will look like this:

Make sure you deselect the “Design Mode” from the developer tab to use the button you have created.

The VBA application in our editor looks like this:

 We do however have to add a statement to only calculate commissions for salespeople who reached the sales target. For a tutorial on how to use the “IF ELSE” statement within VBA, you can read VBA IF ELSE Statements for Powerful Microsoft Applications from the Udemy blog. For an advanced Excel course that will teach you how to harness the power of macros within your spreadsheets, sign up for the VBA – Introduction to VBA Macros course from Udemy.

 We therefore need to add the following piece of code to our VBA editor:

If salesTotal > targetTotal Then

Else

               Cells(j, 5) = 0

End If

 

The completed VBA editor code will look like this:

And the resultant worksheet looks like this:

You can see from the results above, the VBA macro goes through each salesperson using the “For” loop and it then checks if the salespersons sales are greater than the sales target. If the sales are greater than the target, then the VBA macro calculates the commission due. If the sales not less than the target then the VBA macro assigns a value of zero to the commission due.

To learn to apply these powerful macros and functions in your worksheets at work, sign up for the Advanced Excel for Professionals course today to learn valuable time saving tricks as well as how to use the various other features of Excel like MS Reporting, Data Cleaning and Data Analysis techniques.