How to deallocate memory in Excel 2007?


New Member
Mar 13, 2009
I have some macros that I have setup to continuously run under Excel 2007. Among other things, the macros connect to a webpage and import data from there, also import data from local HTML files and from other workbooks. The macros run 24/7, unattended, on a dedicated machine. Now everything works fine, EXCEPT:
After about 18 hours of running, Excel takes up about 800-1000 MB in memory. I assume this is because of incorrect/incomplete memory release when macro ends. thing is, I don't know how to make sure memory is released when macro ends. I added code to delete data conections and unused worksheets, but it did not help at all.
The macros:
At startup, clear all worksheets of all data already present
1. Connect to the webpage and get data from there (every time macro runs)
2. Connect to a local HTML file and get data from there (once a day)
3. Generate some reports out of the imported data in 4 worksheets
4. Generate charts from the data in the reports
5. Save the charts as HTML files
6. Delete the worksheets containing the charts

Theoretically, there shouldn't be any increase in memory occupied, but there is. Any idea of how to improve things so memory is properly released?
One more thing: The memory is released if i simply maximize Excel and minimize it back, without needing to stop the macros or close the file. So I am assuming some weird "keep it all in the cache" Windows idea! Anyway, I need a tip of how to achieve this automatically, without needing to get into the prod machine every day and maximize/minimize Excel...

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
1) Excel / the OS may not release memory unless it is needed elsewhere. Is performance hurting when Excel uses 1GB of memory?

2) If maximizing and minimizing the window is enough to release the memory, can you do the same programmatically and get the same results? Something like
    Application.WindowState = xlMaximized
    Application.WindowState = xlMinimized
Upvote 0
Well, memory IS needed elsewere. because I have multiple programs running on that machine (RSS-to-IRC script, a pretty large Java application and some scheduled tasks). I also was running the same file on a test machine which has a lot of programs running concurrently and the same thing was happening.
I added the code for maximizing, then minimizing the application, I am now waiting for memory occupancy results...
...and YES, those 2 tiny lines of code solved the problem. Memory occupancy went from 6 MB to 18 MB, and then back to about 8 MB. I will continue to check it in the long term and if still too much memory is occupied, I'll post here.
Thank you very much for the idea!
Upvote 0
Sorry tusharm, although indeed it helps a little bit, the solution still doesn't do the trick. After 2 hours of running, Excel was taking up 55 MB of memory; when I manually maximized and then minimized the window, memory usage went down to 6 MB. Would there be any difference between manually maximize/minimize and doing this via VBA?
...and I am wondering what would happen when entering a delay between Maximize and Minimize command... if you would be so kind to let me know the command as well... Should I use Application.Ontime?
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back