Is there a way to prevent extra elements in VBA dynamic arrays?

Question

As the title states, is there a way to prevent extra elements from showing up in VBA dynamic arrays when they are non-zero based?

For example, when using code similar to the following:

While Cells(ndx, 1).Value <> vbNullString
    ReDim Preserve data(1 To (UBound(data) + 1))
    ndx = ndx + 1
Wend

You have an extra empty array element at the end of processing. While this can be eliminated with the following:

ReDim Preserve data(1 To (UBound(data) - 1))

This doesn't seem like the best way of resolving this problem.

As such, is there a way to prevent that extra element from being created in the first place? Preferably something that doesn't require additional logic inside of the loop.

Answer 1

Visual Basic arrays are zero-based. This can be changed with the Option Base statement, though.

With your arrays, the extra elements are because you do a UBound() + 1, UBound will give you the correct number already. If the array has 5 Elements, UBound will be 5. But the last index will be 4, so ReDim to UBound will give you an array sized +1.

As arrays are a pain to use anyway (in VBA, that is) and ReDim Preserve acually is an array copy operation to a new fixed size array, I would recommend you to use Collections wherever you can. They are a lot easier to iterate (For Each ... In ...) and much more efficient at adding, finding and removing elements.

' creation ' 
Dim anyValue as Variant
Dim c as New Collection

' and adding values '
c.Add anyValue, strKey

' iteration '
For Each anyValue in c
  Debug.Print anyValue
Next c

' count values '
Debug.Print c.Count

' element deletion '
c.Delete strKey

(You can use Scripting.Dictionary from VBScript for added comfort, but you need to reference that first.)

You can also have multiple dimensions of Collections by simply placing them inside each other.

Answer 2

ReDim Preserve is a relative expensive operation and probably not something to do on every iteration. Better to ReDim the array to an upper bounds larger than you need (perhaps in chunks) then reducing the array to the required upper bounds when you are done.

Also, you may want to investigate other ways of reading an Excel Range into an array e.g.

  Dim a()
  With Sheet1
    a = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value
  End With
  Debug.Print a(1, 1)

Looping is often very slow :)

Answer 3

VB6 and COM use a mixture of 0- and 1-based indexing (arrays are 0-based except when you change this with Option Base or declare them otherwise explicitly).

COM collections are usually 1-based in earlier COM object models, but sometimes 0-based...

Dim/Redim data(1 To N) is an array of N elements indexed from 1 to N

Dim/Redim data(0 to N-1) is an array of N elements indexed from 0 to N-1

Dim/Redim data(N) is an array of N+1 elements indexed from 0 to N (if Option Base is 0)

The last case is the one that sometimes confuses, data(N) usually means data(0 To N) which is an array of N+1 elements.

Personally I always declare arrays explicitly as (0 To N-1) and don't rely on Option Base, which is more familiar for developers who use more than one language.

There is one edge case: VBA does not support zero-length arrays, you must always have at least one element (for each dimension in multidimensional arrays). So the smallest array you can declare is data(0 To 0) or data(1 To 1) with one element.

In your case, I suspect you are creating an array with one element, then adding an element each time through the loop:

ReDim data(1 To 1)    
While Cells(ndx, 1).Value <> vbNullString    
    ReDim Preserve data(1 To (UBound(data) + 1))    
    ndx = ndx + 1
Wend

Instead (and leaving aside for the moment considerations of the efficiency of calling ReDim Preserve in a loop), you should be using:

ReDim data(1 To 1)    
nStartIndex = ndx
While Cells(ndx, 1).Value <> vbNullString    
    ' On the first iteration this does nothing because
    ' the array already has one element
    ReDim Preserve data(1 To ndx - nStartIndex + 1)    
    ndx = ndx + 1
Wend

Answer 4

Granted, its been a while since I've done classic VB6, and my VBA experience is even rustier... if memeory serves, the array syntax is different for VB in more than just the base being 1 instead of 0. The syntax also says that the "size" that you specify doesn't denote the total number of elements in the array, but rather the last index to be addressable.

Answer 5

So this has turned out to be an annoying little problem as it looks like there really isn't a way to prevent the issue from coming up. Based upon the answers provided by the other users, I tired the following approaches to solving the problem.

Using a Collection - While this approach works quite will in situations where you need to read and store data, you can't use user-defined types with a Collection. Being able to define the item key is useful as you can use it to cross-reference two Collections; however, in VBA there is no way to get the list of keys in the Collection which can be limiting.

Reading an Excel Range into an Array - Another extremely nice approach, but it seems to work best when you know what the ranges are going to be ahead of time. If you have to figure out the ranges on the fly then you might find yourself in a situation where using a Collection or a smaller ReDim array easier to work with.

Building the Array on the fly with ReDim Preserve - While this can be a fairly straightforward operation, there are two issues involved with it. One is that ReDim can be an expensive operation as Visual Basic actually creates a new array with the given size, copies the old array, and then deletes the old array (or releases it for the Garbage Collector in Visual Basic .NET). So you would want to minimize the calls to ReDim if you are going to be working with extremely large arrays.

Additionally, you are likely going to run into a situation similar to the one in the question where you have an extra element at the start of the array or at the end of the array that is empty. The only way around this seems to be to either check to see if you need to resize before doing the operation, or to delete the empty element before returning the results.