Out of Memory?

[CLR]

Hi All......

I'm working on a Excel97 Program that has several hundred macros. My office
computer (833mhz, 512mb RAM) has started giving me a "Microsoft Visual Basic
Out of Memory" error message. Sometimes it will go away if I click ok about
20-25 times, sometimes not. The same file runs fine on my laptop (366mhz,
96MB RAM).....just recently MIS put a icon in my systray about "Webroot Spy
Sweeper".....might they have finally added the "straw", or could something
else maybe be causing this problem. I don't have the same problem on simpler
Excel files on the same computer, but DO have it on other complicated files.

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Jim Thomlinson]

Everything you wanted to know about Excel Memory but were affraid to ask...

http://www.decisionmodels.com/index.htm

 

[CLR]

That's interesting Jim, thanks, but my problem persists.
My filesize is less than 2MB
=INFO("memused") reports less than 5MB
The file runs fine on a system that has half the processor speed and 20% the
RAM of the offending system with the only other difference being the machine
that won't run is Win98SE (on a network) and the little one that will is
WINMe stand alone. This would kinda/sorta infer that the problem is
machine-related. Also, FYI, I run this file off my pocket Jump Drive,(not
the network) so it's "really" the same file that will work on one machine and
not on the other.......maybe some kind of a setting in Excel..........the
file opens fine on both systems, I just get this Out of Memory" pop-up when I
try to go to the VBE.

One more thing I just noticed......both systems are Excel97, but the
offending one is SR-2 and the one that works is SR-1........could the problem
be therein?

Vaya con Dios,
Chuck, CABGx3

 

[Jim Thomlinson]

While the macro is running check out the memory usage in the Task Manager to
get an accurate reading of how much memory Excel I actually using. Caompare
this to the addressable memory listed on that site that I sent you. I highly
doubt that your issue is machine related. The service pack issue is a more
likely cause. The new service pack could be doing something that ups the
memory usage...

 

[CLR]

There is no macro running........I only open the book, right-click on the
MainMenu tab and choose ViewCode, and that's when I get the "Out of Memory"
pop-up,....it won't do anything from there.......

Vaya con Dios,
Chuck, CABGx3

 

[Jim Thomlinson]

What does your task manager say the memory usage on excel is just prior to
opening the VBE and just after?

 

[CLR]

Dont know, the problem machine is at work and I'm home now......will check
tomorrow...........I do remember that the =INFO("memused") was less than 5
MB before I tried to open the VBE, but no way to tell about after, because
it would not open............

Vaya con Dios,
Chuck, CABGx3

 

[Jim Thomlinson]

Excel has a limit and I was just wondering what the OS thought that the
system was using. If I recall correctly the PLDT virus could chew up some
memory. Well still be here tomorrow...

 

[CLR]

Hi again..........

Here at home I have WinMe and Excel2k, 512MB RAM, 1.2ghz AMD
processor................I just used the System Monitor to measure 47MB
difference in unused memory just by starting Excel, then 6mb more by opening
my WorkBook, and then no noticible difference by starting the VBE, and/or by
firing a macro that inserts about 300 VLOOKUP formulas into one of the 39
sheets.............

Keep in mind, the Workbook works fine on the little laptop with only 96MB
RAM, and Excel97SP-1, as it does here at home on XL2k. The only place I'm
having trouble with it is the machine at work on the LAN with
Excel97SP-2......there, the VBE just will not open anymore, responding "Out
of Memory".


Vaya con Dios,
Chuck, CABGx3

 

[Vic Eldridge]

Two things I'd try,

1\ Don't run the file from your "Jump Drive". Copy it to your hard drive
first and run it from there.

2\ Download Rob Bovey's Code Cleaner utility and see if it helps.


Regards,
Vic Eldridge

 

[CLR]

Ok Vic..........I appreciate the comeback and will give those things a try
when I get to work this morning.........I have a detour first, have to take
our 20yo Tuxedo cat to the Vet for her weekly injection........that is my
TOP priority.

Your suggestions won't explain why the file works fine from the Jump drive
on a lesser machine and a greater machine, but not on the offending one
which is on a network......, but I'm sure my code could use a lot of
cleaning up, so that will be a good thing in any event..........

I still feel the "Out of Memory" problem I'm having lies in the difference
between the SR-1 and SR-2, and/or excessive intervention on the
network.........neither of which I know much about.

Thanks again,
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Okey........

I put the file on my C: drive of the offending machine and I still have the
"Out of Memory" problem when I try to start the VBE.
I obtained and ran Rob Bovey's Code Cleaner and I also still have the problem.

Any other ideas please?

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Hi Jim.......
I don't know about "task manager", don't know how to use it or where it's
at.......I used "System Monitor" on the offending machine and it opened up at
about 475mb "allocated memory" which went up to near the 512 limit when
starting Excel and then maked out at the 512 line when I opened my
file..........must be some vermin there somewheres.......I called MIS three
days ago but they havent had time to get to me yet........it's a great life

Now sometimes I get the "Out of Memory" error just upon opening the file,
but only on this one machine........I'm pretty well convinced, it's either
the SR-2 or the network monitors.......but MIS has controls on this thing so
I can't even boot up without being on the network, so I can't eliminate that
part.

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

Do you have the zoom set to 100% on all sheets? Not being at 100% was a
source for out of memory errors in xl97. Also, charts used to be
troublesome and it had something to do with font scaling - but I would think
the later SP would be more resiliant than the early one, but one never
knows.

 

[CLR]

Thanks for the reply Tom......It's time to leave today, but I will check that
out tomorrow for sure.....I have 39 sheets and 36 of them are set to
50%......but of course the file does run on other machines without the "Out
of Memory" error.......

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Hi Tom.........
Well, the problem seems to have changed a bit.....NOW, I get the "Out of
Memory" error immediately upon opening the file, whether from my jump drive
or the C:\ dirve.....I did go ahead and change all 36 sheets to 100% zoom as
you suggested, and I have no charts involved, but the problem remains......

The following, all running the same file either from my Jump Drive or when
copied to the individual c:\ drives.....
366 Laptop, WIN98SE, 96mbRAM, XL97SR-1, no network, file works fine
1.2 Desktop, WINMe, 512mbRAM, XL2k, no network, file works fine
866 Desktop, WIN98SE, 512mbRAM, XL97SR-2, company network, "Out of Memory"
error, WAS occuring only when I tried to open VBE, NOW occurs immediately
upon starting the file.....clicking it off about 25 times clears the dialog
box but when I try to do INsert > Name > Define, I get another type error
message about not enough memory, etc.......same with just pressing F5.

I have about 50 "buttons" with macros assigned to them, maybe 200 named
ranges, about 200 small macros in regular modules, and both "Activate" and
"Deactivate" macros on each of the 36 Matrix sheets.....I shouldn't think
this would be "too much", as the file does run on two of the three machines.


Any other ideas?
Vaya con Dios,
Chuck, CABGx3