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.

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.

VBA functions
Function | Description |
---|---|
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
- About the Black-Scholes model
- A sequence of input boxes with the Black-Scholes model
- Coding Box Cancel buttons - with message boxes and input boxes
- Coding the xlfSplit function - VBA code for an xlfSplit macro, xlfSplit function, and comparison to the VBA split function
- This example was developed in Excel 2013, and VBA 7.1
- Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Standard Time (EST)]