KeithRoberts
11-05-2008, 12:21 PM
I am trying to create a dynamic multidimensional array. I am trying to store data for use in a Monte Carlo simulation. The array needs to be three dimensional, to store task id, finish date, and count. As the array will change on the number of tasks chosen, the number of iterations chosen, I need to ability to redim preserve the array on the fly. I need to capture the task id (or name), the different dates, and count the number of times that date was selected during the monte carlos process. I will then use the data to create the appropriate charts.
The data might look like:
Task ID Date Count
215 11/5/08 1
215 11/6/08 2
215 11/7/08 1
216 11/6/08 2
216 11/7/08 2
217 11/5/08 3
217 11/7/08 1
I can predetermine the number of tasks and the iterations for the Monte Carlos simulation, but I cannot predetermine the number of dates that would be used as this is randomized using optimistic and pessimistic durations.
How would I code VBA to create, populate, and retrieve the individual elements within the array? For example, if I create an array as:
myArrary(2, 4, 0)
I know that the first elements would be the task ids (0 - 2, three tasks), the second elements would be dates (0 - 4, five possible dates), and the third elements would the counts (0, one running count). I would have thought that I could access the array to populate the first elements by using only one index, such as myArrary(lIndex1), but after that, I am totally lost. Please note that the dates for each task are probably different as the finish dates will be different.
I want to be able to loop through the array, find the task id, find the date, and increment the count by one if the date is found. If the task id is not found, I need to add the task id. If the task id is found, but the date is not found, I need to add the date and set the count to 1.
Am I asking too much from VBA? Or is it my ignorance? :dunno
The data might look like:
Task ID Date Count
215 11/5/08 1
215 11/6/08 2
215 11/7/08 1
216 11/6/08 2
216 11/7/08 2
217 11/5/08 3
217 11/7/08 1
I can predetermine the number of tasks and the iterations for the Monte Carlos simulation, but I cannot predetermine the number of dates that would be used as this is randomized using optimistic and pessimistic durations.
How would I code VBA to create, populate, and retrieve the individual elements within the array? For example, if I create an array as:
myArrary(2, 4, 0)
I know that the first elements would be the task ids (0 - 2, three tasks), the second elements would be dates (0 - 4, five possible dates), and the third elements would the counts (0, one running count). I would have thought that I could access the array to populate the first elements by using only one index, such as myArrary(lIndex1), but after that, I am totally lost. Please note that the dates for each task are probably different as the finish dates will be different.
I want to be able to loop through the array, find the task id, find the date, and increment the count by one if the date is found. If the task id is not found, I need to add the task id. If the task id is found, but the date is not found, I need to add the date and set the count to 1.
Am I asking too much from VBA? Or is it my ignorance? :dunno