CODEWITHSUNDEEP
vbaperformanceexcelconditional-formatting
DavidN
DavidN

Reputation: 712

Conditional Formatting vs VBA formatting speed/memory

I'm developing a template spreadsheet that will resize arrays of data when refreshed. Currently I'm using conditional formatting to add borders as the arrays grow and vice-versa.

Is it cleaner to use VBA to format the cells when they're refreshed rather than having the majority of the sheet subject to the conditional formatting rules? What are the benefits of one vs. the other?

Thanks!

Upvotes: 0

Views: 3019

Answers (1)

Tim
Tim

Reputation: 2902

Consider the following code:

Option Explicit

Private Sub fill()
    Dim i As Long
    Dim num As Long

    Randomize

    For i = 1 To 500000
        num = Int(50 * Rnd) + 1
        Sheet1.Cells(i, 1).Value = num
        Sheet1.Cells(i, 2).Value = num
    Next
End Sub

Sub format()
    Dim i As Long

    Debug.Print Now
    With Sheet1
        For i = 1 To .UsedRange.Rows.Count
            If .Cells(i, 1).Value > 20 Then .Cells(i, 1).Interior.ColorIndex = 20
        Next
    End With
    Debug.Print Now
End Sub

Sub conditionally_format()
    Debug.Print Now

    With Sheet1.Columns(2)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=40"
        .FormatConditions(1).Font.Color = -16383844
        .FormatConditions(1).Font.TintAndShade = 0
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.Color = 13551615
        .FormatConditions(1).Interior.TintAndShade = 0
    End With

    Debug.Print Now
End Sub

On my HP-asaurus, I get the following results

format:
7/22/2016 1:10:34 PM
7/22/2016 1:10:49 PM
conditionally_format:
7/22/2016 1:25:06 PM
7/22/2016 1:25:07 PM

However, it should be noted that the results for the conditionally_format sub represent the time it takes to run that code, not to actually conditionally format the cells. I don't know how to measure that except by changing a value on the spreadsheet and seeing how long it takes. Even doing that, is impossible because it happens so fast that I can't measure it. This means the code to loop through each cell runs much slower than setting a conditional format. This really is to be expected since Excel is loading each Cell object, then checking the cell objects .Value and then setting the Interior.ColorIndex property based on that evaluation. The same is done with conditional formatting except VBA stays out of the picture. It is Excel's underlying (and optimized) compiled code performing the actions. Is the speed difference huge? Not really. My slow laptop did half a million comparisons in 15 seconds vs the essentially instantaneous formatting by conditional formatting rules.

Benefits... that's a tougher call. I would think more end users would understand conditional formatting rules, which would also mean more end users can monkey with them. This may not be desirable. Is it cleaner? Tough call again. If you set the formatting rules from the UI, a developer may not think to look for any conditional formatting rules, and since nothing shows up in the IDE about conditional formatting, this again could lead to undesirable consequences. There have been bugs with conditional formatting in the past, but I do not know if this is still an issue. I would think the method you ultimately choose will come down to how much control you want to give an end user vs. how much code you want to create.

Upvotes: 2

Related Questions