Visual Basic Out of Memory

[GOGGS75]

Hi

I am currently having a problem with a spreadsheet and am hoping someone could help or let me know if they have had this problem also.

I have set up a project with 1 module and 7 userforms that is always running with no problem. The problem sometimes but not always occurs when trying to to save the file or just closing the file without saving it. The message 'Visual Basic Out of Memory' appears and Excel is forced to close.

I have read about the amount of memory Excel VBA has available and am under the impression the problem should be happenning when a macro is running not when saving or closing the file.

Thanks very much for any help.

Goggs75

[SamT]

VBA is noted for keeping deleted stuff around forever. To clean it out:

First: Export all forms and modules to a new empty folder
Second: Remove all forms and modules from the book
Third: Import all the forms and modules from the new folder.

Then look for code that creates New Objects and insure that Object is Set = Nothing when it is longer needed.

[Paul_Hossler]

I like to use Ron Bovey's Code Cleaner add in to automate the process:


http://www.appspro.com/Utilities/CodeCleaner.htm

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.

Another thing to look at is the size of the modules. Using the CC, use the [Export] option and look at the KB's of the exported modules. I've always been told that there's a 64KB limit to a single module. Any exported moduules > 1/2 that I try to break up. If the UF has a LOT of code, I try to re-structure to put the 'processing' subs into a standard module, and only leave the user interface part in the UF module

Paul

[Dave]

Trial turning your document recovery settings off. HTH. Dave

[GOGGS75]

Thanks for your advise.

I had already tried the code cleaner, it reduced the file size slightly but the problem started again.

I am planning to start from a blank spreadsheet and fully re-create the spreadsheet so see if this helps.

I have also found a way to create a userform by using one that has been saved separately and is not part of the xls file. A macro can then remove it after using it. This will allow me to reduce the number of userforms the xls file saves. The two complex VBA lines allowing this being as follows:

ThisWorkbook.Application.VBE.ActiveVBProject.VBComponents.Import "C:\Users\Goggs\Documents\PRICEFORM.FRM"

Application.VBE.ActiveVBProject.VBComponents.Remove Application.VBE.ActiveVBProject.VBComponents("PRICEFORM")

I have measured the size of each userform using a macro I read about. This shows the largest one to be 7K.

Hopefully, the problem will stop

Cheers

Goggs75