Excel Integration & VBA Script: wait until (re-)calculation is finished

Hello,

I have a fairly complex Excel file with a macro that automatically creates 1-n new files based on multiple source files. In the course of this, an area with DBGET cells is copied and pasted into the new file(s) as "values only".
This used to work in v11, but in v12, the INFOR.READ.VALUE formulas return #BUSY! errors for a while - and the macro ends up copying lots of "#BUSY!" to the new file instead of the actual values.
I tried to write a macro that will check the cells in the range to be copied for #BUSY! errors, wait a second and re-check as long as it finds any. But any kind of waiting seems to block re-calculation (re-calculating the workbook manually took ~30s, but when the macro was running the "#BUSY!" remained up to 10 min before I killed it). Even when I implemented waiting with a loop like this - DoEvents does not seem to work for the Add-In:
waitPeriod = Now + TimeValue("0:00:01")
While Now() < waitPeriod
DoEvents
Wend

Do you have any other ideas?

Best regards,
Kai

Find more posts tagged with

Infor EPM Discussions

Comments

martin-reischl

Hi Kai,

the problem is, the recalculation will probably work fine but it will start running when your macro finishes. There is no way to let your macro code wait until the recalculation is done.

The solution is the "Application.OnTime" function. So your macro will finish, the recalculation will be done, and then a new macro routine will kick in at a given time and do the rest of the processing. Basically, you set a timer for every macro step that needs to be done to process your requests.

The problem is, of course, defining the right timing that will give enough time for the recalculation, and handling any errors - the routines "programmed" with the OnTime function will run with no mercy, even if an error has occurred that makes the rest of the processing irrelevant.

I sent you an example how it might work.

Best Regards

Martin

Vit Holy

Hello Kai, Martin,

There is also possible solution to continue with the process once the workbook is calculated, i.e. have some robust solution which listens to calculation events, like https://learn.microsoft.com/en-us/office/vba/api/excel.application.aftercalculate or https://learn.microsoft.com/en-us/office/vba/api/excel.application.sheetcalculate.

Another option could be checking how it works with Office Scripts cause there the behavior should be much better (at least that's what I would expect). Though, the API is not that rich and mature as VBA, see https://learn.microsoft.com/en-us/office/dev/scripts/tutorials/excel-tutorial.

MikeLewis

Maybe you can listen for Cell Change events to find when "BUSY" changes to another value.

Kai Reischl

Thanks for all your suggestions - looks like I'll definitely need to split the macro into (at least) two distinct macros, then. But I should at least be able to avoid having users manually click another button with one of those triggers

Kai Reischl

Hi,
Just to bring it back into this thread, I decided to make this little sub to wait for the calculation on my active workbook, and call another sub once it's finished:

Dim cancelTime As Date

Sub CheckIfCalculationFinished()

    ' set cancelTime if it has not been set by a previous execution of this sub
    If CStr(cancelTime) = "00:00:00" Then
        cancelTime = Now + TimeValue("0:01:00")
        Debug.Print ("Set cancelTime to " + CStr(cancelTime))
    End If

    ' check if we're past cancelTime; if so, recalculation is taking too long, don't re-check anymore
    If Now > cancelTime Then
        Debug.Print (CStr(Now) & ": max. waiting time reached, quitting")
        Exit Sub
    End If

     ' check for errors
    ThisWorkbook.Activate

    On Error Resume Next 'necessary because if no error cells are found, this causes a runtime error
    Dim Rng As Range
    Set Rng = Worksheets(1).Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0
    Do While Not Rng Is Nothing

       ' if there are "BUSY" errors: call yourself again in 1s, finish sub
       ' "Error 2051" = #BUSY!
        If CStr(Rng.Value) = "Error 2051" Then
            Debug.Print ("Found Error 2051 at cell " & Rng.Address & ", checking again in 1s.")
            Application.OnTime Now + TimeValue("0:00:01"), "CheckIfCalculationFinished"
            Exit Sub
        Else
            Debug.Print ("Found different error: " & CStr(Rng.Value))
        End If

        ' check next cell(s)
        Set Rng = Worksheets(1).Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    Loop

    ' otherwise: call the copy sub, reset cancelTime
    Debug.Print ("Finished loop - no BUSY errors")
    cancelTime = "00:00:00"
    Call TestKopie

End Sub

Probably not the most elegant, but functional and fairly simple. (Now to wrangle the rest of the macro so I can do this in what was originally a sub called from within another sub called repeatedly from a while loop within that sub...)

florian-konath

Hi Vit, do you have a working example with events? For me it seems, that the event aftercalculate is fired to fast if I use Application.Calculate in VBA, have an EventHandler registered and then call a macro which requires calculated cells (e.g. setting infor formulas to values) inside the event handling method.

And maybe someone else knows: For MS SSAS the function Application.CalculateUntilAsyncQueriesDone , is something like that available or User defined functions like the Infor ones?

florian-konath

Hi Kai, thanks for posting this snippet, even it is not the most elegant, it works well. I would use Rng.Value = CVErr(2051) instead of CStr(Rng.Value) = "Error 2051" due to issues with languages.

Vit Holy

Hello @florian-konath ,

unfortunately it seems to not work anymore. I had sample which was working 2 years ago but now it is totaly unstable, the event Application.AfterCalculate is triggered 3 times
Right now I feel it is not possible.

Let me think about some possible improvements from our end to have better integration with VBA.

Kai Reischl

Hi,

that's a great suggestion, thanks - I've noticed the language is a factor, but couldn't think of a better alternative.
I've also found an issue with merged cells (specifically, multiple merged areas adjacent to each other), so I've changed it to explicitly check single cells:

Sub CheckIfCalculationFinished()

    ' set cancelTime if it has not been set by a previous execution of this sub
    If CStr(cancelTime) = "00:00:00" Then
        cancelTime = Now + TimeValue("0:01:00")
        Debug.Print ("Set cancelTime to " + CStr(cancelTime))
    End If
    
    ' check if we're past cancelTime; if so, recalculation is taking too long, don't re-check anymore
    If Now > cancelTime Then
        Debug.Print (CStr(Now) & ": max. waiting time reached, quitting")
        Exit Sub
    End If
     
     ' check for errors
    ThisWorkbook.Activate
    
    On Error Resume Next 'necessary because if no error cells are found, this causes a runtime error
    Dim Rng As Range, c As Object
    Set Rng = Worksheets(1).Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0
    If Not Rng Is Nothing Then
        'Debug.Print ("Checking area: " & Rng.Address)
        
        ' check each cell for errors
        For Each c In Rng.Cells
            'Debug.Print ("Cell: " & c.Address)
            
            ' if there are "BUSY" errors: call yourself again in 1s, finish sub
            ' "Error 2051" = #BUSY!
             If c.Value = CVErr(2051) Then
                 Debug.Print ("Found Error 2051 at cell " & c.Address & ", checking again in 1s.")
                 Application.OnTime Now + TimeValue("0:00:01"), "CheckIfCalculationFinished"
                 Exit Sub
             Else
                 Debug.Print ("Found different error: " & CStr(c.Value))
             End If
        Next c
    End If

    ' otherwise: call the copy sub, reset cancelTime
    Debug.Print ("Finished loop - no BUSY errors")
    cancelTime = "00:00:00"
    Call TestKopie

End Sub

Copyright © 2025 Infor. All rights reserved.