ACC2000: How to Use Visual Basic for Applications to Fill an Array

Option Explicit
					

 
Function FillOneDimArray()
   Dim intCounter As Long
   Dim dbSample As DAO.Database
   Dim rstSample As DAO.Recordset
   Dim lngRecordCount As Long
   On Error GoTo ErrorHandler
   Set dbSample = CurrentDb()
   Set rstSample = dbSample.OpenRecordset("Employees")

   ' Get number of records.
   With rstSample
      .MoveLast
      lngRecordCount = .RecordCount
      ' Create the (zero-based) array.
      ' Address elements starting from row 0 rather than 1.
      ReDim AnArray(lngRecordCount - 1)
      ' Fill the array.
      .MoveFirst
      For intCounter = 0 To lngRecordCount - 1
         AnArray(intCounter) = ![LastName]
         .MoveNext
      Next intCounter
      ' View the array contents.
      For intCounter = 0 To lngRecordCount - 1
         Debug.Print AnArray(intCounter)
      Next intCounter
      .Close
   End With
   dbSample.Close
   Exit Function

ErrorHandler:
   MsgBox Error
   Exit Function
End Function

					

? FillOneDimArray()
						

Option Explicit
					

 
Function FillIndefArray()
   Dim dbSample As DAO.Database
   Dim rstSample As DAO.Recordset
   Dim intArrayCount As Integer
   Dim aryTestArray() As Variant
   Dim intCounter As Long
   
   Set dbSample = CurrentDb()
   Set rstSample = dbSample.OpenRecordset("Employees")
   intArrayCount = 0
   ReDim Preserve aryTestArray(0)
   
   ' Fill the array.
   With rstSample
      .MoveFirst
      Do Until rstSample.EOF
         ' Fill the array row with the last name.
         aryTestArray(intArrayCount) = ![LastName]
         ' Increase the number of elements in the array
         ' by one to accommodate the next record.
         ReDim Preserve aryTestArray(UBound(aryTestArray) + 1)
         intArrayCount = intArrayCount + 1
         .MoveNext
      Loop
   ' Remove the remaining empty array row.
   ReDim Preserve aryTestArray(UBound(aryTestArray) - 1)
   .Close
   End With
   dbSample.Close
   
   ' View the array contents.
   For intCounter = 0 To intArrayCount - 1
       Debug.Print aryTestArray(intCounter)
   Next intCounter
End Function
					

? FillIndefArray()