Excel VBA: FOR...on error goto NEXT

Question

I use a For ... Next loop to insert pictures to an Excel worksheet. The name of the picture comes from column B. Sometimes, there is no corresponding picture on the hard disk. In such cases, Excel will throw an error message and stop running the codes. First, I tried "On Error Resume Next". This is not good because all the codes after the error occurs are executed. So, I tried the following code to try to avoid running codes when the picture is not found. This, however, only catches the first error. Excel still throws an error message ("unable to get the insert property of the pictures class") when the second time a picture is not found. All I want is if an error occurs, Excel would skip the rest of the code and go to the next case. How can this be done? Thanks for any help.

......
On Error GoTo gotoNext
For Each cell In rng
......
Set p = Workbooks(ActiveSheet.Parent.Name).Sheets(Sheet_to_Insert_Picture).Pictures.Insert(Path_Prefix & "\" & _
Replace(cell.Value, "/", "-") & ".jpg") 'when the picture is not found, Excel throws an error
......
gotoNext:
Err.Clear
Next

This might help stackoverflow.com/questions/7653287/vba-error-handling-in-loop — Dave
– Dave, Commented Mar 13, 2015 at 10:15
I don't use On Error Resume Next because if I do so, the codes after "Set p..." and before gotoNext will be executed. That would cause problems. — joehua
– joehua, Commented Mar 14, 2015 at 2:14

user4039065 · Accepted Answer · 2015-03-13 10:22:36Z

1

You can quickly check the existence of the image file with the Dir command. It will return the name of the file (hence a returned string length greater than zero) if it is found.

For Each cell In rng
  if cbool(len(dir(Path_Prefix & "\" & Replace(cell.Value, "/", "-") & ".jpg"))) then
    Set p = Workbooks(ActiveSheet.Parent.Name).Sheets(Sheet_to_Insert_Picture).Pictures.Insert(Path_Prefix & "\" & Replace(cell.Value, "/", "-") & ".jpg")
  end if
next cell

answered Mar 13, 2015 at 10:22

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

joehua Over a year ago

Thanks. It works. Actually, I remove cbool and use only "if len(dir(Path_Prefix & "\" & Replace(cell.Value, "/", "-") & ".jpg")) then..."

user4039065 Over a year ago

@ykya - Glad to hear you got sorted out quickly. By the strictest definition, False is equivalent to zero and True can be considered 'anything that is not false'. I use the CBool wrapper because a) it reminds me that I am determining a boolean result and b) on a worksheet, TRUE is considered 1 but in VBA True is -1. I've been bitten in the past over some badly formulated boolean criteria so I like to use the CBool wrapper.

Collectives™ on Stack Overflow

Excel VBA: FOR...on error goto NEXT

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related