Calculate number of days using 360 day basis

[Numbers1]

Excel has a formula function Days360 which is used to calculate the number of days between 2 dates as if each month had 30 days. It appears this is not available when writing a Macro in Excel. DateDiff uses a 365 day calendar.
I am writing a macro in Excel to calculate the accrued interest on a list of bonds since their last interest payment. I want to be able to insert a new date to accrue to and the macro will calculate each time that I change the date. Some bonds are calculated on a 365 day basis and others on a 360 day basis. I load the basic information needed to make the calculation from the Excel worksheet and then perform the calculation for that bond. Below is the portion of code that does the actual calculation. I have not shown the DIM statements. The line that doesn't work is noted with ***

' Load data to perform calculation from present line
Worksheets("Accr Int").Cells(intRow, intCouponCol).Select
sngCoupon = ActiveCell
Worksheets("Accr Int").Cells(intRow, intDayBasisCol).Select
intDayBasis = ActiveCell
Worksheets("Accr Int").Cells(intRow, intLastIntPmtCol).Select
dteLastIntPmt = ActiveCell
Worksheets("Accr Int").Cells(intRow, intAccrIntCol).Select
curAccrInt = ActiveCell

' Choose day basis and calculate accrued interest
If intDayBasis = 365 Then
curAccrInt = curFaceAmt * sngCoupon * (dteAccrDate - dteLastIntPmt + 1) / 365
ActiveCell = curAccrInt
Else
*** dblNumberDays = Application.WorksheetFunction.Days360(dteLastIntPmt, dteAccrDate)
curAccrInt = curFaceAmt * sngCoupon * dblNumberDays / 360
ActiveCell = curAccrInt
End If

Thanks,
Numbers

 

[AnotherHiggins]

Numbers,

Your last two threads posing the exact same question were at least in the correct forum (forum707 - the VBA Visual Basic for Applications (Microsoft) Forum).

I suggest you follow up in one of those threads to get your answer since you are looking for VBA help.

After waiting a bit to give you a chance to read my reply, I will Red Flag this thread and ask that it be deleted.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.