asked on

Out of Memory error on VBA because of an array

I have an array c() which is defined as a double. and it has 2 dimensions.

I am running simulations on vba and this variable is redim according to the user's desired number of simulations and number of values they want to use. However when the number of numbers they want to use and the number of simulations are a lot, excel vba gives an "Out of Memory" error highlighting the redim of the array C.

For example lets say Redim C(a1,a2)

a1 is the number of values that are going to be used and a2 is the number of simulations.
If I try a2=100 and a1=2620905, it doesnt work and gives me the "out of memory" error.

I need to go down to a2=10 in order to make it work. but i want to set a2 as 1000 at least.

How can I overcome this issue?

Should I define array C as something else so it can hold more values.

andrewssd3

If you know that all the elements in the array will hold the same data type, it's more efficient on memory usage to define the array with an explicit data type rather than allowing it to default to Variant, e.g.

Dim C() As Single

Open in new window

Obviously the smaller the space taken up by the data type, the more elements yuo'll be able to get. Otherwise, I don't think there much you can do, as you don't have any control whatever over the way VBA allocates memory. Is you machine short of RAM?

GrahamSkan

I calculate that the array would need more that 2gB memory. Do you need to use doubles at eight bytes per element?

A G

ASKER

Thanks for your response. i tried to define it as single but i still get the out of memory error.

I am not totally sure if it is a RAM error. Because, the computer runs smoothly even after I get the error. I always thought that the error might be cause because the array cannot hold that many elements.

So this is the "Run-time error 7 - Out of Memory Error" on Microsoft Visual Basic.

Martin Liss

Can you please show us the whole sub in which the array filling occurs?

andrewssd3

What version of Excel are you on?

A G

ASKER

andrewssd3: Excel Version is 2003 running on an XP Computer.
MartinLiss: I cannot post the sub. But array is filled according to user's preferences. Meaning, if user may want to use 3000 values for a1 and do 1000 (a2) simulations.
Grahamskan: it is all numbers that fills the array. So, it is all going to be percentages with decimal points. So, I am not sure if I need to use doubles or not. I am open to suggestions.

GrahamSkan

VB(A) can swap working memory to the hard disc, so it probably won't affect subsequent performance.

However this is from a Microsoft article for VB.net 2005

The length of every dimension of an array is limited to the maximum value of the Integer data type, which is (2 ^ 31) - 1. However, the total size of an array is also limited by the memory available on your system. If you attempt to initialize an array that exceeds the amount of available RAM, the common language runtime throws an OutOfMemoryException exception.

andrewssd3

You could try Dim'ing it as Integer and holding the percentages as 4 digit integers if you can live with only keeping 2 places of decimals e.g. store 25.35% as 2535.

A G

ASKER

GrahamSkan: Ok so 2^31 equals approximatley 2.1 billion. When I run 1000 simulations it is around 2.6billion values. but even if I run 500 simulations (1.3 billion values), I still get the out of memory error. So maybe my system does not have enough memory.

andrewssd3:I still get the Out of Memory error when I set array c as integer. However, do i need to something additional to set it for 4 digits?

andrewssd3

No I just suggested 4 digits because the maximum for a VBA Integer is 32,767 so if you tried for 3 places for decimals you would not be able to go over 37.767%

A G

ASKER

andrewssd3: So, how would you set the variable? I am sorry, I dont know how to set the variable as you said.

GrahamSkan

How much RAM do you have?

A G

ASKER

GrahamSKan : 3 gb

andrewssd3

It depends slightly how you are currently holding your percentages. If you are holding them as whole number figures e.g. if you currently store 34.2537328 for 34.25%, then you could round them to 2 places of deicmals and multiply by 100, e.g.

?C(i,j)=round(a/b*100,2)*100

Open in new window

If you store them as fractions as Excel does internally e.g. 25.324456% = 0.25324456, you would need to round to 4 places, and multiply by 10,000:

?C(i,j)=round(a/b,4)*10000

Open in new window

Then when you get them out of the array you would divide by 100 or 10,000

andrewssd3

I have just done some experimenting with allocating arrays and on my machine with Excel 2007 and 4GB of RAM I always get that Out of memory error when the total size of the array exceeds about 190MB. It doesn't seem to matter how the dimensions are organised. So for an array of Integer, which takes 2 bytes in VBA, I could have a total of about 99,000,000 elements, say

Redim C (99 to 1000000)

Open in new window

It looks from what you are saying as though you mu=ight have a similar limitation, but I can't find this documented anywhere. You might just have to work out what your limits are and stop them requesting too much.

Rob Brockett

hi everyone,

I think that all the posters here so far have much more knowledge than me, but I've got to learn some how so here are my two cents ;-)
I've read about memory limits before on Charles Williams' site & he states:

"Excel 2007 is limited to 2 Gigabytes of memory for the Excel process under Windows XP/Vista (Windows memory limit).
This 2 Gigabyte limit is a limit on the Virtual Memory address space. Virtual memory used by a process is larger than the working set memory reported by Windows Task Manager, so the amount of useable memory under Excel 2007 is considerably less than twice that of Excel 2003.
Because Excel 2007 (Excel12) also requires more memory to store the indexes to the increased number of rows and columns you may not be able to load larger workbooks under Excel 2007 than was possible under Excel 2003. Memory Fragmentation may also mean that it is difficult to make use of all of the available 2 GB of virtual memory."

Sourced from: http://www.decisionmodels.com/memlimitsc.htm

Plus a very similar response:

"...Part of this lack of real improvement is caused by differences in
terminology (virtual memory is not the same as working set memory), part by
changes in the way Excel 2007 uses memory and part by increased size of
indexes for the big grid.
"

by Charles Williams & sourced from: http://www.pcreview.co.uk/forums/out-memory-error-t3756797.html

I don't think these are as relevant but here are a list of links: http://windowssecrets.com/forums/showthread.php/128704-Max-Array-size-in-Excel-2007-Worksheet-Function?s=7711cf569b6ffcad7cd7f9164a922b26&p=748939&viewfull=1#post748939

----------
I recognise there will be no impact on the current issue, but is the Redim being done once or is inside a loop?
I guess you could use a set of arrays that contain your results in smaller "blocks".

Anyway, back to the more knowledgeable people :-)

hth
Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

A G

ASKER

I have a work around for the problem. So, instead of running 1000 simulations at once, I do this. So each simulation routine might have 10 simulations, and when you repeat the process for 100 times, we get 1000 simulations. By this way we do not get the "Out of Memory Error"

Sub sim()
For I = 0 To 100
Run "simulationroutine"
Next
End Sub

ASKER CERTIFIED SOLUTION

aikimark

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

aikimark

@awesomejohn19

Thanks for the points. What was your eventual solution configuration?