Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Out of Memory error on VBA because of an array

Posted on 2011-10-10
19
Medium Priority
?
1,970 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:awesomejohn19
  • 6
  • 6
  • 3
  • +3
19 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36942694
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?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 36942707
I calculate that the array would need more that 2gB memory. Do you need to use doubles at eight bytes per element?
0
 

Author Comment

by:awesomejohn19
ID: 36942724
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 50

Expert Comment

by:Martin Liss
ID: 36942734
Can you please show us the whole sub in which the array filling occurs?
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36942751
What version of Excel are you on?
0
 

Author Comment

by:awesomejohn19
ID: 36942771
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 36942779
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.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36942790
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.
0
 

Author Comment

by:awesomejohn19
ID: 36942820
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?
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36942846
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%
0
 

Author Comment

by:awesomejohn19
ID: 36942898
andrewssd3: So, how would you set the variable? I am sorry, I dont know how to set the variable as you said.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 36942949
How much RAM do you have?
0
 

Author Comment

by:awesomejohn19
ID: 36942987
GrahamSKan : 3 gb
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36943000
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
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36943057
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.
0
 
LVL 10

Expert Comment

by:broro183
ID: 36945800
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...
0
 

Author Comment

by:awesomejohn19
ID: 36948650
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
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 36956035
You will probably have to replace your array with a memory table, direct access file, or database.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37041044
@awesomejohn19

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question