CarLoan Code Explanation – Chapter 14
Event Code for OptionsForm
Userform_Initialize Sub We make sure the option button named PaymentOpt is checked when the user sees the form.
Private Sub UserForm_Initialize() PaymentOpt = True End Sub
OKButton_Click Sub We set the public integer variable AppOpt equal to 1, 2, or 3, depending on which option button the user selects, and then we unload the form.
Private Sub OKButton_Click() If PaymentOpt Then AppOpt = 1 ElseIf SensOpt Then AppOpt = 2 Else AppOpt = 3 End If Unload Me End Sub
CancelButton_Click Sub We unload the form and terminate the program.
Private Sub CancelButton_Click() Unload Me End End Sub
Event Code for InputsForm
Userform_Initialize Sub By this time, the ExpString variable has been defined in the Main sub. For example, it might be "Supply the following inputs and the application will calculate the monthly car payment, along with total interest paid." We change the Caption property of the ExpLabel to this string. Then we capture the values from the worksheet ranges Price, DownPay, and IntRate, and enter them, suitably formatted, in the three text boxes. Note that the default property of a text box is the Value property. Therefore, a statement that begins as PriceBox = is equivalent to PriceBox.Value =.
Private Sub UserForm_Initialize() ExpLabel.Caption = ExpString PriceBox = Format(Range("Price"), "0") DownPayBox = Format(Range("DownPay"), "0") IntRateBox = Format(Range("IntRate"), "0.0000")
Based on the value in the Term range, we check the appropriate option button. If the value in the Term range is anything other than 12, 24, 36, 48, or 60, we use 36 as the initial term value.
Select Case Range("Term") Case 12: Opt12 = True Case 24: Opt24 = True Case 36: Opt36 = True Case 48: Opt48 = True Case 60: Opt60 = True Case Else: Opt36 = True End Select End Sub
OKButton_Click Sub The majority of this sub is a loop through all controls for error checking. Note that Me refers to the current user form. Therefore, Me.Controls refers to the collection of all controls on this form.
Private Sub OKButton_Click() Dim ctl As Control, Response As Variant For Each ctl In Me.Controls
The function TypeName can be used to check what type of control any particular control is. If the control type is a text box, we check that it is not blank and that it contains a numeric value. If not, we exit the sub without unloading the form, so that the user has to try again. If the first check is passed, we then check that the text box contains a positive value. Again, we exit the sub without closing the form if this check is not passed.
If TypeName(ctl) = "TextBox" Then If ctl = "" Or Not IsNumeric(ctl) Then MsgBox "Enter a positive number in each box.", _ vbInformation, "Improper input" ctl.SetFocus Exit Sub End If If ctl <= 0 Then MsgBox "Enter a positive number in each box.", vbInformation, _ "Improper input" ctl.SetFocus Exit Sub End If End If Next
Next, we check that the down payment is not greater than the price.
If Val(DownPayBox) > Val(PriceBox) Then MsgBox "The down payment can't be greater than the price of the car!", _ vbInformation, "Improper input" DownPayBox.SetFocus Exit Sub End If
Next, we check that the interest rate isn’t greater than 25%. If it is, we ask the user if the interest rate is really supposed to be this large.
If Val(IntRateBox) > 0.25 Then Response = MsgBox("You entered an annual interest rate greater than 25%. " _ & "Do you really mean this?", vbYesNo, "Abnormal interest rate") If Response = vbNo Then IntRateBox.SetFocus Exit Sub End If End If
By this time, all inputs are valid, so we capture them in the Price, DownPay, IntRate, and Term worksheet ranges, and we unload the form.
Range("Price") = Val(PriceBox) Range("DownPay") = Val(DownPayBox) Range("IntRate") = Val(IntRateBox) If Opt12 Then Range("Term") = 12 ElseIf Opt24 Then Range("Term") = 24 ElseIf Opt36 Then Range("Term") = 36 ElseIf Opt48 Then Range("Term") = 48 Else Range("Term") = 60 End If Unload Me End Sub
CancelButton_Click Sub We unload the form and terminate the program.
Private Sub CancelButton_Click() Unload Me End End Sub
Event Code for SensForm
Userform_Initialize Sub By default, we select the option corresponding to the PriceOpt option button.
Private Sub UserForm_Initialize() PriceOpt = True End Sub
OKButton_Click Sub We assign 1, 2, 3, or 4 to the public integer variable SensOpt, depending on which option button is checked, and we unload the form.
Private Sub OKButton_Click() If PriceOpt Then SensOpt = 1 ElseIf DownPayOpt Then SensOpt = 2 ElseIf IntRateOpt Then SensOpt = 3 Else SensOpt = 4 End If Unload Me End Sub
CancelButton_Click Sub We unload the form and terminate the program.
Private Sub CancelButton_Click() Unload Me End End Sub
Code in the Module
Main Sub We see which option of the application the user wants to run by showing the OptionsForm.
Sub Main() OptionsForm.Show
For each possible case (based on the value of AppOpt), we use a Case construction to take the appropriate action.
Select Case AppOpt
For the first case, we define the explanation string, show the InputsForm to get the user’s inputs, and then display a message about the monthly car payment and the total interest paid. These latter two values are captured from the Payment and TotInterest worksheet ranges.
Case 1 ExpString = "Supply the following inputs and the application " _ & "will calculate the monthly car payment, along with total " _ & "interest paid." InputsForm.Show MsgBox "The monthly payment for these inputs is " _ & Format(Range("Payment"), "$0.00") & "." & vbCrLf _
& vbCrLf & "The total interest paid is " _ & Format(Range("TotInterest"), "$0.00"), vbInformation, "Payment information"
For the second case, we first show the SensForm to let the user select the input to vary for the sensitivity analysis. Then we define the explanation string and show the InputsForm so that the user can choose the values of the inputs to base the sensitivity analysis on. Finally, we perform the sensitivity analysis for the selected input. For example, if the user selects the first option, we run the PriceSensitivity sub. Note that one Case structure is nested within another here.
Case 2 SensForm.Show ExpString = "Enter the following inputs. The sensitivity analysis " _ & "will use these as starting points." InputsForm.Show Select Case SensOpt Case 1: PriceSensitivity Case 2: DownPaySensitivity Case 3: IntRateSensitivity Case 4: TermSensitivity End Select
For the third case, we define the explanation string, show the InputsForm to get the user’s inputs, and run the Amortization sub to create an amortization table.
Case 3 ExpString = "Enter the following inputs. The amortization table will be based on these." InputsForm.Show Amortization End Select End Sub
PriceSensitivity Sub This sub runs the sensitivity analysis on the price of the car. First, we unhide and activate the Sensitivity sheet.
Sub PriceSensitivity() Dim CurrPrice As Currency, Price As Currency, RowOff As Integer, _ i As Integer, DataRange As Range, XRange As Range With Worksheets("Sensitivity") .Visible = True .Activate End With
Next, we enter some labels and clear the sensitivity table from a previous run, if any.
Range("C9") = "Price" With Range("B11") .Value = "Price" Range(.Offset(1, 0), .Offset(1, 2).End(xlDown)).ClearContents End With
We capture the current price in the CurrPrice variable and explain how the various prices for the sensitivity analysis will be chosen.
CurrPrice = Range("Price") MsgBox "The price will be varied from half the current price " _ & "to double the current price, in increments of 10% of the " _ & "current price. (But it will never be less than the current " _ & "down payment.)", vbInformation, "Price range"
The various prices are found through a For loop. Note that each price is the current price multiplied by 1+i/10, where i varies from –5 to 10. This makes the price range extend from 50% below the current price to 100% above it. For each possible price (that is not less than the down payment), we enter it in the cell named Price and then store the corresponding payment and total interest values in the sensitivity table. Finally, we restore the current price to the Price cell. Note that RowOff (for row offset) is a counter that indicates how many rows to go down below row 11 for the next price value.
RowOff = 0 With Range("B11") For i = -5 To 10 Price = CurrPrice * (1 + i / 10) If Price >= Range("DownPay") Then RowOff = RowOff + 1 Range("Price") = Price .Offset(RowOff, 0) = Format(Price, "$0.00") .Offset(RowOff, 1) = Format(Range("Payment"), "$0.00") .Offset(RowOff, 2) = Format(Range("TotInterest"), "$0.00") End If Next End With Range("Price") = CurrPrice
We set the ranges for the sensitivity chart and then call the UpdateSensChart with three arguments: a title for the chart, a data range containing monthly payments and total interest paid, and a range for the horizontal axis containing car prices.
With Range("B11") Set DataRange = Range(.Offset(1, 1), .Offset(1, 2).End(xlDown)) Set XRange = Range(.Offset(1, 0), .End(xlDown)) End With UpdateSensChart "Price of Car", DataRange, XRange End Sub
UpdateSensChart Sub This sub updates the already existing chart of car payments and total interest payments versus price. We first unhide and activate the chart.
Sub UpdateSensChart(InputParameter As String, DataRange As Range, XRange As Range) With Charts("SensChart") .Visible = True .Activate
We use the SetSourceData method, with the DataRange object variable as the argument, to indicate which data to plot. Next, we define the names for the two series being plotted. (These appear in the chart’s legend.) Next, we set the XValues property to the XRange object variable. This is for the values along the horizontal axis. Next, we define the caption for the horizontal axis title and the text for the chart title. (These depend on which variable is the input variable for the sensitivity analysis.) Finally, we deselect the chart (so that the handles around its border don’t show.)
.SetSourceData DataRange .SeriesCollection(1).Name = "Monthly payment" .SeriesCollection(2).Name = "Total interest" .SeriesCollection(1).XValues = XRange .Axes(xlCategory).AxisTitle.Caption = InputParameter .ChartTitle.Text = "Sensitivity to " & InputParameter .Deselect End With End Sub
Amortization Sub This sub creates the amortization sub, using the formulas already in place in its top row. (These are entered at design time and never change.) First, we unhide and activate the Amortization sheet, and clear any old results..
Sub Amortization() Dim Term As Integer, DataRange As Range, XRange As Range With Worksheets("Amortization") .Visible = True .Activate End With With Range("B10") Range(.Offset(2, 0), .Offset(2, 1).End(xlDown)).ClearContents Range(.Offset(1, 2), .Offset(1, 5).End(xlDown)).ClearContents End With
We capture the term of the loan in the Term variable. The amortization table will extend this many rows down. The DataSeries method can be used to “autofill” the first column of the table with 1, 2, 3, etc. It takes (at least) the three arguments shown: we want the series to go down a column (not across a row), we want it to increment by 1 month at a time, and we want it to extend to the number of months in the term. Then we copy the formulas already supplied at design time down the other columns by keeping careful track of the offsets. (Refer to the Amortization sheet for details.)
Term = Range("Term") With Range("B10") .DataSeries Rowcol:=xlColumns, Step:=1, Stop:=Term .Offset(1, 1).Copy Range(.Offset(2, 1), .Offset(Term - 1, 1)) Range(.Offset(0, 2), .Offset(0, 5)).Copy Range(.Offset(1, 2), .Offset(Term - 1, 5)) End With
We set the ranges for the amortization chart. These include the DataRange, containing the principal and interest amounts, and the XRange, containing the months. Then we call the UpdateAmortChart sub with these two ranges as arguments.
With Range("B9") Set DataRange = Range(.Offset(1, 3), .Offset(1, 4).End(xlDown)) Set XRange = Range(.Offset(1, 0), .End(xlDown)) End With UpdateAmortChart DataRange, XRange End Sub
UpdateAmortChart Sub This sub functions almost exactly like the UpdateSensChart to update the already existing amortization chart.
Sub UpdateAmortChart(DataRange As Range, XRange As Range) With Charts("AmortChart") .Visible = True .Activate .SetSourceData DataRange .SeriesCollection(1).Name = "Principal" .SeriesCollection(2).Name = "Interest" .SeriesCollection(1).XValues = XRange .Deselect End With End Sub