VBA input box method (2)


Delimited parameters using an input box w/ the Black-Scholes model

This module shows how one input box can be used with comma delimited list of the five parameters to operationalise the Black-Scholes model. The parameters are split, then assigned to a dynamic array. The procedure returns the option price with a list of inputs in a message box.


The input box uses the VBA InputBox method. Default parameters are shown in figure 1.


Input box method - delimited
Fig 1: The input box - with a list of default parameter values

The VBA code for the input box is shown in code window 1.


The VBA code

To allow easy testing of the input box method, the user is able to allow default values by setting the switch in line 11. TRUE displays default values, FALSE disables the default value feature. The CANCEL buttons exits the procedure.



Code 1: Declarations, Sub and Function BSCallDelimitedInputs (lines 5 to 34), BSCall (lines 37 to 44) displays a delimited parameter inputbox. Allows optional default values - returns call price and parameters to a message box
Option Explicit
Option Base 1

'' ================================= 
Sub BSCallOptDelimitedInputs()
Dim BSCallOpt As Double
Dim Default As String
Dim InData As String
Dim ID() As String      '' IDarray

Dim Switch As Boolean: Switch = True  '' <<<< Select: TRUE for defaults, FALSE for no defaults (blank)
 
If Switch Then Default = "42, 40, 0.05, 0.2, 0.5" Else Default = ""

InData = Application.InputBox(Prompt:="Enter the Stock Price, Exercise Price, Risk Free rate, Volatility, Time to Expiration" _
                        & " - Separated with commas", _
                         Title:="xlf Option Pricer - Delimited parameters", _
                         Default:=Default, _
                         Type:=2)
    If Val(InData) = False Then Exit Sub
    ID = Split(InData, ",")
    
    BSCallOpt = BSCall(Val(ID(0)), Val(ID(1)), Val(ID(2)), Val(ID(3)), Val(ID(4)))
    MsgBox "Theoretical price of call: " & Format(BSCallOpt, "$#,##0.0000") & vbNewLine _
           & "====================================" & vbNewLine _
           & "With values - " & vbNewLine & vbNewLine _
           & "Stock price: " & Format(ID(0), "Currency") & vbNewLine _
           & "Exercise price: " & Format(ID(1), "Currency") & vbNewLine _
           & "Risk free rate: " & Format(ID(2), "0.00%") & " p.a." & vbNewLine _
           & "Volatility (sigma): " & Format(ID(3), "0.00%") & " p.a." & vbNewLine _
           & "Time to expiration: " & Format(ID(4), "0.0") & " years" & vbNewLine & vbNewLine _
           & "xlf Input Box demonstrator", , "  Financial modeling | excelatfinance.com"
     
End Sub

'' ================================= 
Function BSCall(Stock As Double, Exercise As Double, Rate As Double, Sigma As Double, 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


Line 21 uses the Split function with a comma delimiter as the second argument. When the five parameters are assigned to the ID array, the line 23 code passes the values in the array elements to the BSCall function procedure - lines 37 to 44. ID array is zero based, with element values ID(0): 42; ID(1): 40; ID(2): 0.05; ID(3): 0.2; ID(4): 0.5. The Base 1 declaration in line 2 has no effect on the LBound value of ID.


Line 24 to 32 of code 1 then displays the message box shown in figure 2.


Input box result - displayed in a message box
Fig 2: The final box - displaying the Black-Scholes price and a list of parameter values

VBA functions


FunctionDescription
Split(expression[, delimiter[, limit[, compare]]])Returns a zero-based, one-dimensional array of substrings
Val(string)Returns the numbers (values) contained in a string as a numeric value

Related materials

  1. About the Black-Scholes model
  2. A sequence of input boxes with the Black-Scholes model
  3. Coding Box Cancel buttons - with message boxes and input boxes
  4. Coding the xlfSplit function - VBA code for an xlfSplit macro, xlfSplit function, and comparison to the VBA split function