Array vs table – store color property speed comparison

All in all, array is awesome to operate on big data. It is much faster vs standard Excel table. But how they gonna behave in the situation presented like in https://simpleexcelvba.com/can-arrays-store-cell-properties-like-font-colors-or-fill-colors/ where I’m using 3rd dimension to store color property in array?

I took code from previous post, linked above, prepared code for standard table and the only change was the range. To make operation on data more “complex” I put a condition for different cell color:

            If (zakres(i, j1, 1) Mod 2) = 0 Then
                zakres(i, j1, 2) = 3
            Else
                zakres(i, j1, 2) = 4
            End If

And the same thing I made in the table test code.
I did that just to simulate real code conditions – complexity of daily office tasks.

Alright, here we are! Without further ado I want to present You the code part!

Code for array test:

Sub main1()

Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet
Dim j1 As Long
Dim i As Long
Dim clock As Double

Dim zakres As Variant 'our 3 dimension Array

clock = Timer

Set ws = Sheet1 'set your worksheet
Application.ScreenUpdating = False

With ws
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

    ReDim zakres(1 To lastRow, 1 To lastCol, 1 To 2)

    For i = 1 To lastRow

        For j1 = 1 To lastCol

            zakres(i, j1, 1) = .Cells(i, j1)                        'cells value
            zakres(i, j1, 2) = .Cells(i, j1).Interior.ColorIndex    'cells color

        Next

    Next
    
    For i = 1 To lastRow
        
        For j1 = 1 To lastCol
            
            If (zakres(i, j1, 1) Mod 2) = 0 Then
                zakres(i, j1, 2) = 3
            Else
                zakres(i, j1, 2) = 4
            End If
        
        Next
        
    Next
   
    For i = 1 To lastRow
        For j1 = 1 To lastCol
            .Cells(i, j1) = zakres(i, j1, 1)
            .Cells(i, j1).Interior.ColorIndex = zakres(i, j1, 2)
        Next
    Next

End With

Debug.Print Round(Timer - clock, 3)

Application.ScreenUpdating = True

End Sub

Code for table test:

Option Explicit

Sub main1()

Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet
Dim j1 As Long
Dim i As Long
Dim clock As Double

Dim zakres As Variant 'our 3 dimension Array

clock = Timer
Application.ScreenUpdating = False
Set ws = Sheet1 'set your worksheet

With ws
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    
    For i = 1 To lastRow
        
        For j1 = 1 To lastCol
            
            If (.Cells(i, j1) Mod 2) = 0 Then
                .Cells(i, j1).Interior.ColorIndex = 3
            Else
                .Cells(i, j1).Interior.ColorIndex = 4
            End If
        
        Next
        
    Next

End With

Debug.Print Round(Timer - clock, 3)
Application.ScreenUpdating = True

End Sub

I made couple of tests for same amount of columns in data – 10, but changing the rows number. The results are set in the table and the below values are presented in seconds.

Rows(x10)	Array	Table
10	0,004	0,008
100	0,035	0,023
1000	0,316	0,164
10000	4,156	1,727
100000	45,812	14,805

Results & conclusions

Unfortunately my enthusiasm turned down after all the tests. To be honest, I was a little bit excited at the beginning, that I found out amazing method to deal with cell properties.

From test to test I was realizing, that array method is not faster, not even equal in time, but slower and slower. Third dimension made everything turtle fast or at least in case of that complex conditional code in the middle of loop I put.

It is possible to store color property in array, but with what cost? You can clearly see that if it’s not necessary and you need to work with any cell property, instead of array just use looping through standard Excel table cells to get their color property. But in case there is no place for standard approach, now there is an alternative!

Stay cool and use this knowledge wisely!