Not Enough Memory Excel For Mac 2011

/ Comments off

Posted by Jeff Nyveen on November 27, 1999 8:33 PM

Thus, your computer may have enough space in its local memory but excel will show 'Microsoft Excel cannot open or save any more documents because there is not enough available memory or disk space'. Issues like this are related to the security settings innate in Excel 2013 or Excel 2016. A friend of mine just bought an MBA. He installed Office 2011 in order to work mainly with Excel. As he opened one of his file (6.5MB - multiple sheets - company logo - no graphs) and wanted to save it this box appears: where you can read something like Not enough memory. If I click on OK the box appears again like 15 times. Microsoft Excel users will sometimes get a message that says their computer is out of memory, enough system resources to display completely, cannot complete this task with available resources or Excel can not open workbook with available resources. Excel 2007-2019 users tend to see them more than Excel 2003 users.

i have two potentially related problems that have been frustrating me for some time now. i read all the message boards and did web searches, but haven't been able to find any answers. maybe you can help.

i use excel98 for the mac, and i have created a spreadsheet of formulas to use on stock data that i get via a VBA web query. i create a list of stock ticker symbols, and then i call on my macro to go down that list of tickers, retrieve historical quote data from MSN Investor on the web, and then apply a series of formulas to the data i have collected.

Not Enough Memory Excel For Mac 2011

works fine for the first few tickers, but after 4 or 5, apparently during the web query portion of my macro, i get a 'Not enough memory' error which halts the macro till i click 'OK'. it then proceeds to give me exactly 14 more of the same 'Not enough memory' errors, which i have to click 'OK' for, before continuing with the macro and moving onto the next ticker in the list.

problem is, i was hoping to run the macro for 1000 tickers at a time. and these error messages get to be quite a nuisance.

i've also noticed that after the error-filled macro finishes, i save the data in my file, close it, and try to re-open it, i am left with a useless file. every time i try to open it, i watch the little 'opening file' bar graphic on the bottom of the screen open the file halfway before presenting me with the exact same 'Not enough memory' error message that the macro did. i have tried using virtual memory and allocating more RAM to Excel, but it doesn't seem to make a difference.

i tried performing the same macro my brothers PC (with Excel97), and i get the same error messages.

i would be more than happy to share the code and/or the file if you think you could help me figure out the problem.

thanks.

-jeff nyveen

Jeff
Not sure, but maybe this will help
http://support.microsoft.com/support/kb/articles/Q183/8/47.ASP

Ivan

What to do if you get an out of memory or not enough resources message

Microsoft Excel users will sometimes get a message that says their computer is out of memory, enough system resources to display completely, cannot complete this task with available resources or Excel can not open workbook with available resources. Excel 2007-2019 users tend to see them more than Excel 2003 users. Microsoft re-wrote large portions of Excel when they released 2007. This re-write appears to have affected memory usage.

The conclusion we reached from the much testing is that COM add-ins are the major cause of memory issues.

  • COM add-ins sneak onto your PC without permission (or clearly asking).
  • The two COM add-ins we have seen that cause memory issues are Adobe and Blue Tooth. We have never seen a need for them in Excel. They should be removed immediately.
  • COM add-ins are compiled computer code that manages their own memory. Often at the expense of Excel, especially if they are badly written. Ususally you can run one COM add-in without issues. Two is like walking on thin ice. Three or more - just wait for the crash. It will happen.
  • VBA add-ins (the kind we write) are not compiled and are called Excel add-ins and do not cause memory issues. Excel manages memory for them. We have had all of our add-ins open at one time and no memory issues.

The following are steps one can take that may solve memory problems. It is also possible that none of these steps will solve. Memory problems are often difficult to solve. We have tried to order the steps from easy to do to hard to do. And from inexpensive to expensive.

The number one thing you can do: If you have any COM add-ins installed, un-install them unless they are absolutely required. COM add-ins are a special type of add-in written in machine language. They are often installed without explicit approval. COM add-ins are often reported as causing memory problems (our add-ins are not COM add-ins). Two frequently mentioned as problems are Blue-Tooth and Adobe COM addins. To un-install COM add-ins:

  • If you are using Excel 2003, download our free Add-in Information Lister and use it to un-install the COM add-ins.
  • If you are using Excel 2007, click the round Office Button, Excel Options, Add-ins, change the dropdown box to COM add-ins and click Go.
  • If you are using Excel 2010-2019, click File, Options, Add-ins, change the dropdown box to COM add-ins and click Go.

The easiest way to run out of memory and get the message 'Excel can not complete the task with available resources.' is to have 1) Multiple Excel sessions open and 2) other applications open. Run only one Excel session. Many Excel users will open new Excel sessions each time a new workbook is opened via double clicking on a workbook link. To see if you have multiple sessions open, press ALT-CTL-DELETE and check how any Excel applications are running. There should be just one running. Regarding other applications, it depends on what they are and how much memory they need before a problem happens. If a new Excel session opens each time you double click on a workbook, try unchecking the Excel Option 'Ignore other applications' if it is checked on the Options General tab.

We have gotten reports that the 64 bit version of Office 2010 has memory problems if other Office program are open.We do not recommend using the 2010 64 bit version of Excel. Very few users will see any benefit from it. The 64 bit version of Office 2013 and Office 265 runs fine. We run it often.

Close Excel every 1-2 hours. Excel does not seem to release all memory when workbooks are closed. Ultimately a crash will occur. Only closing Excel will solve.

Some cases of out of memory or resources are caused by doing a copy and paste that is not valid. Instead of advising that one can not do such an action, Excel says either out of memory or out of resources. It can happen if one is trying to paste a selection containing hidden cells or has merged cells. Try unhiding all rows and columns and then doing the copy and paste. Also try removing all formats first In Excel 97-2003 one would select Edit, Clear, Formats. In Excel 2007-2010 we're not certain how to do (except by using the Spreadsheet Assistant) Lastly, if this happens, select only the used range and copy and paste it.

Excel may think your worksheets are larger than you do! This can greatly consume memory. Normally your scroll area controlled by the scroll bars is very small. However, sometimes Excel thinks there are cells well below your used range. One way is to check where Excel thinks the last cell is located. Do this by pressing CTRL+SHIFT+END. If it well below your used range, then select all 'unused' columns in this range and delete them. Then select all unused rows in this range and delete them . Then close and re-open Excel.

Install the latest upgrades to your version of Office. Detailed steps are found on our page on how to repair Office. Do not shortcut the steps. Use the Temp File Deleter to first remove EXD files prior to doing a SP upgrade and prior to doing a repair is very important. Also doing a repair of Office after installing an SP upgrade is an essential step.

If you have all the upgrades in place, do a repair of Officeif you start getting memory or resource problems. We have noticed problems tend to appear after a Microsoft Windows automatic update or critical patch is done. Most PCs are set to run such automatically and often the user does not know any change was made to his or her PC. We suspect the update is causing the problem. be sure to run the Temp File Deleter before repairing Office.

Delete temp files on your PC. Use the free Temp File Deleter to do so.

If you are using Google Desktop Search, un-install it. Google Desktop Search appears to be a memory hog and has been reported to interfere with Microsoft Excel. Specifically, it installs a COM add-in that monitors every action in Excel so that it can index it. This obviously ties up a lot of memory and slows down Excel tremendously. The next suggestion advises how to remove COM addins.

Enough

Un-install any VBA add-ins you do not need. VBA add-ins are normally very passive and do not cause memory problems. We have 20-30 loaded all the time. Although our testing indicates VBA add-ins are not a major memory user, you should only install as add-ins those you need all the time. To un-install un-needed VBA add-ins:

  • If you are using Excel 97-2003, select Tools and Add-ins. A dialog with the available add-ins will appear. Uncheck the ones you do not need
  • If you are using Excel 2007, click the round Office Button, Excel Options, Add-ins, change the dropdown box to Excel add-ins and click Go.
  • If you are using Excel 2010, click File, Options, Add-ins, change the dropdown box to Excel add-ins and click Go.

There is a very small chance that you have an un-needed add-in or workbook in your XLSTART folder.

Such open automatically.Search for the folder XLSTART on your PC. It may be a hidden folder so you will have to set options to show hidden folders while searching. Some add-ins install themselves in this folder. Such addins are not listed in the above dialogs. Remove any you files in XLSTART folder that you do not need (with Excel closed). Typically the folder should be empty or contain only Personal.XLS.

See if you have an alternate startup folder and check its content, and remove anything you do not need (with Excel close). One program known to do this approach is called GoldMine, and its removal solved one user's memory problems. To find your alternate startup location:

Not Enough Memory Excel For Mac 2011 Download

  • If you are using Excel 97-2003, select Tools, Options, and the General tab.
  • If you are using Excel 2007, click the round Office Button, Excel Options, Advanced, and go to the General section.
  • If you are using Excel 2010-2013, click File, Options, Advanced, and go to the General section.

Delete your XLB file. It can become corrupt but cause no noticeable problems. If corrupt it can consume lots of memory. Excel will recreate, but button customization will be lost. This is a file where Excel stores its toolbar settings. To delete it, use the XLB File Deleter which is a free product we provide. There have been reports that doing this will solve problems. Any special toolbar customization will be lost, but hopefully easy to add back.

It is possible that your printer or its driver is causing the problem. Several years ago HP printers were causing a memory problem with Excel. We do not know if HP fixed the problem and it may still be around or surfacing again. Change your default printer if you have other printers available. Do not use a system printer as your default if you can avoid it. See if there is an update to your printer's driver. Another solution is to specify a different printer as your default, even if you do not have the one you are specifying. This means you will have to change your printer when you need to print, which is a headache. Today we use a Canon laser G3 as our printer of choice and do not have memory problems. Back when we had problems with HP printers we switch to a Brothers laser and the problem went away. We have no plans of ever using a HP printer again.....

Extensive printing was one of the causes of memory problems in Excel 97-2000. In some cases doing a lot of page setup changes either manually or via macros can cause problems. This was a definite problem back with Excel 97-2000. Use of recorded macros that change print settings often change many settings that do not need to be changed. Optimize such code by eliminating what does not need changing. We suspect this problem is solved with Excel 2003 as we did 100 page setup changes from a recorded macro and memory usage did not change.

Enough

Use of macros that do very extensive file creating, data manipulation, and graphing have been known to cause memory leak problems. Such macros are ones that typically run for 30 minutes or longer. Minimizing the number of add-ins installed (especially COM add-ins) and closing all other applications can help. Closing and re-opening Excel after such extensive macro or add-in work is the best way to fix memory problems such intense work causes.

Use of many large arrays in VBA can cause problems. For example, ReDim Array1(10000, 10000) will cause an out of memory problem. Use of too many Public declared variables in VBA can cause problems as these stay in memory even when the application is done running. Public arrays are a double sin.

If you are using Outlook 2007/13, there have been reports of memory and resource problems if it is running at the same time as Excel 2007/13. We do not use Outlook and thus can not confirm. Try keeping Outlook 2007/13 closed when using Excel macros or add-ins . A side benefit is you will focus on your Excel work.

Get a new video card with lots of video memory. Excel 2007-2013 charting system was re-written and may be using far more graphic resources than past versions. We do not have any recommendations on which card to get. If your PC does not have a video card and you can install one, that will free up memory. Especially if you are using Windows XP and already have 4 GB of memory installed. Changing your resolution to a lower setting will also help. If you do have a video card, you consider disabling the on board video to insure that memory does not get allocated to it. Making changes to BIOS settings can have adverse effects on the way a computer works. Use caution when performing BIOS modifications.

Use of Excel workbooks created on a Mac machine can cause problems. They are supposed to be in interchangeable, but we have had Mac users send up workbooks that will crash our computers. Try not to exchange files between PC and Macs!

Close Excel once every hour if you are doing a lot of editing or creating lots of charts.

If you have Track Changes turned on in Excel, turn off Track Changes as it uses a fair amount of memory. The default is Off.

Turn off AutoRecovery, as this takes another hunk of Excel memory. However, have a backup if you do. To turn off AuoRecovery:

  • Excel 2003: Tools, Options, Save tab. Uncheck AutoRecovery
  • Excel 2007: Office button, Excel Options, Save. Uncheck Auto Recovery
  • Excel 2010-2013: File,Options, Save. Uncheck Auto Recovery

Always open Excel as your first application. This gives it first rights to memory (or so we have heard). If you close Excel, close other applications and then re-open Excel to allow it first memory rights. Always open Excel before opening Outlook.

If you workbooks had a lot of conditional formats this can cause problems. Minimize use of condition formats whenever possible. There are reports of a bug in Excel 2007 where just copying and pasting conditionally formatted cells duplicates the formats over and over. Too many conditional formats can also cause slow workbook opening and slow response in Excel.

Minimize the number of charts in your open workbooks. Charts can cause a significant demand on memory. In Excel 97-2003, you can select Tools, Options, View, and click on the option under Objects to 'Show placeholders' to help on memory.

If your workbooks have a lot of formulas, see if there is a way to minimize formulas. Users with workbooks with tons of formulas often report memory problems. For example, if you create a lot of Vlookup formulas, consider doing a copy, paste special values to convert the results to values and eliminate the lookup.

Avoid Array formulas (ones entered by pressing CTL-Enter)

Not Enough Memory Excel For Mac 2011

If your workbooks have links to other workbooks, Excel must open and read those workbooks in order to evaluate your formulas. Look for ways to avoid workbook links.

If you have a lot of Windows applications appearing in your system tray, remove any system tray application that are not essential. Each application in the system tray is running and using resources all the time. We try to minimize the number we have. Their removal can often be very difficult and there is no one common way to remove them.

Press ALT-CTL-Delete, go to the Processes tab and click twice on CPU to sort by memory use in descending order. The total CPU use is shown at the bottom. A level below 50% should be a cause of concern. Monitor the results for awhile to see what is running and consuming resources. Then do a internet search on the application name. Some will be routine Windows programs. Some will be anti-virus programs. Others will be candidates for removal.

Problems in your application data folder for Excel can be the cause. The folder is typically 'c:documents and settings%username%application datamicrosoftexcel'. This is a hidden folder, so set your Explorer options to show hidden folders. After backing up, rename or delete this folder and its subfolders. Reboot the machine and open Excel. Excel will recreate the folder and needed contents.

Run the following two commands (change the number 11 (Excel 2003) to 12 for Excel 2007, 14 for Excel 2010 and 15 Excel 2013, and 16 for Excel 2016 or 2019) from the Run box to reset the Excel registry entries: You will need to get the full path to your excel file to do so (The ones below are for our PCs):

  • 'C:Program FilesMicrosoft OfficeOFFICE11excel.exe' /unregserver
  • 'C:Program FilesMicrosoft OfficeOFFICE11excel.exe' /regserver

These commands remove most of the Excel registry entries and then resets them. However, they do leave some residual settings.

Not Enough Memory Excel For Mac 2011

A more extensive way to clean the registry is to rename the Excel registry key and let Excel recreate it. It depends on the version of Excel. First, close Excel. Then do Run, Regedit and go to the Excel registry key. It will be
'HKEY_CURRENT_USERSoftwareMicrosoftOffice%version_number%Excel'
where %version_number% is 11 for Excel 2003, 12 for Excel 2007, 14 for Excel 2010 and 15 for Excel 2013. Rename this to OldExcel (this will back it up). Then re-open Excel. Excel will rebuild the registry entry. You will need to manually install any needed add-ins.

Not Enough Memory Excel For Mac 2011 Full

Lastly, your PC may not have enough memory. Lots of the cheaper PCs one can buy come with only 1 or 2 GB of memory. This is enough for browsing the internet and not much more.

Not Enough Memory Excel For Mac 2011 Free

  • If you are running Windows XP or Windows 7 32 bit, then you should have 4 GB of memory. More doesn't help we have been told.
  • If you have Windows 7 or windows 10, 64 bit, then buy lots of memory. We have 8 GB on our machines. They run fast!
Not Enough Memory Excel For Mac 2011

If you find other solutions that work, please let us know.