Range as argument in function

[Asif]

For a function that returns array, the user has to select the appropriate
number of cells and then type the formula and press ctrl+shift+enter. Is it
possible in the following function to add another argument for the range
where the output will be printed. so that the user can select it (or type
the range) as an argument?

Thanks. ~Asif

Function SLV(Rental As Long, Term As Integer, Optional NumRenPaid, Optional
DiscRate = 0.12)


Dim TVal As Double
TVal = 0

ReDim presval(Term) As Double

Dim yr As Integer
yr = Int(Term / 12)

ReDim an(yr + 1) As Long

Dim RemMon As Integer
RemMon = Term Mod 12

Dim i As Integer

'Take correct upperbound of "an" array depending on whether RemMon is 0
or not.
If RemMon = 0 Then
ReDim an(yr) As Long
For i = 1 To Term
presval(i) = Rental / (1 + DiscRate / 12) ^ (i - 1)
TVal = TVal + presval(i)

If i Mod 12 = 0 Then
an(yr) = CLng(TVal)
yr = yr - 1
End If
Next i
Else
ReDim an(yr + 1) As Long
For i = 1 To Term
presval(i) = Rental / (1 + DiscRate / 12) ^ (i - 1)
TVal = TVal + presval(i)

If i = RemMon Then
an(yr + 1) = TVal
ElseIf (i - RemMon) Mod 12 = 0 Then
an(yr) = CLng(TVal)
yr = yr - 1
End If
Next i
End If

SLV = Application.Transpose(an)

End Function

 

[Chip Pearson]

Asif,

Functions cannot modify any cell other than the ones from which
it was called, so you can't have a destination as an argument to
the function.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

Not sure what you are trying to achieve. The output is determined by where
you array enter the formula. What role would entering that range as a
argument play.

 

[Asif]

The range as an argument will be the destination of the output. But I guess
Chip Pearson has already answered that it's not possible. Thanks.

Tom Ogilvy wrote in message ...