CODEWITHSUNDEEP
excelvba
Mark Morrisson
Mark Morrisson

Reputation: 2703

Insufficient memory error in Excel

Due to a large amount of data (more than 2,000,000 entries in a collection), Excel terminates my VBA program with an "Insufficient memory error" (code 7). I know that VBA is not the right language to handle so many data, but is there a way to increase the memory that Excel uses to run a VBA program? I precise it's not a stack overflow, the number of stack calls are limited to 10.

Thanks in advance, Mark

Upvotes: 2

Views: 1579

Answers (1)

Charles Williams
Charles Williams

Reputation: 23520

Depends what Excel version you are using. Versions earlier than Excel 2007 tend to have quite restricted memory capacity.
32-bit Excel 2007 and later in theory can use a total of up to 2GB but in practice its often hard to get more than about 512MB for VBA.
64-bit Excel 2010 and later are much better: I have successfully used up to 4 Gigabytes of VBA memory.

Its possible that using a Dictionary rather than a Collection will be somewhat more memory efficient, but I have not tested it.

Upvotes: 4

Related Questions