VBA自动刷新报‘424对象必需’错误，求原因分析

Hey there! Let's break down why you're hitting that Object Required error '424' and fix it step by step.

Key Causes of the Error #

• Incorrect Worksheet Reference: ThisWorkbook(4) and ThisWorkbook(5) are invalid ways to target worksheets. The ThisWorkbook object represents the entire workbook—you need to explicitly reference its Worksheets (or Sheets) collection to access a specific sheet. Skipping this collection makes VBA unable to locate the worksheet object, triggering the '424' error.
• Incomplete Application.OnTime Syntax: The placeholder "..." in your OnTime call isn't a valid macro name. VBA needs a clear, exact reference to the procedure you want to run at the specified time.

Corrected Code #

Sub autoRefreshClean()
    Dim myRangeClean As Range
    Dim myRangeVprod As Range
    Dim wsclean As Worksheet
    Dim wsvprod As Worksheet
    
    ' Fix: Use Worksheets collection to reference sheets by index (or sheet names for reliability)
    Set wsvprod = ThisWorkbook.Worksheets(4)
    Set wsclean = ThisWorkbook.Worksheets(5)
    
    ' Define your target ranges (this part works once sheets are properly referenced)
    Set myRangeClean = wsclean.Range("C2:G1059")
    Set myRangeVprod = wsvprod.Range("C2:G1059")
    
    ' Add your refresh logic here (example actions below)
    ' myRangeClean.ListObject.Refresh ' If your range is part of a table
    ' wsclean.Calculate ' Force sheet recalculation
    
    ' Fix: Set up valid daily auto-refresh with OnTime
    Dim nextRunTime As Date
    nextRunTime = TimeValue("15:22:10")
    
    ' If today's time has passed, schedule for tomorrow
    If Now > Date + nextRunTime Then
        nextRunTime = Date + 1 + nextRunTime
    End If
    
    ' Re-schedule the macro to run daily
    Application.OnTime nextRunTime, "autoRefreshClean"
End Sub

Additional Practical Tips #

• Use Sheet Names Instead of Indexes: Sheet indexes can shift if you add/remove sheets later. Referencing by name is far more reliable:

  Set wsvprod = ThisWorkbook.Worksheets("VprodSheetName")
  Set wsclean = ThisWorkbook.Worksheets("CleanSheetName")
  

• Add Error Handling: Catch issues like missing sheets to avoid unexpected crashes:

  On Error Resume Next
  Set wsvprod = ThisWorkbook.Worksheets(4)
  If wsvprod Is Nothing Then
      MsgBox "Worksheet index 4 not found!", vbExclamation
      Exit Sub
  End If
  On Error GoTo 0
  

• Cancel Scheduled Runs: If you need to stop the auto-refresh, run this quick sub:

  Sub CancelAutoRefresh()
      Dim nextRunTime As Date
      nextRunTime = TimeValue("15:22:10")
      If Now > Date + nextRunTime Then nextRunTime = Date + 1 + nextRunTime
      Application.OnTime nextRunTime, "autoRefreshClean", Schedule:=False
  End Sub
  

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