VBA While Wend Loop

Last Updated on January 11, 2023 by token

While Wend loop – introduction

The VBA While Wend loop is the simplest in construction loop available in VBA. If your code need to be short, not complicated and have a simple structure, it is recommended to use this loop instead of the Do…Loop and For…Next Loop. It gives You similar possibilities. Below is described the simplest example of a loop displaying a loop counter until it reaches a value of 5.

The disadvantage of the While … loop Wend is not possibleexit from the loop before its completion. For example loops Do … Loop and For … Next give us this opportunity.

Example of using the While Wend Loop

Let’s do an example. Let’s paste the data from the table below into the Excel worksheet, starting with cell “A1”.

Let’s write a simple macro using a loop. Our task will be to fill the last column with the value of increase, decrease or no changes depending on the +/- sign in the “C” column. Negative values ​​will inform about the decrease.

Sub WhileWendExample()

Dim intCounter As Integer
intCounter = 2

While intCounter <= 6

 Select Case Sgn(Cells(intCounter, 3))
 Case -1
 Cells(intCounter, 4) = "Increase"
 Case 1
 Cells(intCounter, 4) = "Decrease"
 Case Else
 Cells(intCounter, 4) = "No changes"
 End Select
 
 intCounter = intCounter + 1
Wend
End Sub

As you see, the loop counter begins with the value 2, that means from the second row. The plus or minus sign has been checked using the VBA SGN function. This function returns a value from the set -1/0/1 depending on the character of the argument. 

The assignment instruction is in Select CASE statement. Of course, you can do a similar example using If … Then … Else. By put the code intCounter = intCounter + 1, you increase the loop counter by 1. It means that go to the next row.

Quick Tasks (You can enter the solution in a comment)

1. Modify the example from this chapter using the VBA If Then Else statement .
2. Modify the example in such a way that the result Increase / Drop / no change additionally fills the cell with the color eg green, red, yellow. I described the formatting of cells, fonts and ranges in this part of the training.