A matter of scope: visibility of code elements

[HansV]

Variables and constants can be made available at three different levels: within a procedure (sub) or function, within a module or within all modules in a VBA "project" (i.e. a Word document, an Excel workbook, etc.)

Procedure-level variables

If you declare a variable within a procedure or function, it will be known within that procedure or function only. For example:

Code: Select all

Sub Test1()
  Dim MyVar As String
  MyVar = "Lounge"
End Sub

If you refer to MyVar outside the procedure Test1, you'll get an error message if you have Option Explicit at the top of the module, or it will be an empty string, not "Lounge", even after you have run Test1. MyVar is called a local variable.

Normally, the value of a local variable is forgotten once the procedure or function ends. But if you declare it using the keyword Static instead of Dim, the variable will retain its value when the procedure or function ends, until you close the document/workbook/… or an unhandled error occurs.

Example:

Code: Select all

Sub Test2()
  Static MyVar As Long
  MyVar = MyVar + 1
End Sub

Each time you run the procedure Test2, the value of MyVar will be increased by 1.

Module-level variables

If you declare a variable at the top of a module with the keyword Dim or Private, it will be known within all procedures and functions in that module, but not in other modules within the same document, workbook etc. For example:

Code: Select all

Dim MyVar As Long

Sub Test1()
  MyVar = MyVar + 2
End Sub

Sub Test2()
  MyVar = MyVar - 3
End Sub

In this example, MyVar is a private variable. If I had used

Code: Select all

Private MyVar As Long

the effect would have been the same.

Global variables

If you declare a variable using the keyword Public, it will be known to all procedures and functions in all modules in the same document, workbook etc. For example:

Public MyVar As Long

MyVar is called a public or global variable.

Declaring a variable at more than one level

It is possible to declare a variable with the same name as a local variable and as a private or public variable, or as a private variable and as a public variable, or even as all three. This can easily lead to confusion, so I would avoid it if possible. But if you do so, the code will see the lowest-level declaration. For example:

Code: Select all

Private MyVar As String
Sub Test1()
  Dim MyVar As String
  MyVar = "Eileen"
  Debug.Print "In Test1: " & MyVar
End Sub

Sub Test2()
  MyVar = "Lounge"
  Debug.Print "In Test2, before: " & MyVar
  Call Test1
  Debug.Print "In Test2, after: " & MyVar
End Sub

If we run Test2, we'll see the following in the Immediate window:

Code: Select all

 In Test2, before: Lounge
In Test1: Eileen
In Test2, after: Lounge

As you see, the value assigned in Test1 doesn't carry over to Test2.

Constants

Constants are declared with the keyword Const, for example:

Code: Select all

Const MyDrive = "C:"

A constant declared within a procedure or function is local to that procedure or function.

A constant declared at the top of a module is private by default:

Code: Select all

Const MyDrive = "C:"

is equivalent to

Code: Select all

Private Const MyDrive = "C:"

If you want the constant to be known in all modules, you must prefix the keyword Const with Public:

Code: Select all

Public Const MyDrive = "C:"

Public and private procedures

By default, procedures and functions are public, i.e. they can be called in all modules in the same document, workbook etc. The procedure header

Code: Select all

Sub Test1()

is equivalent to

Code: Select all

Public Sub Test1()

If you prefix the keyword Sub or Function with Private, the procedure or function will be known only within the same module; if you try to call it from another module, you'll get an error message. Example:

Code: Select all

Private Sub Test2()
  ...
End Sub

Declaring a procedure with the same name in more than one module

It is possible to create a private procedure or function with the same name in more than one module.

It is also possible to create a public procedure or function in one module, and a private procedure or function with the same name in another module. If you call the name from any module except the one with the private version, the public version will be run. In the module with the private version, that will be run.

As with variables, although this is valid, it can easily lead to confusion, so in general I'd avoid it.

A final warning

Please note that the default behavior for procedures/functions is the opposite of the default behavior of variables and constants at the top of a module: a procedure/function is public unless you explicitly use the keyword Private in its definition, but a variable/constant is private unless you explicitly use the keyword Public in its declaration. For example:

Code: Select all

Dim MyVar As String

Sub Test1()
  ...
End Sub

MyVar is a private (module-level) variable, but Test1 is a public procedure.

[Jan Karel Pieterse]

Nice article again, Hans. Small addition: The behaviour of publicly declared variables differes when done in a class module, in a userform or in one of the sheet modules. A variable declared publicly in a normal module is available as such throughout your project. A variable declared public in any of the other code windows is available as a property of that particular object.

So if Thisworkbook contains this line:
Public Safe2Close As Boolean
then you can only access its value like this:
ThisWorkbook.Safe2Close = True

[HansV]

Thanks for the addition!