One of the VBA programmer’s tool of debugging tricks is using the Stop statement in code to stop code execution at a certain point where they want to examine what is happening.
Of course, this is similar to setting a breakpoint (F9), but it is more permanent (especially useful if you are not finished today, and want to shut down so that you can continue again on a later date).
However, generally you will find that programming “tutorials” will discourage you from using this, and with good reason. The biggest problem is scattering your code with Stop statements that don’t come out in your debugging, but do interrupt your code when your users are running it.
Now I am not going to suddenly start recommending that you start prolifically using Stop statements, but I have found that in certain instances, I really do find it useful. But at the same time, I am really fearful of leaving a Stop statement into my code, and not deleting it before I release the AddIn to the public.
So today, I thought of a simple solution for myself that will not work in all contexts, but may help someone out there too. The reason that it works for me is that I generally have two versions of my AddIns. One is named xxx unprotected.xxxm (e.g., Word uTIlities unprotected.dotm). When I feel that the AddIn is ready to release to the public (it’s been more than a year since I last released an update to the Word uTIlities, but I have some big improvements coming), I make a copy without the unprotected suffix (e.g., Word uTIlities.dotm). Then I add a password to that, package it in an install file, and it’s good to go. That fact—that the version I am working on, and debugging, does not have a password, while the one my users are running does, provides me with a clue to setting up my Stop statements in my code that will work for me, but not interrupt my users.
So all I started doing was changing the Stop statements in my code as follows:
If Not ThisDocument.HasPassword Then Stop
If Not ThisWorkbook.HasPassword Then Stop
This, at least, means that so long as the document has a password, the Stop statement will not interrupt the code. Yes, I will still do my best to delete my Stop statements when I am done with them, and if I should not delete those statements and I forget to give my AddIn a password, then it will still bomb out.
What if you don’t have a password? Well, you could create a global variable, but then you would have to remember to “deactivate” that variable before distributing.
So, for example, you would include this dim statement:
Dim bAllowStopping as Boolean
Then the code would just be:
If bAllowStopping then Stop
You can only activate stopping by setting:
bAllowStopping = True
But if you ship the AddIn with that line of code still there, then it will stop, and remember that, because the variable is global, it will stop anywhere the Stop statement is found.
Note that I chose something like bAllowStopping, which, when declared, has the value of False; and not something like bOverrideStopping, which would logically, want to be set to True to not stop—I would want to have to explicitly set it to True before it starts stopping my code. So, as long as I don’t actively set it to True, it won’t stop my code.
One last option, of course, is to tie the Stop statement to the username, e.g.:
If LCase(Application.UserName) = "joe bloggs" Then Stop
However, I consider this to be quite risky, as firstly, I don’t like shipping my application username with my code, necessarily, and secondly, I might, for who knows what reason, might choose to change my username, and thirdly, the odds of there being another user out there with the same username as my own (either through coincidence or being purposefully set) are not that remote.
For my part, I will stick to my first option of checking for the password, and still try to use Stop statements as little as possible, and also follow that up with deleting them when I am done with them.