0

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?

Tim Stack
  • 3,209
  • 3
  • 18
  • 39
  • Why don't you just run both on the same amount of cells and stop the time with a timer? Then you know it. • Actually that is what anyone else here would need to do too. See [How do you test running time of VBA code?](https://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code) – Pᴇʜ Mar 27 '19 at 14:57
  • Because I am under the impression some of us here know a lot more about computing than I do, and I would like to understand why this difference, if there is any, exists – Tim Stack Mar 27 '19 at 14:59
  • I suggest to read the data into an array and then test the values in the array for `vbNullString`. This should be a alot faster than testing each cell. Actually you can only do this with `.Value` but not with `.Interior.Color`. – Pᴇʜ Mar 27 '19 at 15:01
  • I'm afraid I can't do that, it's a tad more complicated than that. Before a cell is checked, values in other columns need to be found and compared with other data. – Tim Stack Mar 27 '19 at 15:03
  • You still can use an array for this check only and it still should be faster than reading each cell on its own. • Or use the array for your other checks and compares too to make these even faster too. – Pᴇʜ Mar 27 '19 at 15:05
  • In what sense would that be faster? I know check if a cell holds a value, and if not, I populate the cell. By your method I would instead place the cell's address in an array and after all cells have been added to the array I'd loop through it to check the values. Isn't that a worse method? – Tim Stack Mar 27 '19 at 15:13
  • 2
    I've been programming in Excel for years, but know almost nothing about its internals, at last nothing that would allow me to predict Value vs Color performance. I suspect that's true for many others who answer here too. As @PEH notes, a quick test would provide a pretty reliable answer to your question. – Tim Williams Mar 27 '19 at 15:19
  • @TimStack You can read a whole range into an array like `MyArray = Range("A1:B100").Value` which is only one read action instead of 100 read actions. This makes it a lot (not only a bit) faster. Then you can access the values in that array like `MyArray(row, column)` eg `MyArray(5, 2)` will return the value of B5 (which is also much faster than reading the cell). In the end you can even write the whole array at once back to the cells `Range("A1:B100").Value = MyArray`. Note that the range size must exactly match the array size here when writing data. – Pᴇʜ Mar 27 '19 at 15:30
  • @Pᴇʜ cheers, that seems like a sound method. I've always steered clear from multi-dimensional arrays but I have to widen my knowledge – Tim Stack Mar 27 '19 at 15:37
  • @TimWilliams got it. Thanks for the time – Tim Stack Mar 27 '19 at 15:37
  • @TimStack have a look here for a performance test: [Performance difference between looping range vs looping array](https://stackoverflow.com/questions/33302962/performance-difference-between-looping-range-vs-looping-array) It's 96.39% faster! – Pᴇʜ Mar 27 '19 at 15:38
  • Blimey, that's way more significant than I had anticipated. Very useful information, cheers! @Pᴇʜ if you make that into an answer I'll happily accept it – Tim Stack Mar 27 '19 at 15:45

2 Answers2

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

Dominique
  • 16,450
  • 15
  • 56
  • 112
2

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73