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!
One thought on “Array vs table – store color property speed comparison”