troubleshooting Question

VBA - Help with syntax to reference dynamic array element

Avatar of qeng
qeng asked on
8 Comments2 Solutions60 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros