I have written the following functions and when I try to evalute it I get #Value in excel. For a test you can use S = 50, K = 60, T = 0.25, R = 0.05, V = 0.3, B = 0.01
I started having trouble when I tried to use the excel function NORM.S.DIST()
Please Login or Register to view this content.
I think you are calling the function incorrectly. I think you need to use the "worksheet functions" collection of the application object. Follow instructions here: http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx and follow the link to the list of worksheet functions available to visual basic. It appears that norm.s.dist function called from VBA uses underscores for the dots.
Originally Posted by shg
Ahh the underscores fixed it. The difference between application and worksheet functions is with the error handling: http://www.cpearson.com/excel/callin...ionsinvba.aspx
Thank you for the help.
I think it might be called using:But I've never used it and my Excel 2010 machine is switched off right nowPlease Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
TMS -- I think the difference between the NORMSDIST and NORM.S.DIST (NORM_S_DIST) functions is the latter is the 2010+ implementation. I cannot test them extensively, but they should be essentially the same function. Excel help claims that the newer versions of these functions use improved algorithms to calculate the same quantity. The main questions would probably be around whether you need backwards compatibility to pre 2010 or if you are more of a "forward thinking" programmer and in the mood to abandon all older implementations.
@MrShorty: thanks for the insight. As you might be able to tell, I've never needed to use the function in any of its incarnations.
Cheers, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks