ExcelVBA ThisWorkbook.RefleshAllで更新待ちしてくれない対策

サンプル。指定シートの更新を行ってから待つ

クラス CQtEvents

Option Explicit

Private WithEvents mQryTble As QueryTable
Private mRefreshed As Boolean
Private mCallback As String

Public Property Set QryTble(ByVal QryTable As QueryTable): Set mQryTble = QryTable: End Property
Public Property Get QryTble() As QueryTable: Set QryTble = mQryTble: End Property

Public Property Let Refreshed(ByVal paramRefreshed As Boolean): mRefreshed = paramRefreshed: End Property
Public Property Get Refreshed() As Boolean: Refreshed = mRefreshed: End Property

Public Property Let Callback(ByVal SpecifiedProcName): mCallback = SpecifiedProcName: End Property

Private Sub Class_Initialize()
    mRefreshed = False
End Sub

Private Sub mQryTble_BeforeRefresh(Cancel As Boolean)
    mRefreshed = False
    Debug.Print "*** mQryTble_BeforeRefresh(): Refreshed=" & Refreshed
End Sub

Private Sub mQryTble_AfterRefresh(ByVal Success As Boolean)
    mRefreshed = True
    Debug.Print "*** mQryTble_AfterRefresh(): Refreshed=" & Refreshed, "Success=" & Success
    If mCallback <> "" Then Application.Run mCallback, Success
End Sub

メインコード

    Dim TargetTable As ListObject: Set TargetTable = Sheets("hogehoge").ListObjects(1)
    TargetTable.DataBodyRange.Rows.Delete ' いったんテーブルのデータを全てクリア
    
    Dim TargetQueryTable As QueryTable: Set TargetQueryTable = TargetTable.QueryTable
    Dim classQtEvents As CQtEvents: Set classQtEvents = New CQtEvents
    Set classQtEvents.QryTble = TargetQueryTable
    
    Dim BeforeTime: BeforeTime = Timer
    Debug.Print "[Before] TargetQueryTable.Refreshing:" & TargetQueryTable.Refreshing, "Rows.Count: " & TargetTable.Range.Rows.Count
    
    classQtEvents.QryTble.Refresh BackgroundQuery:=False ' クエリ更新を待つ
    
    'Debug.Print "[After]  TargetQueryTable.Refreshing:" & TargetQueryTable.Refreshing, "Rows.Count: " & TargetTable.Range.Rows.Count
    'Debug.Print "[Completed] " & Format(Timer - BeforeTime, "00:00:00.000")

ネタ元

gist.github.com