I am new to VBA..i wrote the following code..it basically take 5
variables and use as imputs for a a formula.
i won't work for me..can someone help see what is wrong. Thanks for
your help!

Option Explicit

Private Sub CommandButton1_Click()

Dim Spot As Double
Dim strike As Double
Dim RF As Double
Dim Vol As Double
Dim YTM As Double

Dim Call_PX As Variant
Dim d1 As Variant
Dim d2 As Variant
Dim CallPx As Variant

On Error GoTo EndMacro

Spot = ActiveCell.Range("B12").Value
strike = ActiveCell.Range("B13").Value
RF = ActiveCell.Range("B14").Value
YTM = ActiveCell.Range("B15").Value
Vol = ActiveCell.Range("B16").Value

d1 = (Log(Spot / strike) + (RF + (0.5 * Vol ^ 2)) * YTM) / (Vol * YTM
^ 0.5)
d2 = d1 - Vol * YTM ^ 0.5
CallPx = Spot * WorksheetFunction.NormSDist(d1) - strike * Exp(-RF *
YTM) * WorksheetFunction.NormSDist(d2)
Range("B18").Value = CallPx
EndMacro:
End Sub

It is valid but any error will throw and error and exit the procedure
without a by your leave.

Your fomulae is complex and consequently your input values must comply with
some rules about their valid ranges, dependencies with other values etc...
I recommend that you build these into the code so that each input error can
be detected before you get the blank response.

At the very least try check the intermediate formulae for values d1, and d2,
you are going to get overflows and not know it, the way it stands at
present.

Cheers
Nigel

wrote in message
m...
I am new to VBA..i wrote the following code..it basically take 5
variables and use as imputs for a a formula.
i won't work for me..can someone help see what is wrong. Thanks for
your help!

Option Explicit

Private Sub CommandButton1_Click()

Dim Spot As Double
Dim strike As Double
Dim RF As Double
Dim Vol As Double
Dim YTM As Double

Dim Call_PX As Variant
Dim d1 As Variant
Dim d2 As Variant
Dim CallPx As Variant

On Error GoTo EndMacro

Spot = ActiveCell.Range("B12").Value
strike = ActiveCell.Range("B13").Value
RF = ActiveCell.Range("B14").Value
YTM = ActiveCell.Range("B15").Value
Vol = ActiveCell.Range("B16").Value

d1 = (Log(Spot / strike) + (RF + (0.5 * Vol ^ 2)) * YTM) / (Vol * YTM
^ 0.5)
d2 = d1 - Vol * YTM ^ 0.5
CallPx = Spot * WorksheetFunction.NormSDist(d1) - strike * Exp(-RF *
YTM) * WorksheetFunction.NormSDist(d2)
Range("B18").Value = CallPx
EndMacro:
End Sub




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---