Public Variables Are Not Seen on Modules for Sheet or UserForm

• Microsoft Excel 97 for Windows

SYMPTOMS

If you declare a public variable on a Visual Basic for Applications code module that is associated with an object, such as a UserForm or a Worksheet object, only the procedures in the code module in which you declare the variable can access the value the variable stores.

CAUSE

A variable that you declare with the Public statement in code modules that are associated with an object, such as a UserForm and Worksheet object, is available only to the module in which you declare it.

RESOLUTION

If you want to access a public variable in all procedures in all modules of a project, declare the variable on a general module. To insert a general module in a project, click Module on the Insert menu.

STATUS

This behavior is by design of Microsoft Excel 97.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The following examples demonstrate the difference between declaring a public variable in a general module and declaring a public variable in a code module that is associated with an object.

Declaring a Public Variable in a Code Module Associated with an Object

The following example declares a public variable in a code module that is associated with an object:

1. Save and close any open workbooks, and then create a new workbook.

2. Start the Visual Basic Editor (press ALT+F11).

3. In the Project Explorer window, double-click Sheet1.

   This step displays the code module associated with Sheet1.

4. Type the following code in this module:

   Public gMyVar As Integer

   Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

             gMyVar = gMyVar + 1
             MsgBox gMyVar
   
          End Sub
   
   

5. On the Insert menu, click Module.

   This step inserts a general module into your project.

6. Type the following code in this module:

           Sub General_Module()
   
              gMyVar = gMyVar + 1
              MsgBox gMyVar
   
           End Sub
   
   

7. Press ALT+F11 to switch to Microsoft Excel, and then click Sheet1.

8. Click any cell other than the current cell in Sheet1.

   A message box appears with the current value of the variable "gMyVar."

9. Click OK to dismiss the message box.

10. Repeat steps 8 and 9 several times until the value that is displayed in the message box is incremented to 5.

11. Run the General_Module macro.

    A message box that displays the value 1 appears. This is the value of gMyVar in the general module. The value is 1 because the General_Module macro can not access the public variable gMyVar that you declared in the code module associated with Sheet1.

12. Select a different cell in Sheet1.

    A message box that displays the current value of gMyVar from the code module associated with Sheet1 appears.

Declaring a Public Variable in the General Module

The following example declares a public variable in the general module:

1. Start the Visual Basic Editor.

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.

3. Switch to Microsoft Excel and click any cell other than the current cell in Sheet1.

   A message box that displays the current value of the variable gMyVar appears.

4. Click OK to dismiss the message box.

5. Repeat steps 3 and 4 several times until the value that is displayed in the message box is incremented to 5.

6. Run the General_Module macro.

   A message box that displays a value that is one more than the value that appears in step 5 appears. Because the variable gMyVar is a public variable that is declared on a general module, any module in the project can access its value.

REFERENCES

For more information about the scope of variables, click the Office Assistant in the Visual Basic Editor, 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 Visual Basic for Applications Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office
               Program or Component