Variable Declaration General Question

[stinkingcedar]

Hey everyone,

So I just have a general question about variable declaration in vba.

If you declare a variable in a module, can it then be used in other subs/functions in that same module without declaring it again? Can it be used in other modules without declaring it again as well? I use option explicit almost exclusively, not sure if that has anything to do with it, but I was just wondering the scope in which the variables could be used under so I could possibly make my code more compact. Thank you!

 

[stinkingcedar]

Also, does the private/public designation for sub-procedures and functions play a role in this as well?

 

[RoryA]

It depends on the scope.

If you declare the variable in a routine, it is only visible to that routine.
If you declare it with Dim or Private, at the top of a module, it is visible to routines in that module only.
If you declare it as Public, it is visible to all routines in the project.

The scope of the routine is irrelevant as far as the variable is concerned.

 

[stinkingcedar]

It depends on the scope.

If you declare the variable in a routine, it is only visible to that routine.
If you declare it with Dim or Private, at the top of a module, it is visible to routines in that module only.
If you declare it as Public, it is visible to all routines in the project.

The scope of the routine is irrelevant as far as the variable is concerned.

So normally I declare all my variables with a Dim statement at the top of each module, what would declaring the variables in the other three ways that you mention look like syntax-wise?

 

[RoryA]

The first two look the same, it's just a question of where the declaration line is - at the top of a module above all routines, or inside a routine. Public variables are declared:

Public someVariable as string

for example at the top of the module before any routines.

 

[stinkingcedar]

What if I were to set a variable, such as a worksheet variable, to a sheet in the top of the module? Would I then not have to do it in each individual procedure in that module?

 

[RoryA]

You can't assign a value to a variable outside a routine. You can only declare them.