How to include Background Query Completing time in timing my macro

[Tardisgx]

Currently my code is basically as below but the finishing time pops up and the background queries are still running.

I want them to run but I want the timer to consider them instead of ignoring them.

Sub CalculateRunTime_Minutes()
'PURPOSE: Determine how many minutes it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim StartTime As Double
Dim MinutesElapsed As String

'Remember time when macro starts
  StartTime = Timer

'*****************************
'All Pivot Tables and equations Refresh
    ActiveWorkbook.RefreshAll
    DoEvents
'*****************************

'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

End Sub

 

[CalcSux78]

When you use RefreshAll, tje code refreshes everything, but continues the code before completing the refresh. If you want to see how long it takes to refresh each one, you could use a loop to time the refresh of each table.

Sub RefreshAllTables()
Dim sh As Worksheet
Dim pt As PivotTable
Dim StartTime As Double
Dim MinutesElapsed As String


'Remember time when macro starts
  StartTime = Timer
  
For Each sh In ActiveWorkbook.Sheets
    sh.Activate
    For Each pt In sh.PivotTables
        pt.RefreshTable
    Next pt
Next sh


'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")


'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation


End Sub

 

[John_w]

Disable background refresh via the UI.

 

[Tardisgx]

Cool thank you very much

 

[Tardisgx]

Cool did this also in combination with the other answer.