VBA Loops

Loops are used to execute statements repeatedly while a condition or a  combination of condition is met. In VBA, there are five loops:

     -For next loop

     -While loop

     -Do While loop

     -Do Until loop

     -For each loop

 

-For next Loop

For loop executes statements repeatedly from a start value to an upper limit value. The step of the loop is optional. The default step is 1.

 

Syntax:

For initial_value To end_value [Step number]

               Do something… (block code)

Next

 

Example:

 

Dim i,sum As Integer

sum=0

For i=0 to 100

    sum=sum+i

Next

Msgbox “Total=” & sum

 

-While Loop

The while loop is used for more general purpose. The while loop exits when a condition is no longer true.

Syntax:

 

While condition

     do something

Wend

 

 

Example:

Dim x As Integer

x=0

while x>=0

     x=InputBox(“Enter a positive value:”)

     Msgbox “The Square of this value is:” & x*x

Wend

 

-Do While Loop

Do while loop can test the condition at the top or at the bottom.

Syntax:

 

    Do While condition

        do something

     Loop

 

Or

     Do

         do something

     Loop While condition

 

     Example:

     Dim con As String

     Dim result As Integer

     con=”y”

     Do while con=”y”

          result=Cint((6*rnd)+1) ‘generate random number using from 1 to 6

          Msgbox “The result of rolling a die is:” & result

          con=InputBox(“Continue?y/n:”)

         

    Loop

         

-Do Until Loop

The Do Until loop executes the statements until an upper limit is reached.

Syntax:

 

    Do Until condition

        do something

      Loop

Or

   Do

     do something

   Loop Until condition

 

    Example:

 

Dim x As Integer

x=0

Do until x>10

   x=x+1

Loop

 

-For each Loop

This loop is used to traverse through a collection of values especially an array.

Syntax:

 

for each value in collection--array

                   do something

Next

 

Example:

Dim fd As TableDef
Set df = CurrentDb.TableDefs 'Tables collection
For Each t In df
    Debug.Print t.Name & vbCrLf  'Output table names.Press Ctrl+G to see the result
Next

-Exit a loop

You can exit a loop at any time when you want by using the Exit command.
      Example:
         Dim i As Integer
         For i = 0 To 10
             Debug.Print i * i & vbCrLf
             If i = 5 Then 'exit the for loop when i is 5.
                Exit For
             End If
         Next


HTML Comment Box is loading comments...



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.