Constantly getting "Out of memory" messages when VBA window is open

Cross-posted at
MrExcel
Microsoft Community
Reddit

For quite some time I have been getting pop-up dialogs that say "Out of memory" when I am using Excel. The system memory utilization is typically around 50% when this happens. I have 32G of physical memory.

These messages typically occur after I have been using Excel for a while. It doesn't seem to matter how many files I have open; it happens if only one file is open. However, once it starts, it only happens when the VBA development window is open, and happens immediately after any action on worksheet, even selecting another cell. It does not happen when VBA code is actually running. Closing the VBA window causes the messages to stop, until the next time I open it.

I have been working around it but it's time to solve it. I have found no clues in searches since most of them recommend reducing memory usage or adding more physical memory. But there is 16G of headroom, so this should not be an issue.

Currently on version 2402, Build 17328. 20184, Current Channel.

As an example, it's happening right now. Excel has four files open, and Task Manager shows Excel is using only 137MB of memory.

I've never seen this problem so my suggestions below are based purely on some searching. They all involve using tools to check how much memory Excel thinks it has, as opposed to how much your system actually has. Apologies if you've already been down these roads.

1. This page suggests that there might be a 4GB limit for VBA, which might (I'm stretching here) apply to the VBE too. It suggests using the MS Technet Process Explorer to measure Virtual Memory use.

2. This MS page about Excel performance and limits has a link to a Memory Checking Tool which also might help narrow down possible causes.

3. Finally, it's possible to check how much memory Excel thinks it's got using the _Application.MemoryFree property, though of course this only works when Excel/VBE hasn't already stopped working.

Disclaimer: I haven't tried any of the tools linked above, as I've never needed to.
Good luck.

Thanks for the references. I'll dig in. #3 is a .NET call, not sure how you get to it. I don't know if there is a VBA API or if I have to develop a .NET application in C#.