Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Resume at a certain line of code! (Excel 2000 >)

    VBA help says it is possible to resume a macro at a certain line, but they do not show how to do it. Can I ask for your assistance.

    EG:

    Sub Test()
    On error goto EH
    1-----
    2----- (Error occurs here!)
    3----- (Resume must skip this line!)
    4-----
    5-----
    Exit Sub
    EH:
    Msgbox "Blah blah"
    Resume [Line 4]
    End Sub
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resume at a certain line of code! (Excel 2000 >)

    Insert a label before "line 4":

    Sub Test()
    On error goto EH
    1-----
    2----- (Error occurs here!)
    3----- (Resume must skip this line!)
    ExitHere:
    4-----
    5-----
    Exit Sub
    EH:
    Msgbox "Blah blah"
    Resume ExitHere
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resume at a certain line of code! (Excel 2000 >)

    Tx for your response Hans, but I will rephrase the question.

    I would like the macro to resume three lines after an error occured. Using the label technique in your reply is great is you are anticipating an error to occur on a specific line.

    Based on the EG: in my previous post: The macro opens, prints and closes several workbooks, but if any one of the workbooks does not exist, (and this I won't know, so error can occur anywhere!) it must resume with the next workbook....
    Sub Test()
    On error goto EH
    1Open WB1
    2 Print WB1
    3 Close WB1
    4 Open WB2 (If error occurs because WB2 does not exist, it must resume at line 7)
    5 Print WB2
    6 Close WB2
    7 Open WB3 (IF error occurs at 4, skip line 5 and 6 and resume here!)
    8 Print WB3
    9 Close WB3
    10 etc

    Exit Sub
    EH:
    Msgbox "Blah blah"
    Resume [7] (The help in VBA says something about resuming at a line, but they don't explain how??)
    End Sub

    Thanx
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resume at a certain line of code! (Excel 2000 >)

    You can put line numbers in your code, for example

    37 Workbooks.Open "Test.xls"

    and use

    Resume 37

    to resume at the line with line number 37, but the line number must be a constant., you cannot use a variable:

    Resume lngNumber

    will not work. Instead, you have to keep track of where you are, and use that information:

    Sub Test()
    Dim lngPosition As Long
    On Error GoTo EH

    lngPosition = 1
    Open WB1
    Print WB1
    Close WB1

    ResumeAfter1:
    lngPosition = 2
    Open WB2 (If error occurs because WB2 does not exist, it must resume at line 7)
    Print WB2
    Close WB2

    ResumeAfter2:
    lngPosition = 3
    Open WB3 (IF error occurs at 4, skip line 5 and 6 and resume here!)
    Print WB3
    Close WB3

    ...

    Exit Sub

    EH:
    Msgbox "Blah blah"
    Select Case lngPosition
    Case 1
    Resume ResumeAfter1
    Case 2
    Resume ResumeAfter2

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Resume at a certain line of code! (Excel 2000 >)

    Hi Rudi,
    The only method of resuming at a certain line (other than the next one via Resume Next) is to use the labelling method Hans spoke of (edit to add: as Hans points out, you can use line numbering, but this would involve specific error-trapping routines at multiple points within your code, which will end up hard to maintain). You cannot say in an error handler something to the effect of "if there's an error, resume three lines after the error occurred". Nor would that be a good idea because if an error occurred where you were not expecting it, your code would not resume where you expected it to either. In your example, the simplest thing would be to either check the file exists before you try and open it or assign the result of the Open method to a workbook variable. Then you can trap the specific error of the workbook not existing and bypass your Print and Close code by enclosing it within an <pre>If Not Wbk is Nothing then...</pre>

    type statement.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resume at a certain line of code! (Excel 2000 >)

    Hans and Rory, thankyou VERY much.

    Your advice is excellent. This was a head scratcher for me. Hans, the example you give is great, and I suppose, in retrospect, quite logical....that is to keep track of the current position in code, and resume from there!

    Rory, even though you disagree regarding the ability to resume with skipping lines, I still think this could be viable in some macros that are fairly repetative in code structure. I think it would be great to have the ability to say: Resume [3] or even : Resume Next Next Next !!

    Anyways...thnx again...your advice and guidance will help a lot... <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Resume at a certain line of code! (Excel 2000 >)

    Rudi,
    The problem with that is as I said; for example, in the code you gave, suppose the workbook exists but there is a printer problem. You've told the code to skip 3 lines on an error, thinking it will take you to opening the next workbook. Instead it takes you straight to trying to print another workbook, which you haven't opened yet. As a result, this will also error out, as will any further processing loops, probably without need. I also wouldn't use the line number approach since it represents a step backwards, in much the same way that, although you can still use GoSub and Return, it's not really a great idea!
    Also, FWIW, in your specific example, you could just use On Error Resume Next.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resume at a certain line of code! (Excel 2000 >)

    <P ID="edit" class=small>(Edited by Rudi on 23-Feb-05 13:40. Sorry, I was Too fast on replying. I tried the On Error Resume Next....and it did not work as it closes the workbook that contains the macro. ???)</P>
    <hr>Also, FWIW, in your specific example, you could just use On Error Resume Next.<hr>
    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Why did you not say that in the beginning!!! This is true!!

    PS...after explaining it that way, I see the predicaments that it can cause! It is true that it can be more harmful than helpful!!!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resume at a certain line of code! (Excel 2000

    Also, given the code you cite, it appears that you are applying the same process to a number of workbooks. If so, can you not use a loop for the work process and - on error - increase the loop counter by 1 before you resume? HTH
    Gre

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resume at a certain line of code! (Excel 2000

    Yes, u r correct. But the code I supplied was just sample code to illustrate the question I had regarding resuming at a random position in code.
    Tx for your interest in participating in the thread!
    Regards,
    Rudi

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Resume at a certain line of code! (Excel 2000 >)

    Rudi,

    I'm not sure if Rory said this in his reply but if you do have a repetitive set of items for each workbook (ie, open, print, close), why not use a loop to do the open to see if it succeeds, as already suggested. Put the rest of the code for the workbook in a subroutine called only if the open succeeds. If the open fails, you're skipping one step - the call to the subroutine.

    Fred

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resume at a certain line of code! (Excel 2000 >)

    tx fburg! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •