Keeping Properties and Methods Outside Loops

Your code can get variable values faster than it can get property values. Therefore, if your code gets the value of a property within a loop, it will run faster if you assign the property to a variable outside the loop and use the variable instead of the property inside the loop. The following example is slow because it gets the Value property each time through the loop.

For iLoop = 2 To 200
    Cells(iLoop, 1).Value = Cells(1, 1).Value
Next I

The following example is faster because the value of one property has been assigned to the variable cv before the loop begins. Visual Basic must therefore access only one property value, instead of two, each time through the loop.

cv = Cells(1, 1).Value
For iLoop = 2 To 200
    Cells(iLoop, 1).Value = cv
Next i

If you're using an object accessor inside a loop, try to move it outside the loop. The following example calls the ActiveWorkbook property, the Sheets method, and the Cells method each time through the loop.

For c = 1 To 1000
    ActiveWorkbook.Sheets(1).Cells(c, 1) = c
Next

Rewriting this example using the With statement moves the ActiveWorkbook property and Sheets method calls outside the loop. You could also move these calls outside the loop using an object variable.

With ActiveWorkbook.Sheets(1)
    For c = 1 To 1000
        .Cells(c, 1) = c
    Next
End With