I have this Sub which does 2 basic calculations in a loop and then I have a UDF (see below).
My issue: When I run the Sub BSM_Table
it calls the function at this line: If Cells(i, 2) > 0 And Cells(i, 3) > 0 Then
and the sub stop.
I guess it is because these cells: Cells(i, 2)
and Cells(i, 3)
are used by the function so whenever these cells change the function recalculate itself automatically.
Is there a way to call the function only when I want ?
Sub BSM_Table()
Dim i As Long
Dim LastRow As Long, LastRow2 As Long
Dim Firm_Value As Double, Face_Value As Double, Rate As Double, Volatility_value As Double, Time As Double
With Sheets("BlackScholeMerton")
LastRow = Sheets("BlackScholeMerton").Range("B42:B" & Rows.Count).End(xlDown).Row
For i = 42 To LastRow
' If Cells(i, 2) > 0 And Cells(i, 3) > 0 Then
Range("E" & i).Value = Range("B" & i).Value + Range("C" & i).Value
x = Cells(i, 4) * Cells(i, 2)
y = Cells(i, 5)
Cells(i, 6) = x / y
' End If
Next i
End sub
My function:
Function DefProb(Firm_Value As Double, Face_Value As Double, Rate As Double, Volatility_value As Double, Time As Double) As Double
Dim t1 As Double, t2 As Double, t3 As Double
Dim d2 As Double, ta As Double, tb As Double
Dim V As Double, S As Double, x As Double
Dim r As Double, ti As Double
S = Firm_Value
x = Face_Value
r = Rate
V = Volatility_value
ti = Time
t1 = Log(S / x) + (r + V ^ 2 / 2) * ti
t2 = V * Sqr(ti)
t3 = Log(S / x) + (r - V ^ 2 / 2) * ti
d2 = t3 / t2
DefProb = WorksheetFunction.NormSDist(-d2)
End Function
You can try adding .Value
: If Cells(i, 2).Value > 0 And Cells(i, 3).Value > 0 Then
But the option that will really work is Application.Calculation = xlCalculationManual
. This line stops automatic recalculation (makes it manual).
To enable it again use Application.Calculation = xlCalculationAutomatic