Microsoft KB Archive/210442

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

Article ID: 210442

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210442



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



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 </li>  To test this function, type the following line in the Immediate window, and then press ENTER: ? FillIndefArray Note that you receive the following results: <pre class="fixed_text">  Davolio Fuller Leverling Peacock Buchanan Suyama King Callahan Dodsworth </li></ol>

Keywords: kbhowto kbprogramming KB210442

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.