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