option pricing with range

[bines]

Hello ,

I have function is written before 11 years
The xRange As Range, In the function without range was k

Function XCallDol(S, q, r, SDiv As Range, multiply, T As Single, xRange As Range, OptQnt)
Counter1 = 0
XCallDol = 0
For x = 1 To xRange.Rows.Count
MyExp_R = Exp(1) ^ -(q* T)
MyExp_ar = Exp(1) ^ -(r* T)
MyLan = Log((S * MyExp_R) / xRange.Cells(x))
d1 = (MyLan + (r+ 0.5 * SDiv.Cells(x) ^ 2) * T) / (SDiv.Cells(x) * Sqr(T))
d2 = d1 - SDiv.Cells(x) * Sqr(T)
Nofd1 = Application.NormSDist(d1)
Nofd2 = Application.NormSDist(d2)

Counter1 = Counter1 + 1
XCallDol_Here = (S * MyExp_R * Nofd1 - MyExp_ar * xRange.Cells(x) * Nofd2) * multiply* OptQnt(Counter1)
XCallDol = XCallDol + XCallDol_Here
Next
End Function

I want to change two of the functions to the functions on range like the previous function I am not successful to do this



Option Explicit



Function Black_Scholes_Call(S, K, r, SDiv , q, T)
'
' Inputs are S = initial stock price
' K = strike price
' r = risk-free rate
' SDiv = volatility
' q = dividend yield
' T = time to maturity
'
Dim d1, d2, N1, N2

d1 = (Log(S / K) + (r - q + 0.5 * SDiv * SDiv ) * T) / (SDiv * Sqr(T))
d2 = d1 - SDiv * Sqr(T)
N1 = Application.NormSDist(d1)
N2 = Application.NormSDist(d2)
Black_Scholes_Call = Exp(-q * T) * S * N1 - Exp(-r * T) * K * N2

End Function

Function Black_Scholes_Call_Gamma(S, K, r, SDiv , q, T)
'
' Inputs are S = initial stock price
' K = strike price
' r = risk-free rate
' SDiv = volatility
' q = dividend yield
' T = time to maturity
'
Dim d1, d2, N1, N2, nd1
d1 = (Log(S / K) + (r - q + 0.5 * SDiv * SDiv ) * T) / (SDiv * Sqr(T))
d2 = d1 - SDiv * Sqr(T)
N1 = Application.NormSDist(d1)
N2 = Application.NormSDist(d2)
nd1 = Exp(-d1 * d1 / 2) / Sqr(2 * Application.pi)
Black_Scholes_Call_Gamma = Exp(-q * T) * nd1 / (S * SDiv * Sqr(T))
End Function

 

[bines]

Attached file​