Macro runs slowly by launching from push button, but faster from code editor

I implemented my own Newton-Raphson solver to calculate effective APY for my investment. Attached is an example worksheet.
The push button “Calculate APY” is linked to macro “SolveAPYNR”.

When I click the button, I can see the numbers change, my mouse cursor goes back and forth between “busy” and normal, and in the bottom status bar, “calculating” flashes multiple times. It takes about a second to finish.

However, if I open “Basic” code editor from menu “Tools → Macros → Edit Macros” and launch the macro there by hitting F5 key, it runs much faster. At least 2x faster.

Why the difference in speed?

Version: (x64) / LibreOffice Community
Build ID: e114eadc50a9ff8d8c8a0567d6da8f454beeb84f
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL

contribution-example.ods (35.9 KB)

export basic:

REM  *****  BASIC  *****

Sub Main

End Sub

Sub SolveAPYNR()

    Dim oSheet As Object
    Dim oRange As Object
    Dim oCell As Object
    Dim bal_actual As Double
    Dim bal1 As Double
    Dim bal2 As Double
    Dim apy1 As Double
    Dim apy2 As Double
    Dim da As Double
    Dim db As Double
    Dim db_da As Double
    Dim e As Double
    Dim c As Integer
    Dim s As boolean

    oSheet = ThisComponent.CurrentController.ActiveSheet
    oCell_apy = oSheet.getCellRangebyName( "APY_TARGET_" & oSheet.Name ).getCellByPosition(0,0)
    oCell_bal = oSheet.getCellRangebyName( "BAL_TARGET_" & oSheet.Name ).getCellByPosition(0,0)
    oCell_bal_actual = oSheet.getCellRangebyName( "BAL_ACTUAL_" & oSheet.Name ).getCellByPosition(0,0)
    oCell_loopnum = oSheet.getCellRangebyName( "LOOPNUM_" & oSheet.Name ).getCellByPosition(0,0)
    oCell_error = oSheet.getCellRangebyName( "ERROR_" & oSheet.Name ).getCellByPosition(0,0).value
    oCell_dapy = oSheet.getCellRangebyName( "DAPY_" & oSheet.Name ).getCellByPosition(0,0).value
    bal_actual = oCell_bal_actual.value
    apy1 = oCell_apy.value
    da = apy1 * 0.001
    bal2 = oCell_bal.value
    c = 0
        bal1 = bal2
        oCell_apy.value = oCell_apy.value + da
        bal2 = oCell_bal.value
        db = bal2 - bal1
        db_da = db/da
        e = bal_actual - bal2
        da = SmoothLimit(e / db_da, oCell_apy.value)
        c = c + 1
        oCell_loopnum.value = c
        oCell_error = e
        oCell_dapy = da
        s = Abs(e) < 0.0001 Or c > 50
    Loop Until s
End Sub

Function SmoothLimit(x As Double, y As Double)
    SmoothLimit = x * (0.55-0.45*Tanh((Abs(x/y)/.1)-1))
End Function

Function Tanh(x As Double)
    Dim e1 As Double
    Dim e2 As Double
    Dim t As Double
    e1 = Exp(x)
    e2 = Exp(-1*x)
    Tanh = (e1-e2)/(e1+e2)
End Function

Your file does not contain macros.

Updated. Thanks.

Time is spent updating the screen.
Add to the beginning of the macro


to the end of the macro

Do While ThisComponent.hasControllersLocked
Msgbox "Calculation completed!"

and feel the difference. :slightly_smiling_face:


That was fast! Your response was quick, and the program speed now is impressive. Thanks a lot!