Wait until cubevalues have loaded data

Question

I have a worksheet with many cells that use CUBEVALUE to load data. These cubevalues point to some common cells, which are the main parameters for the cubevalue query. With my VBA script, I change these common parameters and hence all the cubevalues are reevaluated. I want to wait until all the cubevalues are reevaluated. How can I do that?

I tried this:

Sub WaitUntilCalculationDone()
    Dim waittime As Integer
    waittime = 0
    
    Do Until Application.CalculationState = xlDone
        Application.Wait Now + TimeValue("0:00:02")
        waittime = waittime + 2
    Loop
    
    Debug.Print "waited " & CStr(waittime)
End Sub

This code waits for the Application.CalculationState to change to xlDone before finishing the script. However, it's not working as expected. How can I modify this code to wait until all the cubevalues are reevaluated? This question is related to my previous question: Saving pomegranate Bayesian Network models

You might need to use `DoEvents` as suggested here: https://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished — DecimalTurn, Jun 18 '23 at 15:55
The link you provided me helped me. DoEvents was not successful, but application.wait was the right thing to do! Thanks! — Massimo, Jun 22 '23 at 05:44

score 1 · Answer 1 · answered Jun 18 '23 at 10:57

Could you handle the application's AfterCalculate event?

You create a class (Insert->Class Module) which stores an instance of the Application (in this example, I've called the class clsApp):

Option Explicit

Private WithEvents mApp As Application

Private Sub Class_Initialize()
    Set mApp = Application
End Sub

Private Sub mApp_AfterCalculate()
    MsgBox "After calculation"
End Sub

And in your module, instance the class:

Option Explicit
Private mApp As clsApp

Public Sub RunMe()
    Set mApp = New clsApp
End Sub

Wait until cubevalues have loaded data

1 Answers1