Hey everyone

I`m trying to calculate the implied volatility (could only be found with the Newton Raphson Algorithm) of Call/Put Options and for that I`ve to use VBA. The thing is that in the VBA code I somehow have to tell the programme to use one type of formula for the call and another for the put option. Correspondingly, the 2 formulas need to be written in the VBA code and the variables in them have to be defined. In my case it`s the Black-Scholes formula and I wrote it explicitly in the VBA code. I tried to formulated some code but it seems not to work.
My mistake is probably with the defining of the variables....actually in my Excel worksheet I`ve a column for each one of the variables and the column is called exactly like the variable. The range is also given in the code, because I`m not sure how the VBA will know up to where to calculate the values.
Furthermore, after I`ve written the code I`ve pressed F5 which is supposed to show me the value of the implied volatility in the corresponding column in the Excel worksheet but nothing appeared there. Maybe I just don`t know what to do after I write my code, I`m reading the whole time about macros in Excel/VBA but I`ve no idea to what extent I need them in this case.

So it would be more than great to hear from you.........and to read your comments on my code. Thank you in advance for the help, guys!

Here`s my VBA code:

EXERCISE_PRICE As Double, MATURITY As Double, Interest_RATE As Double, STOCK_PRICE As Double, OPTION_PRICE As Double, sType As String) As Double
'check for arbitrage violations:
'if price at almost zero volatility greater than price, return 0
Dim IMPLIED_VOLATILITY_Low As Double
Dim SETTLEMENT_PRICE As Double
Dim IMPLIED_VOLATILITY As Double
IMPLIED_VOLATILITY_Low = 0.0001
IMPLIED_VOLATILITY = -1
'calculate the price of the volatility was almost zero, to see what would be the minimum price
Select Case sType
Case "C": OPTION_PRICE = STOCK_PRICE * NormSDist((Ln(STOCK_PRICE / EXERCISE_PRICE) + (Interest_RATE + 0.5 * IMPLIED_VOLATILITY_Low ^ 2) * MATURITY) / (IMPLIED_VOLATILITY_Low * SQRT(MATURITY))) - (EXERCISE_PRICE * Exp(-MATURITY * Interest_RATE) * NormSDist(Ln(STOCK_PRICE / EXERCISE_PRICE) + (Interest_RATE + 0.5 * IMPLIED_VOLATILITY_Low ^ 2) * MATURITY) / (IMPLIED_VOLATILITY_Low * SQRT(MATURITY) - IMPLIED_VOLATILITY_Low * SQRT(MATURITY)))
Case "P": OPTION_PRICE = (EXERCISE_PRICE * Exp(-MATURITY * Interest_RATE)) * NormSDist(-((((Ln(STOCK_PRICE / EXERCISE_PRICE) + ((Interest_RATE + 0.5 * IMPLIED_VOLATILITY_Low ^ 2) * MATURITY)) / (IMPLIED_VOLATILITY_Low * SQRT(MATURITY))) - IMPLIED_VOLATILITY_Low * SQRT(MATURIYT)))) - STOCK_PRICE * NormSDist(-(((Ln(STOCK_PRICE / EXERCISE_PRICE) + (Interest_RATE + 0.5 * IMPLIED_VOLATILITY_Low ^ 2) * MATURITY)) / (IMPLIED_VOLATILITY_Low * SQRT(MATURITY))))
OPTION_PRICE = Main_Data.O2: O202573.Value
EXERCISE_PRICE = Main_Data.N2: N202573.Value
Interest_RATE = Main_Data.L2: L202573.Value
STOCK_PRICE = Main_Data.M2: M202573.Value
MATURITY = Main_Data.K2: K202573.Value

If SETTLEMENT_PRICE > OPTION_PRICE Then
IMPLIED_VOLATILITY = 0
SETTLEMENT_PRICE = Main_Data.Q2: Q202573.Value
IMPLIED_VOLATILITY = Main_Data.P2: P202573
Else
'simple binomial search for the implied volatility.
'relies on the value of the option increasing in volatility
ConstACCURACY = 0.00001 'make this smaller for higher accuracy
ConstMAX_ITERATIONS = 100
ConstHIGH_VALUE = 10000000000#
Const ERROR = -1E+40
'want to bracket sigma.first find a maximum sigma by finding a sigma
'with a estimated price higher than the actual price.
Dim IMPLIED_VOLATILITY_high As Double
IMPLIED_VOLATILITY = 0.4
Select Case sType
Case "C": OPTION_PRICE = STOCK_PRICE * NormSDist((Ln(STOCK_PRICE / EXERCISE_PRICE) + (Interest_RATE + 0.5 * IMPLIED_VOLATILITY_high ^ 2) * MATURITY) / (IMPLIED_VOLATILITY_high * SQRT(MATURITY))) - (EXERCISE_PRICE * Exp(-MATURITY * Interest_RATE) * NormSDist(Ln(STOCK_PRICE / EXERCISE_PRICE) + (Interest_RATE + 0.5 * IMPLIED_VOLATILITY_high ^ 2) * MATURITY) / (IMPLIED_VOLATILITY_high * SQRT(MATURITY) - IMPLIED_VOLATILITY_high * SQRT(MATURITY)))
Case "P": OPTION_PRICE = (EXERCISE_PRICE * Exp(-MATURITY * Interest_RATE)) * NormSDist(-((((Ln(STOCK_PRICE / EXERCISE_PRICE) + ((Interest_RATE + 0.5 * IMPLIED_VOLATILITY_high ^ 2) * MATURITY)) / (IMPLIED_VOLATILITY_high * SQRT(MATURITY))) - IMPLIED_VOLATILITY_high * SQRT(MATURIYT)))) - STOCK_PRICE * NormSDist(-(((Ln(STOCK_PRICE / EXERCISE_PRICE) + (Interest_RATE + 0.5 * IMPLIED_VOLATILITY_high ^ 2) * MATURITY)) / (IMPLIED_VOLATILITY_high * SQRT(MATURITY))))

End Select
Do While (SETTLEMENT_PRICE < OPTION_PRICE)
IMPLIED_VOLATILITY_high = 2# * IMPLIED_VOLATILITY_high 'keep doubling.
Select Case sType
Case "C": OPTION_PRICE = STOCK_PRICE * NormSDist((Ln(STOCK_PRICE / EXERCISE_PRICE) + (Interest_RATE + 0.5 * IMPLIED_VOLATILITY_high ^ 2) * MATURITY) / (IMPLIED_VOLATILITY_high * SQRT(MATURITY))) - (EXERCISE_PRICE * Exp(-MATURITY * Interest_RATE) * NormSDist(Ln(STOCK_PRICE / EXERCISE_PRICE) + (Interest_RATE + 0.5 * IMPLIED_VOLATILITY_high ^ 2) * MATURITY) / (IMPLIED_VOLATILITY_high * SQRT(MATURITY) - IMPLIED_VOLATILITY_high * SQRT(MATURITY)))
Case "P": OPTION_PRICE = (EXERCISE_PRICE * Exp(-MATURITY * Interest_RATE)) * NormSDist(-((((Ln(STOCK_PRICE / EXERCISE_PRICE) + ((Interest_RATE + 0.5 * IMPLIED_VOLATILITY_high ^ 2) * MATURITY)) / (IMPLIED_VOLATILITY_high * SQRT(MATURITY))) - IMPLIED_VOLATILITY_high * SQRT(MATURIYT)))) - STOCK_PRICE * NormSDist(-(((Ln(STOCK_PRICE / EXERCISE_PRICE) + (Interest_RATE + 0.5 * IMPLIED_VOLATILITY_high ^ 2) * MATURITY)) / (IMPLIED_VOLATILITY_high * SQRT(MATURITY))))
End Select
If (IMPLIED_VOLATILITY_high > HIGH_VALUE) Then
GoTo ReturnValue 'return ERROR;// panic, something wrong.
End If
End Function