While Wend loop – introduction
The While Wend loop is the simplest in its construction loop available in VBA. If our code is to be short, uncomplicated and have a simple structure, it is recommended to use this loop instead of the Do … Loop loop. It gives us similar possibilities. Below is 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.
An example of using the While … loop. Wend
Let’s take a brief 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 change depending on the +/- sign in the “C” column. Negative values will here inform about the decrease.
Dim intCounter As Integer
intCounter = 2
While intCounter <= 6
Select Case Sgn(Cells(intCounter, 3))
Cells(intCounter, 4) = "Spadek"
Cells(intCounter, 4) = "Wzrost"
Cells(intCounter, 4) = "brak zmian"
intCounter = intCounter + 1
As you can see, the loop counter begins with the value 2, that is from the second row. The plus or minus sign has been checked using the 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 our case Select CASE . Of course, we can do a similar example using If … Then … Else . By blocking the code intCounter = intCounter + 1, we increase the loop count by 1 by going to the next rows.
Quick Tasks (You can enter the solution in a comment)
- Modify an example from a chapter using the 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.