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 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).

However, VBA has no Continue statement  - to omit an iteration in the loop rather than 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 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:

VBA-continue

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 has been:

VBA-simple-For

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-multiple

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:

VBA-continue-array

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:

VBA-continue-omit-arra

You can see, the two fruit names are not displayed in the message box.