MorkaLork Development

Interesting stuff I've picked up over the years...

VBA Loops

2009-07-29 19:59:28 | 546 views | vba loop loops collection

How to loop in VBA


There are four standard ways to loop in VBA and they work like in most other programming languages, they are as follows:



We'll look through each of them and at the end of this article there will be an example sub to test with and a screen shot of what it outputs.
There will be two variables declared already:


Dim row As Integer
Dim column As Integer




The loops



Do-Until



'*****************
'The Do-Until loop
'-----------------
'
'Syntax:
'Do
' [Do things here]
'Loop Until [expression]
'*****************
row = 1
column = 1

Cells(row, column).Select

Do
Cells(row, column).Value = "Do-Until " + CStr(row)
row = row + 1
Loop Until row = 5


This loop will loop until an expression is true. It works similar to the Do-While but will run at least one time.
In our example we increment the variable row (which start with the value 1) every loop until it reaches the value 5.


Do-While



'*****************
'The Do-While loop
'-----------------
'
'Syntax:
'Do While [expression]
' [Do things here]
'Loop
'*****************
row = 1
column = 2

Cells(row, column).Select

Do While (row < 5)
Cells(row, column).Value = "Do-While " + CStr(row)
row = row + 1
Loop


This is similar to the Do-Until loop but won't always run, e.g. it can fail directly if the expression is true.


For-Next



'*****************
'The For-Next loop
'-----------------
'
'Syntax:
'For [var = startValue] to [stopValue]
' [Do things here]
'Next [var]
'*****************
column = 1

Cells(row, column).Select

For row = 6 To 9
Cells(row, column).Value = "For-Next " + CStr(row)
Next row


The For-Next loop need a start and a stop value. It will then loop until the start value equals the stop value. Needless to say, the start value really should be set lower than the stop value.


For Each



'*****************
'The For-Each loop
'-----------------
'
'Syntax:
'For Each [var] in [collection]
' [Do things]
'Next [var]
'*****************
row = 6
column = 2

Cells(row, column).Select

Dim collection(0 To 3) As String
collection(0) = "For Each 1"
collection(1) = "For Each 2"
collection(2) = "For Each 3"
collection(3) = "For Each 4"

For Each thing In collection
Cells(row, column).Value = thing
row = row + 1
Next thing


The For Each loop loops through a collection of objects, in this case, an array.


The complete sub



Public Sub looping()

Dim row As Integer
Dim column As Integer

'*****************
'The Do-Until loop
'-----------------
'
'Syntax:
'Do
' [Do things here]
'Loop Until [expression]
'*****************
row = 1
column = 1

Cells(row, column).Select

Do
Cells(row, column).Value = "Do-Until " + CStr(row)
row = row + 1
Loop Until row = 5



'*****************
'The Do-While loop
'-----------------
'
'Syntax:
'Do While [expression]
' [Do things here]
'Loop
'*****************
row = 1
column = 2

Cells(row, column).Select

Do While (row < 5)
Cells(row, column).Value = "Do-While " + CStr(row)
row = row + 1
Loop


'*****************
'The For-Next loop
'-----------------
'
'Syntax:
'For [var = startValue] to [stopValue]
' [Do things here]
'Next [var]
'*****************
column = 1

Cells(row, column).Select

For row = 6 To 9
Cells(row, column).Value = "For-Next " + CStr(row)
Next row


'*****************
'The For-Each loop
'-----------------
'
'Syntax:
'For Each [var] in [collection]
' [Do things]
'Next [var]
'*****************
row = 6
column = 2

Cells(row, column).Select

Dim collection(0 To 3) As String
collection(0) = "For Each 1"
collection(1) = "For Each 2"
collection(2) = "For Each 3"
collection(3) = "For Each 4"

For Each thing In collection
Cells(row, column).Value = thing
row = row + 1
Next thing






End Sub


If you run this, this will be the outcome:

Displaying four loops


Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.