Error Handling in VBA

[J3nny]

Hi,

Please excuse my total ignorance in VBA, and I've tried multiple solutions myself but my fixes are making things worse.

I've found a wonderful bit of VBA here - Excel - A macro to insert pictures - which I'm using to import photographs en-mass from a folder into Excel. It works very well, but my problem is that the images can have either jpg, gif or jpeg file extensions. The photo import is working perfectly for jpg files, but what i want it to do is:

Try to import the file as a .jpg extension
If the file is imported correctly, then move on to resizing it, etc. (ResumeFromHere
If the import fails then try to import the file as a .gif extension (ErrorTryGif
If the file is imported correctly, then move on to resizing it, etc. (ResumeFromHere
If the import fails then try to import the file as a .jpeg extension (ErrorTryJpeg
If the file is imported correctly, then move on to resizing it, etc. (ResumeFromHere
If the import fails then go right to the end of the VBA where the 1ThisRow value is updated to +1 to move on to the next record, and go back to the start. (ErrorHandler

I don't think this should be too complicated but i have no idea what to put where to get this to work. Could someone please give me a clue what I need to fix in the VBA below?

JennyB

Sub GetPicture()
 Dim picname As String
    
 Dim pasteAt As Integer
 Dim lThisRow As Long
    
    lThisRow = 5
    
    Do While (Cells(lThisRow, 2) <> "")
       
        'Range("A6").Select 'This is where picture will be inserted
        'pasteAt = Cells(lThisRow, 1)
        Cells(lThisRow, 1).Select 'This is where picture will be inserted
           
        'Dim picname As String
        'picname = Range("B6") 'This is the picture name
         picname = Cells(lThisRow, 17) 'This is the picture name"
        On Error GoTo ErrorTryGif
        ActiveSheet.Pictures.Insert("//location/" & picname & ".jpg").Select 'Path to where pictures are stored
        GoTo ResumeFromHere
ResumeFromHere:
        ' This resizes the picture
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        With Selection
            '.Left = Range("A6").Left
            '.Top = Range("A6").Top
            .Left = Cells(pasteAt, 1).Left
            .Top = Cells(pasteAt, 1).Top
               
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Height = 100#
            .ShapeRange.Width = 80#
            .ShapeRange.Rotation = 0#
        End With

ErrorHandler:
           Resume Next
        lThisRow = lThisRow + 1
       
    Loop
       
    Range("A10").Select
    Application.ScreenUpdating = True
       
    Exit Sub
       
ErrNoPhoto:
    MsgBox "Unable to Find Photo" 'Shows message box if picture not found
    Exit Sub
    Range("A1").Select
ErrorTryGif:
        On Error GoTo ErrorTryJpeg
        ActiveSheet.Pictures.Insert("//location/" & picname & ".gif").Select 'Path to where pictures are stored
        GoTo ResumeFromHere
ErrorTryJpeg:
        On Error GoTo ErrorHandler
        ActiveSheet.Pictures.Insert("//location/" & picname & ".jpeg").Select 'Path to where pictures are stored
        GoTo ResumeFromHere

End Sub

 

[cerfani]

Re: Help with Error Handling in VBA

im a stickler for this kind of thing but you should present your code like this so people can read it easier...

Sub GetPicture()
 
    Dim picName As String
    Dim pasteAt As Integer
    Dim row As Long

    row = 5

    Do While (Cells(row, 2) <> "")
    
        Cells(row, 1).Select 'This is where picture will be inserted
        picName = Cells(row, 17) 'This is the picture name"

        On Error GoTo ErrorTryGif

        ActiveSheet.Pictures.Insert("//location/" & picName & ".jpg").Select 'Path to where pictures are stored

ResumeFromHere:
        ' This resizes the picture
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        With Selection
            .Left = Cells(pasteAt, 1).Left
            .Top = Cells(pasteAt, 1).Top
    
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Height = 100#
            .ShapeRange.Width = 80#
            .ShapeRange.Rotation = 0#
        End With
    
ErrorHandler:
        Resume Next
        row = row + 1
    
    Loop
    
    Range("A10").Select
    Application.ScreenUpdating = True
    
    Exit Sub
    
ErrNoPhoto:
    MsgBox "Unable to Find Photo" 'Shows message box if picture not found
    Exit Sub
    Range("A1").Select

ErrorTryGif:
    On Error GoTo ErrorTryJpeg
    ActiveSheet.Pictures.Insert("//location/" & picName & ".gif").Select 'Path to where pictures are stored
    GoTo ResumeFromHere

ErrorTryJpeg:
    On Error GoTo ErrorHandler
    ActiveSheet.Pictures.Insert("//location/" & picName & ".jpeg").Select 'Path to where pictures are stored
    GoTo ResumeFromHere

End Sub

you should indent 4 spaces when entering a sub, an if statement, a loop, a with, etc... it helps people see scopes within the subroutines... google "programming scope"

you will notice now it is easy to see exactly what code is looping and what is doing what

if i notice anything to help i will reply... i also renamed your variables for readability and consistency

 

[J3nny]

Re: Help with Error Handling in VBA

Partially fixed - turns out I'm a nugget and I stopped it from defining the pasteat variable which the code needed further down. Just need to check it again with one which sends it into the error handlers.

 

[cerfani]

Re: Help with Error Handling in VBA

oh lol, nice catch

 

[J3nny]

Re: Help with Error Handling in VBA

Got it.. Not quite indented properly, but a wee bit better than nothing.

Sub GetPicture()
 Dim picname As String
    
 Dim pasteAt As Integer
 Dim lThisRow As Long
    
    lThisRow = 5
    
    Do While (Cells(lThisRow, 2) <> "")
       
        'Range("A6").Select 'This is where picture will be inserted
        'pasteAt = Cells(lThisRow, 3)
         Cells(lThisRow, 1).Select 'This is where picture will be inserted
           
        'Dim picname As String
        'picname = Range("B6") 'This is the picture name
         picname = Cells(lThisRow, 17) 'This is the picture name"
        On Error GoTo ErrorTryGif
        ActiveSheet.Pictures.Insert("https://www.rangers.talent-sport.co.uk/ProfilePhotos/ProfilePhoto/" & picname & ".jpg").Select 'Path to where pictures are stored
        ' This resizes the picture
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        With Selection
            '.Left = Range("A6").Left
            '.Top = Range("A6").Top
            .Left = Cells(lThisRow, 1).Left
            .Top = Cells(lThisRow, 1).Top
               
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Height = 100#
            .ShapeRange.Width = 100#
            .ShapeRange.Rotation = 0#
        End With

        lThisRow = lThisRow + 1
       
    Loop
       
    Range("A10").Select
    Application.ScreenUpdating = True
       
    Exit Sub
       
ErrNoPhoto:
    MsgBox "Unable to Find Photo" 'Shows message box if picture not found
    Exit Sub
    Range("A1").Select
ErrorTryGif:
        On Error GoTo ErrorTryJpeg
        ActiveSheet.Pictures.Insert("https://www.rangers.talent-sport.co.uk/ProfilePhotos/ProfilePhoto/" & picname & ".gif").Select 'Path to where pictures are stored
        Resume Next
ErrorTryJpeg:
        On Error GoTo ErrorHandler
        ActiveSheet.Pictures.Insert("https://www.rangers.talent-sport.co.uk/ProfilePhotos/ProfilePhoto/" & picname & ".jpeg").Select 'Path to where pictures are stored
        Resume Next
ErrorHandler:
           Resume Next
End Sub

 

[Scott T]

Re: Help with Error Handling in VBA

Gaaaah. I can't copy it with the indents, sorry - I've tried a few ways now with the Preview Post option and it gets rid of them every time. :/

you should use the code tags. Press the pound sign in the posting tool bar to insert them or just type them. Put your code between the tags.
you will get something like this with out the spaces in the code tag

[ CODE ]
your code here

[ /CODE ]

 

[cerfani]

Re: Help with Error Handling in VBA

oh i actually had to indent it for you manually, then i did this... (like the above poster mentioned)

 

[J3nny]

Re: Help with Error Handling in VBA

Thanks Scott, that works great.. It's waiting before the post with the code tags appears, I don't know whether it thinks I'm posting something malicious, or it's wondering what I'm up to since I'm replying to myself every few seconds. First VBA issues, and then I can't work the forum either.

I think I might have figured it out, I'm trying to nest errors so that if it hits the first one it goes into the error handler, and then if it hits an error in that it goes onto the next error handler, but that's not working and from googling it doesn't seem like you can do that. So now I just need to figure out what the alternative is to make it try three different possible photo uploads before giving up and moving onto the next row.

 

[J3nny]

Re: Help with Error Handling in VBA

Thanks! Yep I can't work Excel, or the forum. Not a good technology day.

 

[cerfani]

Re: Help with Error Handling in VBA

you dont need resume next after the ErrorHandler: case

what is your intention with that line of code?