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