Can You Use Continue in a While Loop
Contents
- Loops in VBA
- The For Loop in VBA
- Syntax:
- Simple Examples of a For Loop
- Print a Mathematical Table for a Number
- Program to Print Contents of an Array
- Program to Write and Read Array Contents
- Program That Uses the [step] Value
- The Nested "For" Loop
- Structure
- A Few Examples of Nested For Loops
- Program to Format Cells with a Specific Word
- Program with 2D Array and "NESTED FOR" Loop
- The "Continue" Statement in Visual Basic: Skip a Part of an Iteration
- Continue
- Continue Statement is Not Available in VBA
- An Alternative to the Continue Statement
- Conclusion
Loops in VBA
Loops are an essential part of any programming language because they help with the repeated running of any code snippet. Just like any other language, VBA offers the following loops to enable efficient programming:
- Do until
- Do while
- For
- Foreach
In this article we will dive dep into the "for" loop which iterates only for "x" number of times where "x" is a known value before the loop's runtime.
"X" can also be a predictable number at runtime, for example when you determine the Lbound and Ubound values of arrays.
The For Loop in VBA
In VBA, "for loop" is easy to use. It follows a simple and understandable syntax.
Syntax:
For <counter> = <start> to <end> [ <step value> ]
[ <code block to execute> ]
Next [ <counter> ]
where
<counter>
is the iterator variable which keeps incrementing by the <step value>
after every iteration.
<step value>
is optional. If this value is not provided, the default incremental value is "1."
<Code block to execute>
is also optional. It is the snippet of code to be executed during every iteration.
The "Next" statement marks the end of the "for" loop. It indicates to move to the next iteration of this loop with the incremented counter value.
Simple Examples of a For Loop
Print a Mathematical Table for a Number
Sub forloop_demo() ' declare all the required variables Dim counter, input_number As Integer ' Receive input from user input_number = InputBox(" Which tables do you want to print ? ") ' Print the mathematical table of the input number upto 12 count For counter = 1 To 12 Debug.Print (counter &amp;amp; " X " &amp;amp; input_number &amp;amp; " = " &amp;amp; counter * input_number) Next End Sub
This program receives a number as an input from the user. Then, using a "for" loop, it is multiplied with each number from 1 to 12 to print a "times table" table of that input number.
A sample output of this program if "3" is input by the user is seen in the image below.
Program to Print Contents of an Array
This program defines an array with its size. All elements of the array are assigned values one by one. Next they are printed as we iterate through a loop. It is noteworthy to say that the iterator variable (i) of the "for" loop is also used as the array index inside the loop (this is not mandatory but makes it easy to read/understand/maintain).
Sub array_cars() 'declaring variable while defining its size Dim arr_my_cars1(5) As Variant 'initialize array elements for one array arr_my_cars1(0) = "Benz" arr_my_cars1(1) = "Maruthi" arr_my_cars1(2) = "Hyundai" arr_my_cars1(3) = "Ford" arr_my_cars1(4) = "Nissan" 'print all elements of the array For i = LBound(arr_my_cars1) To UBound(arr_my_cars1) Debug.Print arr_my_cars1(i) Next End Sub
Output of the Program:
Benz
Maruthi
Hyundai
Ford
Nissan
Program to Write and Read Array Contents
This program slightly varies from the one above. Here you will create an array and iterate through it using its indices.
Values are given to each array item using a "for" loop initially. (This was not the case in the previous program.)
Then, all the data stored in each element of the same array are read/printed back using another "for" loop.
Two loops are used here one below the other:
- One to write data to array
- Another one to read data from the same array.
Sub array_check_demo1() ' Declaration of array variable Dim arr1(11) ' Assign values to array elements For i = LBound(arr1) To UBound(arr1) 'Allocate data for each array item through a loop arr1(i) = Cells(i + 2, 1).Value Next ' Print values of the array For i = LBound(arr1) To UBound(arr1) 'Read data of each array item through a loop Debug.Print arr1(i) Next End Sub
Input is taken from the table below:
Principal amount | No of yrs | Age of customer |
10000 | 5 | 67 |
340600 | 6 | 45 |
457800 | 8 | 34 |
23400 | 3 | 54 |
12000 | 4 | 23 |
23545 | 4 | 56 |
345243 | 2 | 55 |
34543 | 3 | 24 |
23223 | 2 | 19 |
3656 | 1 | 65 |
Output of the Program
Program That Uses the [step] Value
Check out this program and try to find out why the numbers are not continuous in the output:
Sub step_demo() ' declare a counter Dim p As Integer 'loop through 10 numbers and print them For p = 1 To 10 step 3 Debug.Print p Next End Sub
Output of the Program
1
4
7
10
Yes, you might have found that the iterator variable increments by "3" instead of "1" in every iteration. Hence, the numbers printed are skip counted by "3."
Try the same with different values in the place of [step] to understand it better if you are still confused.
The Nested "For" Loop
A loop within a loop is called a nested loop.
Structure
Let me explain this structure with colors:
- The for loop structure in yellow color is the outermost loop.
- The for loop structure/block in green color is the inner loop for the outermost (yellow color) loop. But it also acts as the outer loop for the "for loop" structure in turquoise color.
- The for loop structure in turquoise color is the innermost loop.
In this way , we can have any number of nested loops.
But there are ample chances for you to get confused when you see your own code after several days. You will wonder inside which loop is a specific line and what role it plays in the whole code.
To avoid this, follow the tips below while you code your logic:
- Maintain the alignment in your code with proper tab spaces as shown in the structure above. This can help you find the block of code/loop in which your statement is.
If possible, use the iterator variable next to the "next" keyword. It can help you indicate which "next" statement marks the closure of which loop. This is also marked in the colored structure for reference.
A Few Examples of Nested For Loops
Program to Format Cells with a Specific Word
This program iterates through all the cells (every column of every row in this case). If the cell content has a word "India," then the specified formatting is applied to it.
Sub format_cell_with() For i = 1 To 15 For j = 1 To 5 cellcontent = Cells(i, j).Value If InStr(cellcontent, "India") &amp;gt; 0 Then With Cells(i, j).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End With End If Next Next
Input sheet:
ICC Men's Cricket World Cup Winners – 50 Overs | ||||
Year | World Cup Winner | Runners Up | Host | Finals Venue |
1975 | West Indies | Australia | England | Lord's Cricket Ground, London |
1979 | West Indies | England | England | Lord's Cricket Ground, London |
1983 | India | West Indies | England | Lord's Cricket Ground, London |
1987 | Australia | England | India & Pakistan | Eden Gardens, Kolkata |
1992 | Pakistan | England | Australia & New Zealand | Melbourne Cricket Ground, Melbourne |
1996 | Sri Lanka | Australia | India, Pakistan & Sri Lanka | Gaddafi Stadium, Lahore |
1999 | Australia | Pakistan | England | Lord's Cricket Ground, London |
2003 | Australia | India | Australia | Wanderers, Johannesburg |
2007 | Australia | Sri Lanka | West Indies | Kensington Oval, Bridgetown |
2011 | India | Sri Lanka | India | Wankhede Stadium, Mumbai |
2015 | Australia | New Zealand | Australia | Melbourne Cricket Ground |
2019 | England | New Zealand | England | Lord's, London |
2023 | — | — | India | — |
Output sheet – after the program is run:
Program with 2D Array and "NESTED FOR" Loop
This program declares a two dimensional array and initializes data in all its elements.
The first dimension holds the students' names, and the second dimension holds the "exam results" of the students in the first dimension. These are written back to an Excel sheet.
Warning: As the code does not select any sheet in specific, please ensure that you keep a blank Excel sheet selected. This can prevent any damage to your existing data.
In case you wish to learn more about multidimensional arrays, please check out this article.
Sub Nested_for_demo2() 'declaring and defining size of an array '3 means 4 rows starting from 0 to 3 and 1 means 2 columns starting from 0 to 1 Dim arr_stu(1 To 5, 1 To 2) As Variant 'initialize array elements arr_stu(1, 1) = "Dave" arr_stu(1, 2) = "Fail" arr_stu(2, 1) = "Trumpo" arr_stu(2, 2) = "Pass" arr_stu(3, 1) = "Vincent" arr_stu(3, 2) = "Pass" arr_stu(4, 1) = "Rose Mary" arr_stu(4, 2) = "Pass" arr_stu(5, 1) = "Eliza" arr_stu(5, 2) = "Fail" ' print all elements to the open excel sheet. For i = 1 To 5 For j = 1 To 2 Cells(i, j) = arr_stu(i, j) Next j Next End Sub
Output of the Program on the Active Excel Sheet: Here
- The rows indicate the "i" value of the array (first dimension) and the iterator variable of the outer "for" loop.
- The columns indicate the "j" value of the array (Col A – first dimension , Col B – second dimension) and the iterator variable of the inner "for" loop.
The "Continue" Statement in Visual Basic: Skip a Part of an Iteration
When you encounter a situation where you wish to skip running the rest of the code inside the loop for a specific iteration based on a condition, the "continue" statement will come to your rescue in Visual Basic Programming.
Continue
Syntax:
Continue { Do | For | While }
Where { Do | For | While }
are the respective loops in which it has to be used.
A Simple Example
Sub continue_demo() ' declare a counter Dim i As Integer 'loop through 10 numbers and print them For i = 1 To 10 ' we will skip printing "4" If i = 4 Then Continue For End Debug.Print i Next End Sub
Your output will not have "4" in this case. As the condition is met in the 4th iteration, the continue statement is hit and the rest of the code is skipped for that iteration. The control then moves to the "next" statement of the loop (inner loop in case of nested loops).
Note: The Next
statement is the closure of a loop, as you may be aware.
Output of the above program
1
2
3
5
6
7
8
9
10
Continue Statement is Not Available in VBA
VBA does not offer the "continue statement" because well-built logic in a loop can help us avoid using a "continue" statement. For example, rearranging statement blocks like nested loops and if conditions can help avoid the use of "continue" statements.
An Alternative to the Continue Statement
In unavoidable situations, the Goto statement can be used.
This article can provide you with more insights on how to properly use the "Goto" statement in VBA.
Conclusion
In my experience, I would say that the "for" loop is my favorite compared to the "do while "/"Do until"/" For each" loops. The reason is the comfort of defining it completely in one line. Even during run-time, you can find how many iterations are completed and how many are yet to come.
I have also been able to use this loop in situations where I do not know the number of iterations. I manage it using conditions inside the loop. It also comes handy when I want to wait for a page load during automation. Once you start using this loop, you will even start playing around nested "for loops" with much more confidence and fewer of mistakes.
So, what are you waiting for ? Why not give it a try? 😊
Source: https://software-solutions-online.com/vba-for-loop-and-continue/
0 Response to "Can You Use Continue in a While Loop"
Post a Comment