XL98: Public Variables Not Seen on Modules for Sheet or UserForm

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. The following examples demonstrate the difference between declaring a public variable in a general module and declaring a public variable in a module that is associated with an object.

Declaring a Public Variable in a Module Associated with an Object

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.
10. Select a different cell in Sheet1.
    
    A message box displays the current value of gMyVar from the module associated with Sheet1.

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 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.
3. Switch to Microsoft Excel, and click somewhere else in Sheet1 to remove focus from the current cell.
   
   A message box displays the current value of the variable gMyVar.
4. Click OK to close the message box.
5. Repeat step 3 and step 4 several times until the value displayed in the message box is 5.
6. Run the General_Module macro.
   
   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.