<p>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? </p>

<p>For example, when using code similar to the following:</p>

<pre><code>While Cells(ndx, 1).Value &lt;&gt; vbNullString
    ReDim Preserve data(1 To (UBound(data) + 1))
    ndx = ndx + 1
Wend
</code></pre>

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

<pre><code>ReDim Preserve data(1 To (UBound(data) - 1))
</code></pre>

<p>This doesn't seem like the best way of resolving this problem. </p>

<p>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.</p>

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.