Pause VBA until #GETTING DATA Power Pivot is complete

Question

I have an Excel workbook with a large Data Model connected to two slicers. I need to cycle through every entry in the slicer, allow the workbook to catch up on loading a large number of cube formulas, then copy one particular worksheet over into another.

I've written VBA which does all of this, but I can't get the VBA to wait for the workbook to finish uploading before it continues with the rest of the script.
I can rule out background refresh-based solutions, which don't apply to OLAP.
Various solutions I've found online which recommend waiting for calculations to be complete don't to work. The script barrels through those lines.

The only solution I've seen which seems to apply involved identifying every cell which would be updated as a result of the slicer change and looping through them until they no longer say #GETTING DATA.

For my workbook, this would be hundreds of cells to identify and check and feels unsustainable.

Applcation.Wait waits for the selected amount of time during which the workbook pauses getting data.

Setting different values of a slicer connected to a Data Model and automating some output feels like it should be such a common task that we have a solution for.

Running Office 365

Sub generate_all_forecasts()
'Cycle through all products and push forecast values to fcst_output'
Application.ScreenUpdating = True

Dim SC_products As SlicerCache
Dim selection, product_array As Variant
Dim push As Boolean

Set SC_products = ThisWorkbook.SlicerCaches("Slicer_PRODUCT_GROUPING_WRITTEN") 'The product slicer on the Inputs worksheet'

product_array = Range("product_array") 'Named range product_array on Tbl_Codes worksheet'

For Each p In product_array 'For each product'

push = WorksheetFunction.Index(Range("fcst_push_array"), WorksheetFunction.Match(p, product_array, 0)) 'Check if the product has been selected for this run'

If push = True Then
    
    If p = "Major Medical Plan" Then  'If "Major Medical" '
    selection = Array("[Query1 1].[PRODUCT_GROUPING_WRITTEN].&[Major Medical Plan - CMM]", _
    "[Query1 1].[PRODUCT_GROUPING_WRITTEN].&[Major Medical Plan - GMM]")  'selection will be both CMM and GMM'
    Else
    selection = Array("[Query1 1].[PRODUCT_GROUPING_WRITTEN].&[" & p & "]")  'Otherwse selection is the single product'
    End If

    SC_products.VisibleSlicerItemsList = selection  'Change slicer to current selection'

    'This is where the script needs to pause until #GETTING DATA is complete'

    Application.Run "push_to_output"  'Run the forecast update macro'
    
End If

Next p

Worksheets("Fcst_Output").Range("B2:B1381").Value = "" 'Clear prior month's comments'

Application.ScreenUpdating = True
End Sub

Solutions which have not worked:

• Wait time after change slicer in power pivot
• Getting vba to wait before proceeding
• Wait until Application.Calculate has finished

The solution I really don't want to use: Force VBA to wait until power pivot finishes refreshing

Answer 1

Thanks to Tragamor for linking to a thread where they already had a working answer. I included the following immediately after the slicer selection in my VBA and it appears to properly wait until all data fetching is complete:

    Dim CalculationState As Long
    With Application
        CalculationState = .Calculation
        .Calculation = xlCalculationAutomatic
        .CalculateUntilAsyncQueriesDone
        Do Until .CalculationState = xlDone
            DoEvents
        Loop
        .Calculation = CalculationState
    End With