Using Loops in Excel VBA

Looping is used to repeatedly running a set of codes till it completes given condition. The loops are the fundamental component of any programming Language. In Excel VBA too there are many loop constructs. Let us have a close look into basic loop constructs.

Basic Loop constructs of Excel VBA:

  • For…..next
  • Do……While
  • Do……Until
  • While……….Loop
  • For Each……Next
For……Next Loop:

This loop is used when there is a need to execute a statement or a block of statements for certain number of times.
Example for For…..Next Loop:
For i =1 to 10
     Total=Total+i
Next i

This code will calculate the sum of numbers from 1 to 10.  In this example statement ‘Total=Total+i’ repeats 10 times.  ‘i’ and ‘Total’ are the variable used in the code.
In this example for For….Next loop following steps took place:
This loop will work as long as the value of ‘i’ is more than 1 and less than 10.
  1. The For loop initializes the value of ‘i’ as 1. As 1 is less than, 10 the next statement executed.
  2. The code   ‘Total=Total+i’ is executed for first time.
  3. The code ‘Next’ increments the value of ‘i’ by 1
  4. The control shifts to the begining of the loop, where the value of the variable ‘i’ value is checked
  5. Till value of ‘i’ is less than 10 step 2 and 3  are executed.
  6. As soon as value of ‘i’ becomes more than 10  the loop terminates. 
0 comments… add one

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.