Topic: ReDim Arrays???

[Kheilmann]

I keep getting errors when I have the Preserve keyword, when I take it out, I don't get errors...
I need to Preserve the data in my arrays so, how do I preserve and make my array large at the same time.
The notes in Help say you can only change the last dimension when using Preserve, but I do not understand what that means...

My arrays are set as follows

Public roughPart() as string
Public roughPartQty() as integer
Public topoutPart() as string
Public topoutPartQty() as integer

Here is the section of code relevant to the above problem...

Code: [Select]

Else:
        Set blkPart = TempSet(X)
            If blkPart.Name = "Assembly" Then
                cntAssembly = cntAssembly + 1
                For Each attPart In blkPart.GetAttributes
                    Select Case attPart.TagString
                        Case "ASSEMBLY":
                            If attPart.TextString = "" Then
                                Exit For
                            Else:
                                Assembly = attPart.TextString
                            End If
                    End Select
                Next attPart
               
                'SQLstring = "Select PartID, QTY, AssemblyType From tblAssembly Where AssemblyName ='" & Assembly & "';"
                SQLstring = "Select tblAssembly.PartID, tblAssembly.Quantity, tblAssembly.AssemblyType, tblParts.PartName From tblAssembly Inner Join tblParts ON tblAssembly.PartID = tblParts.PartID Where tblAssembly.AssemblyName ='" & Assembly & "';"
                Set PlumbingREC = PlumbingDB.OpenRecordset(SQLstring, dbOpenDynaset)
               
                If PlumbingREC.RecordCount > 0 Then
                    If PlumbingREC.EOF And PlumbingREC.BOF Then
                        Exit Sub
                    Else:
                        PlumbingREC.MoveFirst
                        Y = 0
                        Do While Not PlumbingREC.EOF
                       
                            ReDim roughPart(Y, cntAssembly) As String
                            ReDim roughPartQty(Y, cntAssembly) As Integer
                           
                            If PlumbingREC.Fields("AssemblyType").value = "Rough" Then
                                roughPart(Y, cntAssembly) = PlumbingREC.Fields("PartName").value
                                roughPartQty(Y, cntAssembly) = PlumbingREC.Fields("Quantity").value
                                Y = Y + 1
                            End If
                           
                            PlumbingREC.MoveNext
                        Loop
                       
                        PlumbingREC.MoveFirst
                        Y = 0
                        Do While Not PlumbingREC.EOF
                       
                            ReDim topoutPart(Y, cntAssembly) As String
                            ReDim topoutPartQty(Y, cntAssembly) As Integer
                           
                            If PlumbingREC.Fields("AssemblyType").value = "TopOut" Then
                                topoutPart(Y, cntAssembly) = PlumbingREC.Fields("PartName").value
                                topoutPartQty(Y, cntAssembly) = PlumbingREC.Fields("Quantity").value
                                Y = Y + 1
                            End If
                           
                            PlumbingREC.MoveNext
                        Loop
                    End If
                End If
               
                PlumbingREC.Close
                Set PlumbingREC = Nothing

Summary of what I am trying to do:
I have 1 block, "Assembly" with 1 attribute "ASSEMBLY".  The value of that Attribute is an AssemblyName stored in an *.mdb file.  So my parts are stored in a DB instead of in multiple attributes in a block. 
One Assembly block may have an AssemblyName which references only one or two parts, however another may have 20 to 30 parts.  So When I create (redim) my array ...roughPart(X,Y) There may be only 2 rows of data in the first column, and then when the next Assembly block is Queried, and it has 30 parts there will be 30 rows in that column.  Is that a problem??

Thanks in Advance for your help...I hope this is clear enough, I'll post more detail if you need it...

Thanks

[David Hall]

for some reason, I'm thinking you can only redim a single dimension array.

[Chuck Gabriel]

The notes in Help say you can only change the last dimension when using Preserve, but I do not understand what that means...

It means that once you have have dimensioned the array to its initial size, like so:

ReDim roughPart(Y, cntAssembly) As String

in your loop, each subsequent iteration can only substitute a new value for cntAssembly.  The value of  Y cannot change.

One way you can accomplish your goal is to figure out the record counts up front so that the first dimension of each array can be established before you start looping.

[Kheilmann]

At this point I'm considering calling a procedure which will take the data found and export it immediately to an Excel Workbook.  Instead of quering all the data and compiling into hundreds of variables and then sending those variables to the spreadsheet.  I think this would eliminate the need for my arrays...

Would you agree?  Or I am missing something?

BTW..
When I read the Help again for ReDim..it made more sense..
You can only change the size of the last dimension, and you can not change the Number of dimensions at all.
So if you start with a single you can not change to a 2 dim array.  However, if you have one dim you can change the size of that dim any time, and if you have a multi dim array you can only change the size of the last dim (2nd for double, 3rd for 3 tier, etc.)
Which means if I want to use this method I will have to preset my first Dim (Rows) to a large enough # to hold all the possible parts, and then I can change the # of my 2nd dim (Columns) whenever a new Assembly is found.

I think exporting the data to excel immediately will be much easier than compiling the data into an Array first.  I will do that unless one of you has a reason why I should not.

Thanks again for your help...

[Kheilmann]

Thanks Chuck;

The problem I have with presetting it is 2 fold:
1.  When I use my SQLstring to query my DB the recordcount for PlumbingREC is always 1.  Is that because I am doing an Inner Join? 
2.  The other problem is that the first block queried may need the first Dim of my array to be 5 entries and the next block may need 30.  So it seems to me that I would have to do a recordcount of all my Assemblies then reloop through the Assemblies to apply the parts to the Array.

Am I right in thinking this?

[Dnereb]

I totally agree on exporting to Excell inmediatly as long as it not neccesary to rework your data (combined sorting and filtering stuff)

A workaround  for creating jagged arrays is using the collection type in an array like this:

Code: [Select]

Sub test()

Dim Cl() As New Collection

ReDim Preserve Cl(2)

Cl(0).Add ("YourStuff0.1")
Cl(0).Add ("YourStuff0.2")
Cl(0).Add ("YourStuff0.3")
Cl(0).Add ("YourStuff0.4")
Cl(1).Add ("YourStuff1.1")
Cl(1).Add ("YourStuff1.2")
Cl(1).Add ("YourStuff1.3")
Cl(1).Add ("YourStuff1.4")
Cl(2).Add ("YourStuff2.1")
ReDim Preserve Cl(3)
Cl(3).Add ("YourStuff3.1")
Cl(3).Add ("YourStuff3.2")
Cl(3).Add ("YourStuff3.3")
Cl(3).Add ("YourStuff3.4")

End SubYou can add in two dimensions this way. Drawback the collection object is slow in comparison with true arrays.

So if you don't need to keep the data in memory... don't do it.

[Dnereb]

Thanks Chuck;

The problem I have with presetting it is 2 fold:
1.  When I use my SQLstring to query my DB the recordcount for PlumbingREC is always 1.  Is that because I am doing an Inner Join? 

Did you move to the last record (.movelast) because the count will not show the actual size until all records are really loaden into the recordset

2.  The other problem is that the first block queried may need the first Dim of my array to be 5 entries and the next block may need 30.  So it seems to me that I would have to do a recordcount of all my Assemblies then reloop through the Assemblies to apply the parts to the Array.

Am I right in thinking this?

Maybe I'm mistaking what you want but If you know the number of records and the number of fields (Recordset.Fields.Count)you can redim an array just right.

On the other hand if you already the data in a recordset I see no need to transfer it in an array without any special reason.

[Fatty]

Kevin, try to use GetRows method:

Here is my program code snip:

Code: [Select]

Private Sub UserForm_Initialize()
    On Error GoTo ErrorHandler

    Dim Cnxn As ADODB.Connection
    Dim rstNew As ADODB.Recordset
    Dim strCnxn As String
    Dim strSQL As String
    Dim retArr As Variant
    Dim i As Long, j As Long
   
    ' Open connection
   
    strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=D:\AUTOLISP\LISPS\VBA\ACCESS\data.mdb;"
    Set Cnxn = New ADODB.Connection
    Cnxn.Open strCnxn
   
    ' Open table
   
    Set rstNew = New ADODB.Recordset
    strSQL = "prot_Viabilita" ' table name
    rstNew.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable
    [b]retArr = rstNew.GetRows[/b]
    Me.Caption = "Data from table " & Chr(34) & strSQL & Chr(34)
    '
    ' Transpose record data and populate list box
    '
    ReDim dataArr(UBound(retArr, 2), UBound(retArr, 1)) As String
    For i = 0 To UBound(retArr, 1)
    For j = 0 To UBound(retArr, 2)
    dataArr(j, i) = CStr(retArr(i, j))
    Next
    Next
    ListBox1.List() = dataArr

    ' clean up
    rstNew.Close
    Cnxn.Close
    Set rstNew = Nothing
    Set Cnxn = Nothing
    Exit Sub
   
ErrorHandler:
    ' clean up
    If Not rstNew Is Nothing Then
        If rstNew.State = adStateOpen Then rstNew.Close
    End If
    Set rstNew = Nothing
   
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
   
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub

>'J'<

[quicksilver]

Can you switch the y and cntassembly variables so y is the last one?

[Kheilmann]

Quicksilver,

Actually that would work.  Funny, it's such as easy answer.  Though I like using the export to Excel right away method, it has worked 10000% better than I ever imagined, so I will not be using the Array scenario anyway.

I appreciate everyones input and help.

Hope you all have a great weekend.

[quicksilver]

A day late and a penny short.   

[Keith™]

A trick I often use whenever I have a scenario where I would like to grow both dimensions of the array (if I actually did it that way) would be to create an array of arrays ... I know it sounds a bit crazy, but if you consider the way the arrays are handled, the concept is exactly the same except you have to modify the variable a little differently.

Consider the following code

Code: [Select]


Sub VarTest()

Dim Var1() As Variant
Dim VarA() As Variant
Dim VarB() As Variant
Dim X, Y As Integer

ReDim Preserve VarB(3)
For X = 0 To UBound(VarB)
    ReDim Preserve Var1(3)
    For Y = 0 To UBound(Var1)
        Var1(Y) = X & "," & Y
    Next Y
    VarB(X) = Var1
Next X
MsgBox GetMDimArray(VarB, 3, 2)
VarB = SetMDimArray(VarB, 3, 2, "This Is A Test")
MsgBox GetMDimArray(VarB, 3, 2)
End Sub

Function GetMDimArray(ByRef MyArray As Variant, X As Integer, Y As Integer) As Variant
  GetMDimArray = MyArray(X)(Y)
End Function

Function SetMDimArray(ByRef MArray As Variant, X As Integer, Y As Integer, NewValue As Variant) As Variant
  MArray(X)(Y) = NewValue
  SetMDimArray = MArray
End Function

As you can see, there is actually an array being placed in the main array, thus you can easily access the depth of the array by using multiple dimensions after the fact .i.e. MyArray(X)(Y). Conceivably, you should be able to add a third or fourth dimension making it MyArray(W)(X)(Y)(Z).

This picture should clarify the idea.