Excel ran out of resources while attempting to calculate one or more formulas

Question

I received the error message "Excel ran out of resources while attempting to calculate one or more formulas" on ONE of my computers (Out of 2).

My worksheet contains: (Inside a single worksheet, not workbook, there are formulas in another worksheet)

• 1,000,000 formulas

• a Pivot table base on 900,000 rows of data

When I run excel/vba on the action "Calculate Sheet" (The current worksheet only), the program would pop the following error message:

Excel ran out of resources while attempting to calculate one or more formulas

on neither I "refresh" a pivot table in (Excel or VBA) or "Calculate Sheet" in (Excel or VBA)

I have 2 computers:

• both running 64 bits Windows 7,

• both running Excel 2007 32Bit,

• I run Excel right after starting Windows,

• my development PC with 2GB RAM can run without problem,

• another PC with 6GB RAM shown the ran out of resources error message

• Running on same set of data, same excel file

I also notice that on my development PC, it is using ~ 1.2G RAM, and the non-working PC, it is using 900M RAM prior to clicking the "Refresh"/"Calculate" Action.

EDIT

The Non-working Computer can handle data within 100,000 rows of data

My questions:

1. Why it is working for the computer with less memory but not the one with more memory? (main question)
2. What can I do to reduce the memory used by Excel? (sub question) (Other than deleting data)

Any help is appreciated, please point me into the right direction or simply give some clue.

EDIT:I am thinking of removing the formulas, and move the logic into vba, and do it by caching the data maybe per 10,000 rows. But this won't solve my problem if "refresh" pivot table would show the same error.

Solution 1

thanks for all the suggestion, readings and help. I have solved the problem by

1. Change all formulas into values step by step per ~ 100,000 rows --- This reduce the memory held by excel by 10%
2. Removed any unnecessary data/Worksheet(s) in the workbook (The raw data before data massaging ) --- This reduce the memory held by excel by 40%

And the reading http://www.add-ins.com/support/out-of-memory-or-not-enough-resource-problem-with-microsoft-excel.htm suggest why 6G RAM PC is facing this issue rather than 2G RAM PC. I think it's because the 6G PC has a lot of excel add-in on it consuming RAM as well.

Thanks for the helps.

Solution 2

The reason is probably differing 32-bit memory fragmentation on the 2 Pcs (its often difficult to use all of the theoretically available 2 GB).
You can reduce the amount of memory used by the pivot table by reducing the number of columns and/or reducing the memory required by some of the columns (text strings are a good candidate).
(You can measure the amount of memory used by the pivot cache with VBA PivotCache.memoryUsed)
I assume you are creating the pivot cache by reading the data from the query directly into the pivot cache rather than putting the query data onto a worksheet and basing the the pivot on the worksheet, which would use much more memory.
You don't say what the >1000000 formulas are so I don't have any suggestions for improvement for them.
If you want to use large amounts of data in Excel pivots etc you would get better results from the 64-bit version of Excel 2010 which does not have the 2-GB limit

Solution 3

old queston but still valid for all the versions, I run into similar issues when the formulas I used had too big scope even though most of the fields were empty, ex: DO_CALCULATION(D1:D100000) will take significant amount of time and memory even if only cells D1:D10 have any value. So be careful trying to make formulas ready for data expansion, keep the ranges small :)

Solution 4

The answer is to upgrade to a 64-bit version of Excel. It can address a much larger memory range, and is also just generally much faster and the right tool for the job if you have a big spreadsheet.

Solution 5

It looks like you construct very large Excel sheets. 32-bit applications can only use up to 2GB of RAM, unless they can be configured to use up to 3.2GB with a special "switch". So Excel 32-bit is actually running out of memory after 2GB.

So it doesn't matter if you have 16 or 32GB RAM even. You need 64-bit MS Office so that it will be free to use RAM above 2GB. Only then will your extra RAM of 6GB be actually useful to Excel. Otherwise, it is stuck with having to figure out how to run with only 2GB of RAM, and most likely, will start using the pagefile on the hard drive.

One possibility would be to increase the size of the Windows page file. I never let windows manage the pagefile. I set it to 1.5X physical RAM and set minimum and maximum pagefile size THE SAME. This prevents RAM fragmentation. So if you have 4GB of RAM, set it to 6144 / 6144MB min/max.

View more solutions

Comments

[lc.]

Odd that the non-working computer has more RAM. Can you verify there is enough memory free (both physical and swap) on the computer that errors out? Other than that I would say to optimize or split the sheet if possible. 1M formulas sounds a bit big for an Excel sheet?

[Larry]

@lc. On that computer, in task manager I saw the free memory is 2.2G

[lc.]

Strange. Is it the exact same file with the exact same data?

[Larry]

@lc. Yes, same set of data, same file.

[iDevlop]

Do the 2 machines have the same Calculation options in Excel ? Are the 900.000 rows located in the Workbook or external db ?

[Larry]

@iDevlop Happy new year, I forget to mention, the error would also occur if I change the calculation option from MANUAL to AUTO. And the rows of data are in another worksheet in the workbook.

[iDevlop]

Both running same version of Office ? 32 bits or 64 ?

[Larry]

@iDevlop Excel 2007 only has 1 version, 32bit, office starts to support 64 bit in 2010 version and I suppose the subversion of OS and Excel are the same.

[iDevlop]

Weird. You can try try saving some resources by using array formulae instead of regular formulae, but I doubt it will solve your problem. Are the saved file sizes similar on both machines ?

[iDevlop]

Sorry, I can't chat. SO chat is blocked where I work at the moment :-/

[Larry]

@iDevlop Thanks for your input again, I will check that tomorrow. Currently I have no access to the non-working computer. The worse part of the problem is I cannot "refresh" the pivot table ( based on 900,000 rows of data * 9 Columns) I will first try removing as move formula as possible, saving them as value Then try the limit of pivot table range on the non-working pc the worst case solution I can think of is using a pivot table on the aggregate result of part of the original pivot table and the rest of the data

[iDevlop]

Why not moving the data to another workbook, or even to an Access mdb ? With your data in Access, you will remove most limits on your dataset size.

[Larry]

@iDevlop The 900,000 rows of data are query result extracted from Oracle DB, I needed a pivot table presentation of the result.

[iDevlop]

Ok. Then why moving the lines to Excel ? You can set your Pivot to directly query Oracle. If the are no specify counter indication, I would play it that way.

[Admin]

Or even better, if your running at least Oracle 11g then it natively supports PIVOT queries, the work would then be done server side rather than locally and any resource problems should be avoided.

[Admin]

@Larry, I've run into the same problem quite few times and generally chalked it up to the 32-bit version of Excel being the issue. As to why it works on one machine over another... I'd say it's just the way the OS / install has allocated resources. The only 2 solutions I've found for this are: 1) Writing .Net solutions that do the largest part of the work, then have an excel macro call the .exe through shell and then import the already-calculated data / do minimal clean-up work. 2) LOTS of playing around with my code to try and make it more efficient / less-memory-consumptive... Good luck!!!

[Larry]

iDevlop thanks for the suggestion once again. But I have to do some data massaging process in between ... @JohnBustos On the 1st point, one of my major problem is "refresh" pivot table would already cost the problem. Can .NET plot pivot table as well? On 2nd point, yeah I will try that .. Although I know if the data size increase more, then... nothing will works. Thanks a lot! I will update tomorrow, thanks again.

[Pynner]

2 things off the top of my head to check. 1) does the 6gb machine have other programs using more memory? 2) are both excel versions EXACTLY the same (same updates, etc applied)

[Larry]

Thanks for the suggestion, I am currently trying stuff, found a good link add-ins.com/support/…