4 Examples as Solution to VBA Continue Statement
As we know, VBA has “Exit For”, “Exit Do” to come out of the loop entirely – which is achieved by the break statement (normally in other programming languages).
How to achieve 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).
However, VBA has no Continue statement – to omit an iteration in the loop rather completely exiting the loop.
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 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 omit an iteration code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 be:
Omitting multiple iterations
Just add more items by using 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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.
