Functions that return multiple calculated values
 

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

Functions that return multiple calculated values
 

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" wrote in message
m...
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

Functions that return multiple calculated values
 

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

"Bob Phillips" wrote in message ...
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" wrote in message
m...
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

Functions that return multiple calculated values
 

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

Tom Kreutz wrote:
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

"Bob Phillips" wrote in message ...

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" wrote in message
. com...

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