Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vsto08-ExcelAddIn-Performance Increase while Dragging Excel Window
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.srcCellAdd ress).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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vsto08-ExcelAddIn-Performance Increase while Dragging Excel Window
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 -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97135 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vsto08-ExcelAddIn-Performance Increase while Dragging Excel Wi
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 "broro183" wrote: 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 -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97135 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Events for CheckBox inside Excel using ExcelAddIn project | Excel Programming | |||
Increase the number of lines buffered in the Immediate Window? | Excel Programming | |||
ExcelAddIn - Excel Crashes under User Account - tool to troubleshoot | Excel Discussion (Misc queries) | |||
In Excel - how to delete new window created from window on tool ba | Excel Worksheet Functions | |||
How do I undo the Excel, Window Menu, New Window command | Excel Discussion (Misc queries) |