CODEWITHSUNDEEP
arraysvbaexcelfunction
Mas
Mas

Reputation: 351

Cannot assign variant array to typed array

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

Upvotes: 3

Views: 2020

Answers (1)

A.S.H
A.S.H

Reputation: 29332

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")

Upvotes: 4

Related Questions