COM add-ins and 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 cannot open a workbook with available resources.

The conclusion we reached from 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). Routinely check and remove them by clicking File, Options, Add-ins, change the dropdown box to COM add-ins and click Go
• 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. Usually 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 hard to solve. We have tried to order the steps with the most likely solutions first. And from inexpensive to expensive.

The number one thing you can do: If you have any COM add-ins installed, uninstall 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 add-ins. To uninstall COM add-ins: 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 cannot 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 many 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.

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

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 cannot 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. One way to do so is to copy the format of a blank cell in a new workbook using the Format Painter. Then apply it to all cells you are copying.

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 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 is 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.

If you have all the upgrades in place, do a repair of Office if 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.

If you are using Google Desktop Search, uninstall 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 add-ins.

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 switched to a Brother laser and the problem went away. We have no plans of ever using an HP printer again.

In some cases doing a lot of page setup changes either manually or via macros can cause problems. Use of recorded macros that change print settings often changes 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.

Use of macros that do very extensive file creating, data work, 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.

Get a new video card with lots of video memory. Excel's charting system 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 might consider disabling the onboard video to ensure 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 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 AutoRecovery: 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 your workbooks have a lot of conditional formats, this can cause problems. Minimize use of conditional 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.

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).

If your workbooks have links to other workbooks, Excel must open and read those workbooks 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 is 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 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 a while to see what is running and consuming resources. Then do an internet search on the application name. Some will be routine Windows programs. Some will be antivirus 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 data\microsoft\excel". 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 contents.

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.

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