PDA

View Full Version : Function returning Arrays



MamboKing
07-01-2008, 10:42 AM
Function NewVector() As Double()
Dim Vector(10) As Double 'Or any size but MUST be double
...
...
NewVector = Vector
End Function

Problem:
Excel does not display the Function results (through the regular area selection and Ctrl-Shft-Enter).
It comes out with the error #VALUE! in every selected cell.

If I change the Function type to Variant the compiler says "cannot assign to array" (it means the Function).

I cannot change the Vector type. Must be Double.

Any hint, please...? Thanks!

mae0429
07-01-2008, 11:04 AM
I've always written functions returning arrays as:

Function NewVector() As Variant
'You can Dim it whatever here
....
....
NewVecor = Vector
End Function


Is there a reason this wouldn't return as a double for you?

Bob Phillips
07-01-2008, 01:31 PM
Why must it be a Doule, Varaiant will hold whatever type you want.

mikerickson
07-01-2008, 03:56 PM
NewVector = Vector
If newVector is type Double() or Variant() this will give an error. (NewVector() is an array of values, but NewVector is not a variable. Its almost like there is nothing called NewVector, only NewVector(1), NewVector(2), etc. )
Inside a function called from a spreadsheet, that will result in #VALUE.

If NewVector is Variant, it can accept the value of an array.