Question about declaring "Array" return type in VBA function

[Paul]

Dear all,

I want to know how to declare an array return type in VBA function.

For example, if my VBA function returns Integer, it would be something
like this

Public Function MyFunction() As Integer
....
....
End Function


But how about array? e.g.

Public Function AnotherFunction()
Dim a(10) as Integer
...
...
AnotherFunction=a
End Function

How should I declare the return type??

Thanks.

Paul

 

[Bob Phillips]

Declare it as variant.

The problem is not what to declare it as, it is in returning the array in a
meaningful manner. If you will act upon the array, say SUM it, no problem,
but if you want to return the array of values to a range you need to set it
up correctly.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Mike Woodhouse]

Dear all,

I want to know how to declare an array return type in VBA function.

For example, if my VBA function returns Integer, it would be something
like this

Public Function MyFunction() As Integer
...
...
End Function

But how about array? e.g.

Public Function AnotherFunction()
Dim a(10) as Integer
...
...
AnotherFunction=a
End Function

How should I declare the return type??

Actually, you already did. The default type for a function is Variant,
which is what you need to return an array. If you want to be more
explicit, decalre the function "As Variant".

Mike