Hi all,
I've written a function that's not to complicated and works and does exactly what I want it to do. The problem is that its far, far to slow.
I need to compute interest on an amount that is past due. The interest needs to be computed from the date it was due up to a date defined by the user, usually from 2 weeks to 63 days into the future.
In the following function the dates are defined as DateFrom and DateTo. The interest rate also needs to be computed at a daily rate because it changes during the year. The way this function works, it looks at the DateFrom (The date to start computing from) and then using Select case, finds the appropriate interest rate, then multiplies that rate times the amount due and adds it to the variable CompIntVarRt.
It then adds 1 day to DateFrom and repeats. It keeps doing this until DateFrom is equal to DateTo.
It works pretty good if DateFrom and DateTo aren't to far apart. But if I have a due date back in 1990 and I need to compute up to now, it takes way to long to be practical.(About 30 - 45 seconds for 1 computation on a pentium 166)
Here is a short version of my function using only the year 1999:
(These values do not in any way reflect the actual dates or amounts of interest rates, but are just for experimentation.)
Function CompIntVarRt(DateFrom As Date, DateTo As Date, TaxAmount As Double) As Double
Do Until DateFrom = DateTo
Select Case DateFrom
Case DateValue("1/1/99") To DateValue("6/1/99")
IntAmount = 0.00001 'daily rate for this period
Case DateValue("6/2/99") To DateValue("12/31/99")
IntAmount = 0.00005 'daily rate for this period
End Select
CompIntVarRt = CompIntVarRt + (TaxAmount * IntAmount)
DateFrom = DateFrom + 1
Loop
CompIntVarRt = Format(CompIntVarRt, "#,###.00")
Exit Function
End Function
This is the exact same function with just more years added to the Select Case statement.
Function CompIntVarRt(DateFrom As Date, DateTo As Date, TaxAmount As Double) As Double
Do Until DateFrom = DateTo
Select Case DateFrom
Case DateValue("1/1/90") To DateValue("6/1/90")
IntAmount = 0.00001
Case DateValue("6/2/90") To DateValue("12/31/90")
IntAmount = 0.00005
Case DateValue("1/1/91") To DateValue("6/1/91")
IntAmount = 0.00001
Case DateValue("6/2/91") To DateValue("12/31/91")
IntAmount = 0.00005
Case DateValue("1/1/92") To DateValue("6/1/92")
IntAmount = 0.00001
Case DateValue("6/2/92") To DateValue("12/31/92")
IntAmount = 0.00005
Case DateValue("1/1/93") To DateValue("6/1/93")
IntAmount = 0.00001
Case DateValue("6/2/93") To DateValue("12/31/93")
IntAmount = 0.00005
Case DateValue("1/1/94") To DateValue("6/1/94")
IntAmount = 0.00001
Case DateValue("6/2/94") To DateValue("12/31/94")
IntAmount = 0.00005
Case DateValue("1/1/95") To DateValue("6/1/95")
IntAmount = 0.00001
Case DateValue("6/2/95") To DateValue("12/31/95")
IntAmount = 0.00005
Case DateValue("1/1/96") To DateValue("6/1/96")
IntAmount = 0.00001
Case DateValue("6/2/96") To DateValue("12/31/96")
IntAmount = 0.00005
Case DateValue("1/1/97") To DateValue("6/1/97")
IntAmount = 0.00001
Case DateValue("6/2/97") To DateValue("12/31/97")
IntAmount = 0.00005
Case DateValue("1/1/98") To DateValue("6/1/98")
IntAmount = 0.00001
Case DateValue("6/2/98") To DateValue("12/31/98")
IntAmount = 0.00005
Case DateValue("1/1/99") To DateValue("6/1/99")
IntAmount = 0.00001
Case DateValue("6/2/99") To DateValue("12/31/99")
IntAmount = 0.00005
End Select
CompIntVarRt = CompIntVarRt + (TaxAmount * IntAmount)
DateFrom = DateFrom + 1
Loop
CompIntVarRt = Format(CompIntVarRt, "#,###.00")
Exit Function
End Function
Each year in this example are all the same but in reality they will not be. These interest rates change at different times and to different values throughout different years, but that is not important here.
All I am looking for is some way to do the exact same thing, only much faster.
If this takes more effort than the points I offered is worth, I'll be glad to up the points.
Thanks for any comments or answers,
ahammar
