EXCEL, VSTO: Fastest way to access multiple cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi Alan,

Not sure what you are using for code in VSTO but whatever it is changing
cell formats individually is slow. One way to speed up is to colour the
entire range to suit the condition you expect most of the cells to meet.
Then ignore changing these individually in the loop. In VBA, and similar in
VB6 with additional ref to Excel, something like this:

Sub Test()
Dim i As Long, j As Long
Dim rng As Range
Dim wksheet As Worksheet
Dim vals
Dim XXX

Set wksheet = ActiveSheet
Set rng = wksheet.Range("A1:Z1000")

rng.Rows(3).Value = 1
rng.Rows(4).Value = -1

vals = rng.Value

XXX = 0

rng.Interior.ColorIndex = 2

For i = 1 To UBound(vals)
For j = 1 To UBound(vals, 2)
If vals(i, j) > XXX Then
rng(i, j).Interior.ColorIndex = 1
ElseIf vals(i, j) < XXX Then
rng(i, j).Interior.ColorIndex = 3
End If
Next
Next

End Sub

iso of colorindex = 2 (white), maybe use xlAutomatic (-4142)

Regards,
Peter T
 
Back
Top