Control Structures (If statements, loops) in Excel VBA

Introduction

In Visual Basic for Applications (VBA), control structures are used to control the flow of your program. Control structures include If statements, loops, and other conditional statements. In this guide, we will walk you through the different types of control structures in VBA.

Prerequisites

Before you begin, make sure that you have Microsoft Excel installed on your computer. You will also need a basic understanding of programming concepts.

If Statements

If statements are used to test if expressions are TRUE or FALSE, running different code based on the results. Here’s an example:

  If Range("A1").Value > 0 Then
    Range("B1").Value = "Positive"
ElseIf Range("A1").Value < 0 Then
    Range("B1").Value = "Negative"
Else
    Range("B1").Value = "Zero"
End If

This tests if the value in Range A1 is greater than 0. If so, setting Range B1 equal to “Positive”. If the value is less than 0, setting Range B1 equal to “Negative”. If the value is zero, setting Range B1 equal to “Zero”.

Loops

Loops are used to repeat a block of code until a certain condition is met. There are two types of loops in VBA: For loops and Do loops.

For Loops

For loops are used to repeat a block of code a specific number of times. Here’s an example:

  For i = 1 To 10
    Cells(i, 1).Value = i
Next i

Sub ForEachLoopExample()
    Dim myArray As Variant
    Dim element As Variant
    
    myArray = Array("Apple", "Banana", "Orange")
    
    For Each element In myArray
        MsgBox element
    Next element
End Sub

This code will write the numbers 1 through 10 in column A.

Do Loops

Do loops are used to repeat a block of code until a certain condition is met. Here’s an example:

  Do While ActiveCell.Value <> ""
    ActiveCell.Offset(1, 0).Select
Loop

Sub DoWhileLoopExample()
    Dim i As Integer
    
    i = 1
    Do While i <= 5
        MsgBox i
        i = i + 1
    Loop
End Sub

Sub DoUntilLoopExample()
    Dim i As Integer
    
    i = 1
    Do Until i > 5
        MsgBox i
        i = i + 1
    Loop
End Sub

Conclusion

Understanding control structures is essential for anyone looking to automate tasks or perform complex calculations using VBA code.

Previous Next

MS Excel VBA