Can VBA Arrays store cell properties like Font Colors or Fill Colors?

user_10175920 0
Viewed 1861

The screenshot below shows that dates filled are highlighted with different colors and I need to add more to flag different issues from the installation process.

img

I have to calculate productivity and account for the installation issues in the calculation. The only way for me to identify the issues is by assigning different fill colors/font colors.

If I pass this range of data in a variant array, will I be able to refer font/fill color properties or is there an indirect way to refer to the cell address from the array?

vba excel arrays
1 Answers

I agree with @chris neilsen , but... I just realized that You can implement to your Array 3rd dimension, which can hold, for example, color property.

I checked that. Even on small amount of data it is faster than standard approach to this issue!

Let's show it on example code: (worksheet structure) in first row are headers, in first column we got ID of products. So first of all we need to declare and build our Array (called zakres) with cells value and color property.

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 property

        Next

    Next

After all things we want to do on that table - I mean Array zakres - just paste all the values to your worksheet, using double loop (rows and columns)

For i = 1 To lastRow
    For j1 = 1 To lastCol
        .Cells(i, j1) = zakres(i, j1, 1)
        .Cells(i, j1).Interior.Color = zakres(i, j1, 2)
    Next
Next

I made that modification to my code, which is using only 1 property of cell - color property - and that approach accelerates my code a lot.

Ps. If You don't want to fill Array with empty cells just make condition functions in loops, which will skip iterations for empties. It will also help and speed up macro.

    If .Cells(i, j1) <> "" Then
        zakres(i, j1, 1) = .Cells(i, j1)
        zakres(i, j1, 2) = .Cells(i, j1).Interior.ColorIndex
    End If

I hope it will help somebody :)

user_116365880
Related