Excel VBA Basic Training

Excel VBA Basic Training

Loops

VBA LOOPS

A loop is used to perform the same task multiple times. This enables you to execute a block of code multiple times without having to replicate the code multiple times.  This simplifies your code.

Loop Type & Description
1FOR LOOP: Executes a sequence of statements multiple times and abbreviates the code that manages the loop variable.
2FOR EACH LOOPThis is executed if there is at least one element in the group and reiterated for each element in a group.
3WHILE LOOP: This tests the condition before executing the loop body.
4DO WHILE LOOP:  The do..While statements will be executed as long as the condition is True.(i.e.,) The Loop should be repeated till the condition is False.
5DO UNTIL LOOP:  The do..Until statements will be executed as long as the condition is False.(i.e.,) The Loop should be repeated till the condition is True.

 

FOR LOOP – The FOR LOOP uses a index variable to count the number of time the loop has run. THe index variable can be a number, a row, a worksheet, etc. For example to loop all rows, or loop all worksheets.

Dim i, lastRow As Integer
lastRow = Activesheet.UsedRange.Rows.Count
For i = 1 to lastRow
   MsgBox "Row # " & i
Next i
Dim i, WS_Count As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To WS_Count
    MsgBox ActiveWorkbook.Worksheets(i).Name
Next i

FOR EACH – Same as FOR loop but instead of using an index it uses an object. For example instead of counting the number of worksheets and looping 1 to number of worksheets. The FOR EACH loop will loop however many Worksheets they are referencing the loop count as the worksheet objects instead of a number.

Dim ws As Worksheet
For Each ws in Worksheets
    msgbox ws.Name
Next ws

WHILE LOOP – Loop while condition is TRUE.  Once condition evaluates as FALSE end looping.

While Counter < 15     
      Counter = Counter + 1   
      msgbox " Counter is : " & Counter
   Wend

 

DO WHILE – Loops while a condition is TRUE.

Dim region as String
Dim i as Long
i = 1
region = Range("C2").Value
DO WHILE region = "US"
   region = Cells(i, 3).Value
   i = i + 1
   Msgbox "Region: " & region & ", Row: " & i
Loop

DO UNTIL LOOP – Loops until contition is FALSE

Dim i as long
i = 1
Do Until IsEmpty(Cells(i,1))
    msgbox "Rows: " & i
i = i + 1
Loop

[efb_likebox fanpage_url="https://www.facebook.com/vbamarket/" box_width="840" box_height=340"" responsive="1" show_faces="1" show_stream="1" hide_cover="1" small_header="0" hide_cta="0" locale="en_US"]

Add comment