Thread: Excel VBA Guru needed....

Mark
Your error arises because something is going wrong with memory management in the background. This is just my feeling, maybe I'm wrong.

As said previously by PM your code is basically unsafe because there is room for ambiguity to arise when multiple workbooks are opened within the same Excel thread. And you have said your uses typically have many workbooks open, including "multiple copies of this workbook" . I assume they must be named slightly differently.

You allow users to open a 'modeless' form. This means they can navigate away from the sheet it was originally opened from to any other sheet in your current workbook, or even any sheet in any other workbook opened within the same Excel thread and yet still interact with your form.

You make the assumption throughout your code that the user is not doing this by referring to objects which are basically unqualified. When a reference is said to be unqualified it means there is scope for ambiguity to arise.

For instance:

ThisWorkbook.Worksheets("Points Takeoff").Cells(Copy_Insert_Row, 1).EntireRow.Select

Which workbook are you referring to here? The workbook from which your user originally opened the modeless form or the workbook that is currently active. Your application will crash if the user is not currently using the 'right' workbook or it may even continue to function if the currently active workbook just happened to have a sheet named "Points Takeoff" within it. The point is you don't really know what will happen when multiple workbooks are open, particularly if there is a chance the workbooks are 'clones' of each other.

You need 'Workbooks("BlahBlahBlah").Worksheets("Points Takeoff").Cells(Copy_Insert_Row, 1).EntireRow.Select

No ambiguity possible here.

Another example

Private Sub cbInsert_Row_Click()
tbInsert_Row = Selection.Row
End Sub

1) What is 'tbInset_Row', is it an integer, a string, an object?. You know it is an a text box object on one of your forms but the compiler does not unless it works it out by first scratching its head and then trying to figure out what you mean. Do yourself a favour and give it a hint - ie

Private Sub cbInsert_Row_Click()
Me.tbInsert_Row.Value = Selection.Row
End Sub

2) Exactly what 'Selection' are you referring to here. It is of course the selection on the currently active sheet but you, I think, are assuming it is a selection on the sheet from which the user originally opened the form. This may not be the case because you allow users to navigate away to any sheet.

Some other points
You have about 150 'named range objects' buried in your application. About 100 of these point no where, ie they reference cell ranges which don't exist. They need to be removed.

'Option Explict' should be used everywhere. It should never be optional.

Don't use public variables. Pass parameters directly to subroutines. There is far less scope for confusion.

Just some things to keep you thinking.

Thanks Ferret. That should keep me awake for a few nights!

What I don't get (and not making excuses for my crappy programming) is why this has started to happen. We (the business) have been running this spreadsheet for 7 years with zero problems. All of a sudden I've got one user with frequent crashes, 2 with infrequent crashes and the rest of us (say 5 users) with no crashes.

It's enough to send me mad(er).



Oh....I must confess that I was being cute (and lazy) using "ThisWorkbook".

Originally Posted by WhiteD3

What I don't get (and not making excuses for my crappy programming) is why this has started to happen.

I don't know exactly either. You may have been using the same code for years but you have not been using the same system configuration for years.

There are memory management processes running in the background which you generally can't see or interact with. They are responsible for keeping track of which instance of objects are in memory and which instance of objects can be freed from memory (ie destroyed) when it is determined they are no longer needed.

Just when an object is no longer needed (ie moves out of scope or becomes unreachable) can be tricky to determine.

This process is referred to as 'garbage collection'. In the case of VB / VBA and MSOffice I think it is somewhat simpler process referred to as 'reference counting'.

Bugs caused by incorrect reference counting in COM systems are notoriously hard to resolve, especially because the error may occur in an opaque, third-party component.

This is why it is good practice (sometimes required practice) to fully qualify references and explicitly define datatypes and also why your problem appears to be random among different users. Again I stress - "I think".

As well a making sure all reference are explicitly qualified I would also make sure users can not navigate away from the sheet that raised the form until they are done with using the form. I would also make it impossible to push the button on the spreadsheet which raises the form while the form is already displayed.

In other words, tie down your application so it can't be used in ways you don't and never intended it to be used.

Peter et al,

Just some feed back......I've got all the objects qualified now, forms Modal, variables declared and cleared as required. Of course there hasn't been a crash for a week with the guys still using the old file that had only some of the changes above completed.

The main protagonist actually called yesterday to say how well things were going................which was great until I pointed out he's using the same file that's been causing all the grief lately.

This, like many things, is a mystery to me.

Thanks for your help and advice............although I'm sure I'll be back