VBA调用子过程失效：批量执行谷歌数据获取与格式化失败

问题描述 #

我尝试通过新建VBA模块调用两个子过程：一个是从谷歌表格获取数据的QueryGoogleSheets，另一个是对Excel模板进行格式设置的Validation_formatting。单独执行这两个子过程均正常，但批量调用时失效。我已尝试在获取谷歌数据后添加断点，现寻求排查方案。

相关代码 #

1. 谷歌表格数据获取子过程 #

Sub QueryGoogleSheets()
    Dim qt As QueryTable
    Dim url As String, key As String, gid As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Master")

    key = "key"
    gid = "111111111"
    url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key _
    & "&gid=" & gid

    Set qt = ws.QueryTables.Add(Connection:="URL;" & url, Destination:=ws.Range("A5"))

    With qt
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .Refresh
    End With
End Sub

2. 格式设置子过程 #

Sub Validation_formatting()
    Dim ws As Worksheet, Agent As Range, Status As Range, Parcel_ID As Range
    Dim lrow As Long
    
    Set ws = ThisWorkbook.Worksheets("Master")
    lrow = ws.UsedRange.Rows.Count
    Set Parcel_ID = ws.Range("A5:A" & lrow)
    Set Agent = ws.Range("M5:M" & lrow)
    Set Status = ws.Range("N5:N" & lrow)
    
    ws.Rows(5).Delete
    
    With Parcel_ID
        .NumberFormat = "0"
    End With
    
    With Agent.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Mike,William,Kevin,Dan"
    End With
    With Status.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Passed to Agent,Outreach,Negotiating,Denied,Signed"
    
    End With
    
    ws.Columns("B:D").ColumnWidth = 32.58
    ws.Columns("E:Q").AutoFit
    ThisWorkbook.Worksheets("Metrics").UsedRange.Rows.AutoFit
End Sub

3. 批量调用过程 #

Sub Calls()
    Call QueryGoogleSheets
    Call Validation_formatting
End Sub

排查与修复方案 #

• 强制同步刷新查询表：QueryTable.Refresh默认是异步执行，批量调用时格式设置可能在数据加载完成前就启动。修改QueryGoogleSheets中的.Refresh为：

  .Refresh BackgroundQuery:=False
  

  确保数据完全加载后再执行后续操作。

• 修正最后行计算逻辑：UsedRange.Rows.Count易受残留格式影响，改为基于数据列动态获取最后行：

  ' 在Validation_formatting中替换lrow的赋值
  lrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  

• 调整操作顺序：先删除行再计算范围，避免操作已删除的行。修改Validation_formatting的顺序：

  Set ws = ThisWorkbook.Worksheets("Master")
  ' 先删除行
  ws.Rows(5).Delete
  ' 再计算最后行
  lrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  ' 最后设置范围
  Set Parcel_ID = ws.Range("A5:A" & lrow)
  Set Agent = ws.Range("M5:M" & lrow)
  Set Status = ws.Range("N5:N" & lrow)
  

• 清理重复查询表：多次调用会重复创建QueryTable，导致数据混乱。在QueryGoogleSheets中添加清理逻辑：

  ' 在Set qt = ...之前添加
  For Each qt In ws.QueryTables
      If qt.Destination.Address = ws.Range("A5").Address Then
          qt.Delete
          Exit For
      End If
  Next qt
  

• 添加错误捕获定位问题：在调用过程中加入错误处理，快速定位错误点：

  Sub Calls()
      On Error GoTo ErrHandler
      Call QueryGoogleSheets
      Call Validation_formatting
      Exit Sub
  ErrHandler:
      MsgBox "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description
  End Sub
  

内容的提问来源于stack exchange，提问作者Kolev_I_N