W
WHA
(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; [email protected]; 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.)
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; [email protected]; 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.)