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

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

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

excel_64_win64You can download the ShowMemory2 tool from here or here

Let me know of any problems!

Advertisements
This entry was posted in Memory, VBA and tagged , , , , . Bookmark the permalink.

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

  1. Do you have an alternate ShowMemory2 download link?

    (because I can not use onedrive for security reasons)

    Sebastian

  2. Frank says:

    Download link does not work !?

  3. Edouard says:

    Hi Charles,

    Would you mind explaining the columns you look at when using Process Explorer ?

    Best regards,

    Edouard

  4. Charlie Hall says:

    I am wondering if you have tried this tool (http://www.ntcore.com/4gb_patch.php) for Excel 2010 and what the results are for it? it allows Excel 2010 to make use of 4GB of memory when running on a Win64 system

  5. Sebastian says:

    Do you have an ShowMemory2 alternate link? This is because i can not use onedrive for security reasons(js, browser dom storage, etc)

    I want create a small cpu/memory usage taskpane based on your code in my NetOffice project.
    (A perfect usefull example what i find, credits was given – of course, a private mail to public[at]sebastian.web.de would also helpful)

    (a private note here as a german: people are not her governments, this vote for trump means not all americans are stupid. what i bevlieve is: a lot of people there vote for trump now shocked he realy win…)

  6. Sebastian says:

    ooops sorry !!! i gave you an answer in mail but no response in my mail account
    now i see you answer me here and i didnt notice.
    (please delete my answer’s – sorry*)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s