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.
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.
"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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With