Selected Answer
Variables can be set with a local, public or global scope, depending upon where they are set and how they are declared.
- A variable declared with the Dim statement within a procedure is "local". It is recognized only within the procedure in which it is declared. VBA will delete it when the procedure has run to its end.
- A variable declared within a procedure but using the Static statement has the same scope but retains its value between calls, even while the user may run other code in other workbooks.
- A variable declared using the Dim or Private statement outside a procedure is of public scope, meaning it's available to all procedures within the same module.
- Most programmers prefer to use the Private statement for such declarations and use the Dim statement exclusively for local variables.
- A variable declared outside a procedure using the Public statement will be available to all procedures within the project in which it is declared.
- Public and Private declarations should be placed at the top of a code module, before any procedures.
There is no way to declare a variable with an even larger scope, such as to let it be available throughout all VBA projects in all open workbooks, but there is a simple way for achieving that effect:-
Public Function MyName() As String
MyName = "John Doe"
End Function
The above function can be called from any workbook, effectively making its value available everywhere. The function can be constructed to return a different value depending on which workbook it is called from, such as the value of Sheet1!A1, or the name of the person who created the workbook.
The location of the function further reduces or expands its scope. For example, it might be placed in a Personal workbook that loads when Excel is opened or it could be in another workbook that can be accessed using code in other workbooks that need it.