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.
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
-
Related materials:
- A sequence of input boxes w/ the Black-Scholes model VBA input box method
- Download the solution worksheet xlf-box-sequence-on-sheet xlsm [31 KB]
- Module developed in: Excel 2016 and VBA 7.1
- Revised: Friday 24th of February 2023 - 03:12 PM, Pacific Time (PT)