What's It Doing?

[Nick H]

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

 

[JP]

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

 

[Nick H]

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.

 

[GB]

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.

 

[Don Guillett]

Could it be a calculation ON problem

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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

 

[Nick H]

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?

 

[Nick H]

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

 

[Peter T]

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]

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

 

[GB]

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?

 

[Peter T]

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