troubleshooting Question

VBA - Help with syntax to reference dynamic array element

Avatar of qeng
qeng asked on
VBA
8 Comments1 Solution60 ViewsLast Modified:
Need help correcting VBA syntax error when defining a dynamic array.


Background:

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

Problem:

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 the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
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 1 Answer 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