Excel VBA 出错时恢复下一个返回值位置不正确

Question

我正在使用URL将图片放置在工作表中。该代码效果很好，除了"下次出错时恢复"将前一个单元格的（良好）值放在发生错误的单元格中，而不是它应该的单元格（向上一行）。然后，它继续将值放置在它们所属的位置，直到出现另一个错误。

我尝试将"下次错误恢复"放在代码的不同区域，但无法解决问题。这是错误处理的位置问题，还是我需要更好的错误处理程序？

谢谢安 迪

Sub InsertPic()
  On Error Resume Next 
  Dim pic As String 
  Dim myPicture As Picture 
  Dim rng As Range 
  Dim cl As Range 
Set rng = Range("F2:F1131")
For Each cl In rng
pic = cl.Offset(0, -1)
Set myPicture = ActiveSheet.Pictures.Insert(pic)
With myPicture
    .ShapeRange.LockAspectRatio = msoFalse
    .Width = cl.Width
    .Height = cl.Height
    .Top = Rows(cl.Row).Top
    .Left = Columns(cl.Column).Left
End With
Next
End Sub

Answer 1

如果您需要检查 URL 是否存在，那么也许一个辅助函数就足够了？

Sub InsertPic()
  Dim pic As String 
  Dim myPicture As Picture 
  Dim rng As Range 'E3:E1132
  Dim cl As Range 'iterator
  Set rng = Range("F2:F1131")
  For Each cl In rng
    pic = cl.Offset(0, -1)
    if URLExists(pic) then
      Set myPicture = ActiveSheet.Pictures.Insert(pic)
      With myPicture
        .ShapeRange.LockAspectRatio = msoFalse
        .Width = cl.Width
        .Height = cl.Height
        .Top = Rows(cl.Row).Top
        .Left = Columns(cl.Column).Left
      End With
    end if
  Next
End Sub
'ref: http://www.mrexcel.com/forum/excel-questions/567315-check-if-url-exists-so-then-return-true.html
Function URLExists(url As String) As Boolean
    Dim Request As Object
    Dim ff As Integer
    Dim rc As Variant
    On Error GoTo EndNow
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    With Request
      .Open "GET", url, False
      .Send
      rc = .StatusText
    End With
    Set Request = Nothing
    If rc = "OK" Then URLExists = True
    Exit Function
EndNow:
End Function