Excel: Worksheet.Calculate causes UDF to run infinitely

Question

I have written a udf returns an array that is inserted at the cell location. the udf can be long running as it reaches out to a rest service to fetch data from a database. so i added a button on my ribbon to do that.

Sub ReCalc(ribbon As IRibbonControl)
    Dim ws As Worksheet
    ActiveWorkbook.ForceFullCalculation = True
    For Each ws In ActiveWorkbook.Worksheets
        ws.Calculate
    Next
    ActiveWorkbook.ForceFullCalculation = False
End Sub

Without the ActiveWorkbook.ForceFullCalculation, worksheet.calculate does not cause the udf to run. If i simply do Application.CalculateFull, it runs the UDF - but ofcourse will also recalculate other workbooks. WHY does this work and above doesnt?

Before the UDF is called, i call this function to turn off automatic calcs and a few other things:

Sub BattenTheHatches(on_off As Boolean, Optional msg As String = "")
    Application.EnableEvents = Not on_off
    Application.DisplayAlerts = Not on_off
    If on_off Then
        Application.Calculation = xlCalculationManual
        Application.Cursor = xlWait
    Else
        Application.Calculation = xlCalculationAutomatic
        Application.Cursor = xlDefault
    End If
    'Application.Volatile Not on_off
    Application.ScreenUpdating = Not on_off
End Sub

Any help in debugging and resolving this issue would be appreciated.

Answer 1

A SWAG: The data gathered by your REST call changes values that causes a recalculation, which causes your REST call to be done again, changing values once more, repeatedly?

Possible "FIX": Do the REST call only once in 15 minutes?
Assuming it completes a single call faster than so.

Also consider https://www.google.com/search?q=excel+calculation+iterations

Related: https://support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123