EXCEL at VBA series

OnSheet form controls - default values, and passing arguments


This module provides the VBA code for the Box Sequence control and links to the BS option pricing model. You can download the file from the link at the end of the module.


box sequence
Fig 1: on sheet controls - if Defaults is true (ticked), then default value is set in message box sequence

1. Form Controls with Button and Default Check Box



Code 1: three procedures from the [Sheet1(code)] module

Line 2: Private Sub BSCallOptBoxes(ByVal Switch As Boolean)

Line 92: Private Function BSCall(ByVal Stock As Double, ByVal Exercise As Double,
ByVal Rate As Double, ByVal Sigma As Double, ByVal Time As Double) As Double

Line 103: Private Sub BoxSequence_Click()
'' ==============================================
Private Sub BSCallOptBoxes(ByVal Switch As Boolean)
' Switch argument: TRUE - display defaults, FALSE - no defaults (blank)

Dim Stock As Variant, Exercise As Variant, Rate As Variant, Sigma As Variant, Time As Variant
Dim BSCallOpt As Double
Dim D(1 To 5) As String

If Switch Then D(1) = 42: D(2) = 40: D(3) = 0.05: D(4) = 0.2: D(5) = 0.5

' InputBox sequence
Step1:  Stock = Application.InputBox(Prompt:="Enter the Stock Price", _
                         Title:="xlf Option Pricer - Step 1 of 5", _
                         Default:=D(1), _
                         Type:=7)       '' 1 + 2 + 4; Number + Text (a String) + Logical
        If Stock = False And TypeName(Stock) = "Boolean" Then
            Exit Sub
        ElseIf Stock <= 0 Or Stock = "" Then
            MsgBox "Enter a Positive Value for the Stock Price"
            GoTo Step1
        Else
            D(1) = Stock
        End If

Step2:  Exercise = Application.InputBox(Prompt:="Enter the Exercise Price", _
                         Title:="xlf Option Pricer - Step 2 of 5", _
                         Default:=D(2), _
                         Type:=7)
        If Exercise = False And TypeName(Exercise) = "Boolean" Then
            GoTo Step1
        ElseIf Exercise <= 0 Or Exercise = "" Then
            MsgBox "Enter a Positive Value for the Exercise Price"
            GoTo Step2
        Else
            D(2) = Exercise
        End If

Step3:  Rate = Application.InputBox(Prompt:="Enter the Risk Free rate as a decimal", _
                         Title:="xlf Option Pricer - Step 3 of 5", _
                         Default:=D(3), _
                         Type:=7)
        If Rate = False And TypeName(Rate) = "Boolean" Then
            GoTo Step2
        ElseIf Rate <= 0 Or Rate = "" Then
            MsgBox "Enter a Positive Value for the Risk Free rate"
            GoTo Step3
        Else
            D(3) = Rate
        End If

Step4:  Sigma = Application.InputBox(Prompt:="Enter the Stock volatility as a decimal", _
                         Title:="xlf Option Pricer - Step 4 of 5", _
                         Default:=D(4), _
                         Type:=7)
        If Sigma = False And TypeName(Sigma) = "Boolean" Then
            GoTo Step3
        ElseIf Sigma <= 0 Or Sigma = "" Then
            MsgBox "Enter a Positive Value for the Stock volatility"
            GoTo Step4
        Else
            D(4) = Sigma
        End If

Step5:  Time = Application.InputBox(Prompt:="Enter the Time to Expiration, in years as a decimal", _
                         Title:="xlf Option Pricer - Step 5 of 5", _
                         Default:=D(5), _
                         Type:=7)
        If Time = False And TypeName(Time) = "Boolean" Then
            GoTo Step4
        ElseIf Time <= 0 Or Time = "" Then
            MsgBox "Enter a Positive Value for the Time to Expiration. Year as a decimal"
            GoTo Step5
        Else
            D(5) = Time
        End If

'' Output - message box
    BSCallOpt = BSCall(Stock, Exercise, Rate, Sigma, Time)
    MsgBox "Theoretical price of call: " & Format(BSCallOpt, "$#,##0.0000") & vbNewLine _
           & "====================================" & vbNewLine _
           & "With values - " & vbNewLine & vbNewLine _
           & "Stock price: " & Format(Stock, "Currency") & vbNewLine _
           & "Exercise price: " & Format(Exercise, "Currency") & vbNewLine _
           & "Risk free rate: " & Format(Rate, "0.0000") & vbNewLine _
           & "Volatility (sigma): " & Format(Sigma, "0.0000") & vbNewLine _
           & "Time to expiration: " & Format(Time, "0.0000") & vbNewLine & vbNewLine _
           & "xlf Input Box demonstrator", , "  Financial modeling | excelatfinance.com"

End Sub
 


'' =================================
Private Function BSCall(ByVal Stock As Double, ByVal Exercise As Double, ByVal Rate As Double, _
                        ByVal Sigma As Double, ByVal Time As Double) As Double
Dim d1 As Double, d2 As Double
    With Application
        d1 = (.Ln(Stock / Exercise) + (Rate + (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time))
        d2 = (.Ln(Stock / Exercise) + (Rate - (Sigma ^ 2) / 2) * Time) / (Sigma * Sqr(Time))
        BSCall = Stock * .Norm_S_Dist(d1, True) - Exercise * Exp(-Rate * Time) * .Norm_S_Dist(d2, True)
    End With
End Function
 


'' =================================
Private Sub BoxSequence_Click()

    BSCallOptBoxes Sheet1.Range("F6").Value

End Sub