Optimized FOR Loop in VBA:6.0

Hi Everyone!


Writting a For loop in vba is not a big deal but one common mistake which lot of developers tend to do. They tend to write the for as below:


InEfficient Method:

Sub prTest()


'Error Handler
On Error GoTo Err_PrTest:


Dim intStartCounter     As Integer
Dim intLoopCounter      As Integer
Dim rngTest             As Range


intLoopCounter = 5
Set rngTest = Range("Test")


For intStartCounter = 1 To intLoopCounter
    If rngTest(intStartCounter, 1) = "Test" Then
        'Do Nothing
    End If
Next intStartCounter


Exit_PrTest:
    Exit Sub
Err_PrTest:
    GoTo Exit_PrTest
End Sub


2)Efficient Method


Sub prTest()


'Error Handler
On Error GoTo Err_PrTest:


Dim intStartCounter     As Integer
Dim intLoopCounter      As Integer
Dim rngTest             As Range


intLoopCounter = 5
Set rngTest = Range("Test")


For intStartCounter = 1 To intLoopCounter
    If rngTest(intStartCounter, 1) = "Test" Then
        'Exit the for loop as soon as you find the Test. Other as soon as the purpose of for loop
        'is satisfied exit
        Exit For
    End If
Next intStartCounter


Exit_PrTest:
    Exit Sub
Err_PrTest:
    GoTo Exit_PrTest
End Sub


The difference between 1 & 2 is that in case of 2 you exit the for loop once the purpose of FOR loop is satisfied and therefore you save execution time. 2 will make code more optimized.


Cheers!