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.

Suckow

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.

Sensational

Do you happen to know if there is any way to access the cell's style reference (StyleID) that I can see in the XML file from VBA/VSTO? In this case one could read all style data from Excel and then map each cell to its style with only one Interop call per cell. – Ceraceous

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

Improbity

Right back at you, @brettdj. I knew there was a way to do it in VBA. Thanks for sharing the code. – Sensational

Thanks, @brettdj! Its a neat approach, though Excel's "Find" is still pretty slow (I know for values, at least, I was much better off storing the 2D array, and then searching through it myself). As far as searching for colors is concerned, though, the code you provided is good if there's only going to be a few colored cells. If there's going to be several hundred, though, the system bogs down -- probably because of the address and union operation. There's probably ways to make it more efficient, but, as per Rachel's response, looks like there's no truly efficient way. But thanks anyway! – Suckow

@MichaelZlatkovsky Find is typically very quick for values unless you have a huge amount - but agree that for formatting this could be a lengthy outcome depending on the size of your file. There are some other workarounds you could run use XLM to detect some cell properties, see xcell05.free.fr/morefunc/english/xlm.lire.cellule.htm – Improbity