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
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")
Array() to a typed array. But not if the Variant array comes from elsewhere as an argument.