vba - 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