How to Identify the Cause of Increased Memory Usage by Excel

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
I set out to do some testing to ensure my VBA code doesn't leak memory and ran into an unexpected problem.

My first step was to try to establish baselines for memory usage prior to running any VBA. Surprisingly, even without executing any VBA or doing any complex operations like queries, it appears difficult to establish an accurate baseline for how much memory Excel needs for a single "blank" workbook or no open workbooks.

Below are the results of some testing. Repeating the entire test a few times shows variances of about 200-300 K bytes.

Prior to running the tests all Add-Ins were uninstalled and all files were removed from \XLSTART (no Personal.xlsb loaded).
Excel Workbook
ABCD
1Memory usage of EXCEL.EXE process reported by Process Explorer**
2
3Using default Undo settingsMemory usage after step
4StepDescriptionPrivate Bytes*Working Set*
50Restart computer then launch Process Explorern/a n/a
61Launch Excel which creates Book1.xlsx14,900 K25,200 K
72Close Book1.xlsx14,700 K25,700 K
83Open New Book which creates Book2.xlsx15,100 K26,400 K
94Enter the value: 1 in range A1:E1048576128,600 K140,200 K
105Clear all values from same range (used Delete key)169,700 K181,400 K
116Close Book2.xlsx17,600 K30,100 K
127Open New Book which creates Book3.xlsx17,900 K30,300 K
13
14*number of bytes rounded to nearest hundred K
15** Process Explorer app can be downloaded at www.systernals.com
Sheet


So these results beg the question: "Why did Excel's usage of Private Bytes/Working Set memory go up by 3,000 K and 5,000 K respectively after running a process then closing the file without saving?"

That's not a huge percentage of available memory, but it makes it difficult to measure memory leaks from VBA or other sources (unless those leaks are large or cumulative).

Repeating steps 4-7 multiple times does not appear to further increase memory usage.

Because Undo can have a large impact on memory use, I repeated the test with Undo disabled....
Excel Workbook
ABCD
20Memory usage of EXCEL.EXE process reported by Process Explorer**
21
22After setting Undo Levels to 0 through registryMemory usage after step
23StepDescriptionPrivate Bytes*Working Set*
240Restart computer then launch Process Explorern/a n/a
251Launch Excel which creates Book1.xlsx15,400 K26,300 K
262Close Book1.xlsx15,300 K27,000 K
273Open New Book which creates Book2.xlsx15,600 K27,800 K
284Enter the value: 1 in range A1:E104857687,200 K99,600 K
295Clear all values from same range (used Delete key)87,200 K99,700 K
306Close Book2.xlsx18,000 K30,900 K
317Open New Book which creates Book3.xlsx18,300 K31,200 K
32
33*number of bytes rounded to nearest hundred K
34** Process Explorer app can be downloaded at www.systernals.com
Sheet


These results show the same behavior of increasing Private Bytes/Working Set memory usage by 3,000 K and 5,000 K.
It's interesting to compare the memory use after steps 3-4 with and without Undo enabled- but that's a different topic.

Can anyone shed some light on this topic, and/or suggest a method through which one can perform testing that identifies memory leaks caused by executing VBA code as distinguished from other causes?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

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 MrExcel.com.
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 "mrexcel.com".
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
Top