Excel 2007 microsoft visual basic out of memory

Try using less data or closing other applications. To increase memory availability, consider: -Using a bit version of Microsoft Excel. If you have encountered any of these messages, then it's time to get full information on the causes and solutions. You may think you have it all figured out from the instructions in the error messages but these don't always reveal the real cause of the problem because of their generic nature.

But if you keep seeing the error while performing any of the functions below, then it is time to figure out what is happening within your file:. Trying to Copy Formulas over A Large Area: If your Excel file is large or contains many features, your memory resources may just be running low. Thus, you are most likely to receive these error messages when trying to copy or fill formulas into a large area on your worksheet. The Excel versions Excel , , , and so on bit is limited because of the setting to 2GB or 32, source cells in cases where you use a smart fill option so trying to work with large sections of the worksheet will only trigger this error.

Problems with Calculations: The error might be related to a recalculation of formulas when you insert rows and columns, copy and paste or perform calculations. For instance, say your spreadsheet contains a million formulas on a sheet, then you insert another column.

Of course, Excel will have to recalculate all the formulas in your spreadsheet to make adjustments for the newly inserted column.

In this case, you may receive the error message "out of resources" depending on how complex your spreadsheet is, the bit capacity of your Excel, what formulas are involved and how your spreadsheet is built. Other Elements of Spreadsheet: There are other areas of Spreadsheets that can cause Excel memory errors such as complex PivotTables, excess shapes, macros, complex charts with many data points, and so on. 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 or Excel Other Applications: Another cause of the excel memory error may be that the other applications on your PC are taking up the bulk of your computer memory thereby leaving not enough memory for Excel to work with.

Here are some actions to take to can avoid seeing the error message "Excel Not Enough Memory":. Perform the Selection of Cells Gradually: First select just the part that needs filling from the whole range of the spreadsheet before you do the same for the next section of cells and continuously, one at a time.

To avoid reaching the limitations of your Excel, select on the last row of the section you previously filled before moving on to the second, the third segments, and so on until you reach the end of your selection. Separate your Excel file into different workbooks: Here, you can split up the large Excel file you are working with into different separate workbooks before going ahead to open each one in a different instance of Excel from the other to avoid complications that come from working with large files.

By implication, each of these workbooks you open will use 2GB as its memory limitation so you get to avoid reaching the memory limitation of 2GB placed on Excel.

To do this with the Move or Copy feature, follow these steps:. Right-click and choose "Move or Copy" from the menu that pops up. You can choose several non-adjacent sheets by holding down the "Ctrl" key and clicking them one after the other. You can also choose several adjacent sheets by holding down the "Shift" key on your keyboard and selecting the first and last options on the Sheet tab.

Hello Frankie, I have a solution for your problem. This will resolve your problem. About Cumba Som. Questions 0. Best Answers 7. It may solve your problem. About Morcelo Toldelo. Thank you Cumba Som! I did what you said and deleted all the files in the temp folder. Thank you very much. My excel works fine now. Many thanks TechyV! About Frankie Hilton. Questions Answers 4.

Best Answers 0. MS Visual Basic is showing an alert box with error Previous. Show 2 more comments. However this won't help if: you need the object after the procedure obviously your object does not fit in memory Another possibility is to switch to a 64 bit version of Excel which should be able to use more RAM before crashing 32 bits versions are typically limited at around 1. Besides holding the least amount of reference in VBA, I think it's very important to reduce the amount of data in Excel.

Remove all unused sheets, unused Cell, conditional formatting, coloring, validation, filter, e. Absolutely - weird formatting in unused cells for example can cause a book to go from 30kB to several MB However, if obj is not needed anymore earlier in the sub, setting it to nothing will free the memory earlier.

I'm having a similar issue: stackoverflow. Add a comment. Answer is you can't explicitly but you should be freeing memory in your routines. Some tips though to help memory Make sure you set object to null before exiting your routine. Ensure you call Close on objects if they require it. Don't use global variables unless absolutely necessary I would recommend checking the memory usage after performing the routine again and again you may have a memory leak.

Dreamwalker Dreamwalker 3, 4 4 gold badges 28 28 silver badges 60 60 bronze badges. I solved it simply by using the line below to get rid of array which is no longer necessary to me, before creating another one: Erase vArray Also if only 2 columns out of 30 are used, it is a good idea to create two 1-column arrays instead of one with 30 columns.

I think it was partially my code hogging too much memory while too many "big things" in my application - the workbook goes out and grabs another departments "daily report".. Mike Mike 11 1 1 bronze badge. This seems odd So on running your macro, wouldn't it be clear anyway? Stevetb77 Stevetb77 31 1 1 bronze badge.

The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Linked 2.

See more linked questions. Related Hot Network Questions. Question feed. Stack Overflow works best with JavaScript enabled.

Accept all cookies Customize settings.