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 isCall = True Then
         answer = BlackScholes.call_pricer(S0, K, r, T, sigma,  dividend)
     Else
         answer = BlackScholes.put_pricer(S0, K, r, T, sigma,  dividend)
     End If
     BlackScholesOptionPrice = answer
 End Function

This will create the COM client component of the Black-Scholes model. The BlackScholesOptionPrice VBA function takes in a number of input parameters from Excel, which we will define later. The CreateObject function is then called and takes the BlackScholes.Pricer input string, which is effectively the name, as defined in the _reg_progid_ variable of the corresponding COM server component. In the COM server, we exposed two methods, call_pricer and put_pricer, to compute and return the Black-Scholes call and put option prices respectively. The selection of this option is determined by the isCall variable, which is true for a call option and false for a put option.

In the same fashion, we can create the COM client functions for our two other pricing methods using the following VBA code:

 Function BinomialTreeCRROptionPrice( _
    ByVal S0 As Integer, _
    ByVal K As Integer, _
    ByVal r As Double, _
    ByVal T As Double, _
    ByVal N As Integer, _
    ByVal sigma As Double, _
    ByVal isCall As Boolean, _
    ByVal dividend As Double)
    Set BinCRRTree = CreateObject("BinomialCRRCOMServer.Pricer")
    answer = BinCRRTree.pricer(S0, K, r, T, N, sigma, isCall, _
        dividend, True)
    BinomialTreeCRROptionPrice = answer
End Function 
Function TrinomialLatticeOptionPrice( _
    ByVal S0 As Integer, _
    ByVal K As Integer, _
    ByVal r As Double, _
    ByVal T As Double, _
    ByVal N As Integer, _
    ByVal sigma As Double, _
    ByVal isCall As Boolean, _
    ByVal dividend As Double)
    Set TrinomialLattice = _
        CreateObject("TrinomialLatticeCOMServer.Pricer")
    answer = TrinomialLattice.pricer(S0, K, r, T, N, sigma, _
        isCall, dividend, True)
    TrinomialLatticeOptionPrice = answer
End Function

Here, the BinomialTreeCRROptionPrice and TrinomialLatticeOptionPrice VBA functions are defined. Similar to the BlackScholesOptionPrice function, the CreateObject function takes in the string value of BinomialCRRCOMServer.Pricer and TrinomialLatticeCOMServer.Pricer, as defined in the _reg_progid_ variable in its respective COM server.

We can compile the code by selecting Debug from the toolbar menu and clicking on Compile VBAProject:

When the code has been successfully compiled, close the Visual Basic Editor window and return to Excel to input our parameters.

Let's assume that we would like to price an option with a strike price of 50. The current underlying price is 50 with a volatility of 0.5 and does not pay dividends. The risk-free rate is 0.05 and the time to maturity is 6 months. We will start with a two-step binomial tree and trinomial lattice with N=2.

In Excel, set up the following cells and values:

We are now ready to price our option using dynamic numerical pricing with COM.

In a new row, set up the following cells and values:

Notice that in cells B12 to B14, we are calling the functions that we have defined in the VBA editor. The input values are derived from the values of cells B2 to B8. The Boolean value in B11 determines whether we are pricing a call option or a put option when calling the COM server. Since we are pricing the call options in column B, let's add another column, C to price the put options:

The formulas are the same, as in the previous table, with the exception of the isCall cell reference to C11 instead of B11. This allows us to price a put option.

Our Excel spreadsheet should look something like this:

The call option prices, as computed by the Black-Scholes model, the binomial tree with CRR parameters, and the trinomial lattice model, are 7.5636, 6.7734, and 7.1468 respectively. Likewise, the put option prices are 6.3291, 5.8685, and 6.0823 respectively.

What happens when we change the value of N to a bigger value?

We can see that the values of the binomial tree by the CRR model and the trinomial lattice model converge to the values by the Black-Scholes model as the number of tree step increases.