Can arrays store cell color property?

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?

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/

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

3 thoughts on “Can arrays store cell color property?”

Leave a Reply

Your email address will not be published. Required fields are marked *