VBA调用excel函数

Posted onAuthorcnliutz

Sub InsertFormula()
 Worksheets("Sheet1").Range("A1:B3").Formula = "=RAND()"  'a1：b3单元格内产生随机数
    '-----计算贷款利率  贷款89000，利率：4.7%，年限：20，结果：572.71元/月------------
    Static loanAmt
    Static loanInt
    Static loanTerm
    loanAmt = Application.InputBox _
     (Prompt:="Loan amount (100,000 for example)", _
     Default:=loanAmt, Type:=1)
    loanInt = Application.InputBox _
     (Prompt:="Annual interest rate (8.75 for example)", _
     Default:=loanInt, Type:=1)
    loanTerm = Application.InputBox _
     (Prompt:="Term in years (30 for example)", _
     Default:=loanTerm, Type:=1)
    payment = Application.WorksheetFunction _
     .Pmt(loanInt / 1200, loanTerm * 12, loanAmt)
    MsgBox "Monthly payment is " & Format(payment, "Currency")
End Sub