<div>
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.<br />

<pre><code class="code-1 nolinks" id="code-20-36942694-1"><span>Dim C() As Single</span>
</code></pre>
Obviously the smaller the space taken up by the data type, the more elements yuo'll be able to get. &nbsp;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?
</div>


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


<div>
Thanks for your response. &nbsp;i tried to define it as single but i still get the out of memory error. <br />
<br />
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. <br />
<br />
So this is the &quot;Run-time error 7 - Out of Memory Error&quot; on Microsoft Visual Basic. 
</div>


<div>
Can you please show us the whole sub in which the array filling occurs?
</div>


<div>
What version of Excel are you on?
</div>


<div>
andrewssd3: Excel Version is 2003 running on an XP Computer. <br />
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. <br />
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. <br />

</div>


<div>
VB(A) can swap working memory to the hard disc, so it probably won't affect subsequent performance. <br />
<br />
However this is from a Microsoft article for VB.net 2005<br />
<br />
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.
</div>


<div>
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.
</div>


<div>
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. <br />
<br />
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?
</div>


<div>
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%
</div>


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


<div>
It depends slightly how you are currently holding your percentages. &nbsp;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.<br />

<pre><code class="code-1 nolinks" id="code-20-36943000-1"><span>?C(i,j)=round(a/b*100,2)*100</span>
</code></pre>
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:<br />

<pre><code class="code-1 nolinks" id="code-20-36943000-2"><span>?C(i,j)=round(a/b,4)*10000</span>
</code></pre>
Then when you get them out of the array you would divide by 100 or 10,000<br />

</div>


<div>
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. &nbsp;It doesn't seem to matter how the dimensions are organised. &nbsp;So for an array of Integer, which takes 2 bytes in VBA, I could have a total of about 99,000,000 elements, say 
<pre><code class="code-1 nolinks" id="code-20-36943057-1"><span>Redim C (99 to 1000000)</span>
</code></pre>
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.
</div>


<div>
hi everyone,<br />
<br />
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 ;-)<br />
I've read about memory limits before on Charles Williams' site &amp;&nbsp;he states:<br />
<br />

<blockquote>
&quot;<i>Excel 2007 is limited to 2 Gigabytes of memory for the Excel process under Windows XP/Vista (Windows memory limit).<br />
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.<br />
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.</i>&quot;
</blockquote>
 Sourced from: <a href="http://www.decisionmodels.com/memlimitsc.htm" rel="ugc">http://www.decisionmodels.com/memlimitsc.htm</a><br />
<br />
Plus a very similar response:<br />

<blockquote>
&quot;<i>...Part of this lack of real improvement is caused by differences in<br />
terminology (virtual memory is not the same as working set memory), part by<br />
changes in the way Excel 2007 uses memory and part by increased size of<br />
indexes for the big grid.<br />
</i>&quot;
</blockquote>
by Charles Williams &amp;&nbsp;sourced from: <a href="http://www.pcreview.co.uk/forums/out-memory-error-t3756797.html" rel="ugc">http://www.pcreview.co.uk/forums/out-memory-error-t3756797.html</a>&nbsp;<br />
<br />
I don't think these are as relevant but here are a list of links: <a href="http://windowssecrets.com/forums/showthread.php/128704-Max-Array-size-in-Excel-2007-Worksheet-Function?s=7711cf569b6ffcad7cd7f9164a922b26&p=748939&viewfull=1#post748939" rel="ugc">http://windowssecrets.com/forums/showthread.php/128704-Max-Array-size-in-Excel-2007-Worksheet-Function?s=7711cf569b6ffcad7cd7f9164a922b26&p=748939&viewfull=1#post748939</a>&nbsp;<br />
<br />
----------<br />
I recognise there will be no impact on the current issue, but is the Redim being done once or is inside a loop?<br />
I guess you could use a set of arrays that contain your results in smaller &nbsp;&quot;blocks&quot;.<br />
<br />
Anyway, back to the more knowledgeable people :-)<br />
<br />
hth<br />
Rob<br />
__________________<br />
Rob Brockett. Always learning &amp;&nbsp;the best way to learn is to experience...
</div>


<div>
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 &quot;Out of Memory Error&quot;<br />
<br />
Sub sim()<br />
For I = 0 To 100<br />
Run &quot;simulationroutine&quot;<br />
Next<br />
End Sub
</div>


<div>
@awesomejohn19<br />
<br />
Thanks for the points. &nbsp;What was your eventual solution configuration?
</div>


<div>
<div class="content wysiwyg-content">
I have an array c() which is defined as a double. and it has 2 dimensions.<br />
<br />
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 &quot;Out of Memory&quot; error highlighting the redim of the array &nbsp;C.<br />
<br />
For example lets say Redim C(a1,a2)<br />
<br />
a1 is the number of values that are going to be used and a2 is the number of simulations. <br />
If I try a2=100 and a1=2620905, it doesnt work and gives me the &quot;out of memory&quot; error. <br />
<br />
I need to go down to a2=10 in order to make it work. but i want to set a2 as 1000 at least.<br />
<br />
How can I overcome this issue?<br />
<br />
Should I define array C as something else so it can hold more values. 
</div>
</div>


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. 

Out of Memory error on VBA because of an array

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

Visual Basic Classic

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.