MS Excel VBA中的错误处理

首先,您有一个只有3个迭代的for循环,并且您有一个用于3个循环的开关盒!!为什么你不能把你的公共代码移到一个新的函数上,然后三次调用它呢?

每个错误都有一个唯一的数字(如果是VBA错误,如下标超出范围等,或者是说明(如果是通用数字,如1004)和其他Office错误)。您需要检查错误号,然后决定如何继续,如果要跳过该部件或解决此问题。

请看一下这段代码。我已经把你的Comon代码移到一个新的函数中,在这个函数中我们将调整形状的大小。 如果形状丢失,那么我们将返回false,并移动到下一个形状。

'i am assuming you have defined drnme, nme as strings and d1 as integer
'if not please do so
Dim drnme As String, nme As String, d1 As Integer

dl = 20

drnme = kt + " 90"
nme = "door90"
If ResizeShape(drnme, nme, d1) Then
    d1 = d1 + 160
End If
'Just call 
'ResizeShape(drnme, nme, d1)
'd1 = d1 + 160
'If you don't care if the shape exists or not to increase d1
'in that case whether the function returns true or false d1 will be increased

drnme = kt + " dec"
nme = "door70" 'decorative glazed'
If ResizeShape(drnme, nme, d1) Then
    d1 = d1 + 160
End If

drnme = kt + " gl"
nme = "door80" 'plain glazed'
If ResizeShape(drnme, nme, d1) Then
    d1 = d1 + 160
End If

ActiveSheet.Shapes("Txtdoors").Select
Selection.Characters.Text = kt & ":   " & kttxt
Worksheets("kts close").Protect Password:="UPS"


End Sub

'resizes the shape passed in.
'if the shape does not exists then returns false.
'in that case you can skip incrementing d1 by 160

Public Function ResizeShape(drnme As String, nme As String, d1 As Integer) As Integer
On Error GoTo ErrorHandler
Dim sh As Shape
Set sh = Worksheets("kitchen doors").Shapes(drnme)
sh.Copy
ActiveSheet.Paste
Selection.ShapeRange.Name = nme
Selection.ShapeRange.Top = 50
Selection.ShapeRange.Left = dl
Selection.ShapeRange.Width = 150
Selection.ShapeRange.Height = 220
Exit Function
ErrorHandler:
'Err -2147024809 will be raised if the shape does not exists
'then just return false
'for the other errors you can examine the number and go back to next line or the same line
'by using Resume Next or Resume
'not GOTO!!
If Err.Number = -2147024809 Or Err.Description = "The item with the specified name wasn't found." Then
    ResizeShape = False
    Exit Function
End If
End Function

我知道这是一个旧的帖子,但也许这会帮助其他人。 使用原始代码但替换 ErrorHandler: 转到25

具有

ErrorHandler: 简历25

对不起,每个人,我都锻炼过了 一 解决方案。清除错误代码不起作用,所以我不得不使用许多goto来代替,现在代码也起作用了(即使这不是最优雅的解决方案)。以下是我的新代码:

dl = 20
For dnme = 1 To 3
BeginLoop:
Select Case dnme
Case 1
drnme = kt + " 90"
nme = "door90"
drnme1 = nme
Case 2
drnme = kt + " dec"
nme = "door70" 'decorative glazed'
Case 3
drnme = kt + " gl"
nme = "door80" 'plain glazed'
Case Else
GoTo EndLoop
End Select

On Error GoTo ErrorHandler
Set sh = Worksheets("kitchen doors").Shapes(drnme)
sh.Copy
ActiveSheet.Paste
    Selection.ShapeRange.Name = nme
    Selection.ShapeRange.Top = 50
    Selection.ShapeRange.Left = dl
    Selection.ShapeRange.Width = 150
    Selection.ShapeRange.Height = 220
25
dl = dl + 160
Next dnme

EndLoop:
     ActiveSheet.Shapes("Txtdoors").Select
    Selection.Characters.Text = kt & ":   " & kttxt
 Worksheets("kts close").Protect Password:="UPS"

 Exit Sub
ErrorHandler:
Err.Clear
dl = dl + 160
dnme = dnme + 1
Resume BeginLoop
End Sub

你不能有两个不同的 ShapeRange 相同名称的对象 Worksheet . 有没有可能 Shape 复制的对象是 肖伯兰 和新的名字一样 肖伯兰 正在创建的对象?

你不应该用gotos来进出循环!!!!

如果您想自己处理错误,可以使用如下方法:

''turn off error handling temporarily
On Error Resume Next

''code that may cause error

If Err.Number <> 0 then
  ''clear error
  Err.clear
  ''do stuff to handle error
End if

''resume error handling
On Error GoTo ErrorHandler

编辑-试试这个- 没有杂乱的哥托斯

  dl = 20
  For dnme = 1 To 3

    Select Case dnme
      Case 1
        drnme = kt + " 90"
        nme = "door90"
        drnme1 = nme

      Case 2
        drnme = kt + " dec"
        nme = "door70" 'decorative glazed'

      Case 3
        drnme = kt + " gl"
        nme = "door80" 'plain glazed'

    End Select

    'temporarily disable error handling'
    On Error Resume Next
    Set sh = Worksheets("kitchen doors").Shapes(drnme)

    'save error'
    ErrNum = Err.Number

    'reset error handling'
    On Error GoTo ErrorHandler

    If ErrNum = 0 Then

      sh.Copy

      ActiveSheet.Paste

      Selection.ShapeRange.Name = nme
      Selection.ShapeRange.Top = 50
      Selection.ShapeRange.Left = dl
      Selection.ShapeRange.Width = 150
      Selection.ShapeRange.Height = 220

    End If

    dl = dl + 160

  Next dnme

  ActiveSheet.Shapes("Txtdoors").Select
  Selection.Characters.Text = kt & ":   " & kttxt
  Worksheets("kts close").Protect Password:="UPS"


NormalExit:
  Exit Sub

ErrorHandler:
  MsgBox "Error Occurred: " & Err.Number & " - " & Err.Description
  Exit Sub

End Sub