VBA Testing Dynamic Array Initialisation

This is a tiny snippet but it took me a little while to find so I thought I'd write it down. Dynamic arrays are great in vba if you need to track a quick collection of data, they have most of the advantages of static arrays but you can change the size of the array dynamically based on other things, like the number of files in a folder for example. Awesome!

Basic Use
 To declare an array of type string you'll declare it likje this:

Dim MyArr() as string

And to re-dimension the array you'll use the following, the word Preserve means that you want to keep the contents of the array intact, otherwise you'll empty the array as you redimension it:

Redim Preserve MyArr(10)

We've just set this array to hold 10 elements and that's great but you may well not know the size of a list that you want to put into an array and so might use a loop like this to increment your array's size each time you hit a new loop:

Dim MyArr() as string
Dim i as integer
For i = 0 to SourceList.count - 1
    Redim Preserve MyArr(Ubound(MyArr) + 1)
    MyArr(UBound(MyArr)) = SourceList(i)
Next

So here we're looping through a sourcelist and adding each element to the MyArr array by using the Ubound() function which returns the highest element position in the array - All good! except this will error on the first go becase an upper bound has not been set yet on the array - without usinig ubound there aren't all that many ways to test as to whether an array has been initialised or not, except...
 

Dim MyArr() as string
Dim i as integer
For i = 0 to SourceList.count - 1    

    If (Not Not MyArr) <> 0 Then
        ' We're initialised, use the standard redim
        ReDim Preserve MyArr(UBound(MyArr) + 1)
    Else
        ' Here we go, we have an empty array, just set it to one element for now
        ReDim Preserve MyArr(0)
    End If
    MyArr(UBound(MyArr)) = SourceList(i)
Next

This should get you through the vast majority of dynamic array initialisation woes, although if you do end up using a few functions like Split() to create your array you'll have to go a little deeper. Chip Pearson has some good advice here: http://www.cpearson.com/excel/IsArrayAllocated.aspx 

Update
 So after having used the above solution for a bit I started hitting some odd and uncomfortable errors, whilst I'd like to think that my code is pretty good, I'm quite certain that I shouldn't be running across errors like this:

I'm not completely sure what was causing this in the code that I was running and your code may not hit this little hiccup but the way that I've fixed it is to go back to Chip and the above link. On checking the function that he proposes doesn't make use of the fact that (Not MyArr) returns the pointer to the SafeArray object used by vba, and so it was an excellent candidate for me to try. On adding his function to the project that was causing me issues in space of using the not not approach, everything worked perfectly! this function (as Chip explains) also insulates against the troublesome string.Split() functions which can half initialise an array. So here's his function and thanks again Chip!:

Function IsArrayAllocated(Arr As Variant) As Boolean
        On Error Resume Next
        IsArrayAllocated = IsArray(Arr) And _
                           Not IsError(LBound(Arr, 1)) And _
                           LBound(Arr, 1) <= UBound(Arr, 1)
End Function

To be honest if you've come this far then you might as well finish the job and write a sub to increment your array for you, otherwise you'd be writing the above a hundred times which breaks the DRY principle hard! here's both:

Sub IncrementArray(ByRef Arr As Variant)
    If Not IsArray(Arr) Then Exit Sub
    If IsArrayAllocated(Arr) Then
        ReDim Preserve Arr(UBound(Arr) + 1)
    Else
        ReDim Preserve Arr(0)
    End If
End Sub

Function IsArrayAllocated(Arr As Variant) As Boolean
        On Error Resume Next
        IsArrayAllocated = IsArray(Arr) And _
                           Not IsError(LBound(Arr, 1)) And _
                           LBound(Arr, 1) <= UBound(Arr, 1)
End Function

Thanks!