Bypassing the 128MB formula memory limitation in 2002?

[karen]

I have several users that need to open 5 different spreadsheets, all
linked to each other, but keep encountering the "Excel cannot complete
this task with available resources. Choose less data or close other
apps."

My research has indicated this is due to the built in limitation of
128MB for formula memory in Excel 2002.

Unfortunately, using Excel 2003 is out of the question due to a third
party app conflict (the 3rd part app is necessary and does not function
in Excel 2003). Naturally Excel 2003 testing has shown no issue in
opening all 5 spreadsheets at the same time.

I have tried opening multiple instances of Excel 2002 and this does
bypass the 128MB limitation as that is on a per instance basis, but the
links no longer function.

Any help or advice would be greatly appreciated.

Thanks.

 

[Harlan Grove]

(e-mail address removed) wrote...

I have several users that need to open 5 different spreadsheets, all
linked to each other, but keep encountering the "Excel cannot complete
this task with available resources. Choose less data or close other
apps."

My research has indicated this is due to the built in limitation of
128MB for formula memory in Excel 2002.

Unfortunately, using Excel 2003 is out of the question due to a third
party app conflict (the 3rd part app is necessary and does not function
in Excel 2003). Naturally Excel 2003 testing has shown no issue in
opening all 5 spreadsheets at the same time.

I have tried opening multiple instances of Excel 2002 and this does
bypass the 128MB limitation as that is on a per instance basis, but the
links no longer function.

Either get the maker of the 3rd party app to upgrade to support Excel
2003, or get your users to redesign their system. There's absolutely no
way around limitations like this in Excel.

Redesign would be far & away the better course. If your users are
really pushing the 128MB limit, they're begging for trouble whether or
not they upgrade to Excel 2003. While Excel can (barely) cope with that
many formulas, it's unwise to built models larger than 1/4 this size.

 

[Charles Williams]

Hi Karen,

Unfortunately I can only see 2 solutions:

- reduce the amount of formula memory needed by the 5 workbooks (redesign
them to be more efficient)
- fix the 3rd-party app (its usually very easy to fix these kind of
problems)

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[karen]

Thanks for the responses...

Yeah, I figured as much, but no harm in asking around, right?

The 3rd party app will be upgraded, but I was given an ETA of next year
so that's the long term solution.

I need a short term solution and so far, I'm coming up empty. I will
discuss the redesign with the users, but I have a sneaking suspicion
that won't do since they let it get this far in the first place.

Thanks again!

 

[karen]

Quick question...is it possible to link two or more instances of Excel
together?

A current workaround for the 128MB limitation is to open separate
instances of Excel, but then the sheets don't link.

Is it possible to link them in this state?

Thanks.

 

[Chip Pearson]

If I correctly understand what you are looking for, the answer is
no.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dave Peterson]

And I'm not sure what you want, but you could have links between workbooks.

But each instance of excel will not see that other instance.

But you can be in one instance, make changes, save the changes, then in the
other instance, you can choose to update those links to the other workbook.
(Edit|Links|Update values)

So it works just like linking to a closed file (for the most part).

 

[Harlan Grove]

(e-mail address removed) wrote...

Quick question...is it possible to link two or more instances of Excel
together?

A current workaround for the 128MB limitation is to open separate
instances of Excel, but then the sheets don't link.

Is it possible to link them in this state?

Purely theoretically, it's possible to use a macro in one Excel
instance to run other Excel instances via Automation, but it would be
fragile, and it won't allow external link references to function
between the different instances.