Excel VBA: End Sub vs. Exit Sub

[BrianG]

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.

BrianG

[Tripredacus]

one or several public variables get lost during the switch to a different module.

I noticed you also said LocalVariables. Have you tried using Global variables for those?

[BrianG]

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.

[Tripredacus]

Is it possible that the code breaks if it encountered an access denied condition? Is this a shared app used by multiple people, or is it using files available to multiple people?

[BrianG]

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.

[IcemanND]

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?