VBA Function Help

[Deuce Bucksman]

Hi,

I am trying to use the Payment function in VBA. The Macro runs but the answer is always zero unless I hard code the principal and interest portion of the code (I'm trying to use an inputbox). I was just wondering if maybe there was someone on here who might be able to tell me if I am missing a step? I really appreciate your help! Please see below for my steps.

Sub MacroTest1()
'
' MacroTest1 Macro
'This macro is to generate a text box for payments
    Dim Principal As Variant
    Dim InterestRate As Variant
    Dim Payment As Variant
InputBox "Please enter principal balance", "Principal Balance" = Principal
InputBox "Please enter Interest Rate", "Interest Rate" = InterestRate
Payment = Pmt(InterestRate / 12, 360, Principal, 0)
MsgBox "Your payment is " & Payment
End Sub

 

[Jonmo1]

Try changing these 2 lines

InputBox "Please enter principal balance", "Principal Balance" = Principal
InputBox "Please enter Interest Rate", "Interest Rate" = InterestRate

To

Principal = InputBox("Please enter principal balance", "Principal Balance")
InterestRate = InputBox("Please enter Interest Rate", "Interest Rate")

 

[Deuce Bucksman]

Thanks! Worked like a charm. Now I just need to learn how to format the output and I'll be in business. I really appreciate your help!

 

[Zack Barresse]

I would recommend a userform. Or if you do not want to go that route, at least performing some error checking to see if the user entered a good value. If they enter text your code will bomb.

Format the output with the Format() function...

Payment = Format(Pmt(InterestRate / 12, 360, Principal, 0), "your format here")

HTH

 

[Jonmo1]

Try

MsgBox "Your payment is " & Format(Payment, "$#,##0.00")

 

[Deuce Bucksman]

Thanks, guys! This was very helpful and effective. I've been working on this for awhile now so I really appreciate your clearing this up. I've been wanting to use userforms but i'm still unfamiliar with them. I suppose that should be my next project. Thanks so much for your help!

 

[Zack Barresse]

The nice thing about userforms, in the way you would be using them, is you can check the information when they click your 'go' button, and if it's not what you want, you can give them a message box telling them so, clear the fields and have them do it again. Or you can just say 'bye bye' and exit the form. Completely custom controls. It's what I love about userforms, they're whatever you want to make them.