"Not Enough System Resouces to Display Completely"

[max57]

I have a fairly complex workbook [ in Excel 2007- on an XP operating system] that has:

• many dynamic ranges
• a class module which allows me to colorize a form containing two multi-page controls
• a class module that positions a form
• two standard modules with code
• six user forms from simple to complex

Recently , I began receiving a message stating: “There aren’t enough systems resources to display completely”.

This began occurring shortly after the addition of the multi-page colorizing class module and the twelve dynamic ranges.

In an effort to get to the root of the problem:

• I experimented by saving different versions of the file after modifying or removing the coloring module and/or the dynamic ranges. Unfortunately, I was still receiving the same message to varying degrees.
  
• I looked at the Class Modules to make sure that the memory hogging objects were killed to free up resources.
  
• I did notice in other modules, objects “set” but not released. One instance is a Dictionary object in a form module. But isn’t this released when the form is unloaded?

I’ve read everything online I could find from Microsoft and the various other newsgroups, but much of it was not very helpful.

My question is this:
Aside from those things mentioned by Microsoft that are attributable to this problem [ and which I had tried to compensate for], has anyone had any relative experience in dealing with this? If so, when did they notice the problem and how did they eliminate or work around it?

• For instance, does a computer’s physical make-up or the Operating System have any effect?
  
• Does the design of the workbook, such as having all the referencing ranges and data lookups on one sheet, create a bottleneck of processes?
  
• Does every object created, have to be set to nothing when done?

I’m not a developer - just a fairly average self taught Excel user.
Any information that you more experienced users may have would be helpful.

Thanks
Mark

[Lifesigns]

Hi there,

I also experience this message with Excel 2007. Especially with workbooks that include various dynamic named ranges/charts/PT's & VBA. At first I was concerned it would influence the actual working but I've just learned to close the message and continue as I've never lost data due to it.

I read up about it here http://www.decisionmodels.com/memlimitsc.htm and it provided some answers.

At work we're still on XP but at home it's Win 7 64 with Office 64 and I have not received the message when using the same files at home so I think it has to do with memory management under XP.

[max57]

Thanks for the web link.

I had visited this site a while back and completely forgot about it.
I shall read it thoroughly and see if there are any bits I had overlooked.
I have a feeling the cause of my problem is a result of a few of those stated issues.

Mark

[Lifesigns]

Yea keep us updated on your findings.

Perhaps there is a specific reason for it, we can avoid in future.

[Marcol]

Are you talking about a networked machine, or a stand alone?

[max57]

Lifesigns,
Will do. Many times these boards give more realistic approaches to topics that are glossed over on the MS Web site.


Marcol,
Stand alone