Excel Memory Checking Tool: Using LAA to increase useable Excel memory

Most of us sooner or later get hit by one or more of the dreaded messages “Out of Memory” or “Excel cannot complete this task with available resources” or “Not enough System Resources/Memory to Display Completely” , regardless of how much RAM or how big a swap-file you have.

Excel’s usable memory has been increasing steadily with each version:

• Excel 2003: 1 Gigabyte of working set memory
• Excel 2007: 2 Gigabytes of virtual memory
• Excel 2010, Excel 2013 and Excel 2016 32-bit: 2 Gigabytes of virtual memory
• Excel 2010, Excel 2013 and Excel 2016 64-bit: 131072 Gigabytes of virtual memory

Although the introduction of the 64-bit versions of Excel  in theory removed any real limitation many people were not able to switch to 64-bit Excel because

• Most OCX controls are only available in 32-bit
• Many third party addins are only available in 32-bit

But the need for larger usable memory has also been increasing:

• Excel models seem to get larger every year
• Each successive Excel version uses more memory than the previous version
• PowerPivot and other BI tools need a lot of memory

So earlier this year the Excel team announced and made available a change to 32-bit Excel 2013 and 2016:

Large Address Aware (LAA) capability change for Excel

If you are using a 64-bit version of Windows this change doubles available virtual memory for 32-bit Excel 2013 and 2016 to 4 Gigabytes.

If you are using a 32-bit version of Windows then this change can increase virtual memory for Excel 2013 and and 2016 to 3 Gigabytes, BUT:

• With 32-bit Windows you need to enable the /3GB boot switch
• This switch halves the amount of memory (from 2GB to 1 GB) available to 32-bit Windows.

This LAA change was introduced in updates in May and June 2016:

• For Excel 2013 you need to be using Build 15.0.4833 or later.
• For Excel 2016 Office 365 you need to be using Build 16.0.6868.2060 or later
• For Excel 2016 MSI you need to be using Build 16.0.4394.1000 or later

For more details on the LAA change see this Knowledge Base article

Excel Memory Checking Tool

Finding out how much virtual memory Excel is actually using, and what the current maximum limit is for your installation, is surprisingly difficult.

• Task Manager only shows working set memory, which is not the same thing as virtual memory.
• Process Explorer can show virtual memory used, but you have to add an additional column.

And I have not found a readily available tool that tells you what Excel’s maximum usable memory is. So I decided to create one using Windows API calls and VBA.
Here are a few examples of the output:

Excel 2013 32-bit with 64-bit Windows: 4GB

Excel 2016 32-bit with 32-bit Windows without the /3GB boot switch: 2GB

Excel 2016 64-bit with 64-bit Windows: 131072 GB

You can download the ShowMemory2 tool from here or here

Let me know of any problems!