Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel and Access talking | Excel Discussion (Misc queries) | |||
Excel 97 - Selecting multiple cells from 2 different Pivot Tables | Excel Discussion (Misc queries) | |||
opening multiple instances of excel | Excel Discussion (Misc queries) | |||
stop excel from shifting cells up when a query returns no data | Excel Worksheet Functions | |||
How do I email selected excel cells to multiple recipients? | Excel Worksheet Functions |