How to achieve the ‘Continue’ Statement Purpose in VBA?
As we know, VBA has “Exit For”, and “Exit Do” to come out of the loop entirely – which is achieved by the break statement (normally in other programming languages).
So, how we can achieve this? The topic of our tutorial.
First Solution – Using an If statement in the For loop
Generally, the continue statement is placed inside the for loop (in any language where it is available).
We are also using the VBA If statement and testing the current value.
See how we will omit the current iteration while the loop still goes on until the condition is False.
VBA code to omit an iteration
Sub for_continue() Dim x As Integer Dim curr_val For x = 1 To 10 Step 1 If x = 4 Then 'Do Nothing Else curr_val = curr_val & vbNewLine & x End If Next x MsgBox curr_val End Sub
Output:
You can see, 4 is not displayed in the MsgBox.
If we have not used the If..Else in the above example, the output should have been:
Omitting multiple iterations
Just add more items by using the Or operator in the If Statement and you may omit more iterations.
For example, we will omit 3, 5, and 7 values in our above example.
See the code and output below:
Code:
Sub for_continue() Dim x As Integer Dim curr_val For x = 1 To 10 Step 1 'Omit multiple iterations If x = 3 Or x = 5 Or x = 7 Then 'Do Nothing Else curr_val = curr_val & vbNewLine & x End If Next x MsgBox curr_val End Sub
Output:
VBA “Continue” with Array example
- Similarly, you may omit the iteration(s) as working with For..Each and VBA arrays.
- In the example below, we have five array elements.
- The array contains fruits’ names.
- Normally, it will display the array elements as below:
Code:
Sub for_continue() Dim Fruits(1 To 5) As String Dim concat_str As String Dim curr_item As Variant 'Array Elements Fruits(1) = "Apple" Fruits(2) = "Mango" Fruits(3) = "Banana" Fruits(4) = "Peach" Fruits(5) = "strawberry" 'Omit values like continue does For Each curr_item In Fruits concat_str = concat_str & curr_item & ", " Next curr_item MsgBox concat_str End Sub
Output:
To demonstrate “Continue”, we will omit two fruit names by using If..Else statements:
Code:
Sub for_continue() Dim Fruits(1 To 5) As String Dim concat_str As String Dim curr_item As Variant 'Array Elements Fruits(1) = "Apple" Fruits(2) = "Mango" Fruits(3) = "Banana" Fruits(4) = "Peach" Fruits(5) = "strawberry" 'Omit values like continue does For Each curr_item In Fruits If curr_item = "Apple" Or curr_item = "Peach" Then 'Do Nothing Else concat_str = concat_str & curr_item & ", " End If Next curr_item MsgBox concat_str End Sub
Output:
You can see, the two fruit names are not displayed in the message box.
