Excel VBA Do While Continue

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will demonstrate the duplicate use of the Excel VBA Do While Continue statement. Continue statement allows you to skip all the remaining lines, and statements and move to the next loop iteration. But the problem is VBA doesn’t offer the Continue statement unlike Visual Basic (VB) and most of the programming languages. But there is replicate use of the Continue statement during the use of the Do While loop in Excel VBA.

An Overview image of Excel VBA Do While Continue

Using the GoTo statement instead of the Continue statement inside a Do While loop, we obtain output till the variable meet 5. Once the variable i meets 5, it escapes from the loop and jumps to the newline with the GoTo statement.


Download Practice Workbook

To practice, please download the Excel Workbook file from the link below.


What Is Excel VBA Do While Continue Statement?

A loop is an integral part of computer programming. Do While…Continue syntax is widely used in Visual Basic(VB) and other computer programming languages. In contrast, Excel VBA doesn’t have any Continue statement to use under a loop.

There are 4 types of loops used in VBA.

  1. For Each loop
  2. For Next loop
  3. Do Until loop and
  4. Do While loop

The Continue statement is used in none of these loops. However, there is a by-default built-in statement Goto to replicate the Continue statement in VBA. Like the Continue statement, the Goto statement skips the remaining statements, lines and jumps to a specific line during the execution of the VBA code.

Syntax:

Do while [condition]

………………

Goto[label]

Loop

Note: Do while Continue statement is available in VB and other computer programming languages. The Continue statement is absent in Excel VBA.


Excel VBA Do While Continue: 3 Duplicate Uses

You already know that Excel VBA doesn’t have the Continue statement while using the Do While loop. Nevertheless, we are going to illustrate 3 duplicate practical uses of the Goto statement instead of the use of the Continue statement.

For your convenience, to run VBA code you need to open code window in Excel.


1. Use of Excel VBA Goto Inside Do While Loop

Use of GoTo statement inside a Do While loop

Predominantly, the GoTo statement in Excel VBA skips the remaining calculation and lines like the Continue statement of other computer programming languages. We run a Do While loop for each value individually until it reaches i = 10. Further, using the GoTo statement to jump to the Newline label once we figure out the value of variable i = 5. Thus the code stops executing after delivering output 0 1 2 3 4 in the Immediate window.

🎯Code

Sub Do_While_Goto()
Dim i As Integer    'declaring variable type
i = 0   'Assigning initial value
Do While i <= 10    'Running Loop with condition
    If i = 5 Then GoTo newline  'Jumping to newline once variable meet specific value
        Debug.Print i   'Displaying result
        i = i + 1   'Increment to continue loop
Loop
Exit Sub    'Stopping the code from execution
newline:    'Label where the code will jump once an error occurs
End Sub

Note: Users may also use the MsgBox function to display output in a pop-up message box instead of Debug.Print function.


2. Use Excel VBA Goto Once Error Occurs

Use of the GoTo statement to bypass errors

While running a VBA code, you may face various errors during execution. However, if you intend to run code anyway, you can utilize the GoTo statement to skip the error and after navigating the error return to the loop. As you can see in the above image, here we run a loop for the value of -4 to 4. When i = 0, it is likely 0/0 which is a Run-time error. Showing the error message, we further obtain the output.

🎯Code

Sub Do_While_On_Error_Goto_label()
Dim p As Integer    'declaring variable type
p = -4      'Assigning initial value
Do While p <= 4     'Running Loop with condition
    On Error GoTo Err   'Jumping to Err(Label) once an error occurs
    Debug.Print p / p   'Displaying result
    p = p + 1   'Increment to continue loop
Loop
Exit Sub    'Stopping the code from execution
Err:    'Label where the code will jump once an error occurs
    Debug.Print "Error: " & Err.Description     'Display error type
    Resume Next     'Showing error type, go back to the loop
End Sub

3. Apply Multiple Goto Statements to Jump onto Label

Application of multiple GoTo statements in a sub-procedure

Often we need to jump to a specific line in order to accomplish our job. As you can observe in the above image, we are likely to figure out the value type. Using the Do While loop we catch values from the array. Then we justify the element of each array. If the array type is a string then the GoTo statement allows it to jump to the msg label. Further, with a logical expression containing an IF conditional it returns to the loop to judge the other elements. Therefore, we obtain Exceldemy and 2 as text and number respectively in the Immediate window.

🎯Code

Sub Find_string_type()
'declaring variable type
Dim p As String
Dim num() As String
Dim i As Integer
p = "Exceldemy,2"   'Assigning initial value in variable
num = Split(p, ",")     'Converting into an array
i = 0   'Initialization value
lup:    'Label-1 to run loop to find out whether it is number or text
    Do While i <= UBound(num)   'Loop with condition
    If IsNumeric(num(i)) = False Then   'Logical expression to find text
        GoTo msg    'Jump to msg(Labe-2) once code meets the condition
    Else        'Otherwise display the message with value
        Debug.Print Val(num(i)) & " is a Number"
    Exit Sub    'Stopping the code from execution
    End If
        i = i + 1   'Increment to continue loop
    Loop
msg:        'Label-2, code will jump here once the code meets the condition under Do-while loop.
    Debug.Print num(i) & " is a Text"   'Display the result with a message.
    i = i + 1       'Increment to continue loop
    If i <= UBound(num) Then    'Logical expression to jump to Do While loop again
        GoTo lup
    End If
End Sub

Frequently Asked Questions(FAQs)

  • How do I do a Continue statement in VBA?

No, Excel VBA doesn’t have a Continue statement whereas the Continue statement exists in Visual Basic and most of the computer programming languages. However, you may use the Goto statement to replicate the use of the Continue statement.

  • Can we use Continue statement in VBA do while statement?

No. Continue statement is absent in VBA unlike most of the programming languages while using Do While statement. Syntax: Do While…Goto…Loop

  • Is there a Continue loop for VBA?

A loop’s Continue statement enables you to omit statements of the current loop iteration and propagate them onto the following loop iteration. However, VBA (Visual Basic for Applications) absences an equivalent to a For…Continue statement compared to VB (Visual Basic).


📝 Takeaways from This Article

  • Replicate use of Excel VBA Do While Continue statement.
  • Excel VBA GoTo statement to skip the remaining lines and jump to a specific one.
  • Use of Excel VBA Goto statement inside the Do While loop to figure out the error and continue the loop.
  • Application of multiple GoTo statements with a sub-routine.

Conclusion

We briefly expound on 3 replicate uses of Excel VBA Do While Continue statement. We believe you have learned the practical use of the Do While loop; Further, escaping from error and returning to the loop using the GoTo statement. Any suggestions including queries are appreciated. Don’t hesitate to leave your thoughts in the comment section. For related articles and knowledge, visit our site.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

MD Tanvir Rahman
MD Tanvir Rahman

MD Tanvir Rahman, BUET graduate in Naval Architecture and Marine Engineering, dedicated over a year to the ExcelDemy project. He is an Excel and VBA Content Developer. Having authored 50+ insightful articles, he actively updates and improves over 80 articles, reflecting his commitment to accuracy and currency, managing day-to-day operations, and analyzing and developing Excel and VBA tutorials. His broad interests encompass Data Analysis, Advanced Excel, VBA Macro, Excel Templates, Excel Automation, and Excel Power Query, showcasing a... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo