On Hand/On_Hand_From_Softbank_1994_Release_2_Disc_2_1994.iso/00202/s/disk1/loan.fr_/loan.bin

Text File | 1993-04-28 | 29KB | 866 lines

VERSION 2.00
Begin Form LoanSheet
   BorderStyle     =   3  'Fixed Double
   Caption         =   "LoanSheet"
   ClientHeight    =   5115
   ClientLeft      =   1230
   ClientTop       =   1845
   ClientWidth     =   7065
   ClipControls    =   0   'False
   Height          =   5805
   Icon            =   LOAN.FRX:0000
   Left            =   1170
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   ScaleHeight     =   5115
   ScaleWidth      =   7065
   Top             =   1215
   Width           =   7185
   Begin CommandButton cmdCalcAmort
      Caption         =   "Show Amortization"
      Enabled         =   0   'False
      Height          =   375
      Left            =   120
      TabIndex        =   16
      Top             =   4560
      Width           =   2295
   End
   Begin CommandButton cmdCalcPmts
      Caption         =   "Show Payments"
      Default         =   -1  'True
      Height          =   375
      Left            =   120
      TabIndex        =   7
      Top             =   4065
      Width           =   2295
   End
   Begin Frame frmLoanLen
      Caption         =   "Years in Loan"
      Height          =   1335
      Left            =   120
      TabIndex        =   13
      Top             =   1200
      Width           =   2295
      Begin TextBox txtLenMax
         Height          =   285
         Left            =   1440
         TabIndex        =   3
         Top             =   360
         Width           =   615
      End
      Begin TextBox txtLenMin
         Height          =   285
         Left            =   1440
         TabIndex        =   4
         Top             =   840
         Width           =   615
      End
      Begin Label lblLen
         Caption         =   "Maximum"
         Height          =   255
         Index           =   0
         Left            =   240
         TabIndex        =   15
         Top             =   390
         Width           =   855
      End
      Begin Label lblLen
         Caption         =   "Minimum"
         Height          =   255
         Index           =   1
         Left            =   240
         TabIndex        =   14
         Top             =   840
         Width           =   855
      End
   End
   Begin TextBox txtDwnPay
      Alignment       =   1  'Right Justify
      Height          =   375
      Left            =   1200
      TabIndex        =   2
      Top             =   720
      Width           =   1215
   End
   Begin Grid grdPayments
      BackColor       =   &H00FFFFFF&
      Cols            =   4
      ForeColor       =   &H00000000&
      Height          =   4815
      HighLight       =   0   'False
      Left            =   2655
      Rows            =   17
      ScrollBars      =   0  'None
      TabIndex        =   11
      Top             =   120
      Width           =   4290
   End
   Begin Frame frmIntr
      Caption         =   "Interest Rates"
      Height          =   1335
      Left            =   120
      TabIndex        =   8
      Top             =   2640
      Width           =   2295
      Begin ComboBox comIntrMax
         Height          =   300
         Left            =   1440
         Style           =   2  'Dropdown List
         TabIndex        =   5
         Top             =   360
         Width           =   735
      End
      Begin ComboBox comIntrMin
         Height          =   300
         Left            =   1440
         Style           =   2  'Dropdown List
         TabIndex        =   6
         Top             =   840
         Width           =   735
      End
      Begin Label lblIntr
         Caption         =   "Minimum %"
         Height          =   255
         Index           =   1
         Left            =   240
         TabIndex        =   10
         Top             =   885
         Width           =   1095
      End
      Begin Label lblIntr
         Caption         =   "Maximum %"
         Height          =   255
         Index           =   0
         Left            =   240
         TabIndex        =   9
         Top             =   405
         Width           =   1095
      End
   End
   Begin TextBox txtPurchTotl
      Alignment       =   1  'Right Justify
      Height          =   375
      Left            =   1200
      TabIndex        =   1
      Top             =   120
      Width           =   1215
   End
   Begin Image imgGraphic
      Height          =   750
      Left            =   1845
      Picture         =   LOAN.FRX:0302
      Top             =   165
      Visible         =   0   'False
      Width           =   750
   End
   Begin Label lblDwnPay
      Caption         =   "Percent Down"
      Height          =   435
      Left            =   120
      TabIndex        =   12
      Top             =   720
      Width           =   900
   End
   Begin Label lblPurchTotl
      Caption         =   "Purchase Amount"
      Height          =   435
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   900
   End
   Begin Menu mnuOptions
      Caption         =   "&Options"
      Begin Menu mnuOptCopy
         Caption         =   "&Copy"
         Enabled         =   0   'False
      End
      Begin Menu mnuOptInstruct
         Caption         =   "&Instructions..."
      End
      Begin Menu mnuOptSep
         Caption         =   "-"
      End
      Begin Menu mnuOptExit
         Caption         =   "E&xit"
      End
   End
   Begin Menu mnuDwnPay
      Caption         =   "&Down Payment"
      Begin Menu mnuOptDwn
         Caption         =   "&Percent Down"
         Checked         =   -1  'True
         Index           =   0
      End
      Begin Menu mnuOptDwn
         Caption         =   "&Amount Down"
         Index           =   1
      End
   End
   Begin Menu mnuLoanLen
      Caption         =   "&Loan Length"
      Begin Menu mnuOptLen
         Caption         =   "&Years"
         Checked         =   -1  'True
         Index           =   0
      End
      Begin Menu mnuOptLen
         Caption         =   "&Months"
         Index           =   1
      End
   End
End
Option Explicit
' Form variables for purchase/down payment
Dim PurchAmt
Dim DPAmt
' Form variable for loan amount
Dim LoanAmt
' Form variables for min/max loan length
Dim LenMin
Dim LenMax
' Form variables for min/max interest rate
Dim IntrMin
Dim IntrMax
' Dynamic array for payments
Dim Payments()
' Form variables to track number of rows and columns
Dim Periods
Dim Rates
' Form variables for current interest rate/period values
Dim Interest
Dim Months
' Array for amortization information
Dim AmortData()
' Form variable to hold payment for amortization calculation
Dim AmortPmnt
' Form variable flag telling if the grid shows amortization
Dim AmortFlag

Sub CalcAmort ()
    ' Declare local variables
    Dim Count
    ' Save monthly payment from selected cell
    AmortPmnt = Payments(grdPayments.Row, grdPayments.Col)
    ' Save interest rate from selected row
    Interest = Payments(grdPayments.Row, 0) / 1200
    ' If the periods = years
    If mnuOptLen(0).Checked = True Then
        ' Make loan length loan match selected cell
        Months = Payments(0, grdPayments.Col) * 12
    Else
        Months = Payments(0, grdPayments.Col)
    End If
    ' Size array holding amortization data
    ReDim AmortData(Months, 3)
    ' Save original loan amount to calculate remaining principal
    AmortData(0, 1) = LoanAmt
    ' For each month in loan period
    For Count = 1 To Months
        ' Calculate interest paid for current month
        AmortData(Count, 2) = Int((AmortData(Count - 1, 1) * Interest + .005) * 100) / 100
        ' Calculate remaining balance of principal
        AmortData(Count, 1) = AmortData(Count - 1, 1) - AmortPmnt + AmortData(Count, 2)
    Next Count
End Sub

Sub CalcPmnts ()
    ' Declare local variables
    Dim RowData
    Dim ColData
    ' Calculate # of periods/interest rates to display
    Periods = (LenMax - LenMin) + 1
    Rates = ((IntrMax - IntrMin) * 2) + 1
    ' Size array for payments
    ReDim Payments(Rates + 1, Periods + 1)
    ' Store row headings (rates) in array
    For RowData = 1 To Rates
        Payments(RowData, 0) = IntrMin + ((RowData - 1) / 2)
    Next RowData
    ' Store column headings (loan lengths) in array
    For ColData = 1 To Periods
        Payments(0, ColData) = LenMin + (ColData - 1)
    Next ColData
    ' If periods = years
    If mnuOptLen(0).Checked = True Then
        ' For each interest rate/loan length, calculate payments
        For RowData = 1 To Rates
            Interest = Val(Payments(RowData, 0)) / 1200
            For ColData = 1 To Periods
                Months = Payments(0, ColData) * 12
                Payments(RowData, ColData) = MonthPay(Interest, Months)
            Next ColData
        Next RowData
    ' If periods = months
    Else
        ' For each interest rate/loan length, calculate payments
        For RowData = 1 To Rates
            Interest = Val(Payments(RowData, 0)) / 1200
            For ColData = 1 To Periods
                Months = Payments(0, ColData)
                Payments(RowData, ColData) = MonthPay(Interest, Months)
            Next ColData
        Next RowData
    End If
End Sub

Sub ClearGrid ()
    ' Declare local variables
    Dim Count As Integer
    ' Clear text from 1st col
    grdPayments.Col = 0
    For Count = 1 To grdPayments.Rows - 1
        grdPayments.Row = Count
        grdPayments.Text = " "
    Next Count
    ' Clear text from 1st row
    grdPayments.Row = 0
    For Count = 1 To grdPayments.Cols - 1
        grdPayments.Col = Count
        grdPayments.Text = " "
    Next Count
    ' Clear text from non-fixed cells
    grdPayments.SelStartCol = 1
    grdPayments.SelStartRow = 1
    grdPayments.SelEndCol = grdPayments.Cols - 1
    grdPayments.SelEndRow = grdPayments.Rows - 1
    grdPayments.FillStyle = 1
    grdPayments.Text = ""
    grdPayments.FillStyle = 0
    grdPayments.SelEndCol = 1
    grdPayments.SelEndRow = 1
End Sub

Sub ClipCopy ()
    ' Declare local variables
    Dim ClipText
    Dim CopyText
    Dim NC
    Dim NR
    Dim Count
    Dim ColStrt
    Dim ColEnd
    Dim RowStrt
    Dim RowEnd
    Dim HeadTxt
    ' Initialize new column (NC) & row (NR) variables
    NC = Chr$(9)
    NR = Chr$(13) & Chr$(10)
    ' Initialize variables for rows/cols selected
    ColStrt = grdPayments.SelStartCol
    ColEnd = grdPayments.SelEndCol
    RowStrt = grdPayments.SelStartRow
    RowEnd = grdPayments.SelEndRow
    ' Initialize variable that gets input from grid
    ClipText = grdPayments.Clip
    ' Initialize variable that holds output to the clipboard
    CopyText = ""
    ' Header row = current row
    grdPayments.Row = 0
    ' For each cell in header row
    For Count = ColStrt To ColEnd
        ' Set current column
        grdPayments.Col = Count
        ' Does grid display payment or amortization info?
        Select Case AmortFlag
        ' If payments displayed
        Case False
            ' Is loan length in years or months?
            Select Case mnuOptLen(0).Checked
            ' If loan length = years
            Case True
                HeadTxt = Payments(0, Count) & " Years"
            ' If loan length = months
            Case False
                HeadTxt = Payments(0, Count) & " Months"
            End Select
        ' If amortization info displayed
        Case True
            ' Define heading text
            HeadTxt = Mid$(grdPayments.Text, 2, Len(grdPayments.Text) - 1)
        End Select
        ' Copy column header to output variable
        CopyText = CopyText & NC & HeadTxt
    Next Count
    CopyText = CopyText & NR
    ' Add 1st row header to output variable
    grdPayments.Col = 0
    grdPayments.Row = RowStrt
    CopyText = CopyText & grdPayments.Text & NC
    ' Copy text from input variable to output variable
    For Count = 1 To Len(ClipText)
        ' If current character isn't carriage return
        If Mid$(ClipText, Count, 1) <> Chr$(13) Then
            ' Concat character to output variable
            CopyText = CopyText & Mid$(ClipText, Count, 1)
        ' If current character is carriage return
        Else
            ' Concat carriage return/linefeed to output variable
            grdPayments.Row = grdPayments.Row + 1
            CopyText = CopyText & NR & grdPayments.Text & NC
        End If
    Next Count
    ' Copy contents of output variable to clipboard
    Clipboard.SetText CopyText
End Sub

Sub cmdCalcAmort_Click ()
    ' Change mouse pointer to hourglass
    MousePointer = 11
    ' Calculate amortization
    CalcAmort
    ' Display amortization
    ShowAmort
    ' Return mouse pointer to default
    MousePointer = 0
End Sub

Sub cmdCalcPmts_Click ()
    ' Disable amortization button
    cmdCalcAmort.Enabled = False
    ' Does Purchase Amount have a value?
    If PurchAmt <= 0 Then
        MsgBox "You must enter a valid purchase amount.", 48, "LoanSheet Error"
        txtPurchTotl.Text = ""
        txtPurchTotl.SetFocus
        Exit Sub
    End If
    ' Does Maximum Length of Loan have a value?
    If txtLenMax.Text = "" Then
        MsgBox "You must enter a maximum length of loan.", 48, "LoanSheet Error"
        txtLenMax.SetFocus
        Exit Sub
    End If
    ' If maximum loan length is less than minimum loan length...
    If txtLenMax.Text <> "" And LenMax < LenMin Then
        MsgBox "Maximum length of loan must be greater than the minimum length of loan.", 48, "LoanSheet Error"
        txtLenMax.Text = ""
        txtLenMax.SetFocus
        Exit Sub
    End If
    ' If minimum loan length has no value, set equal to 1
    If txtLenMin.Text = "" Then
        MsgBox "The minimum length of loan must be one month or year.", 48, "LoanSheet Error"
        txtLenMin.Text = "1"
        LenMin = 1
    End If
    ' If maximum interest rate is less than minimum interest rate...
    If IntrMax < IntrMin Then
        MsgBox "Maximum interest rate must be greater than the minimum interest rate.", 48, "LoanSheet Error"
        comIntrMax.SetFocus
        Exit Sub
    End If
    ' Is maximum interest rate entered?
    If IntrMax < .5 Then
        MsgBox "You must specify a maximum interest rate greater than zero.", 48, "LoanSheet Error"
        comIntrMax.SetFocus
        Exit Sub
    End If
    ' If no minimum rate entered, min rate = max rate
    If IntrMin < .5 Then
        comIntrMin.ListIndex = comIntrMax.ListIndex
        IntrMin = IntrMax
    End If
    ' Calculate loan amount
    If mnuOptDwn(0).Checked = True Then
        LoanAmt = PurchAmt * (1 - (DPAmt / 100))
    ElseIf mnuOptDwn(1).Checked = True Then
        LoanAmt = PurchAmt - DPAmt
    End If
    ' Change mouse pointer to hourglass
    MousePointer = 11
    ' Calculate payments for all loan lengths/interest rates
    CalcPmnts
    ' Display payments in grid
    ShowPmnts
    ' Change mouse pointer to default
    MousePointer = 0
End Sub

Sub comIntrMax_Click ()
    IntrMax = Val(comIntrMax.Text)
    ' If max interest is less than min interest
    If IntrMax < IntrMin Then
        MsgBox "Maximum interest rate must be greater than or equal to the minimum interest rate.", 48, "LoanSheet Error"
        comIntrMax.ListIndex = comIntrMin.ListIndex
        comIntrMax.SetFocus
    End If
End Sub

Sub comIntrMin_Click ()
    IntrMin = Val(comIntrMin.Text)
    ' If max interest is less than min interest
    If IntrMax < IntrMin Then
        MsgBox "Minimum interest rate must be less than or equal to the maximum interest rate.", 48, "LoanSheet Error"
        comIntrMin.ListIndex = comIntrMin.ListIndex
        comIntrMin.SetFocus
    End If
End Sub

Sub Form_Load ()
    ' Declare local variables
    Dim iRate
    Dim Count
    ' Set down payment to 0
    DPAmt = 0
    ' Put app in center of screen
    LoanSheet.Left = (Screen.Width - LoanSheet.Width) / 2
    LoanSheet.Top = (Screen.Height - LoanSheet.Height) / 2
    ' Put allowed interest rates in list boxes
    For iRate = 0 To 25 Step .5
        comIntrMin.AddItem Format$(iRate, "0.0")
        comIntrMax.AddItem Format$(iRate, "0.0")
    Next iRate
    ' Set interest rates 0.0
    comIntrMin.ListIndex = 0
    comIntrMax.ListIndex = 0
    ' Put graphic in cell 0, 0
    grdPayments.Row = 0
    grdPayments.Col = 0
    grdPayments.ColWidth(0) = imgGraphic.Width
    grdPayments.RowHeight(0) = imgGraphic.Height
    grdPayments.Picture = imgGraphic.Picture
    ' Set default # of rates/periods
    Rates = 16
    Periods = 3
    ' Set width of non-fixed cols
    SizeCells grdPayments
End Sub

Sub Form_Unload (Cancel As Integer)
    End
End Sub

Sub grdPayments_Click ()
    ' Only act if cell contains a payment
    If grdPayments.Text <> "" And AmortFlag = False Then
        ' Enable amortization button
        cmdCalcAmort.Enabled = True
    Else
        ' Disable amortization button
        cmdCalcAmort.Enabled = False
    End If
End Sub

Sub grdPayments_DblClick ()
    ' Only act if cell contains a payment
    If grdPayments.Text <> "" And AmortFlag = False Then
        ' Enable amortization button
        cmdCalcAmort.Enabled = True
        ' Change mouse pointer to hourglass
        MousePointer = 11
        ' Calculate amortization
        CalcAmort
        ' Display amortization
        ShowAmort
        ' Change mouse pointer to default
        MousePointer = 0
    Else
        ' Disable amortization button
        cmdCalcAmort.Enabled = False
    End If
End Sub

Sub mnuOptCopy_Click ()
    ClipCopy
End Sub

Sub mnuOptDwn_Click (Index As Integer)
    Select Case Index
        Case 0
            mnuOptDwn(0).Checked = True
            mnuOptDwn(1).Checked = False
            lblDwnPay.Caption = "Percent Down"
        Case 1
            mnuOptDwn(0).Checked = False
            mnuOptDwn(1).Checked = True
            lblDwnPay.Caption = "Amount Down"
    End Select
End Sub

Sub mnuOptExit_Click ()
    End
End Sub

Sub mnuOptInstruct_Click ()
    ' Declare local variables
    Dim MsgText
    Dim PB
    ' Initialize paragraph break variable
    PB = Chr$(10) & Chr$(13) & Chr$(10) & Chr$(13)
    ' Display message
    MsgText = "Enter purchase amount, down payment, length of loan, and interest rates. Click the Calculate Payments button to display monthly payments in the grid on the right."
    MsgText = MsgText & PB & "Select a monthly payment and click the Calculate Amortization button to display an amortization schedule for the selected interest rate and length of loan in the grid."
    MsgText = MsgText & PB & "Highlight cells in the grid and select Copy from the Options menu to copy data to the Clipboard."
    MsgBox MsgText, 64, "LoanSheet Instructions"
End Sub

Sub mnuOptLen_Click (Index As Integer)
    Select Case Index
        ' Loan length = years
        Case 0
            mnuOptLen(0).Checked = True
            mnuOptLen(1).Checked = False
            frmLoanLen.Caption = "Years in Loan"
        ' Loan length = months
        Case 1
            mnuOptLen(0).Checked = False
            mnuOptLen(1).Checked = True
            frmLoanLen.Caption = "Months in Loan"
    End Select
End Sub

Function MonthPay (Interest, Months)
    MonthPay = LoanAmt * (Interest / (1 - (1 / ((1 + Interest) ^ Months))))
End Function

Sub ShowAmort ()
    ' Declare local variables
    Dim Count
    Dim Row2Fill
    Dim Col2Fill
    Dim GridHgt
    Dim GridWid
    Dim NL
    Dim IntrTotl
    ' Clear contents of grid
    ClearGrid
    ' Initialize line break variable
    NL = Chr$(10) & Chr$(13)
    ' Set # of non-fixed cols (4)
    grdPayments.Cols = 5
    ' Replace picture in 0, 0 cell with text
    grdPayments.Row = 0
    grdPayments.Col = 0
    grdPayments.Picture = LoadPicture()
    grdPayments.Text = NL & "Payment" & NL & Format$(AmortPmnt, "Currency")
    grdPayments.ColWidth(0) = 1.1 * TextWidth(grdPayments.Text)
    ' Set remaining col headings
    grdPayments.Col = 1
    grdPayments.Text = NL & "Principal Paid"
    grdPayments.Col = 2
    grdPayments.Text = NL & "Interest Paid"
    grdPayments.Col = 3
    grdPayments.Text = NL & "Principal Balance"
    grdPayments.Col = 4
    grdPayments.Text = NL & "Total Interest"
    ' Display vert scroll bar
    grdPayments.ScrollBars = 3
    ' Initialize variables for non-fixed height/width of grid less fixed
    ' rows/cols and grid border (30).
    GridHgt = grdPayments.Height - grdPayments.RowHeight(0) - 30
    GridWid = grdPayments.Width - grdPayments.ColWidth(0) - 30
    ' Set # of non-fixed rows
    If Months > 16 Then
        grdPayments.Rows = Months + 1
    Else
        grdPayments.Rows = 16
    End If
    ' 1st col = current column
    grdPayments.Col = 0
    ' Set row heights to evenly display 16 rows in non-fixed height and
    ' show payment # in 1st col
    For Count = 1 To Months
        grdPayments.RowHeight(Count) = GridHgt / 16
        grdPayments.Row = Count
        grdPayments.Text = Count
    Next Count
    ' Set col widths to evenly display 2 columns in non-fixed width
    ' less width of vert scroll bar.
    For Count = 1 To 4
        grdPayments.ColWidth(Count) = (GridWid - 255) / 2
    Next Count
    ' Set initial interest total
    IntrTotl = 0
    ' Put values of non-fixed rows in grid
    For Row2Fill = 1 To Months
        ' For each month in repayment period
        grdPayments.Row = Row2Fill
        ' Display part of payment applied to principal in 1st non-fixed col
        grdPayments.Col = 1
        grdPayments.Text = Format$(AmortPmnt - AmortData(Row2Fill, 2), "Currency")
        ' Display interest paid this payment in 2nd non-fixed col
        grdPayments.Col = 2
        grdPayments.Text = Format$(AmortData(Row2Fill, 2), "Currency")
        ' Display balance remaining on principal in 3rd non-fixed col
        grdPayments.Col = 3
        grdPayments.Text = Format$(AmortData(Row2Fill, 1), "Currency")
        ' Display total interest paid to date in last non-fixed col
        grdPayments.Col = 4
        IntrTotl = IntrTotl + AmortData(Row2Fill, 2)
        grdPayments.Text = Format$(IntrTotl, "Currency")
    Next Row2Fill
    ' Disable Calculate Amortization button
    cmdCalcAmort.Enabled = False
    ' Enable amortization flag
    AmortFlag = True
End Sub

Sub ShowPmnts ()
    ' Declare local variables
    Dim Row2Fill
    Dim Col2Fill
    Dim NL As String
    Dim PeriodTxt As String
    ' Clear grid contents
    ClearGrid
    ' Reset picture in cell 0, 0
    grdPayments.Row = 0
    grdPayments.Col = 0
    grdPayments.Picture = imgGraphic.Picture
    grdPayments.ColWidth(0) = imgGraphic.Width
    ' Size non-fixed cells
    SizeCells grdPayments
    ' Initialize line break variable
    NL = Chr$(10) & Chr$(13)
    ' Initialize variable for loan period (years or months)
    If mnuOptLen(0).Checked = True Then
        PeriodTxt = "Years"
    Else
        PeriodTxt = "Months"
    End If
    ' 1st row = current row
    grdPayments.Row = 0
    ' Put col headings in 1st row
    For Col2Fill = 1 To Periods
        grdPayments.Col = Col2Fill
        grdPayments.FixedAlignment(Col2Fill) = 2
        grdPayments.Text = NL & Payments(0, Col2Fill) & NL & PeriodTxt
    Next Col2Fill
    ' Center text in 1st col
    grdPayments.FixedAlignment(0) = 2
    ' Put row headings in 1st col
    grdPayments.Col = 0
    For Row2Fill = 1 To Rates
        grdPayments.Row = Row2Fill
        grdPayments.Text = Format$(Payments(Row2Fill, 0), "0.0") & "%"
    Next Row2Fill
    ' Fill grid with payments from array
    For Row2Fill = 1 To Rates
        For Col2Fill = 1 To Periods
            grdPayments.Row = Row2Fill
            grdPayments.Col = Col2Fill
            grdPayments.ColAlignment(Col2Fill) = 2
            grdPayments.Text = Format$(Payments(Row2Fill, Col2Fill), "Currency")
        Next Col2Fill
    Next Row2Fill
    ' Allow cell highlighting
    grdPayments.HighLight = True
    ' Enable copying from cells to clipboard
    mnuOptCopy.Enabled = True
    ' Set amortization flag to false
    AmortFlag = False
End Sub

Sub SizeCells (ThisGrid As Control)
    ' Declare local variables
    Dim Count
    Dim GridHgt
    Dim GridWid
    Dim ScrollHgt
    Dim ScrollWid
    ' Initialize variables for non-fixed height/width of grid less
    ' fixed row/column and grid border (30)
    GridHgt = ThisGrid.Height - ThisGrid.RowHeight(0) - 30
    GridWid = ThisGrid.Width - ThisGrid.ColWidth(0) - 30
    ' Initialize variables for scroll bar height/width
    ScrollHgt = 255
    ScrollWid = 255
    ' If data fits in default # of rows (16) and cols (3)
    If Rates <= 16 And Periods <= 3 Then
        ' Don't display scroll bars
        ThisGrid.ScrollBars = 0
        ' Set # of non-fixed rows/cols to default
        ThisGrid.Rows = 17
        ThisGrid.Cols = 4
        ' Set row heights to evenly display 16 rows in non-fixed grid height
        For Count = 1 To 16
            ThisGrid.RowHeight(Count) = GridHgt / 16
        Next Count
        ' Set col widths to evenly display 3 columns in non-fixed grid width.
        For Count = 1 To 3
            ThisGrid.ColWidth(Count) = GridWid / 3
        Next Count
    ' If data needs more columns, but rows are OK
    ElseIf Rates <= 16 And Periods > 3 Then
        ' Display horiz scroll bar only
        ThisGrid.ScrollBars = 1
        ' Set # of non-fixed rows and cols
        ThisGrid.Rows = 17
        ThisGrid.Cols = Periods + 1
        ' Set row heights to evenly display 16 rows in non-fixed grid height
        ' minus scroll bar height
        For Count = 1 To 16
            ThisGrid.RowHeight(Count) = (GridHgt - ScrollHgt) / 16
        Next Count
        ' Set column widths to evenly display 3 columns in non-fixed grid width.
        For Count = 1 To Periods
            ThisGrid.ColWidth(Count) = GridWid / 3
        Next Count
    ' If data needs more rows, but cols are OK
    ElseIf Rates > 16 And Periods <= 3 Then
        ' Display vert scroll bar only
        ThisGrid.ScrollBars = 2
        ' Set number of non-fixed rows and cols
        ThisGrid.Rows = Rates + 1
        ThisGrid.Cols = 4
        ' Set row heights to evenly display 16 rows in non-fixed grid height.
        For Count = 1 To Rates
            ThisGrid.RowHeight(Count) = GridHgt / 16
        Next Count
        ' Set col widths to evenly display 3 columns in non-fixed grid width
        ' minus scroll bar width
        For Count = 1 To 3
            ThisGrid.ColWidth(Count) = (GridWid - ScrollWid) / 3
        Next Count
    ElseIf Rates > 16 And Periods > 3 Then
        ' Display both scroll bars
        ThisGrid.ScrollBars = 3
        ' Set number of non-fixed rows/cols
        ThisGrid.Rows = Rates + 1
        ThisGrid.Cols = Periods + 1
        ' Set row heights to evenly display 16 rows in non-fixed grid height
        ' minus scroll bar height
        For Count = 1 To Rates
            ThisGrid.RowHeight(Count) = (GridHgt - ScrollHgt) / 16
        Next Count
        ' Set column widths to evenly display 3 columns in non-fixed grid width
        ' minus scroll bar width
        For Count = 1 To Periods
            ThisGrid.ColWidth(Count) = (GridWid - ScrollWid) / 3
        Next Count
    End If
End Sub

Sub txtDwnPay_Change ()
    ' Store number into form variable
    DPAmt = Val(txtDwnPay.Text)
    ' Validate that DPAmt doesn't exceed 100% or Purchase Amount
    If mnuOptDwn(0).Checked = True And DPAmt > 99 Then
        MsgBox "Down payment percentage cannot exceed 99 percent.", 48, "LoanSheet Error"
        txtDwnPay.Text = ""
        txtDwnPay.SetFocus
    ElseIf mnuOptDwn(1).Checked = True And DPAmt > PurchAmt Then
        MsgBox "Down payment amount cannot exceed purchase amount.", 48, "LoanSheet Error"
        txtDwnPay.Text = ""
        txtDwnPay.SetFocus
    ElseIf DPAmt < 0 Then
        MsgBox "Down payment amount must be zero or greater.", 48, "LoanSheet Error"
        txtDwnPay.Text = ""
        txtDwnPay.SetFocus
    End If
End Sub

Sub txtLenMax_Change ()
    LenMax = Val(txtLenMax.Text)
End Sub

Sub txtLenMin_Change ()
    LenMin = Val(txtLenMin.Text)
End Sub

Sub txtPurchTotl_Change ()
    PurchAmt = Val(txtPurchTotl.Text)
End Sub