Excel 2007 VBA tutorial-Loops


VBA Loops

 VBA for Excel 2007--VBA Loops

Loops are used to execute statements repeatedly while a condition or a combination of conditions 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
 
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
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
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 numbers from 1 to 6
          Msgbox "The result of rolling a die is:" & result
          con=InputBox("Continue?y/n:")
    Loop
          -Do Until Loop
Syntax:
      Do Until condition
     do something      Loop
Or     Do
     do something     Loop Until condition
      Example:
    Dim x As Integer
    x=1
    Do until x>10
        Cells(x,1)=x*x; //show squared values of x in the active sheet
    x=x+1
   Loop
  -For each Loop
Syntax:
 
  for each value in collection--array
                   do something
  Next
 
  Example:
 
  Dim arr(5) As Integer 'Declaring the array
  Dim i As Integer
  Dim val As String
  For i=0 to 4 'Assign values to the arr array
     arr(i)=i*2
  Next
  for each x in arr 'Accessing values from the arr array
   val=val &x & vbcrlf
  Next
  Msgbox val


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.