vsto08-ExcelAddIn-Performance Increase while Dragging Excel Window

[RichardB]

Hello looking for advice, suggestion, thoughts

I have a VB automation add-in for excel. This development / test platform
is vsto2008 on vista target excel 2003


Working on application tuning I have discovered something possibly unusal
and wonder if any one has insight or can explain - please.

Part of the automation writes to a sheet one cell at a time with
request.theSheetReference.Range(request.srcCellAddress).Offset(row,
col).Value = "TheText" I know there are other methods for sending to the
sheet, thats not what this question is about.

Output to the sheet is pretty fast, about 3000 cells in a minute. The above
is in a loop that runs on a dedicated thread and I am working on the tuning.

I have discovered how to speed it up and get 3000 cells in about 20
seconds, but why? how?

And even trickier; what code can I write to emulate this?

I discovered that while automation code is writing to the sheet it speeds up
dramatically when I grab either excel's or the workbook's title bar with the
mouse and simply move the window. If I release it it slows, grab it again
for a speed boost.

Does any one know why?

Hey this could be a nice program feature - like an easter egg or something.
Also I would have to do less error checking. If the user were busy speeding
up the application by wiggling the window around, I would not have to check
for user interference on the sheet?

Thanks for looking and considering this.

 

[broro183]

hi Richard,

In Excel's VBA, I would suggest using
Code:
--------------------
application.screenupdating = false
--------------------
but I'm not sure if you are using VBA or VB which may use a different
syntax.

I suspect that the action of selecting the title bar implicitly forces
Excel to undergo something similar (if not the same) to the above code.

Also I would have to do less error checking. If the user were busy
speeding up the application by wiggling the window around, I would not
have to check for user interference on the sheet?

While I've read your comment that the thread is not about how to write
information to a sheet, I think it would be worth your while writing
"the text" to an array & then adding this to the sheet in a single hit
(depending on the full size). This is likely to be much quicker than
looping through one cell at a time (esp using the above line of code).
Also, the increased speed further minimises the time for the user's
attention to change focus & I think this is of more value than promoting
the active involvement ("wiggling the window").

If you are planning on distributing this add-in then I highly
recommending adding all the error checking - if you can think of a
potential error it is likely to be raised by users. Plus, it's likely
that there will be some errors that you don't think of which may be
highlighted as you test the error handling that you do add.

hth
Rob

 

[RichardB]

Thanks for the advice.

I agree with the error checking. It is an important step. Tricky part
about an add-in, one error and Excel can vanish; and we will tell ms.

Screen updating. Well I did bench mark this. Certainly it is faster when
we are not updating but it is even more fast when we wiggle the title bar.
It is odd.

By the way, I will keep the screen updating visible for this app. Eye Candy