EACutter

I'm encountering some confusion regarding just how to declare my variables to that they will retain their values. Currently I have several modules and I declare most of my variables as Public so they are available throughout. However I find Public variables get erased if I interrupt or debug a macro. I can't use Const and static variables don't seem to work either, that is when I reference a Static variable from another module, I get a compile error.

The Issue is I have several variables that reference specific worksheets (i.e. SanJoaquinCalc = ModelBook.Sheet("San Joaquin Calc") and the are used repeatedly throughout the project, but I would like to run one macro when the spreadsheet is opened to define the sheet name variables and then be done with it. Now I seem to have to run the SheetNames macro at the start of each macro that uses one of the sheet name variables, just to be sure it is defined.

NateBrei

I don't know why you are having problems with the Public variables holding their values when you debug or "break" in your code. They always work fine for me. However, you have mentioned that you have several modules. You will have a problem with Static for sure. Here is how I view variable declarations...

There are 3 levels (scope) of variables depending on where they are declared & what declaration keyword is used to declare them.

Public or Global variables - all procedures in all modules can see & use them. Declared in the General Declarations section of a module using the keyword 'Public'. Even though you can declare public variables in a form module, I would declare all public variables in a standard module (because it is loaded when the application starts & is available throughout the application). Form modules are only available once the form has be loaded & until it is unloaded. So, that could be A source of your problem....

Module level variables - only procedures within that module can see & use them. Procedures in other modules don't know they exist. Declared in the General Declarations section of a module using the keywords 'Dim' or 'Private'. Remain available only as long as the module is loaded (standard - application lifetime; form - form's lifetime).

Local variables - only the procedure in which the variable is declared can see & use them. No other procedure knows they exist. Declared within the procedure (usually immediately following the 'Sub' or 'Function' statement) using the keywords 'Dim' or 'Static'. 'Dim' variables exist only while the procedure is running. Once the procedure exits, the 'Dim' variables are thrown away & recreated on the next entrance into the sub/function (thereby reinitializing their values). 'Static' variables are created the first time a procedure is executed, but unlike 'Dim' variables, they remain even after the procedure exits. When a procedure is re-entered, the 'Static' variable is still there with the value it contained from the last execution of the procedure. It doesn't get deleted until the form is unloaded (form module) or until the application terminates (standard module).

I know this is long-winded, but I've found that a significant number of people are unsure about variables & their declarations & usage. Hope this helps you find your problem. I would start by ensuring all Public variables are in a standard module & not in a form module.

Nate

icjackson

Another idea may be to store these variables as custom document properties, and Timbo posted a good site for that here:
http://www.xtremevbtalk.com/show...ferrerid=25947