Excel VBA Arrays good practices: create a huge blank array at once or use redim preserve all the time?

user_10006506 0
Viewed 690

I like to use an array as if it was an "in RAM memory" spreadsheet, so the code is more clean and it runs faster.

So I would like to ask: generally, is it better start the code with a huge array, say Dim arr(10000), so I can work with it as if it was a blank sheet I fill when necessary, or is that a bad practice, and, instead, I should use Redim Preserve all the time I need to insert data into the array?

Thanks in advance for any help.

vba excel arrays
1 Answers

The answer to your question depends on how you intend to fill the array (noting that this is in the context of Excel). Here are four simple scenarios:

Filling from a Range. Simply Dim a Variant and let VBA do all the work.

Dim myArray as Variant
Dim myRange as Range

'Set myRange = something
myArray = myRange.Value '<-- Fills a 2-D array
For rowIterator = LBound(myArray,1) to UBound(myArray,1) '<- How identify the size of the first dimension
    For colIterator = LBound(myArray,2) to UBound(myArray,2) '<- How identify the size of the second dimension
        'Do something to myArray(rowIterator, colIterator)
    Next colIterator
Next rowIterator

Filling when you define your bounds. In some code, you can learn early in the code running what your bounds are going to be. In this case, you can ReDim before you get into a loop.

Dim myArray() As String
Dim someCounter As Long
'Some early code
someCounter = CLng(myRange.Value)
ReDim myArray(someCounter) '<-- Do this only once.
For iterator = LBound(myArray) to UBound(myArray)
    ' Do Something to myArray(iterator)
Next iterator

Filling within a loop. Avoid this when ever you can as ReDimming an array, especially with ReDim Preserve is computationally expensive. However, if you know it is a small loop, you might want to accept the cost.

An alternative to resizing in a loop is to use a Collection.

Oversizing to start with. Rather than continually re-dimensioning the array, you could oversize the array but you then have the additional management overhead of trying to manage the unused array slots. How do you know if the value in the array of "" or 0 is really "" or 0 instead of a default null value?

user_91019810
Related