"Out Of Memory" error in excel VBA for a very large model. How can I avoid this?

Question

I am trying a declare a new variable in VBA for Excel. I have an excel model which has 9 modules and 7 class modules. Each module is really large, with an average of 60 variables declared in each module and a minimum of a few hundred lines of code to a maximum of a couple of thousand lines of code in each module. Every time I try typing a new variable, I get an error that says "Out Of Memory". How can I avoid this error and continue declaring more variables ?

Answer 1

Please note that there are size limits on VBA Forms, Standard, and Class Modules, Procedures, Types, and Variables.

I've only seen it documented here:

https://learn.microsoft.com/en-us/previous-versions/visualstudio/visual-basic-6/aa716182(v=vs.60)

You either need to reduce the scope of your program, splitting it in to logical steps. Or use a more robust programming language like VB.NET.

Answer 2

Every time I try typing a new variable, I get an error that says "Out Of Memory".

This sounds like a design-time error--an error you get when editing the code rather than a run-time error that you get when running the code.

If indeed this is a design-time error your file may be corrupted. Try rebuilding it by copying all sheets into a new workbook and copying the code into new, blank modules.

Answer 3

As mentioned in the comment we have too little data to provide you with a definite answer.

However the reasons may be plenty:

• You are declaring a lot of objects ("Set obj = ") and never cleaning them (Set obj = Nothing). If you do not reduce the reference to an object it will remain in memory.
• You have a loop in which you are declaring a lot of objects/variables until you get a memory Overflow.
• You are creating too many objects at once that allocate too much memory (e.g. IE object etc.)

How to deal with this?

• Start with the code that raises the error as most likely this is happening in a loop or another place which increases memory usage (use debugging F8 to traverse code). There may be many solutions depending on the source of your issue.

• Leverage memory statistics throughout different milestones in your code https://social.msdn.microsoft.com/Forums/office/en-US/e3aefd82-ec6a-49c7-9fbf-5d57d8ef65ca/check-size-of-excelexe-in-memory or simply use the Task Manager

• See if any of these tips help: https://www.add-ins.com/support/out-of-memory-or-not-enough-resource-problem-with-microsoft-excel.htm