I'm attempting to rediminsion an array in MS Access VBA. What is the most efficient way to do this?
+5
A:
How about...
This will preserve data already in MyArray
Redim Preserve MyArray(15)
This will erase any previous data existing in MyArray
Redim MyArray(15)
Curtis Inderwiesche
2009-05-20 23:19:03
+6
A:
The most efficient way to redimension an array is to limit the number of times you resize that array. Every time you resize the array VB will take the whole array and copy it, wasting time and memory.
If you don't know the size of your array at development time you should make the best guess to the maximum size of the array, then fill the array. Once finished filling the array you can resize it down to the correct size.
In loops it's often best to make this guess by doubling the size of the current array once you run out of space. You can see this in action below with RedimTestA() resizing the array each iteration (1,000,000 times) and RedimTestB() only resizing it occasionally (22 times).
On my laptop RedimTestA() takes 3.93 seconds and RedimTestB() takes 0.41 seconds.
Option Explicit
Sub RedimTest()
Dim tA, tB As Single
tA = RedimTestA(1000000)
tB = RedimTestB(1000000)
MsgBox "Test A takes : " & tA & ", and Test B takes : " & tB
End Sub
Function RedimTestA(iterations As Long) As Single
Dim t As Single
Dim i As Long
Dim aryString() As String
Dim myString As String
t = Timer
Do While i <= iterations
ReDim Preserve aryString(i) As String
aryString(i) = "ABCEFG123"
i = i + 1
Loop
RedimTestA = Timer - t
End Function
Function RedimTestB(iterations As Long) As Single
Dim t As Single
Dim i As Long
Dim aryString() As String
Dim myString As String
t = Timer
ReDim aryString(0) As String
Do While i <= iterations
If i >= UBound(aryString) Then
ReDim Preserve aryString(i * 2) As String
End If
aryString(i) = "ABCEFG123"
i = i + 1
Loop
ReDim Preserve aryString(i - 1) As String ' i - 1 becuase of the final i = i + 1
RedimTestB = Timer - t
End Function
Mark Nold
2009-05-21 00:04:25
Oh, come on -- unless you're ReDimming in a loop with 1000s of iterations, this isn't really going to be a realistic problem. I have plenty of code out there that ReDims arrays all the time and there are neither performance nor memory issues. On the other hand, I don't use arrays for storing 1000s of values (that's what tables and recordsets are for).
David-W-Fenton
2009-05-21 21:07:53
Completely agree @David. This is more efficient at run time, but would i use it? Rarely, probably only if i was writing some sort of magic array handling class.
Mark Nold
2009-05-22 02:23:56
The answer to the implied question if often to use a recordset cursor type that supports the RecordCount property (i.e. the number of rows returned) so you can dimension the array in one hit. If the recordset is ADO, you can use its GetRows method to return an array of contents without getting involved with ReDim at all :)
onedaywhen
2009-05-22 07:50:35
@David, like all programming "rules" "Thou shalt not redim". Is just a rule of thumb. It's important to know what your actually causing to happen and apply common sense. As you say, in a lot of cases there is little to no noticeable performance hit. That said, the "Redim Preserve" statement can be one of the worst performance killers you will encounter if you use it naively. I think a word of caution is order when discussing it. I mean if you google "redim preserve" the first hit you get is: "Hey watch out for this."
Oorang
2009-05-27 10:02:00
+1
A:
Also note that you can only redim the right most dimension of a multi dimensional array.
Tony Toews
2009-05-21 09:26:05
That is an important subtle note to take into consideration.
Curtis Inderwiesche
2009-05-21 20:28:15