Avatar of qeng

asked on 

VBA - Help with syntax to reference dynamic array element

Need help correcting VBA syntax error when defining a dynamic array.


An array defined this way:

Dim arr() As Variant
arr = Range("B4:B6").Value2

Correctly results in the elements of the array being defined.  For example, this works:

    For Each element In arr
        i = i + 1
        MsgBox "arr(" & i & ") = " & element
        Next element


After defining array 'arr' as above, why does the syntax 'arr(1)' cause an error?

e.g.  MsgBox "1st element of array 'arr' is: " & arr(1)  ' will throw an error

I've also tried dimensioning the dynamic array prior to attempting to use 'arr(1)' Dynamic_Array_Error.xlsmusing:

Option Base 1
Dim arr() As Variant

arr = Range("B4:B6").Value2
ReDim Preserve arr(3) ' this throws an error also

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon