PDA

View Full Version : Dynamic MultiDimensional Arrays - setting and accessing the various layers



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

Bob Phillips
11-05-2008, 01:55 PM
That array looks like a simple 2D array to me,

MyArray(2,n)

MyArray(0, n) is the task id
MyArray(1, n) is the date
MyArray(2, n) is the count

where n is the row number.

KeithRoberts
11-05-2008, 03:31 PM
I wish that it were so. I am opening a MS Project file and grabbing flagged tasks to process. For example, in the Project file, there are 3 tasks that have been flagged to process. When the VBA macro runs, it pops up a window that asks for how many iterations to be performed on the selected tasks. I will select 6. This means that there will be six dates generated per task. I need to be able to count the number of occurrences of the date per tasks.

For example:
Tasks 215, 216, and 217 are flagged for processing (task.flag10 - True)
The user selects 5 iterations. The data that is created is:

Task ID Date
215 11/01/08
215 11/01/08
215 11/02/08
215 11/03/08
215 11/01/08
216 11/06/08
216 11/06/08
216 11/07/08
216 11/07/08
216 11/07/08
217 11/12/08
217 11/13/08
217 11/13/08
217 11/14/08
217 11/15/08

What I am trying to achieve is an array that will contain:
215 11/01/08 3
215 11/02/08 1
215 11/03/08 1
216 11/06/08 2
216 11/07/08 3
217 11/12/08 1
217 11/13/08 2
217 11/14/08 1
217 11/15/08 1

This cannot be based on row numbers as there is not a corresponding row number. In addition, the tasks do not have to be sequential. I could have task id 215, 300, 1015, and 2009. Another factor that since the dates are randomly created in a range from the optimistic date to the pessimistic date, there is no telling what dates will be created, only that a certain number will be created for each task.

The end result will be to create a chart for each task, displaying the dates and the percentages that the dates occurred.

I have been able to write the raw data to Excel, but it is the counting by task and date that I am having trouble with.

Bob Phillips
11-05-2008, 04:46 PM
Be smart!. Drop the data into two columns on a worksheet, pivot the data, and create the array from the pivot. 5 minutes, job done!