Performance difference between checking a cell'

Question

I am running an asset management project in excel. Using a connection with an SQL database I am able to import large numbers of maintenance plans.

Based on what I import, I fill a number of cells with specific short strings, and I additionally paint the interior of the respective cells with one of two colours. Cells that later are populated by the user remain unpainted.

Cells populated using the database data will always be coloured

When I later run a command that populates a large number of cells again, some of these cells may already be populated either by the user or the database. These specific cells must be skipped, and so far I am aware of three possible methods of determining if a cell must be skipped:

1. Checking with the database (slow): a poor solution since the cells have already been populated with the database, another run would be overdoing it
2. Checking wether the cell is populated: If not cell.Value = vbNullstring Then
3. Checking whether a cell is coloured: If not cell.Interior.Color = vbRed Then

Now, because in theory the amount of populated cells could be up to half a million or even more, I am wondering about the performance differences between the second and third option

Is there any noticeable difference between checking a cell's value versus a cell's interior colour?

Answer 1

Just extended the code from here: Performance difference between looping range vs looping array

Range tested: A1:A100000

Read/Write Cell        = 15,765625 seconds
Read/Write Array       = 0,203125 seconds

Read Cell              = 0,37109375 seconds
Read Array             = 0,0234375 seconds
Read Interior Color    = 1,421875 seconds

So you have the direct comparison between reading color vs reading array value too.

---

Option Explicit

Const strRANGE_ADDRESS As String = "A1:A100000"

Sub LoopRangeReadWrite()

    Dim r As Range
    Dim lStart As Double
    Dim lEnd As Double

    lStart = Timer

    For Each r In Range(strRANGE_ADDRESS)
        r.Value = r.Value + 1
    Next r

    lEnd = Timer

    Debug.Print "Read/Write Cell = " & (lEnd - lStart) & " seconds"

End Sub

Sub LoopArrayAddOne()

    Dim varArray As Variant
    Dim var As Variant
    Dim lStart As Double
    Dim lEnd As Double

    lStart = Timer

    varArray = Range(strRANGE_ADDRESS).Value
    For Each var In varArray
        var = var + 1
    Next var
    Range(strRANGE_ADDRESS).Value = varArray

    lEnd = Timer

    Debug.Print "Read/Write Array = " & (lEnd - lStart) & " seconds"

End Sub

Sub LoopRangeReadColor()

    Dim r As Range
    Dim lStart As Double
    Dim lEnd As Double
    Dim a As Long

    lStart = Timer

    For Each r In Range(strRANGE_ADDRESS)

        a = r.Interior.Color
    Next r

    lEnd = Timer

    Debug.Print "Read Interior Color = " & (lEnd - lStart) & " seconds"

End Sub

Sub LoopRangeReadValue()

    Dim r As Range
    Dim lStart As Double
    Dim lEnd As Double
    Dim a As Variant

    lStart = Timer

    For Each r In Range(strRANGE_ADDRESS)
        a = r.Value
    Next r

    lEnd = Timer

    Debug.Print "Read Cell = " & (lEnd - lStart) & " seconds"

End Sub

Sub LoopArrayValue()

    Dim varArray As Variant
    Dim var As Variant
    Dim lStart As Double
    Dim lEnd As Double

    lStart = Timer

    varArray = Range(strRANGE_ADDRESS).Value
    For Each var In varArray
        var = var + 1
    Next var

    lEnd = Timer

    Debug.Print "Read Array = " & (lEnd - lStart) & " seconds"

End Sub

Answer 2

There seems to be a performance difference indeed. I just checked a cell's value and a cell's interior colour for one hundred million times and there are clear differences:

Checking the value :  456 seconds
Checking the colour: 1281 seconds

In other words: checking the values goes ±2.8 times faster (following this single simple experiment).