Solved: 2D Arrays

[gscarter]

Hi,

I have an array of customers called customer which are all unique, i also have a list of customers on a spreadsheet (column A for example), with a list of program versions next to them (column B).

I wish to iterate through each customer in the array, each time iterating through the customers on the spreadsheet, if there is a match i wish to change the array 'customer' to a 2d array, so the program version is stored next to the customer. One customer can be running more than one version.

e.g

customer(0) = Gary
customer(0)(0) = V1
customer(0)(1) = V2

This is how im trying to go about it at the moment.



Function uniqueCustomer(column As String) As String()

Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim customer() As String
Dim currentCustomer As String
Dim nextCustomer As String
K = 1
J = 0
L = 0

If column = "A" Then
nextColumn = "B"
Else
nextColumn = "E"
End If

For Each cust In customer
M = 0
Do
currentCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(K))).Value
nextCustomer = ThisWorkbook.Worksheets("Sheet1").Range(column & Trim(CStr(K + 1))).Value
customerVersion = ThisWorkbook.Worksheets("Sheet1").Range(nextColumn & Trim(CStr(K))).Value
If cusomter = currentCustomer Then
ReDim customer(UBound(customer),M)
customer(L)(M) = CStr(customerVersion)
M = M + 1
End If
K = K + 1
Loop While nextCustomer <> ""
L = L + 1
Next cust

uniqueCustomer = customer
End Function

I have copied the parts of the function that apply to the question, if i have left something out please let me know.

I don't think i am reDim'ing the customer() array correctly and frankly its really confused me, i may be going about it the totally wrong way.

Can anyone help me?

Gary

[Bob Phillips]

You cannot have an array that is one-dimensional in parts and two dimensional in other parts.

If the number of versions per customer is variable, you have two choices that I can see, either repeat the customer for each version, like so

customer(0,0) = Gary
customer(0,1) = V1
customer(1,0) = Gary
customer(0,1) = V2

or have the 2-d array with 2 elements in the second dimension, where the second 2nd dimensional element is another array (this has the benefit of flexing accodring to the number of versions, like so

customer(0,0) = Gary
customer(0,1)(0) = V1
customer(0,1)(1) = V2
customer(1,0) = Bob
customer(1,1)(0) = V1
customer(1,1)(1) = V2
customer(1,1)(2) = V3
customer(1,1)(3) = V4

The former is very easy to work with, but you may have lots of sparseness, the latter takes getting your head around but is more flexible (I am using that word a lot today re arrays). For instance, here is a simple example that loads such an array and prints it out, to show you how to access it



Dim customer(0 To 1, 0 To 1)
Dim ary, i, j

customer(0, 0) = "Gary"
customer(0, 1) = Split("V1,V2", ",")
customer(1, 0) = "Bob"
customer(1, 1) = Split("V1,V2,V3,V4", ",")

For i = 0 To 1

Debug.Print "customer(" & i & ",0) = " & customer(i, 0)
For j = LBound(customer(i, 1)) To UBound(customer(i, 1))

Debug.Print "customer(" & i & ",1)(" & j & ") = " & customer(i, 1)(j)
Next j
Next i

[gscarter]

Thanks a lot for the information!, its given me something to think about, you've really helped me out today :).

Thanks
Gary

[Bob Phillips]

BTW, do you understand what I do with SPlit, it is just a simple convenient way to load a dynamic array.

[gscarter]

If the array customer is originally set to a 1d array, can it then be made a 2d in another function? or will this not work?

Gary

[gscarter]

Yeah that split function is fine :)

[Bob Phillips]

If the array customer is originally set to a 1d array, can it then be made a 2d in another function? or will this not work?

Gary

No, you can't do that, it would be nice but impractical, you have to setup all dimesnions initially, even if you only load one.

Note one other critical factor here. You can only Redim the uppermost dimension, so you cannot do



Redim ary(1 to 10, 1 to 2)


and then later do



Redim ary(1 To 20, 1 To 2)


For that reason I often dimension my arrays in what I consider an inverse dimensionality, just so that I can resize the rows, like so



Redim ary(1 to 2, 1 To 10)


as I know the 1 To 2 is fixed.

[gscarter]

Sorry, just trying to get my head around it.

Referring to your second way of doing it:



customer(0,0) = Gary
customer(0,1)(0) = V1
customer(0,1)(1) = V2
customer(1,0) = Bob
customer(1,1)(0) = V1
customer(1,1)(1) = V2
customer(1,1)(2) = V3
customer(1,1)(3) = V4


Is there any reason why i cannot reference like so:

customer(0,0) = Gary
customer(0,1) = V1
customer(0,2) = V2
customer(1,0) = Bob
customer(1,1) = V1
customer(1,2) = V2
customer(1,3) = V3
customer(1,4) = V4

or am i missing something blatantly obvious, sorry if am.

Thanks
Gary

[Bob Phillips]

You could, but that is the first way, not the second. Here you would need to know what the largets possible number of versions you could get were, and set the second dimension to that number. This would mean that there was an element customer(0,3) and customer(0,4) and so on upto the upper bound of the second dimension. They would be empty, but they would be there, and this also means that you would have to iterate the second dimension looking for an empty element rather than using the array bounds as I showed earlier.

[gscarter]

Ah ok, sorry for the confusion.

Thanks for all your help today.

Gary

[Bob Phillips]

No problem, I like arrays, they are more interesting.