VBA 在运行时添加到数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6125916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA adding to array at runtime
提问by shad
I am trying to create an int array at runtime from results of a record set.
我试图在运行时根据记录集的结果创建一个 int 数组。
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)
我需要的是同样的结果，因为这会给我 Array(35588, 35589, 35595)
回答by ray
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
回答by Tmdean
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.
当我需要从 Recordset 复制到数组时，预先将数组重新调整为您想要的大小，而不是在循环内重新调整数组的大小会更有效。
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.
请注意，要使 RecordCount 工作，您需要以静态方式打开记录集。
rstSearchResult.Open "tblSearchResult", cnn, adOpenStatic
回答by Harag
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.
当我在 excel 中执行 VBA 时，我有一个用于数据库访问的类，我有一个将记录集返回到数组的函数。希望以下有帮助。
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
