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 ?
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