Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
WHA WHA is offline
external usenet poster
 
Posts: 21
Default (reposting) Memory problem: Out of Memory and cannot save

(sorry if you're seeing this twice -- first post disappeared from
Google Groups)

Background: I am building a VBA-based tool in Excel 2003 on Windows
XP. I'm having some kind of memory leak problem: I get "Out of
memory," with no mention of Error 7. This can occur simply when I am
in the VBE without any of my code running. Also, after the error
occurs, I am unable to save the workbook, even to a local drive. This
is the fourth of four posts with questions about what the source of
this error might be.

HIDING + UNHIDING FORMS?
I found one thread that seems particularly relevant: http://tinyurl.com/yv3srr
. The gist is that loading+unloading a UserForm can eat up memory. Is
this true? I have not seen it documented anywhere else.

Should I be loading the key forms once, then merely hiding+unhiding
them? Or would that actually increase memory load, since it means
having lots of forms in memory at once?

(NB: I use "<form name.Show" to load the forms, then "Unload Me" from
within each form to close. For object variables (except possibly the
Userform object variables - see my previous post), I set them to
Nothing at the end of every procedure.)

SAVE TO A DIFFERENT LOCATION???
http://support.microsoft.com/kb/214073/EN-US/ describes the "document
not saved" part of my situation, but I tried its remedy (save to a
different location) at least once without success (got the same "not
saved" error). Does the combination of "Out of memory" and "Document
not saved" suggest anything to anyone?

NB: The system also tells me, "An unexpected error has occurred.
AutoRecover has been disabled for this session of Excel."

OTHER
I've read several web pages and forum posts about Excel VBA memory
errors, including these general tip sites:
http://www.mvps.org/dmcritchie/excel/slowresp.htm
http://www.decisionmodels.com/memlimitsc.htm and memlimitsd.htm

However, I am not sure which of their suggested causes/remedies is
indicated by my situation. Some of these (ex., "set object variables
to Nothing") I have already done. There are others that I would like
to understand before I invest time in trying them. These include:

* Memory leak can be caused by "some external data retrieval
actions" (memlimitsd.htm at decisionmodels). My program does read from
and write to an external data file, lots of times (although it does so
in response to specific events, not repeatedly). Is there a way to
determine whether this is the problem?
* Swapfile size: What would be an appropriate size for my computer
(see "FACTS" below)? Is this likely to be relevant?
* Could it be some other specific problem with my operating system or
hardware? If so, how to diagnose?

In sum: The usual suspects seem to be: opening+closing userforms is
bad; read/write of external file is bad; 64K segment boundary; too
many userforms and/or ActiveX controls; objects not released (in spite
of my setting my object variables to Nothing); something with my
computer.

I apologize if this has all been long-winded. I'll be very happy if
there is a two-line answer ļ

Many thanks in advance, WHA


FACTS. I am using:
MS Office Excel 2003, SP3
MS XP Professional Version 2002, Service Pack 2
Dell Inspiron 9400; Intel Core2 CPU; ; 998MHz, 2.00GB of
RAM.
My Excel workbook is about 2MB in size.
When the memory error occurred recently, EXCEL.EXE was taking up about
55MB of memory (according to Windows Task Manager).
Preliminary measuring of =INFO("memused") seems to show 1.2MB when
tool is first loaded, then to fluctuate between 2 and 3 MB. (I did not
test this when the memory error occurred.)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
XL 2007 - Out of Memory - memory leak/bug? PCLIVE Excel Discussion (Misc queries) 0 March 23rd 09 04:31 PM
Memory problem: Out of Memory and cannot save WHA Excel Programming 0 November 30th 07 04:52 AM
How to save the cell formula memory on worksheet? Eric Excel Discussion (Misc queries) 0 October 12th 07 10:13 AM
Out of Memory Problem Todd Huttenstine Excel Programming 1 April 16th 04 08:19 PM
Memory Problem ExcelMonkey[_21_] Excel Programming 9 January 31st 04 06:06 AM


All times are GMT +1. The time now is 10:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"