Excel VBA Tutorial - EasyExcelVBA.com

Excel VBA Tutorial Chapter 4: Repeat statements

Goal of this chapter

In this chapter we explain the use of the repeat statements, those statements that execute a piece of code several times in a row, normally until a certain condition is met. The VBA elements we will discuss are:

For … Next statement
Message Box
Input Box
While… Wend statement

For… Next statement

The For… Next statement is based on a “counter” that goes from one value to another with specified size of the steps, executing a certain piece of VBA code for each increment. As always, let's start directly with an example.

The first example we will build shows you explicitly how the counter changes. Open an empty Excel workbook, go to the VBA editor, and create a new module. If you do not know how to do this then have a look first at Chapter 2: How to write a macro. Type the following code into your module:

Option Explicit

Sub ForNextCounter()

  Dim Counter As Integer

  For Counter = 1 To 10 Step 1
      MsgBox ("The value of the counter is " & Counter)
  Next Counter

End Sub

In the macro we first dimension the only variable we use: Counter. Generally you will dimension For… Next counters as the Integer (or Long) data type as typically the steps are in whole numbers (see the paragraph on Variables in Chapter 2 for data types).

Then the For… Next statement is opened, stating that Counter starts at 1 and goes to 10 in steps of 1. So Counter starts at 1, executes the lines between the For and the Next line, is incremented with the Step when the Next line is reached and goes back to the For line. When the value of Counter is more than 10 the For… Next is ended and code execution continues on the line right after the Next line (skipping the lines in between). Note that this means for our example that the value of Counter will be 11 after executing the For…Next.

Here we have stated explicitly that Step is 1. As 1 is the default Step value we could leave out the “Step 1” code. If we wanted steps of 3 we would have had to write “Step 3”.

Also, after Next I wrote the name of the variable that will be incremented, in this case Counter. This is not necessary either, just Next is sufficient. I do have the habit to write the name of the variable that will be incremented, as it will keep the code more readable when it becomes more complex. For example, here there is only one variable but imagine you have 3 variables you are incrementing, one nested within the other (such as dimensions x, y, z). Then it will make the code more transparant to add the name of the variable after the corresponding Next, especially if there is also a long list of complex code within the For... Next.

To execute the code press F5 while you are within the Sub in the VBA editor. What you should see is a Message Box that tells you: “The value of the counter is 1”. When you press enter it changes to 2, 3 etc. until you reach 10 and then the screen goes back to the VBA editor. We will first further discuss the For… Next statement before we will explain more of the Message Box.

We could expand our example to include another fundamental method you will use often in VBA programming: summing. In the next example “SumCounter” we add a variable Sum that sums the numbers of the counter. Write the following code in your VBA editor:

Sub SumCounter()

  Dim Counter As Integer
  Dim Sum As Integer

  For Counter = 1 To 10

      Sum = Counter + Sum
      MsgBox ("The sum of the counter is " & Sum)

  Next

End Sub

Again, first we define the variables that we need for this macro: Counter and Sum. Then we say that Sum is always zero when we start the routine. This is called initialising as we are giving an (fixed) initial value to a variable.

The For… Next statement indicates we will have Counter going from 1 to 10 and each time Sum will add the value of the Counter to its previous value (remember in VBA you assign values from right to left). So firstly the sum adds 1+0 and the first Message Box shows the value of the sum of the counter is 1. Then it adds 2+1 = 3, Message Box shows 3, then 6, 10, 15 etc. Until it gets to 55, which is the result of the final sum in the tenth step. Lastly, notice the For... Next functions the same as before while now I kept it shorter and did not include Step and Counter after Next as discussed above.

Message Box

In this example we have used another new element as we are not referring anywhere to the Excel worksheet to show results. Instead, we apply a “Message Box” to show how the For… Next statement works. The Message Box is a very versatile tool in VBA programming that you can use for all kinds of purposes. To use a simple Message Box like the one we have used here you only need to type what you want it to show, the “Prompt”.

In our case we wanted to see first a fixed text (string), written between quotes, and then a variable, written with no quotes, and the two elements have to be linked with the ampersand (“&”) symbol. You can combine as many variables and strings in the prompt of a Message Box as you like as long as you link them each time with an ampersand.

When you type the MsgBox function you will see many more options, especially on the format of the box and what buttons you want to show. For more details, the Office VBA Reference has a comprehensive explication of the different options available for the MsgBox function.

Figure 3: Message Box example

Input Box

Another VBA element closely linked to the Message Box is the Input Box. Instead of showing a result the Input Box is used to obtain an input value from the user, prompting the user with a input request. We could adapt our example to include an Input Box as follows:

Sub SumCounter()

  Dim Counter As Integer
  Dim Sum As Integer
  Dim MaxNumber As Integer

  Sum = 0
  MaxNumber = InputBox("How many numbers do you want to sum?")

  For Counter = 1 To MaxNumber

      Sum = Counter + Sum
      MsgBox ("The sum of the counter is " & Sum)

  Next

End Sub

In this case we do not fix the number of iterations in the For… Next statement, but let the user choose it in an Input Box. The value obtained in the Input Box is assigned to the variable “MaxNumber”. The result is again shown in a Message Box. When testing be careful not to put a number too high so you will not have to press enter for two weeks. Whenever you get into a repetition you find too long, or in general have some code that seems “stuck” (infinitely executing), you can always stop execution by the combination of the Ctrl+Break keys on Windows and Cmd+Option+Esc to force quit on Mac.

While… Wend statement

Another form of repeat statement is represented here by the While…Wend statement. In contrast with the For…Next the While… Wend statement does not perform a certain piece of code a predefined amount of times, but instead keeps performing the code until a predefined test is met. While the For…Next statement would for example say “perform action x ten times”, the While… Wend statement would for example say “keep performing action x until y=z”.

Continuing our previous coding example, we could ask the macro to keep summing until a certain number is reached. The adjusted version of the code, applying the While… Wend statement would be:

Sub SumCounter()

  Dim Counter As Integer
  Dim Sum As Integer
  Dim MaxNumber As Integer

  Sum = 0
  Counter = 1

  MaxNumber = InputBox("Until what number do you want to sum?")

  While Sum < MaxNumber
      MsgBox ("The sum of the counter is " & Sum)
      Sum = Counter + Sum
      Counter = Counter + 1
  Wend

End Sub

In this case we do not only initialise Sum, but also Counter. MaxNumber still comes from the Input Box. The While states the test that is performed in each iteration. As long as Sum is less than MaxNumber the code between the While and the Wend line is repeated. As Counter is not automatically raised in each iteration of the While… Wend, we have to put that line explicitly within the statement. In this type of repeat statement it is important to note that if we would not properly raise Sum in each iteration it is likely that the macro will execute infinitely as the test is never met (Sum would always be smaller than MaxNumber). As mentioned above, whenever your code does not stop executing press Ctrl+Break to stop execution on Windows or Cmd+Option+Esc to force quit on Mac.