Insufficient Resources to Calculate

The issue below has been solved. The lesson learned is as follows:

When you are working in Excel and your spreadsheet (particularly, when it is linkage intensive with other workbooks) is near the upper limit of RAM and processor effectiveness, there are errors that you can't see since there is not enough memory to display them. In this case, I had a cricular regerence that triggered an "out of resources" error. When I put the application on my son's gaming computer it showed me where the circular reference was occuring. I fixed the CR and reloaded onto my original laptop. In conjunction with this I wnet from 3 to 4 GB in memory. The fix is working but may appear again as the application grows. I plan to purchase a 6 core HP tower as a permanent fix.


"Excel ran out of resources while attempting to calculate one or more formulas" is the error I get and I have narrowed the error down to one worksheet. Since I believe I have sufficient resources, how can I locate the specific cell or range that is triggering this show stopper.
Perhaps there is a small vba routine that can help me troubleshoot this. I have trimmed the worksheet in question down to the bare bones and I still get the error.

Further explanation:

I have a business application that is comprised of 5 workbooks in Excel. One of the workbooks will not calculate (F9). It gives me an error “Excel ran out of resources while attempting to calculate one or more formulas. As a result these formulas cannot be evaluated.” In the suspect workbook, each sheet can be calculated internally by pressing SHIFT + F9, except for one… and the same error is generated; therefore the source of the error must reside in this worksheet. This would seem to be an issue not with slow calculation but rather with no calculation. I have tried the following:

1) Force a full calculation with Ctrl + Alt + F9
2) Force a complete rebuild of the dependencies and a full calculation by pressing SHIFT+CTRL+ALT+F9
3) Pressing CTRL+SHIFT+I to reveal the error number 101758
4) Cleaned laptop of old files and deleted unused files
5) Reconditioned the registry
6) Defragged
7) Installed updates for everything
8) Optimized worksheet design to minimize excessive calculations

There are a considerable number of data links between the workbooks including calls for data from outside sources as well as a port to send csv data to other computers. My gut tells me that I need more memory.

This remaining challenge in my application is keeping me from launching my business.

see: http://msdn.microsoft.com/en-us/library/aa730921.aspx in which you will find some code to help you time & find bottlenecks.

(poor performance generally results from overuse of SUMPRODUCTs / Arrays and/or Volatility)

I'm not a hardware guy I'm afraid - a few here are so they might be able to offer some guidance ?

What I would say is that you should really be avoiding spreadsheets that necessitate upgrading your hardware.
I am guessing (and really guessing) that you can improve the performance by adopting the optimisation techniques outlined by Charles Williams in the prior link.
See also his own site http://www.decisionmodels.com/calcsecrets.htm

If you can't then that would generally lead to discussion as to whether or not Excel is really the best tool to use for whatever it is you're doing in the first instance.