Tuesday, February 10, 2009

Excel VBA Macro Tutorial: Looping

What is looping? In simple term, looping is a process of repeating tasks. There are three types of loops, For-Next, Do-Loop, and While-Wend. Which we will use depends on the objectives and conditions.


For-Next loops

Repeats a group of statements a specified number of times.

Syntax:

For counter = start To end [Step step]
[statements]
[Exit For]
[statements]
Next [counter]


The following example, we will puts number words "Excel VBA Macro" to range A1:A10 in active sheet.

Sub putWords()
For myNum = 1 To 10
ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
Next myNum
End Sub


Do-Loop loops

Repeats a block of statements while a condition is True or until a condition becomes True.

Syntax:
Do [{While | Until}  condition]
[statements]
[Exit Do]
[statements]
Loop

Or, you can use this syntax:

Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until} condition]

The output of this examples exactly same as example above.

Sub putWords2()
myNum = 0
Do
myNum = myNum + 1
ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
Loop While myNum <>
End Sub

Sub putWords3()
myNum = 0
Do Until myNum = 10
myNum = myNum + 1
ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
Loop
End Sub

While-Wend loops

Executes a series of statements as long as a given condition is True.

Syntax:
While condition
[statements]
Wend

Example:

Sub putWords4()
myNum = 0
While myNum < 10
myNum = myNum + 1
ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
Wend
End Sub

1 comment:

  1. Very nice and clean information about the theoretics about programming. If you want to look at more advanced examples here is a site!

    http://vbaexcel.eu/vba-macro-code/google-translate-by-internet-explorer-automation

    ReplyDelete