VBA adding to array at runtime

Question

I am trying to create an int array at runtime from results of a record set.

Do While Not rstSearchResult.EOF

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

 Call rstSearchResult.MoveNext()
Loop

What I need is that same result as this will give me Array(35588, 35589, 35595)

I don't know how many numbers will match the condition so how would you add to an array with no upper bound. Thats my question — shad
– shad, Commented May 25, 2011 at 14:40

ray · Accepted Answer · 2011-05-25 14:43:07Z

8

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

answered May 25, 2011 at 14:43

ray

8,7478 gold badges46 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C. Ross Over a year ago

This should work, just note that as the array gets large, it turns into a huge memory hog.

Tmdean Over a year ago

The problem isn't memory hogging, anyway. The issue is that this is now a O(n^2) algorithm because you're copying the array into a new array at each iteration.

Tmdean · Accepted Answer · 2011-05-25 17:44:10Z

6

When I need to copy from a Recordset to an array, it's a little bit more efficient to ReDim the array to the size you want beforehand instead of ReDiming it 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

Note that for RecordCount to work you need to open the recordset as static.

rstSearchResult.Open "tblSearchResult", cnn, adOpenStatic

edited May 25, 2011 at 17:44

answered May 25, 2011 at 17:37

Tmdean

9,31945 silver badges51 bronze badges

1 Comment

shad Over a year ago

It makes sense to ReDim outside of the loop. So I ReDim once before the loop to the size of the record set, and again at the end to resize the array to the amount of result matched. Thanks Tmdean

Harag · Accepted Answer · 2011-05-25 14:24:13Z

When I do VBA in excel I have a class I use for the DB accessing in it I have a function that returns the recordset to 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

Collectives™ on Stack Overflow

VBA adding to array at runtime

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related