Thread: Out of Memory
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Walter Briscoe Walter Briscoe is offline
external usenet poster
 
Posts: 279
Default Out of Memory

In message of Mon, 21 Nov 2016 15:06:56 in
microsoft.public.excel.programming, GS writes
I have just worked around a problem. It seems friendly to report my
experience.
I use Excel 2003 and most of my code is in PERSONAL.XLS.
I had created a new sub, which is a renaming of another.
I lost the plot and saved PERSONAL.XLS.
When I opened Excel again, I got an "Out of Memory" dialog box.
Throwing away a lot of redundant code did not help. I could run no
code. I moved PERSONAL.XLS away from Application.StartupPath.
I created an empty PERSONAL.XLS. (Save a dummy macro in Personal
Macro Workbook.)
Previously, I had copied Sub ExportAllComponents from somewhere.
(Possibly <https://www.experts-exchange.com/art...1457/Automate-
Exporting-all-Components-in-an-Excel-Project.html)
I used that code on my old PERSONAL.XLS.
I imported the components, one at a time.
I hit problems with the need to set Tools/References and to define
types. Googling got me information to set references.
When I had got a compilable file, my new PERSONAL.XLS was about 2MB.
The old file was about 20MB. I have no notion what went wrong.


Simply put.., this is an example of how some files get
corrupted/bloated for no apparent reason. You could have just renamed
your original file "Personal.xls.old" and opened it with macros
disabled, then drag-drop its components into your new one.

It would be very worth your while to download Rob Bovey's free
VbaCodeCleaner addin...

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

..and use it regularly for general maintenance.


I previously downloaded it. It installed without error.
It just failed to add an entry to any of my VBIDE (Visual Basic
Integrated Development Environment) menus. Very frustrating as I can't
now find anything to do a similar job.
This is no longer available:
20160524 vba code cleaner orlando.mvps.org VBA CODE DECOMPILER AND
COMPACTOR C:\Users\IBM\AppData\Local\Temp\Temp1_VBADecompile r.zip\VBADec
ompiler.exe.
I got nowhere with:
20161105 vba excel cleaner http://www.spreadsheet1.com/vba-project-code-
cleaner-for-access-excel-powerpoint-word.html.

Thanks for the suggestion. ;)
--
Walter Briscoe