Functions that return multiple calculated values

[Tom Kreutz]

I'm trying to write a function that will return multiple calculated
values. One way is to use the Array() function, which creates a
variant array, such as:

Function test()
Dim y As Variant
y = Array("a", "b")
test = y
End Function

However, I want to be able to output two CALCULATED values for y(1)
and y(2). If I try to do this in the code above, e.g. if ... then
y(1)=26, I get an error.

Am I missing somthing obvious?

Thanks in advance for any good ideas.

Tom Kreutz

 

[Bob Phillips]

Tom,

This works okay for me

Function test()
Dim y As Variant
ReDim y(1)
If Day(Date) > 20 Then
y(1) = 1
Else
y(1) = 0
End If
test = y
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Kreutz]

Bob,

Thanks for your note. I want the function to return TWO calculated
results. John Walkenbach's book "Excel 2000 Power Programming with
VBA" says that that can be done by returning a variant array (as
opposed to an array of variants). Your note got me to trying the
simpler idea of just returning a standard array, which works fine!!
For example:

Function test()
Dim y(2) As Integer
y(0) = 6
y(1) = 7
test = y
End Function

This returns the values 6 and 7 in two different cells, as I wished.

Thanks for your help.

Tom

 

[Alan Beban]

Tom,

You might want to substitute Dim y(1) for Dim y(2). As it is, y returns
an array with the elements 6,7,0; i.e., y(0)=6, y(1)=7 and y(2)=0.

Alan Beban