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.
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”.
|Stock index||Turnover in millions $||Change||Increase / decrease|
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)
- Modify the example from this chapter using the VBA If Then Else statement .
- 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.