The biggest tip I can give you here is to avoid using global variables as
much as possible. For the vast majority of things you will want to do you can
pass local variables (and declare them as static if you need the value to
persist after the procedure ends). You have just experienced one of the
downfalls of using globals (they are cleared when the VBA crashes). They are
also cleared if you have the stand alone line "End" anywhere in your code...
The other problem with globals is that they are a beast to debug. If 10
different procedures all use one global and at some point during the
exectution the value is not what it is supposed to be then you have to try to
figure out which procedure modified it last (this is often darn near
impossible).

I tend to use globals primarily to hold information that will be added once
during execution and then just read there after. Such as capturing a password
from the user to run queries against protected database tables. If at any
point the value is cleared I can just reprompt the user for the value... A
little anoying for the user but certainly not fatal...
--
HTH...

Jim Thomlinson


"Bill Case" wrote:

Hi;

While I am programming (experimenting) with VBA I have lots of crashes as I
try different things out. When a program crashes it loses all its public
setup variables and other entered data. Is there some general programing
tips to keep in mind so that I can setup my playing around so that I can
recover from a crash without having to close down the program and start up
again?

Just some general good practices tips.

I am using the MZ-tools add-on with VBE; but does anybody know of a good
Auto-complete tool I can add-on that helps complete non-VBA key words. VBE
has lots of that assistance, but I want something that helps complete my own
varibale, function and procedures names?

Regards Bill