Developing a UserForm for Annuities
Some Background. In the last webpage we introduced the concept of annuities and presented the formulas. In addition we presented the Excel functions associated with calculating certain unknown parameters. In the present webpage we will focus on annuities where a buyer
This is somewhat of a simplification of what we talked about before by focusing on monthly payments and no money down. This situation can be made to be equivalent to paying off a loan, but we will not consider that perspective in this webpage. Your homework will involve building in some of these features that we are presently leaving out. We are going to develop a form where a user
This list gives us the necessary set of specifications. Next you want to develop a UserForm that looks like the following. |
| The following table contains a listing of the controls and their properties. |
| Control | Property | Value |
| Form | Name | frmAnnuities |
| Caption | Computing Annuity Performance Measures | |
| Font | Tahoma - 12 PT - Bold | |
| Frame | Name | frameRequiredInput |
| Caption | Required Input | |
| Label | Name | lblInterest |
| Caption | Annual Interest Rate | |
| TextBox | Name | txtInterest |
| Frame | Name | frameInputs |
| Caption | Two of the Three Inputs | |
| Label | Name | lblPayment |
| Caption | Monthly Payment Amount | |
| TextBox | Name | txtPayment |
| Label | Name | lblFutureValue |
| Caption | Desired Future Value | |
| TextBox | Name | txtFV |
| Label | Name | lblNumberOfYears |
| Caption | Number of Years | |
| TextBox | Name | txtYears |
| CommandButton | Name | cmdCalculate |
| Caption | Calculate Unknown | |
| CommandButton | Name | cmdClearInputs |
| Caption | Clear Inputs | |
| CommandButton | Name | cmdExit |
| Caption | Exit |
Event Code for the Exit
CommandButton. Now you want to make sure to copy the
following code into the cmdExit_Click( ) procedure, for the click event
of the cmdExit button.
This will end the program. Event Code for the Clear Inputs CommandButton. Now you want to copy the following code for the cmdClearInputs_Click( ) procedure, for the click event of the cmdClearInputs button.
This code very simply takes all of the entries in the textboxes and makes them blank and change all the background colors back to light gray. Event Code for the Calculate Unknown CommandButton. This code will be quite elaborate to develop so that we will present it in sections. Then at the end we will put all of the code together so that you can copy it all at once if you desire. Declaring and Initializing Variables. First we will start with dimensioning and initializing the variables that we will need. This will go in the cmdCalculate_Click( ) procedure that will be initiated if you double click on the command button in design mode.
The following gives a description of the variables. There is always more than one way to program a solution to a problem and even more ways to define the variables. Most of these variables relate to input validation rather than actual computation of the results.
Inputting and Validating the Interest Rate. The next major portion of the code relates to validating the inputs. This section of code can be broken down into
This section of code is quite elaborate. The comments should help. We will also discuss this more after the code and in much more detail in class. The first section relates to the required input.
A blank space followed by an underscore, _ is the line continuation character in VB. It tells the compiler to treat the next line as a continuation of the current line. You can take them out if you want when you copy the code, but make sure to put appropriate things on the same line. In this code segment we didn't do anything unusual to develop the messages if there are errors detected by the the If - Then clauses. We check for blank entries, whether the inputs are non-numeric and if they are numeric whether they fall in the correct range for an interest rate. Inputting and Validating the Elective Inputs. This next section of code gets even more elaborate because the user is expected to input any two of the three entries. We must also check for whether they input numerical values. In this section we are going to focus purely on validating the inputs and developing the error messages in response.
Well, there are some obvious patterns to this segment of code, hopefully made clearer by the comments. Each larger section focuses on each of the variables.
Getting Error Messages to the User. This next section of code relates to giving appropriate feedback to the user depending on what they entered.
Performing the Computations. Now we finally get to the portion of the code where we actually calculate the solution depending on the inputs. You will definitely notice that while the formulas are complicated, the code is really simpler than what we have seen so far.
These formulas are based on those in the last page. Notice we didn't make use of the built-in Excel functions. The other thing to notice is that most of the statements have to do with formatting the output with properties such as the font and background color. The code all at once is in the following table. |
| Private Sub cmdCalculate_Click() Dim BlankMsg, NonNumericMsg, Msg As String Dim BlankPayment, BlankFV, BlankYears As Boolean Dim Payment, AnnualInterestRate, FutureValue As Double Dim InterestRate as Double Dim NumberYears, NumberPayments, BlankCounter, NonNumericCounter, ValidEntries As Integer ' Initializing variables BlankMsg = "" NonNumericMsg = "" Msg = "" BlankCounter = 0 ValidEntries = 0 NonNumericCounter = 0 BlankPayment = False BlankFV = False BlankYears = False ' ' Setting all of the background colors to light gray ' txtFV.BackColor = RGB(223, 223, 223) txtPayment.BackColor = RGB(223, 223, 223) txtYears.BackColor = RGB(223, 223, 223) ' Checking Inputs to Verify They are Sufficient for Calculation
Else
End If ' This section of code is verifying that the user has input at least two of the three necessary
Else ' This segment determines whether the input is non-numeric and takes appropriate action
End If ' Now we validate the input for the FutureValue variable in txtFV If txtFV.Text = "" Then
Else ' This segment determines whether the input is non-numeric and takes appropriate action
' If the input is valid then it is assigned to the variable
End If ' Now we validate the input for the NumberPayments variable in txtYears If txtYears.Text = "" Then
Else ' This segment determines whether the input is non-numeric and takes appropriate action
' If the input is valid then it is assigned to the variable
End If ' Printing out the error messages if some of the inputs are invalid, too many are left out If BlankCounter > 1 Or NonNumericCounter > 0 Or ValidEntries = 3 Then
' The case where any of the inputs are non-numeric
' The case where the user inputs all three of the elective inputs
End If ' Now we get to the section of the program where we actually compute our unknown.
End If
End If
End If End Sub |