Jump to content
MSFN is made available via donations, subscriptions and advertising revenue. The use of ad-blocking software hurts the site. Please disable ad-blocking software or set an exception for MSFN. ×

Excel VBA: End Sub vs. Exit Sub

Recommended Posts

I have an extensive VBA Script in an Excel 2007 workbook which contains nine modules. The script routinely, though not often, fails due to the loss of variables. When this happens, one or several public variables get lost during the switch to a different module. This module switch occurs when a module is executed by a button on the workbook (I have a Print button and a Save button). For instance, in the Print module I set a strPrinted variable to "Y" when the workbook is printed. In the Save module I check the value of the strPrinted variable and if not "Y" prompt to print the workbook. Occasionally the script fails because strPrinted variable doesn't exist. I have been doing research trying to determine what might be the cause and came across references that say an End Statement command will destroy public variables. This has me wondering whether Exit Sub would be a better choice but I have been unable to determine when to use Exit Sub vs. End Sub. The script failures are only occasional so I may be barking up the wrong tree but insight regarding the usage of End Sub vs. Exit Sub would be appreciated.


Link to comment
Share on other sites

All variables are defined as Public.

I have determined that when the problem occurs, many (possibly all) variables are lost. This was determined by going into Debug and adding code to re-establish the known missing variable only to have the code fail on the next line of code that contained a different variable. This problem is random and not PC specific.

Link to comment
Share on other sites

This is a shared app located on a server. On startup the script copies the workbook to the local temp location and starts running from there. This was done to eliminate the multiple user issue.

I have seen access denied errors on new PC's when I forget to set up the trusted locations in the trust center but that generates an access denied error rather than the runtime error generated when the variables have been lost.

Link to comment
Share on other sites

You said they are all public variables but are they declared outside the subroutines or are they global to the entire script having been declared outside any sub?

Can you post the code for us to look at?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...