How to create a UDF to return an array of values with input variab

[Guest]

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

 

[Guest]

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

 

[Dick Kusleika]

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.

 

[Chip Pearson]

Laurie,

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

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

 

[Guest]

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