Redim an Array
Array variables can be static or dynamic. That’s determined by the Dim statement. If you specify dimensions when you declare the variable, it’s static and always will be. If you leave the dimensions blank in the Dim statement, it’s dynamic and can be changed.
Dim Array1(1 To 10) As String ’static array
Dim Array2() As String ‘dynamic array
Dynamic arrays can be changed using the Redim statement.
Dim Arr1() As Double
ReDim Arr1(Selection.Columns.Count, Selection.Rows.Count)
If you use Redim, all the data in your array is lost, unless you use the Preserve keyword. This keeps the data in tact, but limits what you can change with a Redim. For instance, when you use Preserve, you can only change the last dimension of the array. Sometimes you have to organize your array horizontally to accomodate this restriction.
Dim Arr1() As Double
Dim cell As Range
Dim i As Long
For Each cell In Range(”A1:A100″).Cells
If cell.Value < 0.5 Then
i = i + 1
ReDim Preserve Arr1(1 To 2, 1 To i)
Arr1(1, i) = cell.Value
Arr1(2, i) = cell.Row
End If
Next cell
Preserve is an expensive keyword, so you use it sparingly. Many people will Redim their arrays in blocks to avoid having to do it in every iteration of a loop.
ReDim Preserve Arr1(1 To 2, 1 To 10)
For Each cell In Range(”A1:A100″).Cells
If cell.Value < 0.5 Then
i = i + 1
If i Mod 10 = 0 Then
ReDim Preserve Arr1(1 To 2, 1 To i + 10)
End If
Arr1(1, i) = cell.Value
Arr1(2, i) = cell.Row
End If
Next cell
I’m not a big fan of the block Redim, but if you have a really time intensive procedure and this shaves some valuable milliseconds, then go for it. If there’s a way to figure out the upper bounds of the array before you add data, then you may save time there also.
Dim SmallCells As Long
SmallCells = Application.Evaluate(”=sumproduct(–(a1:A100<.5))")
ReDim Arr1(1 To 2, 1 To SmallCells)
For Each cell In Range(”A1:A100″).Cells
If cell.Value < 0.5 Then
i = i + 1
Arr1(1, i) = cell.Value
Arr1(2, i) = cell.Row
End If
Next cell
Jonathan Rynd:
Other ways of doing this:
28 June 2004, 2:12 pm1. Use a collection. Easy to add elements, but it’s very slow. Still this is the best method if you are planning to switch to a language that has a vector type like C++.
2. Computer Science classes teach that the rule of thumb is you should double the array’s size each time you find you are out of space.
Jamie Collins:
Some important points from the VBA help:
If you use the Preserve keyword, you can resize only the last array dimension and you can’t change the number of dimensions at all.
Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound.
If you pass an array to a procedure by reference, you can’t redimension the array within the procedure.
Jamie.
–
29 June 2004, 4:50 amJay Duhon:
> If you pass an array to a procedure by reference, you can’t redimension the array within the procedure.
I’ve seen that in the help before, but I’ve never seen where it causes a problem.
I’ve always been able to do this:
Dim arrTest() As Long
ReDim arrTest(1)
MsgBox “UBound(arrTest) = ” & UBound(arrTest)
Procedure1 arrTest
MsgBox “UBound(arrTest) = ” & UBound(arrTest)
Sub Procedure1(ByRef arr() As Long)
ReDim arr(10)
MsgBox “UBound(arrTest) = ” & UBound(arr), , “Procedure1″
End Sub
Could someone please explain when this wouldn’t work? Or why the help file says that, even though it is apparently not true?
26 August 2004, 9:35 amOwen:
I regularly use sub procedures to resize arrays. In fact, if I couldn’t do that, I’d say 90% of my VBA code would fail to work.
If you are using arrays to create lists of data so you may paste the entire list into an Excel spreadsheet using a single paste operation, then it’s generally a matter of transposing the array, using ReDim Preserve, adding your extra information, and then transposing it back to the way it was.
4 July 2008, 12:04 am