EXCEL, VSTO: Fastest way to access multiple cells

[Alan]

Hi all,

Can anybody help clarifying my issues?

In one part of my Excel/VSTO application, I have to compare values of cells
and assign a background color according to the result of the comparison.
The code looks something like this:

....
rng = wkSheet.Range("A1:Z1000")
vals = Array(1000 * 26 ) ' same size as 'rng'

Do ( for the whole range )
If vals(i, j) > XXX Then
rng(i, j).Interior.ColorIndex = 1
Else If vals(i, j) = XXX Then
rng(i, j).Interior.ColorIndex = 2
Else ' vals(i, j) < XXX Then
rng(i, j).Interior.ColorIndex = 3
End If
Loop

It takes > 10 seconds on my machine for excel to complete the loop, i.e.
excel will stop responding for > 10 secs:
this is not acceptable for my app.

I tried:
- ThisApplication.ScreenUpdating = False
- ThisApplication.Calculation = Excel.XlCalculation.xlCalculationManual
they don't help much.


*** I don't want to use "Conditional formatting" for security reason.


To my understanding, for every change/call to a member/method of a Range
object, there is one out-of-process communication
to the Excel COM Server, correct? (sorry if I used wrong technical terms
about COM; not a COM fan )

And this out-of-process communication is slow; I have 1000*26 calls in my do
loop, so it's (slow*26000) = damn slow

My idea is to have a local copy (variable) of a Range object, then make some
changes and later notify the Excel COM Server of the change just once,
something like:

....
rngNotAsACOMObj = (ISomeInterfaceThatDoesTheTrick)wkSheet.Range("A1:Z1000")

rngNotAsACOMObj.NoConectionToCOMServer()
rngNotAsACOMObj.Interior.Color = 10 <=== Excel doesn't see the change yet.
rngNotAsACOMObj.Interior.ColorIndex = 4 <=== Still not.
rngNotAsACOMObj.NotifyChange() <=== Excel sheet gets updated.

Is this possible at all?
Any better solution exist?


Cheers -- Alan

 

[George Nicholson]

You might consider a more Excel-efficient logic structure

Dim cl as Range 'a Cell
rng = wkSheet.Range("A1:Z1000")

For each cl in rng
With cl
Select Case .Value
Case > XXX
.Interior..ColorIndex = 1
Case = XXX
.Interior.ColorIndex = 2
Case < XXX
.Interior.ColorIndex = 3
End Select
End With
Next cl

vals(i, j) and rng(i, j) require time-eating evaluations in a Loop, and you
are using between 2 and 4 of them (depending on which condition is
satisfied) for every cell in the range. Mine uses one handle.

Don't know how that dovetails with COM.

HTH,