Excel memory

[Ron Cüppers]

Hi All,

I just wrote the first macro that uses all the memory that excel has got;
running the macro and after a short time Excel displays 'Out of stack
memory' than displays 'Out of memory' and finally shuts down. To learn about
the memory usage I took a look at the Excel memory (free/udes/total) while
running different applications and open files, but the memory usage keeps
growing. Saving opened files helps but the used memory does not return to
the initial value. Closing and re-opening Excel seems the only way to
achieve this ?? Is it really this bad or am I overlooking some options !!

Thanks in advance, Ron.

 

[Harald Staff]

Hi Ron

Hate to say this, but it's probably poorly designed code from your hands. The error is 99%
of the time caused by one code that calls another which calls the first again which calls
the second which calls the first (and so on -as you probably guessed by now). Measuring
available memory is no option, this construction will use everything and more no matter
what.

 

[Ron Cüppers]

Indeed it could (can) be bad a poor design but in fact it is quite simple
code. It is just one module with two fo loops (one nested). The maximum loop
count is 4 with the loop counters pointing to an array within the nested
loop, so there are no calls at all (to my opinion).

Harald,

Thanks for your reaction!

Indeed it could (can) be a poor design but in fact the code is quite simple.
It is just one routine (one Sub) with two for loops (nested). The maximum
loop count for each loop is 4 with the loop counter poining to an array of
4. So there are no calls at all.
I do not know if the autofilter routine and the histogram (in analyses)
which are in one loop could lead to memory problems.

Ron

 

[Harald Staff]

Hi Ron

Can you post the code here so we can test run it ?

 

[Ron Cüppers]

Harald,

I did some searching on the web and found that 'autoscalefont' with graphs
is set true by default. I read on the web that someone could only create
about 30 charts with autoscale true, while turning to false he could create
about 140 charts until he ran into memory problems !! All the charts I was
creating had the autoscalefonts set true by default. Turning the
autoscalefonts to false (by VBA) solved my problem,... amazing!!

Thanks for your interest and help Harald.

Best regards,
Ron

 

[Harald Staff]

Thank you for interesting information, Ron. Another trick goes into the
toolbox.

Best wishes Harald
Followup to newsgroup only please

 

[Debra Dalgleish]

You can change a registry setting to make "Off" the default. There are
instructions in the following MSKB article:

http://support.microsoft.com/default.aspx?id=168650

Warning -- Back up your registry before you make any changes to it!!

If you have Excel 2000, add a new DWORD (AutoChartFontScaling) with
Value Data:0 to

HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options

For Excel 2002, add a new DWORD (AutoChartFontScaling) with Value Data:0 to

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options