& Ldquo; Out Of Memory & rdquo; Error in Excel VBA for a very large model. How can I avoid this?


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?