Performance Tunning: ActivateCell vs Cell Referance

 

Today I’d like to talk about an important issue that pertains to the performance of VBA code in Excel.  What I’m referring to is the use of .Activate. To be honest, I can’t think of a time when that function should ever be called in VBA code.  The method is very much designed for visual identification of a focused object in the user interface (UI), and not the manipulation of underlying properties. Using the .Activate function carries a curtain amount of overhead that is not required when all you are doing is iterating through a range of cells. As an example I’d like to show you two separate snippets of code. The first example iterates through all the cells in a range using the .Activate approach, while the second merely iterates through the same cells without activating any of them:

Example 1:

Sub UpdateValuesUsingActiveCell()
    Dim startTime As Date
    startTime = Now
    For Each c In Worksheets("Sheet1").Range("A1:G1000").Cells
        c.Activate
        ActiveCell.Value = 1
    Next
    MsgBox "Execution Time: " & Format(Now - startTime, "HH:mm:ss:ms")
End Sub

Example 2:

Sub UpdateValuesUsingCellRef()
    Dim startTime As Date
    startTime = Now
    For Each c In Worksheets("Sheet1").Range("A1:G1000").Cells
        c.Value = 1
    Next
    MsgBox "Execution Time: " & Format(Now - startTime, "HH:mm:ss:ms")
End Sub

 

The result of this simple test speaks for itself.  In example 1, the ActiveCell technique took just over 4 seconds to run (4.124 sec) while the second method executed in nearly a quarter of that time (1.121 sec).  So if this is the case, why are there so many references online using ActiveCell and .Activate.?  My best guess it that it stems from recording macros and then the re-purposing of code without fully understanding the implications.  When a macro is a recorded it keeps a record of the user’s interactions with the application.  Given that VBA normally has no need to interact with UI, there is no reason to program it as such.  If you don’t believe me, just think of any tool you use in Excel (Formatting, Filling Values, Cell Styles etc.).  Do you see these tools iterating through cells and activating each one?  I rest my case.

 

Conclusion: Don’t call the Activate method on a Cell.  You can accomplish the same thing with a reference to the cell, and it will execute much faster.