I haven't used VSTO except to dabble a bit. But from my experience with
automation I find it's best (if possible) to run a routine that is in the
Excel instance and, if possible again, to have that instance of Excel in the
Windows foreground. If you cannot have an Excel workbook/template preset
with the routines you need you might be able to add them at run time using
VBA Extensibility.

Fyi this routine takes about 3.5 seconds in standalone Excel on a 2.7GHz
machine, so this is the kind of speed that I would think you could get using
this approach.

Sub a()
Dim Cell As Range
Dim StTime As Double
Dim CellVal As Double
Application.ScreenUpdating = False
StTime = Timer
For Each Cell In Range("A1:Z1000")
CellVal = Cell.Value
If CellVal 100 Then
Cell.Interior.ColorIndex = 1
ElseIf CellVal = 100 Then
Cell.Interior.ColorIndex = 2
Else
Cell.Interior.ColorIndex = 3
End If
Next
MsgBox Timer - StTime
End Sub

--
Jim
"Alan" wrote in message
...
| 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
|