How to Identify the Cause of Increased Memory Usage by Excel

[Jerry Sullivan]

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
	A	B	C	D
1	Memory usage of EXCEL.EXE process reported by Process Explorer**
2
3		Using default Undo settings	Memory usage after step
4	Step	Description	Private Bytes*	Working Set*
5	0	Restart computer then launch Process Explorer	n/a	n/a
6	1	Launch Excel which creates Book1.xlsx	14,900 K	25,200 K
7	2	Close Book1.xlsx	14,700 K	25,700 K
8	3	Open New Book which creates Book2.xlsx	15,100 K	26,400 K
9	4	Enter the value: 1 in range A1:E1048576	128,600 K	140,200 K
10	5	Clear all values from same range (used Delete key)	169,700 K	181,400 K
11	6	Close Book2.xlsx	17,600 K	30,100 K
12	7	Open New Book which creates Book3.xlsx	17,900 K	30,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
	A	B	C	D
20	Memory usage of EXCEL.EXE process reported by Process Explorer**
21
22		After setting Undo Levels to 0 through registry	Memory usage after step
23	Step	Description	Private Bytes*	Working Set*
24	0	Restart computer then launch Process Explorer	n/a	n/a
25	1	Launch Excel which creates Book1.xlsx	15,400 K	26,300 K
26	2	Close Book1.xlsx	15,300 K	27,000 K
27	3	Open New Book which creates Book2.xlsx	15,600 K	27,800 K
28	4	Enter the value: 1 in range A1:E1048576	87,200 K	99,600 K
29	5	Clear all values from same range (used Delete key)	87,200 K	99,700 K
30	6	Close Book2.xlsx	18,000 K	30,900 K
31	7	Open New Book which creates Book3.xlsx	18,300 K	31,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?