Varying number of records in multi dimentional array

[svjensen]

First post so please bear with me

My question is related to multi dimentional arrays.

I have a dataset which contains a number of projects. For each project I have a number of data including an account which the project belongs to.

I need to display a list of the projects grouped by account along the lines of:

Account 1
- Project 1 (with certain relevant project data, like starting/ending year, financing, ...)
- Project 5 (with certain relevant project data, like starting/ending year, financing, ...)
- Project 6 (with certain relevant project data, like starting/ending year, financing, ...)

Account 2
- Project 2 (with certain relevant project data, like starting/ending year, financing, ...)
- Project 7 (with certain relevant project data, like starting/ending year, financing, ...)

Account 3
- Project 3 (with certain relevant project data, like starting/ending year, financing, ...)
- Project 4 (with certain relevant project data, like starting/ending year, financing, ...)


I was thinking of loading it all into an array with account info in the first dimension, project number in the next, and other relevant data in the third).
My first question is: Is that the best way to go about it?
And my second question would then be: how could I handle varying number of records in dimension 2?

/Soren

 

[pgc01]

Hi Soren
Welcome to the board

My first question is: Is that the best way to go about it?

I don't have enough information about your problem but to use a spreadsheet to manage projects would be my third choice.

Option 1 - Project management tool
You should use a project tool. It will make it easier to manage your projects as all the usual needs are already available.

Option 2 - Database
If you have the office, try msAccess. Ex. what you need in this case would probably be a simple report without any need of vba

Option 3 - Spreadsheet

If you really need to use excel, try to structure your data in tables, like a database. Excel may be queried with sql using ado, like a database, which may make your life easier in some cases.

Another thing you may want to explore for a question like the one you post is pivot tables. The layout you want is very similar from the one you get from a pivot table.

And my second question would then be: how could I handle varying number of records in dimension 2?

You have to create a jagged array instead of a rectangular array. In a jagged array each element is itself an array.

This is an example of a jagged array where the first element of dimension 1 is an array with 3 elements, and the second element of dimension 1 is an array with 2 elements (like the accounts 1 and 2 in your post)

Try:

Sub JaggedArray()
Dim arr As Variant, v As Variant
Dim i As Long, j As Long, s As String

ReDim arr(1 To 2)  ' first dimension

ReDim v(1 To 3)  ' first element of dimension 1 is an array with 3 elements
arr(1) = v

ReDim v(1 To 2)   ' second element of dimension 1 is an array with 2 elements
arr(2) = v

' initialize the array
arr(1)(1) = 1
arr(1)(2) = 2
arr(1)(3) = 3
arr(2)(1) = 4
arr(2)(2) = 5

For i = LBound(arr) To UBound(arr)
    For j = LBound(arr(i)) To UBound(arr(i))
        s = s & "arr(" & i & ")(" & j & ") = " & arr(i)(j) & vbNewLine
    Next j
Next i

MsgBox s
End Sub

 

[svjensen]

Thank you for your reply.

I agree with your remarks about project management/database tools, but in the specific case I am "stuck" with excel.

The jagged array seems to be just the fix I was looking for. I will try it out and get back if (when) it generates follow-on questions .

/Soren