3

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

Community
  • 1
  • 1
Mas
  • 329
  • 2
  • 13

1 Answers1

4

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")
A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • Thanks. That makes sense. – Mas May 07 '17 at 04:08
  • At least since Excel 2007, [it _is_ possible to assign the result of `Array()` to a typed array](https://stackoverflow.com/questions/19369132/declare-and-initialize-string-array-in-vba). But not if the Variant array comes from elsewhere as an argument. – ivan_pozdeev Jul 03 '18 at 17:24