Consulting

Results 1 to 4 of 4

Thread: Populating 3 Dimension Array

  1. #1
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location

    Question Populating 3 Dimension Array

    My brain is toast today. I used to know how to do this. I have a recordset with 3 fields, and need to store them in an array. If I have, say 10 records, how do I declare the array and populate it with the field's data?

    Thanks,
    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  2. #2
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    This is quick and dirty


    Dim RowBuf As Variant
    Dim DisRS As rdoResultset 
    Set DisRS = cn.OpenResultset(SQL, rdOpenForwardOnly)
    RowBuf = DisRS.GetRows(50)
    Last edited by Aussiebear; 04-29-2023 at 10:05 PM. Reason: Adjusted the code tags

  3. #3
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Here is something I threw together, there is probably a better way.

    Option Base 1
    Option Explicit
    Sub Test()
    Dim x As Long
    Dim y As Long
    Dim z As Long
    Dim MyArrays(10, 10, 10) As String
    Dim MyCol As Long
    Dim MyRow As Long
    MyCol = 1
    MyRow = 1
    For x = 1 To 10
        For y = 1 To 10
            For z = 1 To 10
                MyArrays(x, y, z) = Cells(MyRow, MyCol).Text
                If MyCol = 3 Then
                   MyCol = 1
                Else
                   MyCol = MyCol + 1
                End If
                If MyRow = Range("A65536").End(xlUp).Row Then
                    GoTo z:
                Else
                    MyRow = MyRow + 1
                End If
           Next z
       Next y
    Next x
    z:
    End Sub
    If you had data in A1:C something then this would fill A1:C1. then A2:C2 etc. into the array.

    But this data is hard to visualize (for me at least). If I had three record sets, I would use 3 seperate Arrays to fill the data.
    Last edited by XL-Dennis; 07-14-2004 at 02:16 AM. Reason: Added End Sub

  4. #4
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Hi Jacob,

    Good to see that You challenge arrays

    If we don't insist to use an array of a specific datatype then I would prefer to use the following approach:

    Option Explicit
    Sub Variant_Array()
    Dim wsSheet As Worksheet
    Dim rnData As Range
    Dim vaData As Variant
    Dim i As Long, j As Long, k As Long
    Set wsSheet = ActiveSheet
    With wsSheet
        Set rnData = .Range(.Range("A1"), .Range("C65536").End(xlUp))
    End With
    'Read all data into the array.
    vaData = rnData.Value
    'Variant-arrays that reads data from a range is always 1-based.
    Debug.Print LBound(vaData)
    Debug.Print UBound(vaData)
    'Here we make a dummy addition just to loop through the array:
    For j = 1 To 3
        For i = 1 To UBound(vaData)
            vaData(i, j) = vaData(i, j) * 2
        Next i
    Next j
    'Read the revised data back to the range.
    rnData.Value = vaData
    End Sub

    Hi Tommy,

    Long time since I saw a RDO-approach as most people nowdays prefer ADO

    Thank you for reading my post on this subject,
    Dennis
    Last edited by Aussiebear; 04-29-2023 at 10:09 PM. Reason: Adjusted the code tags
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •