Geek asks and answers

VBA adds an array at runtime

I am trying to create an int array at runtime from the results of a recordset.

Do While Not rstSearchResult.EOF

 If rstSearchResult(ID) = blah Then 
  'Add this Id rstSearchResult(ID) to Array
 End If

 Call rstSearchResult.MoveNext()
Loop

I need the same result that will give me Array(35588, 35589, 35595)

+3
source share
3 answers
Dim myIntArray() as Integer
Dim intDimension as Integer

intDimension = 0

Do While Not rstSearchResult.EOF

 If rstSearchResult(ID) = blah Then   
  'Add this Id rstSearchResult(ID) to Array
  REDIM PRESERVE myIntArray(intDimension)
  myIntArray(intDimension) = rstSearchResult(ID)
  intDimension = intDimension +1
 End If

 Call rstSearchResult.MoveNext()
Loop
+7
source

When I need to copy from a recordset to an array, it is a little more efficient in order to have the ReDim array up to the size you want before, instead of ReDiming inside the loop.

Dim myIntArray() as Integer
Dim intDimension as Integer

rstSearchResult.Filter = "ID = " & blah
ReDim myIntArray(rstSearchResult.RecordCount - 1)

intDimension = 0
Do Until rstSearchResult.EOF
    myIntArray(intDimension) = rstSearchResult!ID
    intDimension = intDimension + 1
    rstSearchResult.MoveNext
Loop

Please note that for RecordCount to work, you need to open the recordset as static.

rstSearchResult.Open "tblSearchResult", cnn, adOpenStatic
+6
source

When I do VBA in excel, I have a class that I use to access the database, I have a function that returns a set of records in an array. Hope the below helps.

Public Function RSToArray(ByVal oRS, Optional ByVal iRows, Optional ByVal iStart, Optional ByVal aFieldsArray)
    If iRows = 0 Then iRows = adGetRowsRest
    If iStart = 0 Then iStart = adBookmarkfirst

    RSToArray = ""  ' return a string so user can check For (IsArray)

    If IsObject(oRS) And oRS.State = adStateOpen Then
        If Not oRS.BOF And Not oRS.EOF Then
            If IsArray(aFieldsArray) Then
                RSToArray = oRS.GetRows(iRows, iStart, aFieldsArray)
            Else
                If iRows <> adGetRowsRest Or iStart <> adBookmarkfirst Then
                    RSToArray = oRS.GetRows(iRows, iStart)
                Else
                    RSToArray = oRS.GetRows()
                End If
            End If
        End If
    End If
End Function
+1
source

More articles:


All Articles