Show List
Tutorials > VBA > Control structures

Control structures

Control structures are used in VBA to control the flow of program execution. They allow you to perform different actions based on certain conditions or iterate through a set of instructions a specified number of times. The main control structures in VBA are if statements, loops, and other flow control structures.

If statements:

If statements are used to execute certain code blocks based on a condition. The syntax for an if statement in VBA is:

vbnet
Copy code
If condition Then
   ' code block to execute if condition is true
End If

Here's an example of an if statement in VBA:

vbnet
Copy code
Sub Example()
   Dim myNumber As Integer
   myNumber = 10
   If myNumber > 5 Then
      MsgBox "The value of myNumber is greater than 5."
   End If
End Sub

In the above example, we use an if statement to check if the value of myNumber is greater than 5. If the condition is true, we display a message box with the corresponding message.

Loops:

Loops are used to iterate through a set of instructions a specified number of times. There are two main types of loops in VBA: the for loop and the while loop.

For loops:

For loops are used to iterate through a set of instructions a specified number of times. The syntax for a for loop in VBA is:

vbnet
Copy code
For counter = start To end Step increment
   ' code block to execute
Next counter

Here's an example of a for loop in VBA:

vbnet
Copy code
Sub Example()
   Dim i As Integer
   For i = 1 To 10
      MsgBox "The value of i is " & i
   Next i
End Sub

In the above example, we use a for loop to iterate through a set of instructions 10 times. We display a message box with the value of the variable i in each iteration.

While loops:

While loops are used to iterate through a set of instructions as long as a certain condition is true. The syntax for a while loop in VBA is:

vbnet
Copy code
While condition
   ' code block to execute
Wend

Here's an example of a while loop in VBA:

vbnet
Copy code
Sub Example()
   Dim i As Integer
   i = 1
   While i <= 10
      MsgBox "The value of i is " & i
      i = i + 1
   Wend
End Sub

In the above example, we use a while loop to iterate through a set of instructions as long as the value of the variable i is less than or equal to 10. We display a message box with the value of the variable i in each iteration and increment its value by 1.

Other flow control structures:

Other flow control structures in VBA include the select case statement and the exit statement.

Select case statement:

The select case statement is used to execute different code blocks based on different values. The syntax for a select case statement in VBA is:

vbnet
Copy code
Select Case expression
   Case value1
      ' code block to execute if expression is equal to value1
   Case value2
      ' code block to execute if expression is equal to value2
   Case Else
      ' code block to execute if expression is not equal to any of the values
End Select

Here's an example of a select case statement in VBA:

vbnet
Copy code
Sub Example()
   Dim myValue As Integer
   myValue = 2
   Select Case myValue
      Case 1
         MsgBox "The value is 1."
      Case 2
         MsgBox "The value is 2."
      Case Else
         MsgBox "The value is not 1 or 2."
   End Select
End Sub
In the above example, we use a select case statement to execute different code blocks based on the value of the variable `myValue`.

Exit statement:

The exit statement is used to immediately exit a loop or a sub or function. Here's an example of an exit statement in VBA:

vbnet
Copy code

Sub Example()
Dim i As Integer
For i = 1 To 10
If i = 5 Then
Exit For
End If
MsgBox "The value of i is " & i
Next i
End Sub

In the above example, we use an exit statement to immediately exit the for loop when the value of the variable `i` is equal to 5. This means that the loop will only iterate 4 times and display a message box with the value of `i` in each iteration.



Next: Procedures and functions

    Leave a Comment

  • captcha text