Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing a UDF to
Hi all,
I have a UDF(see below) that gives me the interest payment on a certain date based on an actual/360 basis. It currently accepts single values or single cell references for each of the arguments. I would like to be able to use this function as an array function. That is, I would like to be able to pass any combination of a range, a single cell reference or a value for each argument and return an array when I hit Ctrl+Shift+Enter. (that I could as an argument for the sum(), avg(), min() or max() functions). You can do this with the IPMT() function that ships with excel(I think in the analysis pack add-in). My questions are.. Is there an easy way to make my Function Accept ranges for any or all of the arguments and return an array when I hit ctrl+Shift+enter? My guess is no. Alternatively, is there a framework or best practice for creating functions that can work as a single or array function?....I imagine you would want to first check and see if any of the values passed are a range. if not just use the normal logic and return 1 value. If there are multi-cell ranges then you have to first make sure all of the arguments are either a single cell/value and that all the arguments that have multiple cell ranges all have the same number of cells. Then I guess you might assign the arguments to a multidimensional array(assigning the same value for every row where an argument referenced a single cell/value) and run each element through the calculations, depositing the results in another array that you will return....Or You could create array's for only those arguments that reference a range and run through all the arguments that way referencing single value variables or arrays as appropriate. Please let me know if you have any suggestions or if you know of any posting anywhere. Any and all help is appreciated! P.S. Sorry for the long post. Public Function Act360IPMT(OrigBal As Double, OrigPmtDate As Date, pmtDate As Date, Ammort As Long, Rate As Double, Optional SvcFee As Double = 0) As Double the same day. 'Some validation code will appear here Dim CurrentPmtdate As Date Dim CurrentBal As Double Dim PmtAmount As Double CurrentPmtdate = OrigPmtDate CurrentBal = OrigBal PmtAmount = -Pmt(Rate / 12, Ammort, OrigBal) Do While CurrentPmtdate < pmtDate CurrentBal = CurrentBal - (PmtAmount - ((CurrentPmtdate - DateAdd("m", -1, CurrentPmtdate)) / 360) * Rate * CurrentBal) CurrentPmtdate = DateAdd("m", 1, CurrentPmtdate) Loop Act360IPMT = Round((((CurrentPmtdate - DateAdd("m", -1, CurrentPmtdate)) / 360) * Rate * CurrentBal) - (CurrentBal * SvcFee * 30 / 360), 2) End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing a UDF to
Here is an example of a UDF that returns an array
'--------------------------------------------------------------------- Function ReturnArray(rng As Range) '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim ary As Variant If rng.Areas.Count 1 Then ReturnArray = CVErr(xlErrValue) Exit Function End If If rng.Cells.Count = 1 Then ary = rng Else ary = rng.Value i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 ary(i, j) = cell + 1 Next cell Next row End If ReturnArray = ary End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "JF3" wrote in message oups.com... Hi all, I have a UDF(see below) that gives me the interest payment on a certain date based on an actual/360 basis. It currently accepts single values or single cell references for each of the arguments. I would like to be able to use this function as an array function. That is, I would like to be able to pass any combination of a range, a single cell reference or a value for each argument and return an array when I hit Ctrl+Shift+Enter. (that I could as an argument for the sum(), avg(), min() or max() functions). You can do this with the IPMT() function that ships with excel(I think in the analysis pack add-in). My questions are.. Is there an easy way to make my Function Accept ranges for any or all of the arguments and return an array when I hit ctrl+Shift+enter? My guess is no. Alternatively, is there a framework or best practice for creating functions that can work as a single or array function?....I imagine you would want to first check and see if any of the values passed are a range. if not just use the normal logic and return 1 value. If there are multi-cell ranges then you have to first make sure all of the arguments are either a single cell/value and that all the arguments that have multiple cell ranges all have the same number of cells. Then I guess you might assign the arguments to a multidimensional array(assigning the same value for every row where an argument referenced a single cell/value) and run each element through the calculations, depositing the results in another array that you will return....Or You could create array's for only those arguments that reference a range and run through all the arguments that way referencing single value variables or arrays as appropriate. Please let me know if you have any suggestions or if you know of any posting anywhere. Any and all help is appreciated! P.S. Sorry for the long post. Public Function Act360IPMT(OrigBal As Double, OrigPmtDate As Date, pmtDate As Date, Ammort As Long, Rate As Double, Optional SvcFee As Double = 0) As Double the same day. 'Some validation code will appear here Dim CurrentPmtdate As Date Dim CurrentBal As Double Dim PmtAmount As Double CurrentPmtdate = OrigPmtDate CurrentBal = OrigBal PmtAmount = -Pmt(Rate / 12, Ammort, OrigBal) Do While CurrentPmtdate < pmtDate CurrentBal = CurrentBal - (PmtAmount - ((CurrentPmtdate - DateAdd("m", -1, CurrentPmtdate)) / 360) * Rate * CurrentBal) CurrentPmtdate = DateAdd("m", 1, CurrentPmtdate) Loop Act360IPMT = Round((((CurrentPmtdate - DateAdd("m", -1, CurrentPmtdate)) / 360) * Rate * CurrentBal) - (CurrentBal * SvcFee * 30 / 360), 2) End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing a UDF to
Hi James,
Yes this is all possible. You need to define the inputs and the function itself as Variants. a Variant input parameter will accept a single cell, a range of cells, a value or an array of values like {1,2,3;4,5,6}. You can use functions like Vartype and ubound to determine what has been passed as input. Then you need to build an array (preferably 2D) to contain the outputs and then assign the array to the function. Excel will happily return the top left cell of the array if the function is NOT array-entered. You can also use Application.Caller to determine the size and shape of the range that the formula has been entered into. regards Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "JF3" wrote in message oups.com... Hi all, I have a UDF(see below) that gives me the interest payment on a certain date based on an actual/360 basis. It currently accepts single values or single cell references for each of the arguments. I would like to be able to use this function as an array function. That is, I would like to be able to pass any combination of a range, a single cell reference or a value for each argument and return an array when I hit Ctrl+Shift+Enter. (that I could as an argument for the sum(), avg(), min() or max() functions). You can do this with the IPMT() function that ships with excel(I think in the analysis pack add-in). My questions are.. Is there an easy way to make my Function Accept ranges for any or all of the arguments and return an array when I hit ctrl+Shift+enter? My guess is no. Alternatively, is there a framework or best practice for creating functions that can work as a single or array function?....I imagine you would want to first check and see if any of the values passed are a range. if not just use the normal logic and return 1 value. If there are multi-cell ranges then you have to first make sure all of the arguments are either a single cell/value and that all the arguments that have multiple cell ranges all have the same number of cells. Then I guess you might assign the arguments to a multidimensional array(assigning the same value for every row where an argument referenced a single cell/value) and run each element through the calculations, depositing the results in another array that you will return....Or You could create array's for only those arguments that reference a range and run through all the arguments that way referencing single value variables or arrays as appropriate. Please let me know if you have any suggestions or if you know of any posting anywhere. Any and all help is appreciated! P.S. Sorry for the long post. Public Function Act360IPMT(OrigBal As Double, OrigPmtDate As Date, pmtDate As Date, Ammort As Long, Rate As Double, Optional SvcFee As Double = 0) As Double the same day. 'Some validation code will appear here Dim CurrentPmtdate As Date Dim CurrentBal As Double Dim PmtAmount As Double CurrentPmtdate = OrigPmtDate CurrentBal = OrigBal PmtAmount = -Pmt(Rate / 12, Ammort, OrigBal) Do While CurrentPmtdate < pmtDate CurrentBal = CurrentBal - (PmtAmount - ((CurrentPmtdate - DateAdd("m", -1, CurrentPmtdate)) / 360) * Rate * CurrentBal) CurrentPmtdate = DateAdd("m", 1, CurrentPmtdate) Loop Act360IPMT = Round((((CurrentPmtdate - DateAdd("m", -1, CurrentPmtdate)) / 360) * Rate * CurrentBal) - (CurrentBal * SvcFee * 30 / 360), 2) End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
allowing each value to be used once | Excel Worksheet Functions | |||
DropDowns - allowing each value to be used once | New Users to Excel | |||
Allowing Only Certain Entrees | Excel Worksheet Functions | |||
Allowing a leading zero if there is one | Excel Discussion (Misc queries) | |||
Allowing entry only once | Excel Programming |