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.
|
1 2 |
Dim Array1(1 To 10) As String 'static array Dim Array2() As String 'dynamic array |
Dynamic arrays can be changed using the Redim statement.
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |