excelvbafunctionuser-defined-functionsexcel-udf

Function is called without being called and stops the main macro


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

Solution

  • 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