Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a UDF to return an array of values with input variab
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a UDF to return an array of values with input variab
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a UDF to return an array of values with input variab
On Wed, 19 Sep 2007 13:26:02 -0700, 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? 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a UDF to return an array of values with input variab
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a UDF to return an array of values with input va
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a List Output from a Horizontal Array Input | Excel Worksheet Functions | |||
How to return an array of values that are not zero/Null | Excel Discussion (Misc queries) | |||
Use array to return array of values | Excel Worksheet Functions | |||
Return an array of values from a function | Excel Programming | |||
VBA Syntax for VLOOKUP to return array of return values | Excel Programming |