Launch Microsoft Visual Studio 2008. Drop down the File menu and select New Project...
Be sure the Windows Application template in the New Project dialog is selected in the Templates pane on the right side, then type LoanCalc in the Name textbox (as shown below):
Now click the OK button.
Save the project by clicking on the Save All button on the standard toolbar. This displays the Save Project dialog box, as shown:
Do not change the default Location path. Be sure to uncheck the Create directory for solution option, as show above, before clicking on the Save button.
This creates a new folder inside the My Documents\Visual Studio 2008\Projects\ folder named LoanCalc:
My Documents\Visual Studio 2008\Projects\LoanCalc
Rename the Form file
Make sure the form file is selected in the Solution Explorer window:
With the form file (Form1.vb) selected in the Solution Explorer window (as shown above), the Properties window directly below it displays it's File properties. Click on the File Name property and type frmLoanCalc.vb and press the enter key (don't forget to include the .vb extension, as shown in the illustration below):
Change the Name and Text properties of the Form
To display the properties of the form in the Properties window, click once on the blank Form, which should be displayed on the left side of the screen in the Design window. Make sure the Name property—which is in parentheses (Name) at the top of the property list so that it's easy to find—is frmLoanCalc. It should have been set to that automatically when we named the form file. Then scroll the properties windows down—the properties are listed alphabetically—and change the Text property to Loan Calculator as shown below:
Using Microsoft Excel within your program
In this project we will create a Loan Calculator that displays a loan amortization chart in a Microsoft Excel spreadsheet.
Like Microsoft Word, Excel was also created to support OLE automation, so most of it's functionality is exposed for our use.
Starting with a simple Loan Payment Calculator
Let's begin by creating a simple loan payment calculator:
Important: Before you add any controls to the form, set these properties of the form, and do not resize the form later:
| Form | |
| Property | Value |
| Size.Width | 218 |
| Size.Height | 273 |
| FormBorderStyle | FixedSingle |
Use the above illustration and add a GroupBox to the form as shown. Set it's Text property to Loan Information. Fill the GroupBox with three Labels, two Textboxes, and one NumericUpDown control, and set their properties like this:
| Label | Textbox | ||
| Property | Value | Property | Value |
| Text | Loan Amount: | Name | txtLoan |
| Text | blank | ||
| Label | Textbox | ||
| Property | Value | Property | Value |
| Text | Interest Rate: | Name | txtRate |
| Text | blank | ||
| Label | NumericUpDown | ||
| Property | Value | Property | Value |
| Text | Years of Loan: | Name | nudYears |
| Minimum | 1 | ||
| Maximum | 30 | ||
If you think the name of the NumericUpDown button is funny (nudYears), just imagine what the name would be if we had used a DomainUpDown control instead (dudYears).
Use the illustration above and add another GroupBox below the first one. Set it's Text property to Payment Information. Fill it with one Label, one Textbox, and two Buttons, and set their properties like this:
| Label | Textbox | ||
| Property | Value | Property | Value |
| Text | Monthly Payment: | Name | txtPayment |
| Text | blank | ||
| Button | Button | ||
| Property | Value | Property | Value |
| Name | btnCalculate | Name | btnExit |
| Text | &Calculate | Text | E&xit |
Calculating a Monthly Payment
We will begin by adding code to the Declarations section and btnCalculate_Click event procedure. In its most basic form, the code to calculate a monthly payment when we provide a valid loan amount, interest rate, and years of the loan would look like this:
'The following code goes in the Declarations section
'Dimension the variables for Loan amount,
' Interest Rate, and Monthly Payment
' in the Declarations section.
Dim dLoan, dRate, dPayment As Double
Dim iYears As Integer
'The following code goes in the btnCalculate_Click event procedure
'Convert the Loan Amount to a double
' and store it in dLoan.
dLoan = CDbl(txtLoan.Text.Trim)
'Convert the Interest Rate to a double
' and store it in dRate.
dRate = CDbl(txtRate.Text.Trim)
'Convert dRate to a percent value
' (required by the Pmt function).
If dRate > 1 Then dRate = dRate / 100
'Convert the Years of Loan to an
' Integer and store it in iYears.
iYears = CInt(nudYears.Value)
'Use the Pmt function to calculate
' the Monthly Payment.
dPayment = Pmt(dRate / 12, _
iYears * 12, -1 * dLoan)
'Display the Monthly Payment in
' the txtPayment textbox.
txtPayment.Text = _
Format(dPayment, "$#,##0.00")
'Display the Payment amount before
' the Amortization process starts.
txtPayment.Refresh()
Add the above code to the btnCalculate_Click event procedure. Be sure to dimension dLoan, dRate, dPayment and iYears in the Declarations section (as shown above) so that they have public (global) scope.
Testing the program so far
Save the project now. Run the program and enter values for Loan Amount, Interest Rate, and Years of Loan. When you click on the Calculate button does it work?
Error Trapping with Try and Catch
The main problem with the above code is that there is no error trapping whatsoever. The CDbl, CInt, and Pmt functions will all generate a critical (program stopping) error it they are not passed the correct values. The CDbl function, for example, will crash the program if the value typed into the txtLoan textbox is not a number value. To prevent your program from crashing if the user makes invalid entries we must do some error trapping.
Here is the code that will trap an illegal entry in the txtLoan textbox (do not type this code):
Try
'Try to convert the value in txtLoan to a double
dLoan =
CDbl(txtLoan.Text.Trim)
Catch
'If a conversion error occurs, this code is executed
MessageBox.Show("Loan
Amount Invalid!",
_
"Invalid Loan Amount", _
MessageBoxButtons.OK,
_
MessageBoxIcon.Exclamation)
'Set the focus back on the txtLoan textbox
txtLoan.Focus()
'Exit the sub, so the user can correct
' their error and try again.
Exit Sub
End Try
If the code inside the Try section above generates a critical error the code in the Catch section is executed. In the above example, if CDbl's attempt to convert the value in txtLoan fails, we display a message box that tells the user they made an invalid Loan entry, set the focus back on the Loan textbox, and then exit the btnCalculate_Click event procedure to let them fix it.
Using the above code as an example of the error trapping Try-Catch format, we would need to modify the code in the btnCalculate_Click event procedure so that every line of code that uses CDbl, CInt, and Pmt is in an error trap. The finished the code in your btnCalculate_Click event procedure would look like this (Note: Do not type this code yet!):
'Convert the Loan amount to a double
' and store it in dLoan.
Try
'Try to convert the value in txtLoan to a double
dLoan = CDbl(txtLoan.Text.Trim)
Catch
'If a conversion error occurs, this code is executed
MessageBox.Show("Loan Amount Invalid!",
"Invalid Loan Amount", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
'Set the focus back on the txtLoan textbox
txtLoan.Focus()
'Exit the sub, so the user can correct
' their error and try again.
Exit Sub
End Try'Convert the Interest Rate to a double
' and store it in dRate.
Try
'Try to convert the value in txtRate to a double
dRate = CDbl(txtRate.Text.Trim)
'Convert dRate to a percent value (required
' by the Pmt function).
If dRate > 1 Then dRate = dRate / 100
Catch
'If a conversion error occurs, this code is executed
MessageBox.Show("Interest Rate Invalid!", _
"Invalid Interest Rate", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
'Set the focus back on the txtRate textbox
txtRate.Focus()
'Exit the sub, so the user can correct
' their error and try again.
Exit Sub
End Try'Convert the Years of Loan to an Integer
' and store it in iYears.
Try
'Try to convert the value in nudYears to a double
iYears = CInt(nudYears.Text.Trim)
Catch
'If a conversion error occurs, this code is executed
MessageBox.Show("Years of Loan Invalid!", _
"Invalid Years of Loan", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
'Set the focus back on the nudYears control
nudYears.Focus()
'Exit the sub, so the user can correct
' their error and try again.
Exit Sub
End Try'Convert the Years of Loan to an Integer
' and store it in iYears.
Try
'Use the Pmt function to calculate the Monthly Payment
dPayment = Pmt(dRate / 12, iYears * 12, -1 * dLoan)
Catch
'If an error occurs, this code is executed
MessageBox.Show( _
"Payment cannot be calculated! Check entries!", _
"Payment Calculation Error", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
'Set the focus back on the txtLoan control
txtLoan.Focus()
'Exit the sub, so the user can correct
' their error and try again.
Exit Sub
End Try
'Display the Monthly Payment in the txtPayment textbox
txtPayment.Text = Format(dPayment, "$#,##0.00")
'Display the Payment amount before the
' Amortization process starts.
txtPayment.Refresh()
The above code would protect our program from input errors. But look at all the extra code this requires! There has to be a way we can reduce the amount of code that adding Try-Catch error trapping requires. How about if we combine all the original code into a single Try-Catch statement. Then we can keep track of which conversion causes the error by incrementing a variable (iError) before each conversion. A Select Case iError statement in the Catch section lets us tell the user where the error occurred:
'Dimension an iError flag to keep track of
' which conversion causes the error.
Dim iError As Integer
'Dimension a generic object reference variable
' so we can reference the control that contains
' the illegal entry and set the focus there.
Dim errControl As Control
Dim sMessage As String
'Convert the values entered into the txtLoan,
' txtRate, nudYears controls to number values.
' Then calculate the Monthly Payment.
Try
'Increment the iError flag before each conversion
' so that we can examine iError in the Catch
' section to determine which conversion caused
' the error.
iError = 0
dLoan = CDbl(txtLoan.Text.Trim)
iError = 1
dRate = CDbl(txtRate.Text.Trim)
If dRate > 1 Then dRate = dRate / 100
iError = 2
iYears = CInt(nudYears.Value)
iError = 3
dPayment = Pmt(dRate / 12, iYears * 12, -1 * dLoan)
txtPayment.Text = Format(dPayment, "$#,##0.00")
'Display the Payment amount before the
' Amortization process starts.
txtPayment.Refresh()
Catch ex As Exception
'If a conversion error occurs, this code is
' executed. The value of iError tells us
' which conversion generated the error.
Select Case iError
Case 0
sMessage = "Loan Amount Invalid!"
'Reference the textbox containing the illegal entry.
errControl = txtLoan
Case 1
sMessage = "Interest Rate Invalid!"
errControl = txtRate
Case 2
sMessage = "Years of Loan Invalid!"
errControl = nudYears
Case Else
'An unknown error occurred. Using a Case Else
' instead of Case 3, stops the syntax checker
' from flagging sMessage and errControl as
' possible "null reference" variables.
sMessage = ex.Message
errControl = txtLoan
End Select
MessageBox.Show(sMessage, "Invalid Entry!", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
'Set the focus back on the control that
' contains the illegal entry.
errControl.Focus()
'Exit the sub, so the user can correct
' their error and try again.
Exit Sub
End Try
Replace all the code in the btnCalculate_Click event procedure with the above code.
Testing the program so far
Save the project now. Run the program and enter some illegal values for Loan Amount, Interest Rate, and Years of Loan (i.e. type your name in the Loan amount textbox) . When you click on the Calculate button are the errors trapped correctly? Is the focus set back to the control where the illegal entries were made?
Adding the Amortize Button
Use the illustration above and add another Button next to the calculate button. Set its properties like this:
| Button | |
| Property | Value |
| Name | btnAmortize |
| Text | &Amortize |
Using Microsoft Excel to display an amortized list of loan payments
For the next page or so, a lot of these instructions will remind you of the My Editor project, because Microsoft Excel, like Word, is also an ActiveX (OLE server) application. This means that it was written and compiled so that most, if not all, of its functionality is exposed. You can write applications that launch Excel, open and modify worksheets, all from within your own applications. In order to access the exposed functionality of Microsoft Excel, we must set a reference to the Microsoft Excel Object Library. This object library contains declarations and definitions for all of the exposed classes that Microsoft Excel contains.
Setting a Reference to the Microsoft Excel Object Library
Drop down the Project menu and select the Add Reference... item:
On the Add Reference dialog box, click on the COM (Component Object Model) tab to display all the COM libraries. Note: The Component Object Model (COM) and its related COM-based technologies DCOM, COM+, MTS and ActiveX« comprise the most widely-used component software model in the world. For more information on COM, I suggest you visit http://www.microsoft.com/com/about.asp.
Scroll down the Component Name list and select the Microsoft Excel ##.0 Object Library item (where ##.0 is the version number). We are using the Microsoft Excel 12.0 Object Library in the classroom:
Now click the OK button to set the reference and close the Add Reference dialog.
Creating a Excel Object Reference variable
Start to type the following dimension statement to the Declarations section at the top of the code window:
Dim ExcelObj As New
When typing a dimension statement, as soon as you type As and a space a dropdown list of available class types appears (Note: If you are using the Microsoft Excel 11.0 Object Library or higher the Excel item may not appear in the class list as shown in the illustration below. If that is the case, add the following Imports line at very top of your code window (above the Public Class frmLoanCalc line):
Imports Microsoft.Office.Interop
Only add the above Imports statement if the Excel item is not available in the class list as shown below:
We will use an object constructor at load-time to access the exposed functionality of Microsoft Excel. Complete the dimension statement in the Declarations section like this (Note: In some versions of the Microsoft Excel Object Library you need to select the ApplicationClass item instead):
'Dimension an Excel.Application reference
' variable and construct an instance of Excel.
Dim ExcelObj As New Excel.Application
By actually constructing the Excel.Application here, Excel is being launched in the background at the same time our application is run. So instead of launching Excel every time the user clicks on the Amortize button, we are launching it only once at program startup. If you recall from the My Editor project, we launched Word and disposed of it within the same procedure, the code from the My Editor looked like this (do not type this code):
'Make sure that WordObj is not already referencing
' an instance of Microsoft Word.
If WordObj Is Nothing Then
'Use the Word.Application object constructor
' to create an instance of Microsoft Word.
WordObj = New Word.Application()
End If
...
... Code to do the spell checking is here
...
'Close the document we created above
' without saving any changes to it.
WordObj.Documents.Close( _
Word.wdSaveOptions.wdDoNotSaveChanges)
'Quit Microsoft Word
WordObj.Quit()
'Set the WordObj reference variable to Nothing so
' that our instance of Word can terminate.
WordObj = Nothing
The second line of code above constructs an instance of Word which launches the application. The last three lines of code above dispose of Word and remove it from memory. By constructing an instance of an application and disposing of it within the same procedure we can be sure it won't be left in memory after our program terminates. In our current project, since we are constructing an instance of Excel at program launch, we must be sure to dispose if it before the program terminates.
Using the form's FormClosing event procedure to dispose of objects
The ideal place to dispose of an object prior to program termination is in the form's FormClosing event procedure. Add the following code to the frmLoanCalc_FormClosing event procedure:
'Disable Alerts so Excel doesn't prompt
' us to save any workbooks we create.
ExcelObj.DisplayAlerts = False
'Close any workbook we created.
ExcelObj.Workbooks.Close()
'Quit Microsoft Excel
ExcelObj.Quit()
'Set the ExcelObj reference variable to
' Nothing so that our instance of Excel
' can terminate.
ExcelObj = Nothing
The form's FormClosing event procedure is automatically raised when the user terminates a program with the Close button in the upper right corner of the window, or when they press Alt-F4. Important: The form's FormClosing event procedure is not executed when the program is terminated with the command Me.Dispose or End. To make sure the FormClosing event procedure is executed the code in the btnExit_Click event procedure should be Me.Close, instead of Me.Dispose. Add the following line of code to the btnExit_Click event procedure:
'Exit the program and raise the
' FormClosing event procedure.
Me.Close()
The big difference between Me.Close and Me.Dispose is that Me.Dispose exits the program immediately, without raising the form's FormClosing event procedure. Note: Visual Basic 6 programmers like to compare the Me.Dispose command to VB6's End command, and the Me.Close command to VB6's Unload Me command.
Creating a Worksheet Object Reference variable
The spreadsheets in Excel are called Worksheets. They are contained inside a Workbook. We need to create a Worksheet reference variable. Once assigned to a worksheet in our Excel application, we can us it to populate the cells on the worksheet with values and formulas. Add the following dimension statement to the Declarations section at the top of the code window:
'Dimension a Excel.Worksheet reference variable
Dim SheetObj As Excel.Worksheet
We can't actually construct a reference a Worksheet here, because a Workbook (which contains all the worksheets) hasn't been created yet. We also need to be sure and delete any reference that SheetObj has been assigned before closing our instance of Excel. So add the following line of code to the beginning (make it the first line of code) of the frmLoanCalc_FormClosing event procedure:
'De-reference SheetObj before exiting Excel
SheetObj = Nothing
The code in your frmLoanCalc_FormClosing event procedure should like like this (the extreme commenting is not required):
Creating a Workbook at program launch
To create a new Workbook at program launch, add the following line of code to the frmLoanCalc_Load event procedure:
'Add a new Workbook to our ExcelObj application
ExcelObj.Workbooks.Add()
This creates a new Workbook, which comes with three new Worksheets automatically.
Coding the btnAmortize_Click event procedure
The first thing we will do in the Click event procedure of the Amortize button is dimension some variables we will need, and set a reference to the first Worksheet in the Workbook we created with the ExcelObj.Workbooks.Add() method call in the frmLoanCalc_Load event procedure. Add the following code to the btnAmortize_Click event procedure:
'iRow is the number of the output row.
' iMonth and iYear are loop counters.
' iPayment is the payment number.
Dim iMonth, iYear As Integer
Dim iRow, iPayment As Integer
'iStartRow and iEndRow are used to specify
' the beginning and ending row values for
' our =Sum() function.
Dim iStartRow, iEndRow As Integer
'dTotInterest and dTotPrinciple accumulate
' the Grand total values for Interest paid
' and Principle paid.
Dim dTotInterest, dTotPrinciple As Double
'Make sure SheetObj is not already referencing
' a Worksheet before referencing the first
' worksheet in the workbook.
If SheetObj Is Nothing Then
SheetObj = _
ExcelObj.Workbooks(1).Worksheets(1)
End If
In the last three lines of the above code we make sure the SheetObj is unassigned before referencing the first Worksheet of our new Workbook.
Populating the Worksheet with our Amortization data
We will need the monthly payment amount (dPayment), interest rate (dRate), loan amount (dLoan), and the total years of the lone (iYears) to do our amortization calculations. In case the user has not already calculated the monthly payment, where these variables are assigned value, we need to call the btnCalculate_Click event procedure ourselves. Add this line of code to the btnAmortize_Click event procedure (below the code above):
'Call the Calculation Payment event to assign values
' to dPayment, dRate, dLoan, and iYears.
btnCalculate.PerformClick()
In case the worksheet is already visible, we will hide it and clear it of any previous data. Add this code to the btnAmortize_Click event procedure as well:
'Hide the spreadsheet while we fill it with data
ExcelObj.Visible = False
'Clear any previous Amortization values
SheetObj.Range("A1:D500").ClearContents()
In the last line of code above, we are using the ClearContents method of the Range class to clear columns A through D all the way down to row 500. A1:D500 is how you specify this range of cells in Excel. The colon (:) between the two cell addresses means through, as in A1 through D500. Note: Our range is a box of cells where A1 is the cell in the upper left corner, and D500 is the cell in the lower right corner. All the cells in-between are included. This range will cover the display of data for the amortization of a 30 year loan with a few rows to spare.
Inserting the Loan Summary Info at the top of the Worksheet
At the top of the worksheet we will insert the loan summary information in the first 4 rows (as shown in the above illustration). Add this code to the btnAmortize_Click event procedure:
'Insert Loan Summary Information, begin with the Loan Amount
SheetObj.Cells(1, 1).Value = "Loan Amount:"
SheetObj.Cells(1, 3).Value = Format(dLoan, "$#,###.00")
'Interest Rate
SheetObj.Cells(2, 1).Value = "Interest Rate:"
SheetObj.Cells(2, 3).Value = Format(dRate, "Percent")
'Years of Loan
SheetObj.Cells(3, 1).Value = "Years of Loan:"
SheetObj.Cells(3, 3).Value = iYears
'and finally, the Monthly Payment amount
SheetObj.Cells(4, 1).Value = "Monthly Payment:"
SheetObj.Cells(4, 3).Value = Format(dPayment, "$#,###.00")
'Change the font for the summary information to Bold
SheetObj.Range("A1:C4").Font.Bold = True
When specifying cell locations for the Cells class, we use row and column numbers: Cells(row,column). Note: In Excel the column headers are assigned letter values: Column letter A is column 1, B is 2, etc. As you can see from the above code, setting the Value property of the Cells class is the way to assign values to individual cells. The last line of code above, sets the Bold property of the Font class to True to make the text in a Range of cells (A1:C4) bold.
Setting the Column Width and changing the Format of cells in a Worksheet
We can use the ColumnWidth method of the Range class to specify the widths of individual columns in our worksheet. Add this code to the btnAmortize_Click event procedure:
'Set the Column Width of the Date column
SheetObj.Range("A1:A500").ColumnWidth() = 10
'Set the Column Width of the Interest column
SheetObj.Range("B1:B500").ColumnWidth() = 13
'Set the Column Width of the Principle column
SheetObj.Range("C1:C500").ColumnWidth() = 13
'Set the Column Width of the Balance column
SheetObj.Range("D1:D500").ColumnWidth() = 15
'AutoFormat the Cells. This line should not
' be broken on 2 lines.
SheetObj.Range("A5:D500"). _
AutoFormat( _
Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting2)
In the last line of code above, we use the AutoFormat method of the Range class to allow excel to automatically format the Amortization data for us. There are several AutoFormat options available. Through a little trial and error, I found that the xlRangeAutoFormatAccounting2 option does the best job for our Amortization data, which is primarily date and currency values.
Inserting the Column Headings for the Amortization output
Before we begin displaying the amortization data, we need to insert column heading for the payment Date, Interest, Principle, and Loan Balance values. Add this code to the btnAmortize_Click event procedure:
'Insert the Column headings in row 5, in
' columns 1 through 4
iRow = 5
SheetObj.Cells(iRow, 1).Value = "Date"
SheetObj.Cells(iRow, 2).Value = "Interest"
SheetObj.Cells(iRow, 3).Value = "Principle"
SheetObj.Cells(iRow, 4).Value = "Balance"
Generating and displaying the Amortization output
We will begin by displaying the monthly payments for the first year of the loan followed by a sub total of the Interest and Principle amounts for that year (as shown in the above illustration). The output will continue down the worksheet and include every year of the loan, followed by a grand total of the Interest and Principle amounts. The total number of payments is the number of years of the loan (iYears) multiplied by 12. We will use two For-Next loops, one for the years and one for the months. The For-Next loop for months will be nested inside the For-Next loop for the years. Add this code to the btnAmortize_Click event procedure:
For iYear = 1 To iYears
'Skip a row to make a blank row between
' each year of output.
iRow += 1
'Store the first row of the year in iStartRow so we
' can use the value later in a =Sum() function
iStartRow = iRow
For iMonth = 1 To 12
'Display the Month and Year in column 1.
' The DateSerial function uses this format
' DateSerial(Year, Month, Day) and
' returns a date in this format mm/dd/yy.
SheetObj.Cells(iRow, 1).Value = _
DateSerial(Year(Now), _
Month(Now) + iPayment, 1)
'Calculate and display the Interest
' amount in column 2
SheetObj.Cells(iRow, 2).Value = _
dLoan * (dRate / 12)
'Calc and display the Principle amount in column 3.
' Subtract the Interest value we calculated in cell
' (iRow, 2) to determine the Principle.
SheetObj.Cells(iRow, 3).Value = dPayment - _
SheetObj.Cells.Item(iRow, 2).Value
'Calc the new Current Balance and display it in column 4
dLoan -= SheetObj.Cells.Item(iRow, 3).Value
SheetObj.Cells(iRow, 4).Value = dLoan
'Move to the next row
iRow += 1
'Increment the Payment counter
iPayment += 1
Next iMonth
'Store the last row of the year in iEndRow so we
' can use the value in a =Sum() function.
iEndRow = iRow - 1
'Display Sub Total on the row after a year of data
SheetObj.Cells(iRow, 1).Value = "Sub Total"
'Insert two =Sum() functions to let Excel calculate
' the sub totals for us. The format of the =Sum()
' function is =Sum(StartRange:EndRange)
SheetObj.Cells(iRow, 2).Value = _
"=SUM(B" & iStartRow & ":B" & iEndRow & ")"
SheetObj.Cells(iRow, 3).Value = _
"=SUM(C" & iStartRow & ":C" & iEndRow & ")"
'Accumulate the Grand Total Interest paid and
' Principle paid amounts.
dTotInterest += SheetObj.Cells.Item(iRow, 2).Value
dTotPrinciple += SheetObj.Cells.Item(iRow, 3).Value
'Add a blank row after each year of output.
iRow +=1
Next iYear
Excel's =Sum() function and using the Values in Cells to do calculations
Instead of adding up the monthly Interest and Principle payments for each year ourselves, we are inserting a =Sum() function at the end of each year so that Excel will do it for us. The =Sum() function requires a Range as a parameter. In this example: =Sum(A5:A17), the values in cells A5 through A17 are added together and displayed in the cell where the =Sum() function is inserted.
Take a look at the following line of code from the above code:
dLoan -= SheetObj.Cells.Item(iRow, 3).Value
Notice how we are using the Value property of the Item collection, to access the number in a cell to do a calculation. The following code would work the same way, by using the Value property of the Cells collection instead:
dLoan -= SheetObj.Cells(iRow, 3).Value
Both ways work the same.
After calculating the values for each year we are accumulating the total Interest paid and total Principle paid in dTotInterest and dTotPrinciple. To display the Grand Total Interest and Principle at the very bottom, add the following code to the btnAmortize_Click event procedure below the Next iYear line:
'Display Grand totals for Interest paid
' and Principle paid.
SheetObj.Cells(iRow, 1).Value = "Grand Total"
'Notice how we don't need to format these
' values as currency because we are
' letting Excel auto format these cells.
SheetObj.Cells(iRow, 2).Value = dTotInterest
SheetObj.Cells(iRow, 3).Value = dTotPrinciple
Formatting the Worksheet some more before displaying it
Up until now the Excel Worksheet has been hidden, recall the ExcelObj.Visible = False command above. Before displaying the Worksheet we need to configure Excel a little bit more. For example, we want to hide the Standard and Formatting toolbars, and the Formula Bar. Then set the Caption to customize the look of Excel to go with our program. Add the following code to the end of btnAmortize_Click event procedure to hide the Standard and Formatting toolbars and the Formula Bar:
'Hide the Formatting and Standard toolbars and the Formula Bar
ExcelObj.CommandBars("Formatting").Visible = False
ExcelObj.CommandBars("Standard").Visible = False
ExcelObj.DisplayFormulaBar = False
By default, the Standard and Formatting toolbars along with the Formula Bar are displayed when you open Excel 2003 and older—Excel 2007 and later does not use toolbars so the above code won't do anything. Since we are using Excel just to display our amortization data, the availability of these toolbars would just confuse the user, so we are hiding them. Add the following code to the end of btnAmortize_Click event procedure to customize the text on Excel's caption bar:
'Customize the text on the Excel Caption bar
ExcelObj.Caption = "Guy's Loan Calculator"
User your name instead of mine!
Changing the size and position of the Excel window
To adjust the size and position of the Excel window so that it will appear next to our form when it is displayed, we can set the WindowState, Width, Height, Left, and Top properties, like this (add the following code below the line of code above, in the btnAmortize_Click event procedure):
'By setting WindowState to xlNormal, it
' can be resized.
ExcelObj.WindowState = _
Excel.XlWindowState.xlNormal
'Adjust the size of the Excel window
' and position it on the screen.
ExcelObj.Width = 325
ExcelObj.Height = 350
ExcelObj.Left = Me.Left + 140
ExcelObj.Top = Me.Top - 24
To make sure the Excel window opens next to the window of our program, add these two lines of code to the frmLoanCalc_Load event procedure so our project window opens in the correct location (Note: Add this code to frmLoanCalc_Load event procedure):
Me.Left = 100
Me.Top = 100
While the above code is supposed to make the Excel window open next to the window of our program, depending upon the screen resolution, this is kind of hit or miss. The units of measurement that our program uses when we set the Left and Top properties is pixels. As far as I can tell, the units of measurement for setting the Left and Top properties of ExcelObj are not pixels. Some experimenting around with the above numbers (which work well on a screen set to 1024x768 resolution) might be required.
Now we are ready to display Excel. Add this final line of code to the btnAmortize_Click event procedure to display Excel:
ExcelObj.Visible = True
Testing the program so far
Save the project now. Run the program and enter values for Loan Amount, Interest Rate, and Years of Loan. Then click the Amortize button and wait. Does the Excel Worksheet that appears look ok? Try entering the Loan Amount (5000), Interest Rate (13.75), and Years of Loan (3) values from the illustration example above. Do your results match?
Adding a ProgressBar to the form
Because the calculations for a loan of 30 years can take some time, the addition of a ProgressBar on the bottom of the form will provide useful feedback to the user during a long calculation process when it looks like the program is frozen. Use the illustration above and add a progress at the bottom of the form. Leave it's default property values unchanged, it's name will be ProgressBar1.
Setting the Maximum property of ProgressBar1 at run-time
The Maximum property of the ProgressBar determines the number of ticks it takes to fill the bar. Because the number of years of a loan can vary, we need to dynamically set the ProgressBar's Maximum property, like this (do not type this code yet):
'Initialize the ProgressBar's Maximum property
ProgressBar1.Maximum = iYears * 12
This sets the Maximum property to the total number of payments, so every time we calculate and write the payment information for a month to the worksheet, we need to increment the progress bar. But we must always be sure we don't try to set the Value property of a ProgressBar to greater than the Maximum value (do not type this code yet):
'Increment the ProgressBar
If ProgressBar1.Value + 1 < _
ProgressBar1.Maximum Then
ProgressBar1.Value += 1
End If
Below is a summary of the code in the btnAmortize_Click event procedure that includes the ProgressBar code. Modify the code in your btnAmortize_Click event procedure to include the ProgressBar code where shown (The six Shaded large font lines):
Dim
iRow,
iMonth,
iYear,
iPayment
As Integer
Dim
iStartRow,
iEndRow
As Integer
Dim
dTotInterest,
dTotPrinciple
As Double
If
SheetObj Is
Nothing
Then
SheetObj = ExcelObj.Workbooks(1).Worksheets(1)
End If
btnCalculate.PerformClick()
'Initialize the
ProgressBar's Maximum property
' and increment it 2 ticks as the process starts.
ProgressBar1.Maximum
=
iYears
*
12
ProgressBar1.Value
=
2
ExcelObj.Visible
=
False
SheetObj.Range("A1:D500").ClearContents()
SheetObj.Cells(1,
1).Value
= "Loan Amount:"
SheetObj.Cells(1,
3).Value
=
Format(dLoan,
"$#,###.00")
SheetObj.Cells(2,
1).Value
= "Interest Rate:"
SheetObj.Cells(2,
3).Value
=
Format(dRate,
"Percent")
SheetObj.Cells(3,
1).Value
= "Years of Loan:"
SheetObj.Cells(3,
3).Value
= iYears
SheetObj.Cells(4,
1).Value
= "Monthly Payment:"
SheetObj.Cells(4,
3).Value
=
Format(dPayment,
"$#,###.00")
SheetObj.Range("A1:C4").Font.Bold
= True
iRow =
5
SheetObj.Cells(iRow,
1).Value
= "Date"
SheetObj.Cells(iRow,
2).Value
= "Interest"
SheetObj.Cells(iRow,
3).Value
= "Principle"
SheetObj.Cells(iRow,
4).Value
= "Balance"
For
iYear = 1 To
iYears
iRow
+= 1
iStartRow
=
iRow
For
iMonth = 1
To
12
SheetObj.Cells(iRow, 1).Value
=
DateSerial(Year(Now),
_
Month(Now)
+ iPayment, 1)
SheetObj.Cells(iRow,
2).Value
=
dLoan
* (dRate / 12)
SheetObj.Cells(iRow, 3).Value
=
dPayment - _
SheetObj.Cells.Item(iRow,
2).Value
dLoan
-=
SheetObj.Cells.Item(iRow, 3).Value
SheetObj.Cells(iRow, 4).Value
=
dLoan
iRow
+= 1
iPayment
+= 1
'Increment the ProgressBar
If
ProgressBar1.Value
+
1 < _
ProgressBar1.Maximum
Then
ProgressBar1.Value
+=
1
End If
Next
iMonth
iEndRow
=
iRow
- 1
SheetObj.Cells(iRow,
1).Value
= "Sub Total"
SheetObj.Cells(iRow,
2).Value
= _
"=SUM(B" &
iStartRow
& ":B"
&
iEndRow
& ")"
SheetObj.Cells(iRow,
3).Value
= _
"=SUM(C" &
iStartRow
& ":C"
&
iEndRow
& ")"
dTotInterest
+=
SheetObj.Cells.Item(iRow,
2).Value
dTotPrinciple
+=
SheetObj.Cells.Item(iRow,
3).Value
iRow
+=
1
Next
iYear
SheetObj.Cells(iRow,
1).Value
=
"Grand Total"
SheetObj.Cells(iRow,
2).Value
=
dTotInterest
SheetObj.Cells(iRow,
3).Value
=
dTotPrinciple
ExcelObj.CommandBars("Formatting").Visible
=
False
ExcelObj.CommandBars("Standard").Visible
=
False
ExcelObj.DisplayFormulaBar
=
False
ExcelObj.Caption
=
"Guy's Loan Calculator"
ExcelObj.WindowState =
Excel.XlWindowState.xlNormal
ExcelObj.Width =
325
ExcelObj.Height =
350
ExcelObj.Left =
Me.Left
+ 140
ExcelObj.Top =
Me.Top
- 24
'Fill the ProgressBar
now that the process is complete
ProgressBar1.Value
= ProgressBar1.Maximum
ExcelObj.Visible = True
Testing the program
Save the project now. Run the program. Test it thoroughly. Fix any bugs. Take a nap.
Required Enhancements
- Display the Year number
Display the Year number above every year of amortization data on the worksheet.
- Display the Total number of Payments in the Summary
Add a fifth row to loan summary info at the top of the spreadsheet that displays the total number of payments
- Turn the mouse pointer into an hourglass during
the calculation
Hint: Windows.Forms.Cursor.Current = Cursors.WaitCursor