VBA Loops

VBA loop structures are similar to those of other programming languages. Looping is one of the few things that is extremely difficult to achieve with pure Excel alone. An If... Then statement, for instance, can be written within Excel and comparable results can be achieved. Loops, on the other hand, can be a lot trickier, if not impossible.

In general, there are three types of loops:

1. For.. Next loop
2. Do While... Loop
3. For Each... Next loop

For... Next Loop

For... Next loops allow you to repeat a specific block of code a set number of times. An example:

Sub ForNext()

    Dim i As Integer, sum As Integer
    
    sum = 0
    
    For i = 1 To 10
        sum = sum + i
    Next i

End Sub

In the above example, the program goes through an iteration from 1 to 10 and keeps adding the value of i to the sum. In a For... Next loop, the end counter is included.

Do While... Loop

A Do While... Loop can be implemented in one of the following ways:

Do While LogicalExpression
	statements
Loop

Do	
	statements
Loop While LogicalExpression

Do While... Loop is typically used when you don’t know beforehand how many times a loop needs to be executed. A loop can be run While a condition is True or Until a condition becomes True.

Sub GuessTheNumber()

  ' Declare variables
  Dim randomNumber As Integer
  Dim guess As Integer
  Dim attempts As Integer

  ' Generate a random number between 1 and 100
  randomNumber = Int((100 - 1 + 1) * Rnd()) + 1

  ' Initialize variables
  attempts = 0

  ' Do While loop to keep guessing until correct
  Do While guess <> randomNumber
    ' Get user's guess
    guess = InputBox("Guess a number between 1 and 100:", "Guessing Game")

    ' Convert guess to a number (InputBox returns text)
    On Error Resume Next  ' Suppress errors for non-numeric input
    guess = CInt(guess)
    On Error GoTo 0       ' Resume normal error handling

    ' Check for valid input (optional)
    If IsError(guess) Then
      MsgBox "Invalid input! Please enter a number between 1 and 100.", vbExclamation
      Exit Do  ' Exit the loop if input is invalid
    End If

    ' Increment attempt counter
    attempts = attempts + 1

    ' Provide feedback to user
    If guess < randomNumber Then
      MsgBox "Your guess is too low. Try again!", vbInformation
    ElseIf guess > randomNumber Then
      MsgBox "Your guess is too high. Try again!", vbInformation
    End If
  Loop

  ' Display result
  MsgBox "Congratulations! You guessed the number in " & attempts & " attempts.", vbInformation

End Sub

In the above example, we keep executing the code until the user’s guess becomes equal to a the random number chosen by the program initially.

For each... Next loop

For Each... Next is a loop structure that is particular to object-oriented language. This loop iterates for each object in a group of objects. For instance, if you have a spreadsheet that was populated with 10 elements in A1:A10, and you needed to run a loop for each of those elements, this looping format would be useful.

Assume you have a spreadsheet that has the following numeric values in cells A1 through A10: -9, 18, -8, -3, -2, -10, -18, 12, 6, 2. Upon running the following code:

Sub CalculateSum()

  ' Declare variables
  Dim cell As Range
  Dim sum As Double

  ' Define the range to iterate through
  Dim dataRange As Range
  Set dataRange = Range("A1:A10")
  
  ' Initialize sum
  sum = 0

  ' Loop through each cell in the range
  For Each cell In dataRange
    sum = sum + cell.Value
  Next cell

  Range("B1") = sum
  
End Sub

Cell B1 will display a value of -12.

Nested Loops

Sometimes, it is necessary to have one loop “nested” inside another loop. For instance, if we have values in Cells A1 through C5.

Sub CountValueInTable()

  ' Declare variables
  Dim row As Integer
  Dim col As Integer
  Dim count As Long
  Dim test As Integer
  

  ' Set table dimensions
  Dim lastRow As Integer
  lastRow = 5  ' Modify if your table extends beyond row 5

  Dim lastCol As Integer
  lastCol = 3   ' Modify if your table extends beyond column C

  ' Initialize counter
  count = 0

  ' Outer loop for rows
  For row = 1 To lastRow
    ' Inner loop for columns
    For col = 1 To lastCol
        test = Cells(row, col).Value
      ' Check if cell value is equal to 10
      If test = 10 Then
        ' Increment counter
        count = count + 1
      End If
    Next col
  Next row

  ' Display the count
  MsgBox "The number of times the value 10 appears in the table is: " & count, vbInformation

End Sub

Upon running the code, we get a Message Box displaying a value of 3. Here we used nested loops to iterate through the columns in each row.

Exiting a Loop

Often, we are faced with searching for a certain object or property when looping. Once that object is found, we can exit the loop without cycling through the rest of the loops. This can be done using Exit For (for a For... Next loop) or Exit Do (for a Do While... Loop). The Exit statement generally rests within an If statement. For instance:

If CellContents.Value <= 0 Then Exit For

This line will exit the loop if the cell’s value is less than or equal to 0.

Conclusion

Loops are a powerful feature in VBA that can save you a lot of time and effort when automating tasks in Excel. Whether you’re iterating through a range of cells, executing a block of code multiple times, or handling complex data structures, mastering loops is essential. Stay tuned for more tips and tricks to enhance your VBA programming skills. Happy coding!