$show=home

VBA - Loops

VBA - Loops Shout4Education
There may be a situation when you need to execute a block of code several number of times. In general, statements are executed sequentially: The first statement in a function is executed first, followed by the second, and so on.
Programming languages provide various control structures that allow for more complicated execution paths.
A loop statement allows us to execute a statement or group of statements multiple times. Following is the general form of a loop statement in VBA.
VBA - Loops Shout4Education

VBA provides the following types of loops to handle looping requirements. Click the following links to check their detail.
Sr.No.Loop Type & Description
1for loop
Executes a sequence of statements multiple times and abbreviates the code that manages the loop variable.
2for ..each loop
This is executed if there is at least one element in the group and reiterated for each element in a group.
3while..wend loop
This tests the condition before executing the loop body.
4do..while loops
The do..While statements will be executed as long as the condition is True.(i.e.,) The Loop should be repeated till the condition is False.
5do..until loops
The do..Until statements will be executed as long as the condition is False.(i.e.,) The Loop should be repeated till the condition is True.

VBA - For Loops

for loop is a repetition control structure that allows a developer to efficiently write a loop that needs to be executed a specific number of times.

Syntax

Following is the syntax of a for loop in VBA.
For counter = start To end [Step stepcount]
   [statement 1]
   [statement 2]
   ....
   [statement n]
   [Exit For]
   [statement 11]
   [statement 22]
   ....
   [statement n]
Next

Flow Diagram

Flow Diagram of VBA - For Loops Shout4Education
Following is the flow of control in a For Loop −
  • The For step is executed first. This step allows you to initialize any loop control variables and increment the step counter variable.
  • Secondly, the condition is evaluated. If it is true, the body of the loop is executed. If it is false, the body of the loop does not execute and the flow of control jumps to the next statement, just after the For Loop.
  • After the body of the For loop executes, the flow of control jumps to the next statement. This statement allows you to update any loop control variables. It is updated based on the step counter value.
  • The condition is now evaluated again. If it is true, the loop executes and the process repeats itself (body of loop, then increment step, and then again condition). After the condition becomes false, the For Loop terminates.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim a As Integer
   a = 10
   
   For i = 0 To a Step 2
      MsgBox "The value is i is : " & i
   Next
End Sub
When the above code is compiled and executed, it produces the following result.
The value is i is : 0

The value is i is : 2

The value is i is : 4

The value is i is : 6

The value is i is : 8

The value is i is : 10

VBA - For Each Loops

For Each loop is used to execute a statement or a group of statements for each element in an array or collection.
A For Each loop is similar to For Loop; however, the loop is executed for each element in an array or group. Hence, the step counter won't exist in this type of loop. It is mostly used with arrays or used in context of the File system objects in order to operate recursively.

Syntax

Following is the syntax of a For Each loop in VBA.
For Each element In Group
   [statement 1]
   [statement 2]
   ....
   [statement n]
   [Exit For]
   [statement 11]
   [statement 22]
Next

Example

Private Sub Constant_demo_Click()  
   'fruits is an array
   fruits = Array("apple", "orange", "cherries")
   Dim fruitnames As Variant
 
   'iterating using For each loop.
   For Each Item In fruits
      fruitnames = fruitnames & Item & Chr(10)
   Next
   
   MsgBox fruitnames
End Sub
When the above code is executed, it prints all the fruit names with one item in each line.
apple
orange
cherries

VBA - While Wend Loops

In a While…Wend loop, if the condition is True, all the statements are executed until the Wend keyword is encountered.
If the condition is false, the loop is exited and the control jumps to the very next statement after the Wend keyword.

Syntax

Following is the syntax of a While..Wend loop in VBA.
While condition(s)
   [statements 1]
   [statements 2]
   ...
   [statements n]
Wend

Flow Diagram

Flow Diagram of VBA - While Wend Loops Shout 4 Education

Example

Private Sub Constant_demo_Click()
   Dim Counter :  Counter = 10   
   
   While Counter < 15     ' Test value of Counter.
      Counter = Counter + 1   ' Increment Counter.
      msgbox "The Current Value of the Counter is : " & Counter
   Wend   ' While loop exits if Counter Value becomes 15.
End Sub   
When the above code is executed, it prints the following in a message box.
The Current Value of the Counter is : 11 

The Current Value of the Counter is : 12 

The Current Value of the Counter is : 13 

The Current Value of the Counter is : 14 

The Current Value of the Counter is : 15

VBA - Do-While Loops

Do…While loop is used when we want to repeat a set of statements as long as the condition is true. The condition may be checked at the beginning of the loop or at the end of the loop.

Syntax

Following is the syntax of a Do…While loop in VBA.
Do While condition
   [statement 1]
   [statement 2]
   ...
   [statement n]
   [Exit Do]
   [statement 1]
   [statement 2]
   ...
   [statement n]
Loop           

Flow Diagram

Flow Diagram VBA - Do-While Loops Shout For Education

Example

The following example uses Do…while loop to check the condition at the beginning of the loop. The statements inside the loop are executed, only if the condition becomes True.
Private Sub Constant_demo_Click()
   Do While i < 5
      i = i + 1
      msgbox "The value of i is : " & i
   Loop
End Sub
When the above code is executed, it prints the following output in a message box.
The value of i is : 1

The value of i is : 2

The value of i is : 3

The value of i is : 4

The value of i is : 5

Alternate Syntax

There is also an alternate Syntax for Do…while loop which checks the condition at the end of the loop. The major difference between these two syntax is explained in the following example.
Do 
   [statement 1]
   [statement 2]
   ...
   [statement n]
   [Exit Do]
   [statement 1]
   [statement 2]
   ...
   [statement n]
Loop While condition

Example

The following example uses Do…while loop to check the condition at the end of the loop. The Statements inside the loop are executed at least once, even if the condition is False.
Private Sub Constant_demo_Click() 
   i = 10
   Do
      i = i + 1
      MsgBox "The value of i is : " & i
   Loop While i < 3 'Condition is false.Hence loop is executed once.
End Sub
When the above code is executed, it prints the following output in a message box.
The value of i is : 11

VBA - Do-Until Loops

Do…Until loop is used when we want to repeat a set of statements as long as the condition is false. The condition may be checked at the beginning of the loop or at the end of loop.

Syntax

Following is the syntax of a Do..Until loop in VBA.
Do Until condition
   [statement 1]
   [statement 2]
   ...
   [statement n]
   [Exit Do]
   [statement 1]
   [statement 2]
   ...
   [statement n]
Loop           

Flow Diagram

Flow Diagram of VBA - Do-Until Loops Shout4Education

Example

The following example uses Do…Until loop to check the condition at the beginning of the loop. The statements inside the loop are executed only if the condition is false. It exits out of the loop, when the condition becomes true.
Private Sub Constant_demo_Click() 
   i = 10
   Do Until i>15  'Condition is False.Hence loop will be executed
      i = i + 1
      msgbox ("The value of i is : " & i)
   Loop 
End Sub
When the above code is executed, it prints the following output in a message box.
The value of i is : 11

The value of i is : 12

The value of i is : 13

The value of i is : 14

The value of i is : 15

The value of i is : 16

Alternate Syntax

There is also an alternate syntax for Do...Until loop which checks the condition at the end of the loop. The major difference between these two syntax is explained with the following example.
Do 
   [statement 1]
   [statement 2]
   ...
   [statement n]
   [Exit Do]
   [statement 1]
   [statement 2]
   ...
   [statement n]
Loop Until condition

Flow Diagram

Flow Diagram of VBA - Do-Until Loops Shout4Education

Example

The following example uses Do...Until loop to check the condition at the end of the loop. The statements inside the loop are executed at least once, even if the condition is True.
Private Sub Constant_demo_Click()  
   i = 10
   Do 
      i = i + 1
      msgbox "The value of i is : " & i
   Loop Until i<15 'Condition is True.Hence loop is executed once.
End Sub
When the above code is executed, it prints the following output in a message box.
The value of i is : 11

Loop Control Statements

Loop control statements change execution from its normal sequence. When execution leaves a scope, all the remaining statements in the loop are NOT executed.
VBA supports the following control statements. Click the following links to check their detail.
S.No.Control Statement & Description
1Exit For statement
Terminates the For loop statement and transfers the execution to the statement immediately following the loop
2Exit Do statement
Terminates the Do While statement and transfers the execution to the statement immediately following the loop

VBA - Exit For

Exit For statement is used when we want to exit the For Loop based on certain criteria. When Exit For is executed, the control jumps to the next statement immediately after the For Loop.

Syntax

Following is the syntax for Exit For Statement in VBA.
 Exit For

Flow Diagram

Flow Diagram of VBA - Exit For Shout For Education

Example

The following example uses Exit For. If the value of the Counter reaches 4, the For Loop is exited and the control jumps to the next statement immediately after the For Loop.
Private Sub Constant_demo_Click()
   Dim a As Integer
   a = 10
   
   For i = 0 To a Step 2 'i is the counter variable and it is incremented by 2
      MsgBox ("The value is i is : " & i)
      If i = 4 Then
         i = i * 10 'This is executed only if i=4
         MsgBox ("The value is i is : " & i)
         Exit For 'Exited when i=4
      End If
   Next
End Sub
When the above code is executed, it prints the following output in a message Box.
The value is i is : 0

The value is i is : 2

The value is i is : 4

The value is i is : 40 

VBA - Exit Do

An Exit Do Statement is used when we want to exit the Do Loops based on certain criteria. It can be used within both Do…While and Do...Until Loops.
When Exit Do is executed, the control jumps to the next statement immediately after the Do Loop.

Syntax

Following is the syntax for Exit Do Statement in VBA.
 Exit Do

Example

The following example uses Exit Do. If the value of the Counter reaches 10, the Do Loop is exited and the control jumps to the next statement immediately after the For Loop.
Private Sub Constant_demo_Click()
   i = 0
   Do While i <= 100
      If i > 10 Then
         Exit Do   ' Loop Exits if i>10
      End If
      MsgBox ("The Value of i is : " & i)
      i = i + 2
   Loop
End Sub
When the above code is executed, it prints the following output in a message box.
The Value of i is : 0

The Value of i is : 2

The Value of i is : 4

The Value of i is : 6

The Value of i is : 8

The Value of i is : 10

Comments

Blogger
Name

.NET_Interview,1,Accenture,1,Accenture News,1,Accenture_GFT,1,Accenture_Prep,1,Advance_Excel,22,Advance_Python,10,Advanced_Linux,6,Advanced_SQL,18,Advanced_Unix,6,AI,1,Alexa,1,Amazon,1,Amazon News,5,AMCAT,1,AMCAT_Prep,1,AMCAT_Solved_Papers,1,Ancient India,5,Android,1,Ansible,2,Apache_Sqoop,10,Aptitude,1,AWS,23,AWS CLI,6,AWS DeepRacer,1,AWS Tutorials,13,AWS_Dumps,1,AWS_Interview,1,AZ-104,1,AZ-900,2,Azure,3,Azure Administrator Associate,1,B_Tech,19,B.Tech,1,B.Tech Jobs,1,Backup,2,Banking Exam,1,Banking_Exam,1,Basic_Linux,29,Basic_Python,19,Basic_SQL,24,Basic_Unix,30,Best_Websites,1,Big_Data_Analytics,70,Blog,447,Blogger,3,Blogging,2,Blogspot,1,Books,2,BTech,20,C++_Interview,1,CBSE,93,Certification,5,Cheat Sheet,2,Civil_1st_Semester,1,Class 11,2,Class 11 Physics,2,Class 12,61,Class 12 Biology,16,Class 12 Chemistry,16,Class 12 English,14,Class 12 Physics,15,Class_12,28,Class_12_Chemistry,4,Class_12_Computer_Science,7,Class_12_Mathematics,1,Class_12_NCERT,15,Class_12_NCERT_Solutions,15,Class_12_Physics,18,Class_12_Physics_NCERT_Solutions,15,Class_12_Science,29,Cloud,2,Cloud_Service,1,CloudFormation,2,Coding,1,Cognizant News,1,Communication,2,Computer,18,Computer_Memory,2,Computer_Programming,2,Computer_Science,4,Control_System,9,Crack_Interview,3,CSE_5th_Semester,1,CSS,1,Data Analyst Jobs,1,Data Science,2,Data Science Interview,1,Data_Analytics,16,Data_Science,18,Data_Science_Interview,1,Database,47,Database Interview,2,Database_Interview,4,DP-900,1,Dumps,2,ECE_1st_Semester,1,ECE_1st_Year,1,ECE_4th_Semester,9,Electrical,2,Electrical_1st_Semester,1,Electronics,2,Electronics & Communication,1,Electronics_&_Communication,14,English,2,Error,1,Ethical Hacking,2,Ethical_Hacking,1,ETL_Tools,17,Exam Dumps,1,Exam Preparation,13,Exam_Cracker,3,Exams,5,Exams_Banking,1,Exams_Prep,1,Excel,22,Excel_Macros,22,Excel_Terms,1,Excel_VBA,22,File System,1,Free_OS,1,Free_Softwares,1,Games,3,GATE,20,GATE EC,1,GATE Electronics & Communication,1,GATE_2019,16,GATE_2020,16,GATE_2021,7,GATE_EC,10,GATE_ECE,9,GATE_ECE_Best_Book,1,GATE_Electrical,1,GATE_Electronics,12,GATE_Made_Easy,1,GATE_ME,6,GATE_Mechanical,6,GGSIPU,1,Google,1,Google Cloud,2,Google News,2,Government Jobs,2,Graphic,1,GRUB,1,Handwritten_Notes,10,Hardware,6,HCL_Prep,1,HDFS,1,Hive,1,Hive Tutorials,1,Hosting,2,How To,15,How_To,8,HR Interview,2,HR Interview Questions,1,HR_Interview,3,Hyderabad_News,1,IBPS,2,IBPS_English,1,IBPS_PO,2,Indian History,5,Informatica,1,Informatica_Interview,1,Information,20,Internet,5,Interview,20,Interview Preparation,5,Interview_Prep,20,IPU,1,ISRO Jobs,1,IT Jobs,4,IT News,1,Java,2,Java Interview,1,Java_Interview,2,Java_Questions_&_answers,1,JavaScript,1,JEE,6,JEE_Mains,6,Job Alert,6,Jobs,1,Kali Linux,1,Kali Linux Tools,1,Katoolin,1,Keyboard,1,Keyboard_Shortcuts,1,Learn_VBA,22,Linux,42,Linux Command,1,Linux Interview,1,Linux Mint,1,Linux Tools,4,Linux Tutorials,1,Linux_Distributions,1,Linux_Interview,1,Linux_Redirections,1,Linux_Scripting,30,Linux_Shell_Arrays,1,Linux_Shell_Functions,1,Linux_Shell_Quote,1,Linux_Signals_And_Traps,1,Logical_Reasoning,1,M.Tech Jobs,1,Machine Learning,1,Machine Learning Interview,1,Machine_Learning,1,Machine_Learning_Interview,1,Macros,22,Manufacturing_Processes,1,ME_1st_Semester,1,ME_Fluid_Mechanics,1,ME_Industrial_Engineering,1,ME_Machine_Design,1,Mechanical,6,Memory,1,Microcontroller,1,Microsoft,5,Microsoft Azure Associate,1,Microsoft Azure Fundamentals,3,Microsoft_Azure,1,Microsoft_Azure_Interview,1,Mobile,1,Mobile News,1,MS_Access,40,MySQL,44,NCERT Solutions,63,Network,2,News,3,Notes,31,OOPs,1,Open_Source_OS,1,OpenTelemetry,1,Operating_Systems,2,Operating_Systems_Interview,1,Oracle,42,Oracle Interview,1,Oracle_Interview,1,Paytm Jobs,1,Physics,2,PL_SQL,42,PL_SQL_Interview,1,Placement,21,Placement Preparation,18,Placement_Prep,24,Poetry,1,Programming,28,Programming_Languages,1,Python,28,Python_Built_In_Strings_Methods,2,Python_built_In_Tuple_Functions,1,Python_CAlling_a_Function,1,Python_CGI,1,Python_Class,1,Python_Data_Types,1,Python_DAte,1,Python_Decision_Making,1,Python_Dictionary,1,Python_DOM_APIs,1,Python_Features,1,Python_Files_Functions,1,Python_For_Loop,1,Python_Functions,6,Python_GUI,1,Python_History,1,Python_If_Else,1,Python_import_Statements,1,Python_Installation,1,Python_Interview,1,Python_JPython,1,Python_Lists,2,Python_Loops,1,Python_Methods,1,Python_Modules,1,Python_MySQL,1,Python_Nested_If_Else,1,Python_Nested_Loops,1,Python_Number_Type_Conversion,1,Python_Numbers,2,Python_Object_Oriented,1,Python_OOP,1,Python_Pass_By_Reference_vs_Value,1,Python_Programming,28,Python_Scripting,28,Python_Special_Operators,1,Python_Strings,2,Python_Strings_Functions,1,Python_Threading_Module,1,Python_Time,1,Python_Tkinter,1,Python_Tuples,2,Python_Tutorial,28,Python_Types_of_Loops,1,Python_Variables,1,Python_Web_Server,1,Python_While_Loop,1,Python_wxPython,1,Python_XML_Processing,1,PythonPath_Setup,1,Quantitative,1,Quantitative_Aptitude,2,RDBMS,1,Run,1,S3,1,Sabrent,1,Samsung,1,SBI Jobs,1,Scripting,52,Scripting Interview,1,Security,1,Server,2,service now,1,Shell_Command_Manual,1,Shell_Logging_Commands,1,Shell_Scripting,31,Shell_Scripting_Interview,1,Software Engineering Interview,1,Software_Engineering_Interview,1,Solutions,1,Spinnaker,1,SQL,46,SQL Interview,1,SQL_Alias_Syntax,1,SQL_Alter_Table_Query,1,SQL_Alter_Table_Statement,1,SQL_AND_OR_Query,1,SQL_AND_OR_Statement,1,SQL_Architecture,1,SQL_Clone_Table,1,SQL_Commands,1,SQL_Conjunctive_Operators,1,SQL_Constraints,1,SQL_Create_Database,1,SQL_Create_Table,1,SQL_DataTypes,1,SQL_Date_Functions,1,SQL_Date_Statement,1,SQL_DCL,1,SQL_Server,39,SQL_Temporary_Table_Statement,1,SQLite,43,Sqoop,9,Sqoop_Tutorial,10,SSC,4,SSC CGL,1,SSC CHSL,1,SSC_CGL,3,SSC_CGL_English,1,SSC_CHSL,1,SSC_CPO,1,SSC_GS,1,SSC_Quantative,1,SSD,1,ssl,1,Storage,1,Talend,17,Talend Interview,1,Talend_ETL,15,Talend_Tutorials,16,TCS Interview,1,TCS Jobs,1,TCS News,1,TCS_Interview,1,TCS_Prep,1,Tech News,15,Tech Tips,18,Teradata_Interview,1,Terraform,2,Tips & Tricks,12,Tips_&_Tricks,18,Top 10,3,Top 50,10,Top_10,1,Top_50,18,Top25,1,Tutorials,165,Tutorials_Python,28,Tutorials_VBA,11,Unix,32,Unix Interview,1,Unix Tutorials,1,Unix_Interview,1,Unix_Scripting,31,UPSC,5,VBA,22,VBA_Basics,22,VBA_Excel,22,VBA_Scripting,22,VBA_Tutorials,22,VirtualBox,1,Visual_Basic_Application,22,VPN,1,Websites,3,Windows,22,WordPress,2,Yarn,1,
ltr
item
Shout4Education - Get Jobs, Tutorials and Notes: VBA - Loops
VBA - Loops
VBA - Loops - VBA Tutorials ... Best VBA Tutorials only @ Shout4Education ... Learn VBA in an Easy and Simplified Way ... Keep Shouting For Education and Keep Learning ... VBA - Loops Shout4Education There may be a situation when you need to execute a block of code several number of times. In general, statements are executed sequentially: The first statement in a function is executed first, followed by the second, and so on. Programming languages provide various control structures that allow for more complicated execution paths. A loop statement allows us to execute a statement or group of statements multiple times. Following is the general form of a loop statement in VBA. VBA - Loops Shout4Education VBA provides the following types of loops to handle looping requirements. Click the following links to check their detail. Sr.No. Loop Type & Description 1 for loop Executes a sequence of statements multiple times and abbreviates the code that manages the loop variable. 2 for ..each loop This is executed if there is at least one element in the group and reiterated for each element in a group. 3 while..wend loop This tests the condition before executing the loop body. 4 do..while loops The do..While statements will be executed as long as the condition is True.(i.e.,) The Loop should be repeated till the condition is False. 5 do..until loops The do..Until statements will be executed as long as the condition is False.(i.e.,) The Loop should be repeated till the condition is True. VBA - For Loops A for loop is a repetition control structure that allows a developer to efficiently write a loop that needs to be executed a specific number of times. Syntax Following is the syntax of a for loop in VBA. For counter = start To end [Step stepcount] [statement 1] [statement 2] .... [statement n] [Exit For] [statement 11] [statement 22] .... [statement n] Next Flow Diagram Flow Diagram of VBA - For Loops Shout4Education Following is the flow of control in a For Loop − The For step is executed first. This step allows you to initialize any loop control variables and increment the step counter variable. Secondly, the condition is evaluated. If it is true, the body of the loop is executed. If it is false, the body of the loop does not execute and the flow of control jumps to the next statement, just after the For Loop. After the body of the For loop executes, the flow of control jumps to the next statement. This statement allows you to update any loop control variables. It is updated based on the step counter value. The condition is now evaluated again. If it is true, the loop executes and the process repeats itself (body of loop, then increment step, and then again condition). After the condition becomes false, the For Loop terminates. Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim a As Integer a = 10 For i = 0 To a Step 2 MsgBox "The value is i is : " & i Next End Sub When the above code is compiled and executed, it produces the following result. The value is i is : 0 The value is i is : 2 The value is i is : 4 The value is i is : 6 The value is i is : 8 The value is i is : 10 VBA - For Each Loops A For Each loop is used to execute a statement or a group of statements for each element in an array or collection. A For Each loop is similar to For Loop; however, the loop is executed for each element in an array or group. Hence, the step counter won't exist in this type of loop. It is mostly used with arrays or used in context of the File system objects in order to operate recursively. Syntax Following is the syntax of a For Each loop in VBA. For Each element In Group [statement 1] [statement 2] .... [statement n] [Exit For] [statement 11] [statement 22] Next Example Private Sub Constant_demo_Click() 'fruits is an array fruits = Array("apple", "orange", "cherries") Dim fruitnames As Variant 'iterating using For each loop. For Each Item In fruits fruitnames = fruitnames & Item & Chr(10) Next MsgBox fruitnames End Sub When the above code is executed, it prints all the fruit names with one item in each line. apple orange cherries VBA - While Wend Loops In a While…Wend loop, if the condition is True, all the statements are executed until the Wend keyword is encountered. If the condition is false, the loop is exited and the control jumps to the very next statement after the Wend keyword. Syntax Following is the syntax of a While..Wend loop in VBA. While condition(s) [statements 1] [statements 2] ... [statements n] Wend Flow Diagram Flow Diagram of VBA - While Wend Loops Shout 4 Education Example Private Sub Constant_demo_Click() Dim Counter : Counter = 10 While Counter < 15 ' Test value of Counter. Counter = Counter + 1 ' Increment Counter. msgbox "The Current Value of the Counter is : " & Counter Wend ' While loop exits if Counter Value becomes 15. End Sub When the above code is executed, it prints the following in a message box. The Current Value of the Counter is : 11 The Current Value of the Counter is : 12 The Current Value of the Counter is : 13 The Current Value of the Counter is : 14 The Current Value of the Counter is : 15 VBA - Do-While Loops A Do…While loop is used when we want to repeat a set of statements as long as the condition is true. The condition may be checked at the beginning of the loop or at the end of the loop. Syntax Following is the syntax of a Do…While loop in VBA. Do While condition [statement 1] [statement 2] ... [statement n] [Exit Do] [statement 1] [statement 2] ... [statement n] Loop Flow Diagram Flow Diagram VBA - Do-While Loops Shout For Education Example The following example uses Do…while loop to check the condition at the beginning of the loop. The statements inside the loop are executed, only if the condition becomes True. Private Sub Constant_demo_Click() Do While i < 5 i = i + 1 msgbox "The value of i is : " & i Loop End Sub When the above code is executed, it prints the following output in a message box. The value of i is : 1 The value of i is : 2 The value of i is : 3 The value of i is : 4 The value of i is : 5 Alternate Syntax There is also an alternate Syntax for Do…while loop which checks the condition at the end of the loop. The major difference between these two syntax is explained in the following example. Do [statement 1] [statement 2] ... [statement n] [Exit Do] [statement 1] [statement 2] ... [statement n] Loop While condition Example The following example uses Do…while loop to check the condition at the end of the loop. The Statements inside the loop are executed at least once, even if the condition is False. Private Sub Constant_demo_Click() i = 10 Do i = i + 1 MsgBox "The value of i is : " & i Loop While i < 3 'Condition is false.Hence loop is executed once. End Sub When the above code is executed, it prints the following output in a message box. The value of i is : 11 VBA - Do-Until Loops A Do…Until loop is used when we want to repeat a set of statements as long as the condition is false. The condition may be checked at the beginning of the loop or at the end of loop. Syntax Following is the syntax of a Do..Until loop in VBA. Do Until condition [statement 1] [statement 2] ... [statement n] [Exit Do] [statement 1] [statement 2] ... [statement n] Loop Flow Diagram Flow Diagram of VBA - Do-Until Loops Shout4Education Example The following example uses Do…Until loop to check the condition at the beginning of the loop. The statements inside the loop are executed only if the condition is false. It exits out of the loop, when the condition becomes true. Private Sub Constant_demo_Click() i = 10 Do Until i>15 'Condition is False.Hence loop will be executed i = i + 1 msgbox ("The value of i is : " & i) Loop End Sub When the above code is executed, it prints the following output in a message box. The value of i is : 11 The value of i is : 12 The value of i is : 13 The value of i is : 14 The value of i is : 15 The value of i is : 16 Alternate Syntax There is also an alternate syntax for Do...Until loop which checks the condition at the end of the loop. The major difference between these two syntax is explained with the following example. Do [statement 1] [statement 2] ... [statement n] [Exit Do] [statement 1] [statement 2] ... [statement n] Loop Until condition Flow Diagram Flow Diagram of VBA - Do-Until Loops Shout4Education Example The following example uses Do...Until loop to check the condition at the end of the loop. The statements inside the loop are executed at least once, even if the condition is True. Private Sub Constant_demo_Click() i = 10 Do i = i + 1 msgbox "The value of i is : " & i Loop Until i<15 'Condition is True.Hence loop is executed once. End Sub When the above code is executed, it prints the following output in a message box. The value of i is : 11 Loop Control Statements Loop control statements change execution from its normal sequence. When execution leaves a scope, all the remaining statements in the loop are NOT executed. VBA supports the following control statements. Click the following links to check their detail. S.No. Control Statement & Description 1 Exit For statement Terminates the For loop statement and transfers the execution to the statement immediately following the loop 2 Exit Do statement Terminates the Do While statement and transfers the execution to the statement immediately following the loop VBA - Exit For A Exit For statement is used when we want to exit the For Loop based on certain criteria. When Exit For is executed, the control jumps to the next statement immediately after the For Loop. Syntax Following is the syntax for Exit For Statement in VBA. Exit For Flow Diagram Flow Diagram of VBA - Exit For Shout For Education Example The following example uses Exit For. If the value of the Counter reaches 4, the For Loop is exited and the control jumps to the next statement immediately after the For Loop. Private Sub Constant_demo_Click() Dim a As Integer a = 10 For i = 0 To a Step 2 'i is the counter variable and it is incremented by 2 MsgBox ("The value is i is : " & i) If i = 4 Then i = i * 10 'This is executed only if i=4 MsgBox ("The value is i is : " & i) Exit For 'Exited when i=4 End If Next End Sub When the above code is executed, it prints the following output in a message Box. The value is i is : 0 The value is i is : 2 The value is i is : 4 The value is i is : 40 VBA - Exit Do An Exit Do Statement is used when we want to exit the Do Loops based on certain criteria. It can be used within both Do…While and Do...Until Loops. When Exit Do is executed, the control jumps to the next statement immediately after the Do Loop. Syntax Following is the syntax for Exit Do Statement in VBA. Exit Do Example The following example uses Exit Do. If the value of the Counter reaches 10, the Do Loop is exited and the control jumps to the next statement immediately after the For Loop. Private Sub Constant_demo_Click() i = 0 Do While i <= 100 If i > 10 Then Exit Do ' Loop Exits if i>10 End If MsgBox ("The Value of i is : " & i) i = i + 2 Loop End Sub When the above code is executed, it prints the following output in a message box. The Value of i is : 0 The Value of i is : 2 The Value of i is : 4 The Value of i is : 6 The Value of i is : 8 The Value of i is : 10 @ Shout 4 Education , @ Shout For Education
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s640/VBA_Tutorials_Shout4Education.jpg
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s72-c/VBA_Tutorials_Shout4Education.jpg
Shout4Education - Get Jobs, Tutorials and Notes
https://www.shout4education.com/2020/04/vba-loops.html
https://www.shout4education.com/
https://www.shout4education.com/
https://www.shout4education.com/2020/04/vba-loops.html
true
7947974353386595563
UTF-8
Loaded All Posts Not Found Any Posts :( View All Read More Reply Cancel Reply Delete By Home Pages Posts View All Similar Posts Label Archive Search All Posts Not Found Any Post Match with Your Request Sorry !! Search Something Blazing :) Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Just Now 1 Minute Ago $$1$$ minutes ago 1 Hour Ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago More than 5 Weeks Ago Followers Follow :) This Premium Content is LOCKED !!! STEP 1: Share. STEP 2: Click the Link You Shared to Unlock Copy All Code Select All Code All Codes were Copied to Your Clipboard :) Can NOT Copy the Codes / Texts, Please Press [CTRL]+[C] (or CMD+C with Mac) to Copy