Thread: Using Existing NORMSDIST() Function in VBA

My entire current function for my Black Scholes Calling Price Model is

Code:

Function BlackScholesCallOption(Stock As Double, Exercise As Double, Time As Double, Interest As Double, Sigma As Double)

Dim a As Single
Dim b As Single
Dim c As Single
Dim d1 As Single
Dim d2 As Single

a = Log(Stock / Exercise)
b = (Interest + 0.5 * Sigma ^ 2) * Time
c = Sigma * (Time ^ 0.5)
d1 = (a + b) / c
d2 = d1 - Sigma * (Time ^ 0.5)
BlackScholesCallOption = Stock * NormSDist(d1) - (Exercise * Exp(-Interest * Time)) * NormSDist(d2)
    
End Function

The only problem is that my NormSDist functions that i use in the second last line is not defined. I need help defining it so that it pulls from the excel function normsdist().

Thanks in Advance.

You would need to access the worksheet function like this

Code:

application.WorksheetFunction.NormSDist(d2)

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

glade,

Please add the link to the cross post

VBA Noob