VBA - Wait for formulae dependant on PivotTable to update before proceeding

Question

As per the title, looking to wait for formulae dependent on PivotTable to update before proceeding.

The set up (in Excel 2010 workbook):

1. PivotTable constructed with
   • One xlDataField (unchanging),
   • Several xlRowFields (unchanging), and
   • One xlPageField (for changing via VBA, to increase speed of data retrieval)
2. Two-dimensional range (columns x rows) populated using formulae (including GetPivotData derived from the above PivotTable)

Processing (via VBA):

3. The above mentioned xlPageField is changed (expecting the values in the above mentioned range are updated)
4. Two-dimensional range “read” into a Variant for further processing via VBA

The process is falling over between the steps 3 and 4. The range values are not updated before being read into a Variant. It could be the PivotTable is not updating, the formulae dependant on the PivotTable are not updating, or both.

Solutions attempted to date (from various StackOverlow threads or external websites):

• PivotTables("*name*").PivotCache.BackgroundQ‌​uery = False (Getting Excel VBA to wait for external workbook links to refresh before continuing). Results in Run-time error '1004'.
• Activeworkbook.RefreshAll DoEvents (Wait until ActiveWorkbook.RefreshAll finishes - VBA) No impact.
• And a mix of:
  • ThisWorkbook.RefreshAll DoEvents
  • PivotTables("*name*").RefreshTable DoEvents
  • Application.Calculate DoEvents
  • Do Until Application.CalculationState = xlDone DoEvents Loop (Wait until Application.Calculate has finished)

Any guidance would be appreciated.

Requested code (truncated)

Dim AllocationRange As Variant
Dim SwitchHistory As PivotTable

Sub Main()
    InitialisePublicVariables

    'For each member
        GetMemberSwitchHistory
        Dim Allocations As New Dictionary: Set Allocations = GetDictionary("Allocations")
    // further processes
    'Next member
End Sub

Private Sub InitialisePublicVariables()
    Set SwitchHistory = Sheets("All Switches (clean)").PivotTables("SwitchHistory")
    AllocationRange = Range("AllocationRange")
End Sub

Private Sub GetMemberSwitchHistory()
    Dim MemberAccountNumber As String: MemberAccountNumber = Range("MemberAccountNumber").Value
    SwitchHistory.PivotFields("Member Account Number").CurrentPage = MemberAccountNumber
    SwitchHistory.RefreshTable
    DoEvents
End Sub

Regards Shannon

Answer 1

Can you refactor your code so that you can use the PivotTableUpdate event on the Worksheet to operate on the Pivot after it is updated?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

  Debug.Print "Pivot table updated - do stuff..."

End Sub

You can also use the PivtoTableChangeSync event (this may be more appropriate as it discerns changes more).

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)

  Debug.Print "Pivot table change sync..."

End Sub

More information on this event can be found here:- https://msdn.microsoft.com/EN-US/library/office/ff838251.aspx