vba - how to force ignore/continue past 1004 error

Question

Basically I have this sub which inserts pictures into my worksheet:

ActiveCell.Select
Dim picname As String
picname = ActiveCell.Value
ActiveCell.Offset(-1, 0).Select

Dim picture
Set picture = ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\Images\" & picname & ".jpg")

I am running this sub with Application.Run inside of a loop. If there is no picture in the path folder, I get "Run-time error 1004: Unable to get the Insert property of the Picture class", and the loop stops. How do I bypass the error so that the loop can continue?

Answer 1

How do I bypass the error so that the loop can continue?

Error Handling in such cases is required when you have no other option or you are expecting an error and want to still continue. In your case, simply use a DIR command to check if the file exists and then only insert the picture. For example.

If Dir(ThisWorkbook.Path & "\Images\" & picname & ".jpg") <> "" Then
    '
    '~~> Your code to insert a picture
    '
End If

The problem with

On Error Resume Next
Set picture = ActiveSheet.Pictures.Insert....
On Error GoTo 0

is that in the later part of the code, you will have to add another line

If Not picture = Nothing then

in case you are interacting with that object. Where as in the DIR case, all your relevant code is sandwiched between IF/EndIF and you will not require additional checks.

Answer 2

insert

on error resume next

before the loop

Answer 3

The On Error construct exists for this kind of thing. It comes with various option:

On Error Goto label

Once your code encounters this statement, it will jump to label: if an error occurs. It is useful if you need to do some error handling - especially if there are multiple places where things can go wrong but they always result in the same "need to fix something" routine. You can use Err.Number or Err.Description to find out what caused the error.

On Error Resume Next

Is useful when you have a single line that might cause an error - but if it does you want to ignore and keep going. This is most likely the right thing to do here.

Don't forget to put

On Error Goto 0

After you have passed the "danger", otherwise your code will (within the scope in which you issued the initial command) continue ignoring errors. Dangerous, that.

So your code would be modified to

On Error Resume Next
Set picture = ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\Images\" & picname & ".jpg")
On Error GoTo 0