Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Alan
 
Posts: n/a
Default EXCEL, VSTO: Fastest way to access multiple cells

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

  #2   Report Post  
Jim Rech
 
Posts: n/a
Default

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
|


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel and Access talking GJR3599 Excel Discussion (Misc queries) 0 March 29th 05 04:59 PM
Excel 97 - Selecting multiple cells from 2 different Pivot Tables jjones_HAB Excel Discussion (Misc queries) 0 March 1st 05 01:01 PM
opening multiple instances of excel John B Excel Discussion (Misc queries) 4 January 29th 05 12:31 AM
stop excel from shifting cells up when a query returns no data DrLostinExcel Excel Worksheet Functions 2 November 9th 04 06:44 PM
How do I email selected excel cells to multiple recipients? AShaffer Excel Worksheet Functions 1 October 30th 04 01:37 PM


All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"