debug -

[laguo]

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

 

[Nigel]

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

 

[Dianne]

In your code, you use ActiveCell.Range("B12") etc.

Did you mean ActiveSheet.Range("B12") ?

Using ActiveCell, B12 is relative to the activecell, whereas with
Activesheet, B12 would be B12.