Use a variable anywhere in the workbook - How to create?

[Guest]

I want to use a few variables in any Module I run in the current Workbook
without having to enter a Dim/Set command. These variables hold a value that
does not change via the program. If a change is needed I would like to go to
one place to update them. As well, I would like to give their value in the
same place.

I also know that they need to go on the top above any SUB() statement but I
am uncertain which Module they belong in so that all other modules can
benefit from their value. I have Worksheet Sheet Modules and I see a folder
with MODULES.
When I open this up, I have Module1-3. I figured these need to go in one of
these Modules but am uncertain is it matters which one.

I also am uncertain of the exact syntax I need to use to enter this type of
variable.

Each of these variables are currently define in the SUB() as follows:

Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Set srcProgramDataInputWs = Sheets("ProgramDataInput")
Set srcProgramSummaryWs = Sheets("ProgramSummary")

 

[Rowan]

Hi

What your are talking about are not variables but rather constants. In
general to declare a constant you would use:
Const myNumber as Integer = 5
With regard to using the constant in any procedure in any module the
scope of a constant follows the same rules as the scope of variables
(search help for "understanding scope").

So if you declare the constant inside a procedure it will be available
in that procedure only. If you declare it in the Declaration section
(top) of a module it will be available to all procedures in the module.
Module level constants are Private by default so they are not available
to procedures in a different module. To allow procedures from a
different module to access the constant you will need to declare it as
Public eg:
Public Const MyNumber as Integer = 5

If you are declaring a constant as Public you can place it in the
declaration section of any standard module, it shouldn't make a
difference which one you use but it can't be a class module eg a module
attached to a sheet.

Having said all that you won't be able to use an object (eg a worksheet)
as a constant. This is because an object is not fixed - it could be
deleted, renamed etc etc.

Hope this helps
Rowan

 

[Guest]

You are referring to Public (or global) variables. You can define them in
any regular module in the declarations section above any subs or functions
and you use the word Public in place of Dim; e.g.
Public srcProgramDataInputWs As Worksheet
Public srcProgramSummaryWs As Worksheet

You could run the code to set them in the Workbook_Open routine and then
they would be available for any code in your project. For your purposes it
should not matter if you put them in module 1, 2 or 3: I personally like
creating a separate module which I name "Globals" just for things like this.
It makes them easier to find if 5 years from now I need to do some debugging!

 

[NickHK]

CRayF,
Something different: Excel's Hidden Namespace:
http://www.cpearson.com/excel/hidden.htm

NickHK