Building the COM client in Excel

In the worksheet cells of Microsoft Excel, we can input a number of parameters for a particular option and numerically compute the theoretical option prices using the COM server components we just built in the earlier section. These functions can be made available in the formula cell using Visual Basic. To begin creating these functions, open the Visual Basic Editor from Excel by pressing the Alt + F11 keys on your keyboard.

Setting up the VBA code

In the Project-VBAProject toolbar window, right-click on VBAProject, select Insert, and click on Module to insert a new module in the Excel workbook:

In the code editor area, paste the following VBA code:

Function BlackScholesOptionPrice( _
     ByVal S0 As Integer, _
     ByVal K As Integer, _
     ByVal r As Double, _
     ByVal T As Double, _
     ByVal sigma As Double, _
     ByVal dividend As Double, _
     ByVal isCall As Boolean)
     Set BlackScholes = CreateObject("BlackScholes.Pricer")
     If...