Load Visual Basic and select New Project under the File menu (make it a Standard project). Press the F4 key to view the Form’s properties—if they are not already displayed.
- Change the Caption property of the form to Loan Payment Calculator
- Change the default Name of the form (form1) to frmCalc
- Open the Properties dialog by selecting Project1 Properties under the Project drop-down menu. Make sure the General tab on the Properties dialog is selected.
- In the Startup Object combobox make sure frmCalc is selected (it should be by default). In the Project Name textbox type LoanCalculator. In the Project Description textbox type the following: A Calculator that uses the Microsoft Excel PMT method to calculate the monthly Payment of a loan. Leave all other settings at their defaults and click the OK button.
- Pull down the File menu and choose Save Project. Save the form as: frmCalc.frm. Save the project as loancalc.vbp.
This project will also introduce you to the UpDown Button control. You can use the UpDown Button control to increment or decrement numbers that are displayed in a text box or other control. In this case the value in the Years of Loan Textbox will be settable by using a UpDown Button (To add the UpDown Button control to the Controls Toolbox, right-click on the toolbox and choose Components from the context menu that appears. On the Components dialog, put a check mark in front of the Microsoft Windows Common Controls-2 #.# object library. The pound signs (#.#) are the newest Visual Basic version number, i.e. 6.0).
Use the illustration above as a guide, and add the following controls to the blank frmCalc form:
2 Frames Create all the other controls inside these Frames
4 Textboxes 1 each for Loan Amount, Interest Rate, Years of Loan, and Monthly Payment.
4 Labels 1 for each of the Textboxes
2 Command Buttons 1 for Calc Payment and 1 for Exit
1 UpDown Button To control the value of Years of Loan
1 Line drawn on the form to separate the two Frames. (This is actually not a control—it’s an Object without event procedures. You can find the Line object in the Control Toolbox).
Below is the code from the cmdCalc_Click event procedure. This code creates an Excel Application object and executes Excel’s PMT method to calculate the payment of a loan. The lines are grouped and numbered and described below (Do not type the line numbers when you enter this code):
- Dim dRate As Double
Dim curPayment As Currency, curLoan As Currency
Dim iYears As Integer- Screen.MousePointer = vbHourglass
- If ExcelObj is Nothing Then
Set ExcelObj = New Excel.Application
End If- dRate = CDbl(txtRate.Text)
iYears = CInt(txtYears.Text)
curLoan = CCur(txtLoan.Text)- curPayment = ExcelObj.Pmt((dRate / 100) / 12, iYears * 12, _
-1 * curLoan)- txtPayment.Text = Format(curPayment, "$#,##0.00")
- Screen.MousePointer = vbDefault
- Here you dimension the variables you need to get the job done. You may not be familiar with the Currency data type: Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Let’s just say that the Currency data type isn’t likely to have rounding error problems! I thought you might find all that gibberish amusing.
- This process may take awhile, so this line changes the MousePointer to an Hour glass to indicate to the user that a process is underway.
- The most time consuming part of this process is the New command. The New command actually launches the Excel application. Because the User may press the Calc Payment button over and over as they enter new values, you don’t want to keep creating an Excel.Application object each time they do. This If structure only executes the New command the first time the User presses the Calc Payment button. (Note: Don’t forget to dimension ExcelObj as an Excel.Application variable in the General Declarations section).
- Here’s how you convert all the Strings in your Textboxes to numbers, so that you can process them:
- CDbl converts a String value to a Double.
- CInt converts a String to an Integer.
- CCur converts a String to a Currency.
- Here is where you execute Excel’s PMT method and store the result in curPayment. Notice that the PMT method requires 3 parameters:
- (dRate / 100) / 12 This is the Interest Rate converted to a monthly interest rate.
- iYears * 12 This is the number of months the loan is to run.
- -1 * curLoan The loan amount as a negative value.
Don’t ask me why Excel’s PMT method requires these parameters in this format (it’s, no doubt, part of a long spreadsheet tradition started by Lotus), just rest assured that it will work.
- The Format function adds a dollar sign and formats curPayment before copying it to the txtPayment Textbox.
- The process is now complete. This line sets the MousePointer back to its default arrow pointer.
The first time the User clicks the Calc Payment button, an Excel.Application object is created (part 3 from the code above).
Set ExcelObj = New Excel.Application
It is important to remember that this command actually launches the Excel program (though it remains hidden). To avoid leaving Excel running when your program exits, you must add this code to Form_Unload event procedure. By setting the ExcelObj reference variable to Nothing, we sever it's connection to Excel which allows Excel to exit. (Note: It is also important that you use the command Unload Me, instead of End in your cmdExit_Click event procedure to exit the program. By using Unload Me, the Form_Unload event procedure will be executed as the program exits, thus executing the following code whether the user exits with your Exit button or the Close button):
If Not ExcelObj is Nothing Then
ExcelObj.Quit
Set ExcelObj = Nothing
End IfBefore this program will work, you need to set a Reference to the Microsoft Excel 9.0 Object Library. This will allow you to dimension a variable of an Excel.Worksheet data type. Select the References item on the Project drop down menu, and put a check mark in front of Microsoft Excel 9.0 Object Library, then click the OK button (If you don’t have version 9.0 of the Excel Object Library, just select the version with the largest number from your list of choices).