some vba code

CellsSheet.vb

Option Explicit

Private Sub CommandButton1_Click()
    Dim Age
    Dim RetirementAge
    Dim SavingsBalance
    Dim AnnualContribution
    Dim AnnualInterestRate
    
    '
    ' CAPTURE USER INPUTS (VIA CELL VALUES)
    '
    
    Age = Range("E9").Value
    RetirementAge = Range("E11").Value
    SavingsBalance = Range("E13").Value
    AnnualContribution = Range("E15").Value
    AnnualInterestRate = Range("E17").Value
    
    '
    ' VALIDATE USER INPUTS
    '
    
    If IsValidAge(Age) = False Then Exit Sub
    If IsValidAge(RetirementAge) = False Then Exit Sub
    If AgesValid(Age, RetirementAge) = False Then Exit Sub
    If IsValidUSD(SavingsBalance) = False Then Exit Sub
    If IsValidUSD(AnnualContribution) = False Then Exit Sub
    If IsValidPct(AnnualInterestRate) = False Then Exit Sub
    
    '
    ' DISPLAY USER INPUTS
    '
    
    Call LogUserInputs(Age, RetirementAge, SavingsBalance, AnnualContribution, AnnualInterestRate)

    '
    ' CALCULATE OUTPUTS
    '
    
    Dim TotalContribution As Double
    Dim TotalInterest As Double

    TotalContribution = SavingsBalance ' count initial savings balance toward total contribution

    Do While (Age <= RetirementAge)
        Dim AnnualInterest As Double

        AnnualInterest = SavingsBalance * AnnualInterestRate
        SavingsBalance = SavingsBalance + AnnualInterest + AnnualContribution

        TotalContribution = TotalContribution + AnnualContribution ' keep track of total contribution
        TotalInterest = TotalInterest + AnnualInterest ' keep track of total accrued interest

        Age = Age + 1 ' increment the age, avoid infinite loop!
    Loop
    
    '
    ' DISPLAY FINAL OUTPUTS
    '
    
    Call LogFinalOutputs(SavingsBalance, TotalContribution, TotalInterest)
    
End Sub

ControlsSheet.vb

Option Explicit

Private Sub CommandButton1_Click()
    Dim Age
    Dim RetirementAge
    Dim SavingsBalance
    Dim AnnualContribution
    Dim AnnualInterestRate
    
    '
    ' CAPTURE USER INPUTS (VIA CONTROLS w/ LINKED CELLS)
    '

    Age = Range("E9").Value
    RetirementAge = Range("E11").Value
    SavingsBalance = Range("E13").Value
    AnnualContribution = Range("E15").Value
    AnnualInterestRate = Range("F17").Value ' use a protected cell F17 that has the formula "=E17/100" where E17 is the control's linked cell

    '
    ' VALIDATE USER INPUTS
    '

    If IsValidAge(Age) = False Then Exit Sub
    If IsValidAge(RetirementAge) = False Then Exit Sub
    If AgesValid(Age, RetirementAge) = False Then Exit Sub
    If IsValidUSD(SavingsBalance) = False Then Exit Sub
    If IsValidUSD(AnnualContribution) = False Then Exit Sub
    If IsValidPct(AnnualInterestRate) = False Then Exit Sub
    
    '
    ' DISPLAY USER INPUTS
    '
    
    Call LogUserInputs(Age, RetirementAge, SavingsBalance, AnnualContribution, AnnualInterestRate)

    '
    ' CALCULATE OUTPUTS
    '
    
    Dim TotalContribution As Double
    Dim TotalInterest As Double

    TotalContribution = SavingsBalance ' count initial savings balance toward total contribution

    Do While (Age <= RetirementAge)
        Dim AnnualInterest As Double

        AnnualInterest = SavingsBalance * AnnualInterestRate
        SavingsBalance = SavingsBalance + AnnualInterest + AnnualContribution

        TotalContribution = TotalContribution + AnnualContribution ' keep track of total contribution
        TotalInterest = TotalInterest + AnnualInterest ' keep track of total accrued interest

        Age = Age + 1 ' increment the age, avoid infinite loop!
    Loop
    
    '
    ' DISPLAY FINAL OUTPUTS
    '
    
    Call LogFinalOutputs(SavingsBalance, TotalContribution, TotalInterest)
    
End Sub

FormattingModule.vb

Option Explicit

'
' FORMATTING FUNCTIONS (AUTHOR: PROF ROSSETTI)
' ... ADAPTED FROM: https://github.com/prof-rossetti/georgetown-opim-557-20-201710/blob/master/notes/visual-basic/datatypes/strings.md#string-formatting
' ... NOTE: public definitions in this file can be accessed and shared by other files in this project
'

' returns a string formatted as US Dollar currency
Public Function FormatUSD(ByVal Price) ' not declaring a datatype here because price can be integer or double.
    FormatUSD = Format(Price, "$##,##0.00")
End Function

' returns a string formatted as a percentage
Public Function FormatPct(ByVal Percentage As Double)
    FormatPct = Format(Percentage, "###0.00%")
End Function

InputBoxesSheet.vb

Option Explicit

Private Sub CommandButton1_Click()
    Dim Age
    Dim RetirementAge
    Dim SavingsBalance
    Dim AnnualContribution
    Dim AnnualInterestRate

    '
    ' CAPTURE USER INPUTS (VIA NUMERIC-TYPE INPUT BOXES)
    ' ... AND VALIDATE INPUTS IMMEDIATELY AFTER EACH IS CAPTURED
    ' ... (FOR BETTER USER EXPERIENCE)
    '

    Age = Application.InputBox(prompt:="Please specify your current age (e.g. 60): ", Type:=1)
    If IsValidAge(Age) = False Then Exit Sub

    RetirementAge = Application.InputBox(prompt:="Please specify your desired retirement age (e.g. 65): ", Type:=1)
    If IsValidAge(RetirementAge) = False Then Exit Sub
    If AgesValid(Age, RetirementAge) = False Then Exit Sub
    
    SavingsBalance = Application.InputBox(prompt:="Please specify your current savings balance (e.g. 50000.00): ", Type:=1)
    If IsValidUSD(SavingsBalance) = False Then Exit Sub
    
    AnnualContribution = Application.InputBox(prompt:="Please specify your predicted annual contribution (e.g. 18000.00): ", Type:=1)
    If IsValidUSD(AnnualContribution) = False Then Exit Sub
    
    AnnualInterestRate = Application.InputBox(prompt:="Please specify your predicted annual interest rate (e.g. 0.05): ", Type:=1)
    If IsValidPct(AnnualInterestRate) = False Then Exit Sub

    '
    ' DISPLAY USER INPUTS
    '
    
    Call LogUserInputs(Age, RetirementAge, SavingsBalance, AnnualContribution, AnnualInterestRate)

    '
    ' CALCULATE OUTPUTS
    '
    
    Dim TotalContribution As Double
    Dim TotalInterest As Double

    TotalContribution = SavingsBalance ' count initial savings balance toward total contribution

    Do While (Age <= RetirementAge)
        Dim AnnualInterest As Double

        AnnualInterest = SavingsBalance * AnnualInterestRate
        SavingsBalance = SavingsBalance + AnnualInterest + AnnualContribution

        TotalContribution = TotalContribution + AnnualContribution ' keep track of total contribution
        TotalInterest = TotalInterest + AnnualInterest ' keep track of total accrued interest

        Age = Age + 1 ' increment the age, avoid infinite loop!
    Loop
    
    '
    ' DISPLAY FINAL OUTPUTS
    '
    
    Call LogFinalOutputs(SavingsBalance, TotalContribution, TotalInterest)
    
End Sub

LoggingModule.vb

Option Explicit

'
' LOGGING PROCEDURES (AUTHOR: PROF ROSSETTI)
' ... NOTE: public definitions in this file can be accessed and shared by other files in this project
'

Public Sub LogDatatype(ByVal MyVal)
    'MsgBox ("The value is: " & MyVal & " (" & TypeName(MyVal) & ").")
End Sub

' displays a message box with nicely-formatted user input values
Public Sub LogUserInputs(ByVal Age, ByVal RetirementAge, ByVal SavingsBalance, ByVal AnnualContribution, ByVal AnnualInterestRate)
    MsgBox ("INFORMATION INPUTS" & vbNewLine & _
            "---------------------------------" & vbNewLine & _
            "Current Age: " & Age & vbNewLine & _
            "Retirement Age: " & RetirementAge & vbNewLine & _
            "Savings Balance: " & FormatUSD(SavingsBalance) & vbNewLine & _
            "Annual Contribution: " & FormatUSD(AnnualContribution) & vbNewLine & _
            "Annual Interest Rate: " & FormatPct(AnnualInterestRate) _
    )
End Sub

' displays a message box with nicely-formatted final output values
Public Sub LogFinalOutputs(ByVal SavingsBalance, TotalContribution, TotalInterest)
    Dim PctContribution As Double
    Dim PctInterest As Double
    
    PctContribution = TotalContribution / SavingsBalance
    PctInterest = TotalInterest / SavingsBalance
    
    MsgBox ("INFORMATION OUTPUTS" & vbNewLine & _
            "---------------------------------" & vbNewLine & _
            "Retirement Savings Balance: " & FormatUSD(SavingsBalance) & vbNewLine & _
            "Total Contribution: " & FormatUSD(TotalContribution) & " (" & FormatPct(PctContribution) & ")" & vbNewLine & _
            "Total Interest Accrued: " & FormatUSD(TotalInterest) & " (" & FormatPct(PctInterest) & ")" & vbNewLine _
    )
    
End Sub

ValidationsModule.vb

Option Explicit

'
' VALIDATION FUNCTIONS (AUTHOR: PROF ROSSETTI)
' ... NOTE: Public definitions in this file can be accessed and shared by other files in this project
' ... NOTE: Exit Sub prevents execution of code below it unless specifically called with a GoTo
' ... NOTE: HandleInvalid statements (custom name you choose) help prevent code duplication
'

' evaluates whether or not a given value looks like an age value
Public Function IsValidAge(ByVal MyVal)
    Call LogDatatype(MyVal)

    If TypeName(MyVal) = "Double" Then ' expect numeric cell values to be doubles by default, even though some could really be integers
        If Int(MyVal) = MyVal Then ' now try to tell if the value is really an integer
            If MyVal >= 18 And MyVal <= 120 Then ' include this business logic assumption about the age of our clients
                'MsgBox ("Detected valid age: " & MyVal & ".")
                IsValidAge = True
            Else
                GoTo HandleInvalid
            End If
        Else
            GoTo HandleInvalid
        End If
    Else
        GoTo HandleInvalid
    End If

    Exit Function
HandleInvalid:
    MsgBox ("Oh, detected invalid age: " & MyVal & ". Please input a positive whole number between 18 and 120.")
    IsValidAge = False
End Function

' evaluates whether or not a given value looks like a currency value
Public Function IsValidUSD(ByVal MyVal) ' public allows other files in this project to invoke this
    Call LogDatatype(MyVal)

    If TypeName(MyVal) = "Double" Or TypeName(MyVal) = "Currency" Then
        If MyVal > 0 Then
            'MsgBox ("Detected valid price: " & MyVal & ".")
            IsValidUSD = True
        Else
            GoTo HandleInvalid
        End If
    Else
       GoTo HandleInvalid
    End If
    
    Exit Function
HandleInvalid:
    MsgBox ("Oh, detected invalid value: " & MyVal & ". Please input a positive number instead.")
    IsValidUSD = False
End Function

' evaluates whether or not a given value looks like a percentage value
' ... parameter should not include a percent sign
Public Function IsValidPct(ByVal MyVal)
    Call LogDatatype(MyVal)

    If TypeName(MyVal) = "Double" Then
        If MyVal >= 0 And MyVal <= 0.6 Then
            'MsgBox ("Detected valid percentage: " & MyVal & ".")
            IsValidPct = True
        Else
            GoTo HandleInvalid
        End If
    Else
        GoTo HandleInvalid
    End If
    
    Exit Function
HandleInvalid:
    MsgBox ("Oh, detected invalid value: " & MyVal & ". Please input an interest rate between 0.00 and 0.60 (e.g. 0.15).")
    IsValidPct = False
End Function

' evaluates whether the retirement is older than the current age
Public Function AgesValid(ByVal MyAge As Integer, ByVal MyRetirementAge As Integer)
    If MyAge > MyRetirementAge Then
        MsgBox ("Oh, please ensure the desired retirement age is older than the current age.")
        AgesValid = False
    Else
        AgesValid = True
    End If
End Function