Cannot assign variant array to typed array
arrays, excel, function, vba
Solution
VBA does not convert a `Variant Array` into a `Typed Array`. You should choose either and work with it.
Dim MyArray() As String ' <--- MyArray is a TYPED array
MyArray = Array("cat", "dog") ' <-- Type Mismatch
`Array(...)` is a VBA function that returns a `Variant Array`. As a result, you cannot do this assignment that converts a variant array into a typed array, even if the elements inside the Variant Array are all of the appropriate type (`String` in your case).
If you insist to do this conversion you will need a loop that fill the typed array element-by-element. Much simpler is to declare MyArray as a variant array, or just as a variant:
Dim MyArray
' Also works: Dim MyArray()
' Also works: Dim MyArray() As Variant
MyArray = Array("cat", "dog")
Problem
Can anyone explain why this VBA function terminates on the line that defines `MyArray` ? ``` Function FindEndPinyin2(Rge As Range) As Integer Dim MyArray() As String MyArray = Array("cat", "dog") FindEndPinyin2 = 2 End Function ``` The function, simply exits and returns a #Value! error once it reaches the line `MyArray = Array( "Cat", "Dog")` I realize this function doesn't do anything meaningful. It's a simplified example. Thanks in advance