VSTO: getting Excel cell properties in bulk

Question

When retrieving values ​​from a range in Excel, it’s 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, this doesn't really matter, but with 10,000 rows of 20 columns, it certainly does. So far so good.

My question is: has anyone found a way to make the same "bulk" choice for other properties? For example, I want to find which cells are colored in a certain way. I would like to do something like "range.Interior.Color", but returns only one value, not an array of values. And so I am ending a cycle that is probably 100 or even 1000 times slower. For large tables, this is really a killer.

PS: It seems that .Formula is behaving exactly the same. Meaning: I can pick up multiple instances at a time. But I have not dimmed the colors to play well.

I appreciate your help!

Answer 1

I do not think that you can get these properties as an array due to how Excel stores this information. Excel does not save the formatting for each cell separately, but saves a specific combination of formats along with an internal "list" of ranges that use this format.

You can get an idea of ​​how formatting is stored by creating a small test file with various formats and saving it in 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 in a certain way."

in VBA, you can start a quick procedure using the Find method, which searches by format. For example, to find all cells with the same cell font color and interior color as a cell in A1. I suppose you can use something like this 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