Appending a dynamic array in VBA

Question

I'm looping through a named range and appending an array based on if the cell is empty or not. I'm not too familiar with arrays in VBA, so perhaps my logic is incorrect. The code keeps overwriting the 1st element in the array and it never gets incremented. I"m not sure why the UBound(myArray) always stays at 0 even after an element has been assigned to the 1st index.

My code looks something like:

Dim myArray() As Double

ReDim Preserve myArray(0)

    For Each cell In [myRange]
        If cell <> "" Then
            If UBound(myArray) > 0 Then
                ReDim Preserve myArray(0 To UBound(myArray) + 1)
            End If
            myArray(UBound(myArray)) = cell.value
        End If
    Next

Answer 1

Use a variable and increment it, it makes no difference if the variable is larger than the ubound when the code completes:

Sub Test()
Dim myArray() As Double, X As Long
X = 0
ReDim Preserve myArray(X)
For Each cell In Range("Hello")
    If cell <> "" Then
        ReDim Preserve myArray(0 To X)
        myArray(X) = cell.Value
        X = X + 1
    End If
Next
End Sub

Answer 2

Change If UBound(myArray) > 0 Then to If UBound(myArray) >= 0 Then that will solve the problem.

Sub Test()
    Dim myArray() As Double
    ReDim Preserve myArray(0)
    For Each cell In Range("Hello")
        If cell <> "" Then
            If UBound(myArray) >= 0 Then
                myArray(UBound(myArray)) = cell.Value
                ReDim Preserve myArray(0 To UBound(myArray) + 1)
            End If
        End If
    Next
End Sub