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

Dan Donoghue · Accepted Answer · 2015-02-23 23:07:01Z

20

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

answered Feb 23, 2015 at 23:07

Dan Donoghue

6,2662 gold badges20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KingKong Over a year ago

Not a fan of introducing a new variable but this does fix my problem. Thank you very much.

Dan Donoghue Over a year ago

If you want to do it without another variable note my comment on Jeanno's solution, it may be what you are after

feetwet · Accepted Answer · 2023-06-16 04:03:44Z

5

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

edited Jun 16, 2023 at 4:03

feetwet

3,5078 gold badges57 silver badges96 bronze badges

answered Feb 23, 2015 at 22:54

Jeanno

2,8894 gold badges26 silver badges33 bronze badges

2 Comments

tospig Over a year ago

We all have them - My only critique now is that the UBound will be incremented after the last element is entered, so you'll have an array one "size" bigger than the range of values.

Dan Donoghue Over a year ago

Get round this by putting ReDim Preserve myArray(0 To UBound(myArray) - 1) just before End Sub

Brian Burns · Accepted Answer · 2022-06-08 11:15:53Z

3

Expanding Dan Donoghue's solution to avoid excessive redim (and excessive recalculation in the case of Jeanno's solution through use of variables), I would use

Dim rng as Range
Set rng = Range("Hello")

Dim myArray() As Double, X As Long, N As Long
X = 0
N = WorksheetFunction.CountIf(rng, "<>")
ReDim Preserve myArray(X)

For Each cell In rng
    If cell <> "" And X < N Then
        myArray(X) = cell.Value
        X = X + 1
        If X < N Then ReDim Preserve myArray(0 to X)
    End If
Next

edited Jun 8, 2022 at 11:15

Brian Burns

22.4k10 gold badges93 silver badges80 bronze badges

answered Nov 20, 2019 at 3:49

B M

312 bronze badges

1 Comment

Dan Donoghue Over a year ago

I like this, in theory you could save even more cost by using worksheetfunction.counta to determine the redim amount once without looping.

Collectives™ on Stack Overflow

Appending a dynamic array in VBA

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related