Declaring and Initializing Arrays in Excel VBA

Question

Can anyone explain to me why this works:

Dim myArr As Variant `or, myArr()`
myArr = Array("Brian", "Steve", "Dan", "Mark", "Tom")

And this doesn't:

Dim myArr As String
myArr = Array("Brian", "Steve", "Dan", "Mark", "Tom")

I get Run-time error '13': Type mismatch

Because the Array statement returns a Variant. Even if you declared a string array (Dim myArr() as String) it wouldn't work. — Rory
– Rory, Commented Apr 21, 2016 at 13:13
Consider Dim myArr() as String and then myArr = Split("Brian,Steve,Dan,Mark,Tom", ","). — Ralph
– Ralph, Commented Apr 21, 2016 at 13:26

Ralph · Accepted Answer · 2016-04-21 13:12:53Z

3

That's because the function Array returns a Variant:

answered Apr 21, 2016 at 13:12

Ralph

9,4544 gold badges35 silver badges42 bronze badges

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

1 Answer 1