Forum Discussion

LemonPeeler's avatar
LemonPeeler
Copper Contributor
Aug 25, 2022

How do we properly exit a vba loop and branch elsewhere in an excel userform?

We have GOTO (Label) statements nested inside IF statements, which are nested inside FOR statements, which are nested inside CASE statements, inside a userform. The inner IF statement is using the GOTO (Label) statement to branch out of the IF statement when TRUE (THEN option) or out of the CASE statement (2 levels above) when FALSE (ELSE option).

 

The problem is that when we use a GOTO statement to branch to another location, the Excel VBA compiler is showing errors like 'Compile error: end if without block if" or 'Compile error: next without block for".

 

Interestingly, our structures all have the matching NEXT and END IF pairs but trigger the compile errors as soon as we insert a GOTO statement to branch out of the loop to another LABEL. If we comment out the GOTO statements again, there are no compile errors. Any thoughts on what the problem might be?

 

Thank you for any assistance.

2 Replies

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor

    LemonPeeler  wrote:  ``The problem is that when we use a GOTO statement to branch to another location, the Excel VBA compiler is showing errors like 'Compile error: end if without block if" or 'Compile error: next without block for".``

     

    I doubt that that alone is the cause of the compiler error.  And the literal text of the error message might be misleading.  Ostensibly, you have a misplaced End IF or Next statement.  But the real problem might be something different altogether.

     

    You have a lot of narrative, which actually tells us very little.  But "a picture is worth a 1000 words; and an Excel file is worth 1000 pictures".

     

    At the very least, show us the entire VBA procedure, in one form or another.  In this case, text is better than image, IMHO.  It allows us to copy-and-paste the code in to our own VBA.

     

    Even better, attach an Excel file that demonstrates the problem.  Click the "browse" link near the bottom of the reply window.  If the forum does not permit that (yet), upload the file to a file-sharing website, and post the download URL.  I like box.net/files; others like dropbox.com.

     

    If the forum does not permit you to post a URL (yet), spell out part of it manually.  For example, the URL for this thread is techcommunity dot microsoft dot com /t5/excel/how-do-we-properly-exit-a-vba-loop-and-branch-elsewhere-in-an/m-p/3609626.

    • LemonPeeler's avatar
      LemonPeeler
      Copper Contributor
      Thank you so much for your response. In the interim, we discovered the problem. As you indicated the source can be allusive. The problem was that we inadvertently placed an underscore after the THEN command of an IF statement rather than just a line return. So, when the compiler read the next line as a continuation, it apparently triggered the message. Perhaps, this will help others when troubleshooting this error. Thank you again for the response.

Resources