Excel VBA Array Function

Question

I'm trying to understand arrays more thoroughly. Can someone please explain to me why this does not compile:

Public Sub Test()
    Dim Names As String
    Names = Array("Brian", "Steve", "Andrea")
    Debug.Print Names(2) 'Compile error: Expected array
End Sub

and why this compiles but does not run:

Public Sub Test()
    Dim Names() As String
    Names = Array("Brian", "Steve", "Andrea") 'Run-time error '13': Type mismatch
    Debug.Print Names(2)
End Sub

but this works, even though I didn't add () (unless declaring it as type Variant makes it an array?):

Public Sub Test()
    Dim Names As Variant
    Names = Array("Brian", "Steve", "Andrea")
    Debug.Print Names(2)
End Sub

and so does this, as expected:

Public Sub Test()
    Dim Names() As Variant
    Names = Array("Brian", "Steve", "Andrea")
    Debug.Print Names(2)
End Sub

A Variant can very well hold a pointer to an array. That makes Variant() pretty much redundant. — Mathieu Guindon
– Mathieu Guindon, Commented Jan 3, 2018 at 19:58

Maciej Los · Accepted Answer · 2018-01-03 19:54:26Z

1

Well, Array function returns Variant data type containing an array. Check MSDN documentation.

Note A Variant that is not declared as an array can still contain an array. A Variant variable can contain an array of any type, except fixed-length strings and user-defined types. Although a Variant containing an array is conceptually different from an array whose elements are of type Variant, the array elements are accessed in the same way.

edited Jan 3, 2018 at 19:54

answered Jan 3, 2018 at 19:51

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

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

4 Comments

Brian Over a year ago

Your update answered my question with the note. Thank you!

Maciej Los Over a year ago

You're very welcome. If it fixes your issue, please, accept my answer as a solution.

Brian Over a year ago

I certainly will, but it said I had to wait 7 minutes. :) +1 for now.

Maciej Los Over a year ago

OK. I'm not in rush ;)

Collectives™ on Stack Overflow

Excel VBA Array Function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related