ID: Q188441
The information in this article applies to:
If you declare a public variable in a Visual Basic for Applications module that is associated with an object, such as a UserForm or a Worksheet object, only the procedures in the module can access the value of the variable.
If you declare a variable with the Public statement in a module that is associated with an object, such as a UserForm and Worksheet object, the variable is only available in that module.
If you want to access a public variable in all procedures for all modules of a project, you must declare the variable in a general module. To insert a general module in a project, click Module on the Insert menu.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/refguide/
To declare a public variable in a module that is associated with an object, follow these steps:
1. Save and close any open workbooks, open a new workbook, and start
the Visual Basic Editor by pressing OPTION+F11.
2. In the Project Explorer window, double-click Sheet1.
NOTE: This step displays the module associated with Sheet1.
3. Type the following code in the module:
Public gMyVar As Integer
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gMyVar = gMyVar + 1
MsgBox gMyVar
End Sub
4. On the Insert menu, click Module.
NOTE: This step inserts a general module into your project.
5. Type the following code in this module:
Sub General_Module()
gMyVar = gMyVar + 1
MsgBox gMyVar
End Sub
6. Switch to Microsoft Excel by pressing OPTION+F11, and click somewhere
else in Sheet1 to remove focus from the current cell.
NOTE: A message box appears with the current value of the
variable "gMyVar."
7. Close the message box by clicking OK.
8. Repeat step 6 and step 7 several times until the value displayed in
the message box is 5.
9. Run the General_Module macro.
A message box displays the value 1. This is the value of
gMyVar in the general module. The value is 1 because the General_Module
macro cannot access the public variable gMyVar that you declared in
the module associated with Sheet1.
A message box displays the current value of gMyVar from the
module associated with Sheet1.
The following example declares a public variable in the general module:
1. Start the Visual Basic Editor by pressing OPTION+F11.
2. Cut the following line
Public gMyVar As Integer
from the module associated with Sheet1 and paste it into the general
declarations section of the general module.
focus from the current cell.
A message box displays the current value of the variable gMyVar.
5. Repeat step 3 and step 4 several times until the value displayed in
the message box is 5.
A message box displays a value that is equal to the value of gMyVar in
Step 5 incremented by 1. Because the variable gMyVar is a public
variable that is declared on a general module, any module in the project
can access its value.
For more information about the scope of variables, click the Office Assistant, type "scope," click Search, and then click to view "Understanding Scope and Visibility."
NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q179216
TITLE : OFF98: How to Use the Microsoft Office Installer Program
Keywords : kbprg kbdta
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbhowtoLast Reviewed: May 18, 1999