When to Dim, Private or Public

[newboy18]

Please help, I am getting a bit confused.
If I want a variable to be only available in the current
Sub routine then I use Dim, If I want the variable to be
available in all Modules then I use Public.
Does it matter if I make the statement is before or after
the beginning of the Sub routine?
What should I do if I want the variable to be available to
the current Module only?
The problem started when I found that a variable that had
been dimmed in 1 module was available in another Module
but the value was not.

 

[chas]

Hi,

As a rough guide

- Declaring a variable inside a subroutine makes the
variable available ony to that subroutine.

- Declaring a variable in the declarations section of a
module with Dim or Public makes that variable available to
all routines in all modules.

- Declaring a variable as Private in the declarations
section of a module makes that varaible available ONLY to
routines in the current module.

For more information search VBA help for the words 'scope'
and 'lifetime' (remove the quotes first).

hth

chas

 

[Marshall Barton]

No.
Dim at module declarations level is the same as Private, not Public.
So to be available in all procs in all modules you must use Public.

You're both right.

Dim at the module level is Public in standard modules and
Private in class modules (includes form and report modules).

Of course the issue would be moot if all module level
variables were explicitly declared Public or Private.

 

[Peter Russell]

You're both right.

Dim at the module level is Public in standard modules and
Private in class modules (includes form and report modules).

Of course the issue would be moot if all module level
variables were explicitly declared Public or Private.

Sorry Marsh but I disagree.
Dim = Private, always.

Regards

Peter Russell

 

[Marshall Barton]

Sorry Marsh but I disagree.
Dim = Private, always.

You're right!

I seem to have a bad case of foot in mouth this week. I
guess I was applying the rule for Sub and Function to Dim -
bad move.

I will say this again though, it's all moot if Public and
Private are always used at module level.