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

SUMMARY

This article shows you two Visual Basic for Applications methods to fill an array. The first method fills an array when you know the number of elements in the array. The second method fills an array when you do not know the number of elements in the array.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

MORE INFORMATION

This example demonstrates how to fill a one-dimensional array when you know the number of elements.

Create a module and type the following line in the Declarations section if it is not already there:
```
Option Explicit
					
```

Type the following procedure:

 
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

To test this function, type the following line in the Immediate window, and then press ENTER:

? FillOneDimArray()

Note that you receive the following results;

   Davolio
   Fuller
   Leverling
   Peacock
   Buchanan
   Suyama
   King
   Callahan
   Dodsworth

The next example demonstrates how to fill an array when you do not know the number of elements. It uses the Preserve argument of the ReDim statement to adjust the size of the array without destroying the array's contents. Realize that using Preserve can cause your code to run more slowly; therefore, if possible, it is better to create an array with a known number of elements.

Create a module and type the following line in the Declarations section if it is not already there:
```
Option Explicit
					
```

Type the following procedure:

 
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

To test this function, type the following line in the Immediate window, and then press ENTER:

? FillIndefArray()

Note that you receive the following results:

   Davolio
   Fuller
   Leverling
   Peacock
   Buchanan
   Suyama
   King
   Callahan
   Dodsworth