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.

 

[limerind]

I might have to switch back to Long. Great information!