Hi,

I need to create a UDF to return an array of values with some specified
input variables/values, like inputing the interest rates and a set of periods
of time to get an array of annuity factors.

How can I do this?

I really appreciate any help in this issue.

Thanks a lot in advance,
Laurie

Function laurie() As Variant
v = Array(1, 2, 3)
laurie = v
End Function


In the worksheet, select cells A1, B1, C1
type:
=laurie() and complete with CNTRL-SHIFT-ENTER
--
Gary''s Student - gsnu200745


"Laurie" wrote:

> Hi,
>
> I need to create a UDF to return an array of values with some specified
> input variables/values, like inputing the interest rates and a set of periods
> of time to get an array of annuity factors.
>
> How can I do this?
>
> I really appreciate any help in this issue.
>
> Thanks a lot in advance,
> Laurie
>
>

On Wed, 19 Sep 2007 13:26:02 -0700, Laurie
<(E-Mail Removed)> wrote:

>Hi,
>
>I need to create a UDF to return an array of values with some specified
>input variables/values, like inputing the interest rates and a set of periods
>of time to get an array of annuity factors.
>
>How can I do this?
>

Here's an example of a UDF that returns an array

Public Function Factors(rRates As Range, rDays As Range)

Dim aReturn() As Double
Dim rCell As Range
Dim i As Long

ReDim aReturn(1 To rRates.Cells.Count)
i = 0

For Each rCell In rRates.Cells
i = i + 1
aReturn(i) = rCell.Value / 365 * rDays(i).Value
Next rCell

Factors = aReturn

End Function

The variable aReturn holds all the return values and the function name is
set equal to this array variable at the end of the function.

Note that it assumed that rRates and rDays are ranges with the same number
of cells that correlate in some way, but there is no error-checking to make
sure that's the case.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Laurie,

See the sections entitled "Returning Arrays From Functions" and "Returning
Arrays With Two Dimensions" on the page
http://www.cpearson.com/Excel/Writin...ionsInVBA.aspx

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Laurie" <(E-Mail Removed)> wrote in message
news:68C7FFB5-8341-4C72-B2EC-(E-Mail Removed)...
> Hi,
>
> I need to create a UDF to return an array of values with some specified
> input variables/values, like inputing the interest rates and a set of
> periods
> of time to get an array of annuity factors.
>
> How can I do this?
>
> I really appreciate any help in this issue.
>
> Thanks a lot in advance,
> Laurie
>
>

Thanks for the big help, Chip, Dick and Gary''s Student!!

Now I know how to create a UDF returning arrays (one or two dimensional),
which is great and is what I was exactly looking for.

The next question is that how I can refer to the resulted arrays in another
UDF to do other calculations? Like referring to a specific element in the
array one at a time in another UDF.


Thank you all again!!!!!!

Sincerely,
Laurie



"Chip Pearson" wrote:

> Laurie,
>
> See the sections entitled "Returning Arrays From Functions" and "Returning
> Arrays With Two Dimensions" on the page
> http://www.cpearson.com/Excel/Writin...ionsInVBA.aspx
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)
>
> "Laurie" <(E-Mail Removed)> wrote in message
> news:68C7FFB5-8341-4C72-B2EC-(E-Mail Removed)...
> > Hi,
> >
> > I need to create a UDF to return an array of values with some specified
> > input variables/values, like inputing the interest rates and a set of
> > periods
> > of time to get an array of annuity factors.
> >
> > How can I do this?
> >
> > I really appreciate any help in this issue.
> >
> > Thanks a lot in advance,
> > Laurie
> >
> >
>