{"id":871,"date":"2018-06-30T18:38:52","date_gmt":"2018-06-30T18:38:52","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=871"},"modified":"2024-03-16T13:26:06","modified_gmt":"2024-03-16T13:26:06","slug":"loops","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/training\/loops\/","title":{"rendered":"Loops"},"content":{"rendered":"\n
VBA, as well as other programming languages, offers loops. Looping is an operation of repeating the same block of code multiple times. Excel offers a few types of loops, each best in different types of situations.<\/p>\n\n\n\n
This is the most common type of loop. In the FOR<\/strong> loop, you specify the value where the loop starts and where it finishes. The code between the FOR<\/strong> and NEXT<\/strong> statement gets repeated. Look at the following example.<\/p>\n\n\n\n In the above example, the MsgBox<\/em> will display the message 6 times, displaying numbers 0, 1, 2, 3, 4, and 5.<\/p>\n\n\n\n Additionally, you can skip some values by using the Step<\/strong> value.<\/p>\n\n\n\n This time the MsgBox<\/em> displays numbers: 0, 2, 4. Because the loop counts to 5 it won\u2019t get to number 6.<\/p>\n\n\n\n In the following example, we have the array with the four names of the movies. This VBA code will display each name inside the MsgBox<\/em> window.<\/p>\n\n\n\n In the next lesson, I will present you with a similar example with the FOR..EACH..NEXT<\/strong> loop.<\/p>\n\n\n\n In Excel VBA, you can use the FOR..EACH..NEXT<\/strong> loop. It loops through each object in a collection. It can be the collection of worksheets or a collection of elements in an array as presented in our example below.<\/p>\n\n\n\n You cannot declare the movie<\/strong> variable in the FOR..EACH..NEXT<\/strong> loop as String.<\/strong> It has to always be declared as a Variant.<\/strong><\/p>\n\n\n\n Unlike the FOR <\/strong>loop, the DO..WHILE<\/strong> a loop doesn\u2019t have specified: start and end values, instead it loops as long as the condition is true.<\/p>\n\n\n\n Look at the following example:<\/p>\n\n\n\n In this example, Excel checks the first value, which is 0. The condition is met, so the iteration of the loop is executed. The MsgBox<\/em> window displays the value 0. The next values that also meet the condition are 0, 1, 2, 3, and 4. When counter = 5<\/em> then the condition is not met and the loop terminates.<\/p>\n\n\n\n The DO..UNTIL<\/strong> loop is very similar to the DO..WHILE<\/strong> loop. The difference between these two loops is that in the DO..WHILE<\/strong> loop the code is executed as long as the condition is True<\/strong> and in the DO..UNTIL<\/strong> loop it is executed as long as the condition is False.<\/strong><\/p>\n\n\n\n The MsgBox<\/em> will display 0, 1, 2, 3, 4. When counter = 5 then the condition is met and the loop terminates.<\/p>\n","protected":false},"excerpt":{"rendered":" VBA, as well as other programming languages, offers loops. Looping is an operation of repeating the same block of code multiple times. Excel…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[188],"yoast_head":"\nFor value = 0 To 5\n MsgBox (value)\nNext value<\/pre>\n\n\n\n
For value = 0 To 5 Step 2\n MsgBox (value)\nNext value<\/pre>\n\n\n\n
Dim movies(1 To 4) As String\n \nmovies(1) = \"Matrix\"\nmovies(2) = \"Lord of the Rings\"\nmovies(3) = \"Star Wars\"\nmovies(4) = \"The Shawshank Redemption\"\n \nFor Count = 1 To 4\n MsgBox (movies(Count))\nNext Count<\/pre>\n\n\n\n
For Each Next Loop<\/h2>\n\n\n\n
Dim movies(1 To 4) As String\nDim movie As Variant\n \nmovies(1) = \"Matrix\"\nmovies(2) = \"Lord of the Rings\"\nmovies(3) = \"Star Wars\"\nmovies(4) = \"The Shawshank Redemption\"\n \nFor Each movie In movies\n MsgBox (movie)\nNext movie<\/pre>\n\n\n\n
CAUTION<\/h3>\n\n\n\n
Do While Loop<\/h2>\n\n\n\n
Dim counter As Integer\ncounter = 0\n \nDo While counter < 5\n MsgBox (counter)\n counter = counter + 1\nLoop<\/pre>\n\n\n\n
Do Until Loop<\/h2>\n\n\n\n
Dim counter As Integer\ncounter = 0\n \nDo Until counter = 5\n MsgBox (counter)\n counter = counter + 1\nLoop<\/pre>\n\n\n\n