If you’re interested in becoming an Excel guru, (or simply showing off) knowing how to construct loops in Excel VBA can be extremely useful. However, you should understand some basic concepts before attempting more complex loops.

In the following example we’ll utilize a basic For… Next… Loop in Excel 2003. The purpose of this tutorial is to introduce you to Excel VBA without putting you in a bored-stupor in the process. Thereafter, if you’re not comatose from boredom, you’ll have the opportunity to review several sources that can help you improve on what you’ve learned. So, if you’re ready to improve your geek status, let’s write a program!

For our program to work, we’ll need to add a Command Button unto our worksheet. In order to add a Command Button onto your worksheet, you’ll need to access the Control Toolbox. To begin, open a blank Excel worksheet. Click on the Excel View menu option, scroll down to the Toolbars, and then click on ‘Control Toolbox’. The Control Toolbox will appear as displayed below:

Click on the Command Button control and drag it onto your worksheet. You can also add a Command Button onto your worksheet by double-clicking on the control as displayed below:

A Command Button will display as follows:

Double click on the CommandButton in order to enter the VBE (Visual Basic Editor) as displayed below:

Enter the following code exactly as it appears below. The code must be entered between the words “Private Sub CommandButton1_Click()”, toward the top of the window, and the words “End Sub”, toward the bottom of the window: (enter the following as it appears)

Dim i As Integer

For i = 1 To 3

MsgBox "Simple as" & " " & i

Next i

After entering the code above, click on the save button within the VBE; give your macro a name and then return to your original worksheet. Click on the “Exit Design Mode” icon on the Control Toolbox, as displayed below. This action will activate your Command button.

Important Note: You must click on the “Exit Design Mode” button in order for your command button to work. After doing so, you can execute your program by clicking on the command button.

(Click on the button to execute the program)

This program will display a message box that contains the words “Simple as” with a number beside it; the integer “i” represents the number. The message will display three times consecutively. Each time that you click on the “OK” button, the message will appear and the integer will increase by 1. Try it for yourself!

Now that you know who to construct a basic For… Next… Loop in Excel, this a good time to offer a few recommendations that can genuinely help you advance your VBA Programming skills.

My first recommendation is a video that demonstrates how to utilize the For… Next… Loop to automatically enter data onto a worksheet (this is known as a worksheet event). The video is produced by an obviously intelligent gentleman named Dave Andrews. I’m unable to steal… I mean insert, the video into this tutorial because the embedding function has been disabled (the nerve of some people). All joking aside, Mr. Andrews does an excellent job of demonstrating the For… Next… Loop in a worksheet event. He utilizes Excel 2007 in his demonstration.

To view the video click on the following link:

How to Create a For… Next… Loop With a Worksheet Event

The TechBookReport, developer book reviews, has published a tutorial entitled “Excel Visual Basic (VBA) - Part 3". The tutorial was created by Pan Pantziarka, and offers an in-depth review of Excel VBA Loops in general. If you’re serious about advancing your VBA Programming skills, I recommend it wholeheartedly.

To view the tutorial click on the following link:

Excel Visual Basic (VBA) - Part 3

Finally, no program of study worth its salt would be complete without a list of noteworthy books (displayed below). The first three titles are books that I actually own and utilize on a continuous basis. The remaining titles all received high remarks from reviewers. I encourage you to read the associate book reviews as each title is designed for readers with diverse levels of experience.

In any case, I hope that this tutorial has been helpful. I welcome your comments or suggestions; Thanks!