Function returning an array 4

[BlueMonkey]

Hi

Is it possible for an array to be returned from a function?

Thanks in advance

B.M.

 

[SQLBI]

I'm not sure as i've never tried it but i don't see why not. You'll probably have to assign a Variant datatype to you function.

If it won't return an array, it will certainly return the value of an array subscript.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[combo]

You can - function has to be Variant. This is the way how it works:

[tt]Sub MainMacro()
MsgBox A(1)
MsgBox A(2)
End Sub

Function A() As Variant
Dim B(2)
B(1) = 1
B(2) = 2
A = B
End Function[/tt]

combo

 

[BlueMonkey]

That's very clever. Thanks for your help again Combo

B.M.

 

[combo]

Thanks B.M. To take most of function returning array, it should be used in a way (in my above example more efficient should be function with one argument):

[tt]Sub MainMacro()
Dim C()
C=A
MsgBox C(1)
MsgBox C(2)
End Sub[/tt]

combo

 

[TonyJollans]

B.M.,

In Excel 2000 you can explicitly return a typed array by declaring it with the type, like ..

Function Func As Integer()

combo,

I didn't realise you could do that in 97. Have a star.

Enjoy,
Tony

 

[glenntb]

Combo, I didn't realize that either. A star from me!

-Glenn

 

[combo]

Tony,
The Integer() is great!! A star for you.

combo

 

[BlueMonkey]

Tony,

The Function Func As Integer is a little over my head - can you explain please.

Thanks a million

B.M.

 

[TonyJollans]

Hi B.M.,

Firstly an apology for leaving out a pair of parentheses. The Function declaration should be ..

Function Func

()

 As Integer()

What this means is that the Function is called Func
It takes no arguments (the empty parentheses after Func).
Integer() means that the function returns an array of Integers.
The size of the (returned) array is not, and can not, be declared here.

Inside the function you build your array called, say, myArray and before finishing you assign it to the return value.

Dim myArray(2) As Integer
myArray(0) = 23
myArray(1) = 34
myArray(2) = 45
Func = myArray

Note that you cannot reference the individual elements of the return array inside the function because the syntax is indistinguishable from a recursive function call.

Enjoy,
Tony

 

[BlueMonkey]

That truly is fantasic - it's exactly the sort of answer I was looking for from my original posting.

Well explained Tony, cheers

B.M.

 

[BlueMonkey]

Can't remember why I asked the question now !!!!

B.M.