PDA

View Full Version : Populating 3 Dimension Array



jamescol
07-13-2004, 10:17 AM
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

Tommy
07-13-2004, 10:37 AM
This is quick and dirty :)


Dim RowBuf As Variant
Dim DisRS As rdoResultset
Set DisRS = cn.OpenResultset(SQL, rdOpenForwardOnly)
RowBuf = DisRS.GetRows(50)

Jacob Hilderbrand
07-14-2004, 02:12 AM
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.

XL-Dennis
07-14-2004, 02:37 AM
Hi Jacob,

Good to see that You challenge arrays :yes

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 :hi:

Thank you for reading my post on this subject,
Dennis