excel - 如何正确处理错误 91 对象变量或未设置块变量

Question

当我尝试运行我的代码时，我收到错误 91，我知道会弹出错误，因为 ar 超出范围，但我仍然想忽略它。接下来我尝试了 On Error Resume，但仍然弹出错误。

Private Sub CheckBox1_Click()

Dim rng As Range, ar As Range
Application.ScreenUpdating = False
On Error GoTo errHandler
Set rng = ActiveSheet.ListObjects("test") _
        .Range.Offset(1).SpecialCells(xlCellTypeConstants)
errHandler:
    If Err.Number = 9 Then
        Exit Sub
    End If

For Each ar In rng.Areas   '<---------- Error 91 on this line

ar.Value = Application.Trim(ar)

Next ar

Application.ScreenUpdating = True

End Sub

标签： excelvba

Answer 1

你需要提防rng成为虚无。我会这样设置：

Option Explicit

Private Sub CheckBox1_Click()
   Dim rng As Range
   Dim ar As Range
   
   Application.ScreenUpdating = False
   
   On Error Resume Next
   Set rng = ActiveSheet.ListObjects("test").Range.Offset(1).SpecialCells(xlCellTypeConstants)
   On Error GoTo 0
   
   If Not rng Is Nothing Then
      For Each ar In rng.Areas
         ar.Value = Application.Trim(ar)
      Next ar
   End If
   
   Application.ScreenUpdating = True
End Sub