Allowing a UDF to

[JF3]

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

 

[Bob Phillips]

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)

 

[Charles Williams]

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