Getting Excel Cell Properties in Bulk

Question

When fetching values from a range in Excel, it is much more efficient to get the values in "bulk" (as a 2D array) than looping through each row and column. For example:

Dim range = Globals.Table.Range("A1:E5")
Dim values(,) As Object = range.Value

With 25 cells it doesn't make much of a difference, but with 10,000 rows by 20 columns it certainly does. So far so good.

My question is: How can one do "bulk" fetching for other properties?

For example, I want to find which cells are colored a certain way. I would love to do something like "range.Interior.Color", but that returns just one value, not an array of values. And so I end up looping, which is probably 100 or even 1000 times slower. For large tables, this is really a killer.

---

It looks like .Formula behaves just like .Value: I can fetch multiple ones at a time. But I've yet to cajole colors into playing nice.

Answer 1

I don't think you can get those properties as an array because of how Excel stores that information. Excel does not store formatting for each cell individually but rather stores a particular blend of formats along with an internal "list" of the ranges that use that format.

You can get a sense of how the formatting is stored by creating a small test file with various formats and saving it as XML format (in 2010 at least, you need to use "XML Spreadsheet 2003").

This article may also help.

Answer 2

"I want to find which cells are colored a certain way"

in VBA you can run a quick routine using the Find method that searches by format. For example to find all cells with the same cell font colour and interior colour as the cell in A1. I presume you can use something similar in VSTO

Sub FindFormat()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim strAddress As String
    With Application.FindFormat
        .Interior.ColorIndex = [a1].Interior.ColorIndex
        .Font.Color = [a1].Font.Color
    End With
    Set rng1 = Cells.Find("", [a1], xlFormulas, , , , , , True)
    If Not rng1 Is Nothing Then
        strAddress = rng1.Address
        Set rng2 = rng1
        Do
            Set rng1 = Cells.Find("", rng1, xlFormulas, , , , , , True)
            Set rng2 = Union(rng1, rng2)
        Loop While rng1.Address <> strAddress
        MsgBox "Range similar format to A1 is " & rng2.Address
    End If
End Sub