Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
This is an OOP based problem - I think...
Borrowing heavily from Bullen, Bovey & Green's 'Professional Excel Development' I've written an Excel 2003 app, which helps an organisation list their portfolio of current and future production runs and tinker with the pipeline dates, resource loads and what have you. The portfolio itself is a datalist in a defined (expandable rows) area on a sheet and this range is encapsulated within a couple of classes - clsPortfolioItem (each row) and clsPortfolioItems (the collection of rows). This allows me to give each portfolio item properties like Locked, Valid, Ticked. The problem I'm having is that if the user decides to delete, say, 500 rows of portfolio items it can take 30 seconds or so before the Sheet_Change event fires and during this time Ctrl+Break is ignored. What's it doing and is there a way I can speed things up? Br Nick H |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
Without seeing your code, it's hard to tell. Try setting breakpoints
in your code and stepping through it to see where the bottlenecks are. --JP On Oct 7, 9:52*am, Nick H wrote: This is an OOP based problem - I think... Borrowing heavily from Bullen, Bovey & Green's 'Professional Excel Development' I've written an Excel 2003 app, which helps an organisation list their portfolio of current and future production runs and tinker with the pipeline dates, resource loads and what have you. The portfolio itself is a datalist in a defined (expandable rows) area on a sheet and this range is encapsulated within a couple of classes - clsPortfolioItem (each row) and clsPortfolioItems (the collection of rows). This allows me to give each portfolio item properties like Locked, Valid, Ticked. The problem I'm having is that if the user decides to delete, say, 500 rows of portfolio items it can take 30 seconds or so before the Sheet_Change event fires and during this time Ctrl+Break is ignored. What's it doing and is there a way I can speed things up? Br *Nick H |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
Hi JP,
Thanks for the fast response. This is a large project with a vast amount of code - the trouble is I don't know which bit of code might be causing the problem, hence I thought it best not to post any for now. I have tried putting breakpoints on every property procedure within the classes and at the top of the Sheet_Change event (which I thought should be firing first. Like I say, the code ignores all my attempts to Ctrl+Break into break mode and continues to do whatever it is doing until rows are deleted, and it then stops at the Sheet_Change event break point. My guess is that, as well as deleting the Excel rows, it is having to deal with my clsPortfolioItem objects somehow. This is why I started by saying I think it is an OOP based problem. I need someone who understands object oriented programming to give me a steer on what, if anything, I can do to manage this delay. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
Though I would agree with JP about needing to see the code (or at least a
rough outline with details removed) it is difficult to pinpoint the problem. It may be though that the screen is being refreshed with each row deletion which takes time especially with a large number of rows/columns. Before the "deletion" try: application.screenupdating = false but also restore your screen updating (=true) after completing such a task and before presenting other on screen info like a message box, form, or update of the status bar. "JP" wrote: Without seeing your code, it's hard to tell. Try setting breakpoints in your code and stepping through it to see where the bottlenecks are. --JP On Oct 7, 9:52 am, Nick H wrote: This is an OOP based problem - I think... Borrowing heavily from Bullen, Bovey & Green's 'Professional Excel Development' I've written an Excel 2003 app, which helps an organisation list their portfolio of current and future production runs and tinker with the pipeline dates, resource loads and what have you. The portfolio itself is a datalist in a defined (expandable rows) area on a sheet and this range is encapsulated within a couple of classes - clsPortfolioItem (each row) and clsPortfolioItems (the collection of rows). This allows me to give each portfolio item properties like Locked, Valid, Ticked. The problem I'm having is that if the user decides to delete, say, 500 rows of portfolio items it can take 30 seconds or so before the Sheet_Change event fires and during this time Ctrl+Break is ignored. What's it doing and is there a way I can speed things up? Br Nick H |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
Thanks GB,
Okay I'll ask the user to open the VBE and in the immediate window type: application.screenupdating = false - before they delete any rows! <g In all seriousness guys that is not the problem - well if it is I don't think there's much I can do about it. VBA code is not 'running' at this point. Objects are instantiated and ready to offer up their properties if called on by a Sheet_Change event but it is the user that is deleting rows not VBA. When the user selects a large number of rows and right-clicks on a row header and chooses 'Delete' the Sheet_Change event doesn't fire for about 30 seconds while Excel does something in the background. In fact I'm pretty sure no VBA code is running during that time. Any uber OOPers out there? What happens when a user deletes one of your objects without you having the chance to terminate it cleanly? Is Excel just juggling/recovering memory? Has anyone found a way to detect a 'Delete' event before the delete happens? Is the only way round this to write a new 'Delete' menu item for the "Row" shortcut menu item? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
Hi Don,
There are no formulas in the workbook. However, I changed calculation to manual to see if it would make any difference but it didn't. Br, Nick H |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
Hi Nick,
As you say screenupdating is unlikely to be relevant, neither is how long deletion takes. While rows are in the process of being deleted no events will fire, no code will run. What happens when a user deletes one of your objects without you having the chance to terminate it cleanly? Deleting rows will not destroy your class objects, at least not directly. At most, object variable references to entirely deleted rows (or cell) will become useless. It(they) still exists but attempting to access any of its properties will result in a 424 error (as distinct from 91 if the variable 'Is Nothing'). Indeed that's about the only way you are going to conclude for sure that rows have been removed, and hence your data deleted. If you look at the sequence of events that occur when rows have been deleted you can infer in code that rows have been deleted, the Target in subsequent selectionChange events will refer to the same row(s). You can also compare Target before after in Change events. Then when you suspect the deletion (in the event) check for validity of range objects in your own class objects. Then as necessary "tear down" the relevant objects and child objects. Perhaps you might also store cell address when the cell is referenced (in your object). As rows are inserted/deleted addresses of still valid cells may change. Be sure to cater for possibility of user deleting/inserting multiple sets of rows at the same time - things get more complicated! Regards, Peter T "Nick H" wrote in message ... Thanks GB, Okay I'll ask the user to open the VBE and in the immediate window type: application.screenupdating = false - before they delete any rows! <g In all seriousness guys that is not the problem - well if it is I don't think there's much I can do about it. VBA code is not 'running' at this point. Objects are instantiated and ready to offer up their properties if called on by a Sheet_Change event but it is the user that is deleting rows not VBA. When the user selects a large number of rows and right-clicks on a row header and chooses 'Delete' the Sheet_Change event doesn't fire for about 30 seconds while Excel does something in the background. In fact I'm pretty sure no VBA code is running during that time. Any uber OOPers out there? What happens when a user deletes one of your objects without you having the chance to terminate it cleanly? Is Excel just juggling/recovering memory? Has anyone found a way to detect a 'Delete' event before the delete happens? Is the only way round this to write a new 'Delete' menu item for the "Row" shortcut menu item? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
Thanks Peter,
You're absolutely right, I have encountered the 424 error when trying to process objects that had passed the 'Not Nothing' test. On Error Resume Next currently gets me round the issue because in all places where it occurs the next line is destroying the object anyway. At the moment I cater for the 'odd' deletion/insertion of rows by tearing down and rebuilding all the portfolio objects if I detect a change in the row count. Even with 1200 rows of data this takes only a second so I felt it a small price for playing safe. While experimenting I've found that if I manually run my 'KillPortfolio' routine (tear down) and then select 600+ portfolio rows for deletion, the delete happens virtually instantaneously. I can then 'RebuildPortfolio' in the Factory module and all's well. I realise I've solved my own problem in that I simply need to control the deletion by rewriting the "Row" 'Delete' menu item - Teardown portfolio, do the delete, Rebuild portfolio - but I feel unfulfilled. I just wish I knew what Excel is doing during that 30 second lull and would it be avoidable if I was a better programmer? What I don't want to do is turn it into a dictator app that forces the user to conform to non-Excel like ways. Br, Nick H |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
Considering that this seems like an application that may be desirable to be
openable by many people at once, is the workbook shared, and if so is it possible that when the user uses either the menu command delete rows or highlights these rows and right clicks to delete, that all of the data is being captured in the "changes"? BTW, in rewriting the Row-Delete menu item, curious about plan of attack. Will you be removing the default option from the menu bar and adding your own code to it, or using some form of VBA override code that is called by Excel in lieu of the default Row-Delete? "Nick H" wrote: Thanks Peter, You're absolutely right, I have encountered the 424 error when trying to process objects that had passed the 'Not Nothing' test. On Error Resume Next currently gets me round the issue because in all places where it occurs the next line is destroying the object anyway. At the moment I cater for the 'odd' deletion/insertion of rows by tearing down and rebuilding all the portfolio objects if I detect a change in the row count. Even with 1200 rows of data this takes only a second so I felt it a small price for playing safe. While experimenting I've found that if I manually run my 'KillPortfolio' routine (tear down) and then select 600+ portfolio rows for deletion, the delete happens virtually instantaneously. I can then 'RebuildPortfolio' in the Factory module and all's well. I realise I've solved my own problem in that I simply need to control the deletion by rewriting the "Row" 'Delete' menu item - Teardown portfolio, do the delete, Rebuild portfolio - but I feel unfulfilled. I just wish I knew what Excel is doing during that 30 second lull and would it be avoidable if I was a better programmer? What I don't want to do is turn it into a dictator app that forces the user to conform to non-Excel like ways. Br, Nick H |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's It Doing?
Good point about possibility of a Shared workbook being linked to
unexpectedly long row deletion time (though a lot of rows can take a while in some scenarios). The Delete button could indeed be removed and replaced with an alternative. Though easier to intercept it's click event, trap the current selection (ie rows) to process later, could abort the Delete button's click event and go on from there (downside is Undo is lost). It's a more difficult to intercept what user does when Deleting from the Cell's right click popup Regards, Peter T "GB" wrote in message ... Considering that this seems like an application that may be desirable to be openable by many people at once, is the workbook shared, and if so is it possible that when the user uses either the menu command delete rows or highlights these rows and right clicks to delete, that all of the data is being captured in the "changes"? BTW, in rewriting the Row-Delete menu item, curious about plan of attack. Will you be removing the default option from the menu bar and adding your own code to it, or using some form of VBA override code that is called by Excel in lieu of the default Row-Delete? "Nick H" wrote: Thanks Peter, You're absolutely right, I have encountered the 424 error when trying to process objects that had passed the 'Not Nothing' test. On Error Resume Next currently gets me round the issue because in all places where it occurs the next line is destroying the object anyway. At the moment I cater for the 'odd' deletion/insertion of rows by tearing down and rebuilding all the portfolio objects if I detect a change in the row count. Even with 1200 rows of data this takes only a second so I felt it a small price for playing safe. While experimenting I've found that if I manually run my 'KillPortfolio' routine (tear down) and then select 600+ portfolio rows for deletion, the delete happens virtually instantaneously. I can then 'RebuildPortfolio' in the Factory module and all's well. I realise I've solved my own problem in that I simply need to control the deletion by rewriting the "Row" 'Delete' menu item - Teardown portfolio, do the delete, Rebuild portfolio - but I feel unfulfilled. I just wish I knew what Excel is doing during that 30 second lull and would it be avoidable if I was a better programmer? What I don't want to do is turn it into a dictator app that forces the user to conform to non-Excel like ways. Br, Nick H |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|