Range as argument in function
 

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

Range as argument in function
 

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



"Asif" wrote in message
...
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

Range as argument in function
 

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.

--
Regards,
Tom Ogilvy



"Asif" wrote in message
...
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

Range as argument in function
 

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 ...
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.

--
Regards,
Tom Ogilvy