VBA While Wend Loop

This tutorial will demonstrate how to use the While Wend Loop in VBA.

Loops, Case Statements and If Statements are an intricate part of all programming languages.  Understanding how these routines work in VBA is essential to mastering VBA correctly.  The While Wend Loop is one of the simplest loops available in VBA and is still included in VBA to make it compatible with older VBA code.  The newer Do and For loops are much more efficient and are preferable to use.

Using the While Wend Loop

The While Wend loop will run while the condition that we are testing for remains TRUE.  As soon as the condition returns FALSE, the loop will stop running.

Sub TestWhileWend()
  Range("A1").Select
  While ActiveCell > 5
    ActiveCell.Font.Color = vbRed
    ActiveCell.Offset(1, 0).Select
  Wend
End Sub

If we were to run this loop starting in cell A1 on the data below, the loop would run until it gets to cell A5, the value of which is not greater than 5 – and therefore the loop would stop – it would not go any further even though the cells from row 6 down are greater than 5 as the loop runs WHILE the condition of the loop is TRUE – when it gets to cell A5, the condition of the loop returns FALSE so the loop stops.

 

Nested While Wend Loop

To add a further condition to the While Wend loop, we can use a Nested Loop.

Sub NestedWhileWend()
  Dim r As Integer
  Dim c As Integer
  r = 1
  c = 2
  Cells(r, 1).Select
  While ActiveCell > 20
     ActiveCell.Font.Color = vbRed
     While Cells(r, c) > 30
      Cells(r, c).Font.Color = vbGreen
      c = c + 1
     Wend
  c = 2
  r = r + 1
  Cells(r, 1).Select
  Wend
End Sub

Running this loop above on the data below will result in the following:

Disadvantages of the While Wend Loop

1. You cannot exit the loop like you can in the Do (Exit Do) or For (Exit For) Loops.
2. You run the risk to creating a perpetual loop if you make a mistake in your code which you might then have to press Ctl+Alt+Delete to exit Excel in order to stop the code from continuing to run!   However this disadvantage is in every type of looping structure if the code is not constructed correctly!