Assign Cell Value to Array Dimension

[limerind]

This forum is a Godsend for people like me trying to teach VBA to themselves. Thanks in advance for any help.

I'm trying to run a Monte Carlo simulation through a four dimensional array, and I'd like to do so in a way that's at least a little user friendly. So I've set up a worksheet with some user-defined parameters.

The Simulation works like this: Each entrant in a group makes 10 decisions (dimension 1 of the array). There are anywhere between 50 and 5000 entrants in a given contest (dimension 3 of the array), and I want to simulate running the contest anywhere between 50 and 5000 iterations (dimension 4 of the array). Dimension 2 of the array are the information and calculations defining the decision. Now I'm trying to use those parameters to define the array, and I'm being told that array dimensions need to be constants.

The nascent code is below:

Public Sub RunDataGenerator()
Dim Entrants As Integer
Dim Iterations As Integer
Entrants = Sheets("Information").Range("C4").Value
Iterations = Sheets("Information").Range("C5").Value

Dim PicksArray(9, 7, Entrants, Iterations) As Double

I'm looking for a way to define the array based on the entries in Cells C4 and C5 of the Information Sheet. I have also tried ReDim with no success.

Also, making the Array global would be a tremendous help, though I can work around it if it's not.

 

[Colin Legg]

Something like this?

Option Explicit
 
Public PicksArray() As Double
 
Public Sub RunDataGenerator()
    Dim Entrants As Double
    Dim Iterations As Double
    
    Entrants = Sheets("Information").Range("C4").Value
    Iterations = Sheets("Information").Range("C5").Value
    
    ReDim PicksArray(0 To 9, 0 To 7, 0 To Entrants, 0 To Iterations)
    'etc...
    
End Sub

 

[Firefly2012]

Hi

Simply:

ReDim PicksArray(9, 7, Entrants, Iterations) As Double

should work. You can define the array as Public at the top of your module with:

Public PicksArray() As Double

 

[p45cal]

untested, but

Dim PicksArray() As Double
ReDim PicksArray(9, 7, Entrants, Iterations)
might work.
</pre>

 

[limerind]

Thanks for the quick help, folks. When i try

Public PicksArray() As Double

above the Sub, I get the error:

Compile error:

Constants, fixed-length strings, arrays, user-defined types and Declare statements are not allowed as Public members of object modules.

Am I missing something?

Code now looks like:

Public PicksArray() As Double


Public Sub RunDataGenerator()
Dim Entrants As Integer
Dim Iterations As Integer
Entrants = Sheets("Information").Range("C4").Value
Iterations = Sheets("Information").Range("C5").Value

ReDim PicksArray(0 To 62, 0 To 7, 0 To Entrants, 0 To Iterations) As Double

End Sub

 

[Colin Legg]

If you want the array as a public variable then your code needs to go in a standard code module (Insert > Module), not in a worksheet (or other class) module.

 

[p45cal]

Does PicksArray need to be global?
Did my suggestiom in message 4 cause an error?

 

[limerind]

Thanks so much for the help, folks. Colin, I especially appreciate you sticking with me through the bone-headedness of a novice. Everything works fine now, so long as I keep low numbers like 20 or 100 in the array, otherwise I get an overflow. is there a limit to the size of an array?

Thanks!

 

[limerind]

Does PicksArray need to be global?
Did my suggestiom in message 4 cause an error?

It would've had I been in a module instead of ThisWorkbook. Thanks for your help.

 

[Colin Legg]

Everything works fine now, so long as I keep low numbers like 20 or 100 in the array, otherwise I get an overflow. is there a limit to the size of an array?

The array size is limited by memory. I suspect that the overflow error relates to your Integer declarations. In VBA, Integer types are 16-bit, so can only hold whole numbers between -32,768 and 32,767. Were you trying to assign a value above that to either Entrants or Iterations?

It's worth noting that, with four dimensions, it won't take much to get an out of memory error - on my PC both of these choked:

ReDim PicksArray(0 To 62, 0 To 7, 0 To 5000, 0 To 50) As Double

ReDim PicksArray(0 To 62, 0 To 7, 0 To 400, 0 To 400) As Double