I am big fan of arrays. Since I found out about arrays I have been trying to use them everywhere! Or maybe just there where it is possible.
I recently wrote about them, if You missed it I highly recommend reading my post https://simpleexcelvba.com/arrays-one-of-the-best-things-you-can-learn-in-excel-vba/ We all know that arrays can store values, for example 2 dimensional array can hold a Excel table values. But can arrays store cell color property?
Everyone will tell You, that it is not possible. That arrays store only cell values, not their properties and that’s why operating on them is so much faster – array “cells” are lighter than standard table cells.
I agree with those people… and also kinda disagree. Why?
You are not limited by 2 dimensions, so why not use next one?
Let’s use array 3rd dimension! To declare value and chosen cell property. In most of cases we need a cell fill or font color, so only one additional place in array.
Take a look at the code I prepared.
Checking how many rows and columns are filled and declaring its first place in 3rd dimension for cell value.
zakres(i, j1, 1) = .Cells(i, j1)
And for cell interior color.
zakres(i, j1, 2) = .Cells(i, j1).Interior.ColorIndex
Here is the first part:
Sub main1()
Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet
Dim comper As Range
Dim j1 As Long
Dim i As Long
Dim zakres As Variant 'our 3 dimension Array
Set ws = Sheet1 'set your worksheet
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
After all things we want to do on that table – I mean on array – just paste all the values to your worksheet to cells value and to cell color property.
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
End Sub
So! This is it! The alternative for standard approach! Maybe not directly, but arrays can actually store cell color property, in their own way!
You may said, that it will be slower than standard table. That all additional dimensions, values, colors etc.
I checked that. Go to: https://simpleexcelvba.com/array-vs-table-fill-color-property-speed-comparison/
3 thoughts on “Can arrays store cell color property?”