VBA Function returns "#VALUE"

[hfler]

Any ideas why this function returns #VALUE? It's try to return the vega greek for black scholes. All variables are public constants.

Function OptionVega(strike, spotprice, time, vol) As Double
volrootime = (time ^ 0.5) * vol
        
topline1 = Log(spotprice / strike)
topline2 = ((vol ^ 2) / 2) * time
topline = topline1 + topline2
d1 = topline / volrootime
If ((strike <= 0) Or (time = 0)) Then
OptionVega = 0
Else
OptionVega = Application.Exp(-0.5 * d1 * d1) * strike * Application.Sqrt((0.25 * time) / (Pi / 2))
 
End If
End Function

Thanks for any advice!

 

[Andrew Poulsom]

I haven't tested your function, but try it with WorksheetFunction.Pi.

I strongly recommend that you put Option Explicit at the top of your module. Then you will get a compile error if there are any undeclared variables.