Styjun

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?

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).

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