Excel 2000 - Error on close

[vestax22]

Hi, I have a fairly large Excel application loaded with VBA (9.5mb). Sometimes when I close my application I get an error message. No data is lost and this only happens on application close. I know it has something to do with the dll msvbvm60.dll which is the visual basic virtual machine because it is mentioned in the error report.

It seems Excel has a hard time clearing the memory or something. Any help for this problem would be greatly appreciated. Thx.

 

[xlbo]

If you are using loads of variables - especially object variables such as ranges and worksheets etc, you should set them to Nothing before ending the macro as this frees up the memory involved. If you are getting memory errors then this would be my 1st step.....

eg
Dim myRange as range
Set myRange = range("A1:A50&quot

'do stuff

Set myRange = nothing

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[vestax22]

Hi, So declare a range on workbook open and initialise it on workbook close? How is this gonna fix the problem? I there a way to erase memory in before close event? A way to initialise the memory to avoid this error?


Thx

 

[xlbo]

No - you are not initialising the variable.

Declare variable
Initialise it using the SET statement
BEFORE THE END OF THE MACRO
SET the variable to NOTHING - this releases the memory used by the variable

Obviously, you don't want to do that for any PUBLIC variables you have so for those, use the workbook_before_close event to se the public variables to nothing

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[vestax22]

Do I do this for normal variables like doubleds and integers as well?

Thx for the replies

 

[xlbo]

you can't set a double or an integer to nothing - you could set them to 0 but as it is still holding a value (albeit a zero value), it doesn't release the memory. It is the object variables that tend to cause memory leakage problems - just set all your object variables to nothing and it may help. Please note that I am not saying that this is the root cause of the problem but you mentioned that you thought it may be a memory error and this is one of the most common causes of that

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244