Re: Setting up a variable array based on form input
- From: Salad <oil@xxxxxxxxxxx>
- Date: Thu, 16 Jul 2009 05:13:39 -0700
DeZZar wrote:
Ok, another new aspect of VB to me and I'm hoping someone can help.
I have built a financial quoting tool into a database. (Basically
type in loan size, term etc and it calculates payments)
What I need to build is an Internal Rate of Return (IRR) into this
calculator. The following code works, but the values are hard coded
and I really need to be able to vary all of them based on the forms
input.
THe IRR works by summing an array of values where at least one of
these is negative and another is positive.
'### Working IRR Code ####
Private Sub Command243_Click()
Dim Guess, Fmt, RetRate, Pmt, Invest, Msg
Static Values(12) As Double 'Array of values
Guess = 0.1 ' Guess starts at 10 percent.
Fmt = "#0.0000" ' Define percentage format.
Pmt = Me.TotalRepayments
Invest = -((Me.Text45) - (Me.Text65 + Me.Text71)) 'Fields from the
form
Values(0) = Invest 'The initial investment (loan) expressed as
negative
' Positive cash flows reflecting income for x successive years.
' Actual values of the array
Values(1) = Pmt: Values(2) = Pmt
Values(3) = Pmt: Values(4) = Pmt
Values(5) = Pmt: Values(6) = Pmt
Values(7) = Pmt: Values(8) = Pmt
Values(9) = Pmt: Values(10) = Pmt
Values(11) = Pmt: Values(12) = Pmt
RetRate = (IRR(Values(), Guess) * 12) * 100 ' Calculate internal rate.
Msg = "The internal rate of return for these " & Me.Term & " cash
flows is "
Msg = Msg & Format(RetRate, Fmt) & " percent."
MsgBox Msg ' Display internal return rate.
End Sub
'###### END IRR EXAMPLE CODE #########
Where I get completely stuck is in modifying this code to multiply out
an array based on a varrying term.
The one above is hard coded for 12 months. But I need to vary this
automatically based on the loan term selected on the form. So anywhere
between 1 to 120+ months.
I also need to be able to seperately specify the value of the very
last value in the array.
So if I have a 48 month loan term and the payments are $500 per month
I need to be able translate this into the code above as:
"Make me an array of 47 Values where EachValue is $500 and the 48th
value is 'X' "
Can anyone help me with this??
Cheers,
DeZZar
You might want to look at the Type Statement in online help. You can provide meaningful names to the elements as well
Private Type Customer
strName As String
lngID As Long
datDate As Date
End Type
Sub AddTo(rst As Recordset)
Static strRows() As Customer
Static Entries As Integer
Dim intCnt As Integer
Entries = 0
'fill the elements with data
For intCnt = 1 to 3
ReDim Preserve strRows(Entries)
strRows(Entries).strName = rst!CustomerName
strRows(Entries).lngID = rst!CustomerID
strRows(Entries).datDate = date() + 1
rst.movenext
Entries = Entries + 1
Mext
'display one of the type elements
For intCnt = 1 to 3
msgbox strRows(intCnt-1).strName
Mext
Endsub
.
- Follow-Ups:
- Re: Setting up a variable array based on form input
- From: DeZZar
- Re: Setting up a variable array based on form input
- References:
- Setting up a variable array based on form input
- From: DeZZar
- Setting up a variable array based on form input
- Prev by Date: close adobe reader
- Next by Date: Re: Duplicate record command button: "CopyPaste isn't available now"
- Previous by thread: Re: Setting up a variable array based on form input
- Next by thread: Re: Setting up a variable array based on form input
- Index(es):
Relevant Pages
|
