Dynamic 2 Dimensional Array

[mo2783]

I am trying to use a dynamic 2 dimensional Array to hold the following information


Model - X250 X350 X150
Cell Ref - B2 C3 F3

The size of the array varies dependent upon the models so the array can be 2x2 or 1x2 or even 4x2.


I can hold one item in the array, but I need to hold all the items in the array and be able to change “cell Ref” any time within the array.

I have the following code but its not achieving the above effect

                Dim vMatrix() As Variant

                Dim intRow As Integer
                Dim intCol As Integer
                Dim sMsg As String
                
                For intCol = 0 To 0
                    For intRow = 0 To 1
                         ReDim Preserve vMatrix(intCol, intRow) As Variant
                         vMatrix(intCol, intRow) = strWrkSheetName
                    Next intRow
                    ReDim Preserve vMatrix(intCol, intRow) As Variant
                    vMatrix(intCol, intRow) = "B2"
                Next intCol
                
                
                For intCol = 0 To UBound(vMatrix, 1)
                    For intRow = 0 To UBound(vMatrix, 2)
                        sMsg = sMsg & vMatrix(intCol, intRow) & vbTab
                    Next intRow
                    sMsg = sMsg & vbCrLf
                Next intCol
                
                MsgBox sMsg

 

[EBGreen]

Redim can only redimension the last dimension of a multiple dimension array. I would suggest a more robust data structure such as a dictionary.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[mo2783]

Never coded a duictionary dad structure, help would be much appreciated. thanks

 

[JerryKlmns]

A disconnected recordset could be an alternative.

 

[CautionMP]

mo2783,
It sounds like a Key/Value pairing, a collection might also be an option.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[EBGreen]

Here is an example of the dictionary and the ADO method. I'm not going to show a collection method because a collection of Name/Value pairs is a dictionary.

Sub Test()

Debug.Print "Dictionary:"
'Using dictionary
'Add a reference to the scripting runtime
Dim dicTest

Set dicTest = New Scripting.Dictionary
dicTest.Add "X250", "B2"
dicTest.Add "X350", "C3"
dicTest.Add "X150", "F3"

For Each strModel In dicTest.Keys
Debug.Print "For model " & strModel & " the cell is " & dicTest(strModel)
Next

Debug.Print ""
Debug.Print "ADO:"
'Using ADO
'Add a reference to MS ActiveX Data Objects

Dim oRS As ADODB.Recordset

Set oRS = New ADODB.Recordset
oRS.Fields.Append "Model", adVariant 'Use a more specific type here
oRS.Fields.Append "Cell", adVariant 'Use a more specific type here
oRS.Open

oRS.AddNew Array("Model", "Cell"), Array("X250", "B2")
oRS.AddNew Array("Model", "Cell"), Array("X350", "C3")
oRS.AddNew Array("Model", "Cell"), Array("X150", "F3")

oRS.MoveFirst
While Not oRS.EOF
Debug.Print "For model " & oRS("Model") & " the cell is " & oRS("Cell")
oRS.MoveNext
Wend

End Sub

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[mo2783]

Thanks for that EBGreen, much appreciated. Infact i used the dictionary object and found it to be much faster to access the cell ref.

 

[EBGreen]

Ya I meant to point out that if I were doing it I would just store the actual cell object rather than a string.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]