VBA: Scope of Variables in Visual Basic for Applications

Last reviewed: February 3, 1998
Article ID: Q141693

The information in this article applies to:
  • Microsoft Visual Basic for Applications, versions 1.0, 5.0
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

SUMMARY

The scope of a variable is determined at the time the variable is declared. In Microsoft Visual Basic for Applications, the three scopes available for variables are procedure, module, and public. The "More Information" section of this article describes each scope in detail.

MORE INFORMATION

Procedure (Local) Scope

A local variable with procedure scope is recognized only within the procedure in which it is declared. A local variable can be declared with a Dim or Static statement.

Dim:

When a local variable is declared with the Dim statement, the variable remains in existence only as long as the procedure in which it is declared is running. Usually, when the procedure is finished running, the values of the procedure's local variables are not preserved, and the memory allocated to those variables is released. The next time the procedure is executed, all of its local variables are reinitialized.

For example, in the following sample macros, "Example1" and "Example2," the variable X is declared in each of the modules. Each variable X is independent of the other--the variable is only recognized within its respective procedure.

   Sub Example1()
      Dim X As Integer
      ' Local variable, not the same as X in Example2.
      X = 100
      MsgBox "The value of X is " & X
   End Sub

   Sub Example2()
      Dim X As String
      ' Local variable, not the same as X in Example1.
      X = "Yes"
      MsgBox "The answer is " & X
   End Sub

Static:

A local variable declared with the Static statement remains in existence the entire time Visual Basic is running. The variable is reset when any of the following occur:

  • The macro generates an untrapped run-time error.
  • Visual Basic is halted.
  • You quit Microsoft Excel.
  • You change the module.

For example, in the RunningTotal example, the Accumulate variable retains its value every time it is executed. The first time the module is run, if you enter the number 2, the message box will display the value "2." The next time the module is run, if the value 3 is entered, the message box will display the running total value to be 5.

   Sub RunningTotal()
      Static Accumulate
      ' Local variable that will retain its value after the module
      ' has finished executing.
      num = Application.InputBox(prompt:="Enter a number: ", Type:=1)
      Accumulate = Accumulate + num
      MsgBox "The running total is " & Accumulate
   End Sub

Module Scope

A variable that is recognized among all of the procedures on a module sheet is called a "module-level" variable. A module-level variable is available to all of the procedures in that module, but it is not available to procedures in other modules. A module-level variable remains in existence while Visual Basic is running until the module in which it is declared is edited. Module-level variables can be declared with a Dim or Private statement at the top of the module above the first procedure definition.

At the module level, there is no difference between Dim and Private. Note that module-level variables cannot be declared within a procedure.

NOTE: If you use Private instead of Dim for module-level variables, your code may be easier to read (that is, if you use Dim for local variables only, and Private for module-level variables, the scope of a particular variable will be more clear).

In the following example, two variables, A and B, are declared at the module level. These two variables are available to any of the procedures on the module sheet. The third variable, C, which is declared in the Example3 macro, is a local variable and is only available to that procedure.

Note that in Example4, when the macro tries to use the variable C, the message box is empty. The message box is empty because C is a local variable and is not available to Example4, whereas variables A and B are.

   Dim A As Integer        ' Module-level variable.
   Private B As Integer    ' Module-level variable.

   Sub Example1()
       A = 100
       B = A + 1
   End Sub

   Sub Example2()
       MsgBox "The value of A is " & A
       MsgBox "The value of B is " & B
   End Sub

   Sub Example3()
        Dim C As Integer    ' Local variable.
        C = A + B
        MsgBox "The value of C is " & C
   End Sub

   Sub Example4()
        MsgBox A
        ' The message box displays the value of A.
        MsgBox B
        ' The message box displays the value of B.
        MsgBox C
        ' The message box displays nothing because C was a local variable.
   End Sub

Public Scope

Public variables have the broadest scope of all variables. A public variable is recognized by every module in the active workbook. To make a public variable available to other workbooks, from a new workbook select the workbook containing the public variable in the Available References box of the References dialog box (from a module sheet, click References on the Tools menu).

A public variable, like a module-level variable, is declared at the top of the module, above the first procedure definition. A public variable cannot be declared within a procedure. A public variable is always declared with a "Public" statement. A public variable may be declared in any module sheet.

It is possible for multiple module sheets to have public variables with the same name. To avoid confusion and possible errors, it is a good idea to use unique names or to precede each variable name with a module qualifier (for example, in a module named "Feb_Sales" you may want to precede all public variables with the letters "FS").

To create the macros:

  1. Create a new workbook and name it CDSales.xls.

  2. In the CDSales.xls workbook, insert a module sheet. Name the module sheet CDSales.

    In Microsoft Excel 97 or Microsoft Excel 98 follow these steps to insert a new module sheet and name the module sheet:

    a. In the CDSales.xls workbook, point to Macro on the Tools menu, and

          then click Visual Basic Editor.
    

    b. On the Insert menu, click Module.

    c. In Microsoft Excel 97, you can rename a module by activating the

          module, clicking to the right of "(Name)" in the Properties window 
          of the Visual Basic Editor, typing a new module name, and pressing 
          ENTER.
    

  3. In the CDSales module sheet, type the following code:

          Public SalesPrice As Integer
          Public UnitsSold As Integer
          Public CostPerUnit As Integer
          Private Markup As Long
    

          Sub CDSales()
             Dim X as String
             SalesPrice = 12
             UnitsSold = 1000
             CostPerUnit = 5
             Markup = 1.05
             X = "yes"
             MsgBox "The Gross Profit for CD Sales is $" & (SalesPrice _
                * UnitsSold) -(UnitsSold * CostPerUnit * Markup)
             ' Displays the value of 7000 as the gross profit.
          End Sub
    
    

  4. Create a new workbook and name it VideoSales.xls.

  5. In the VideoSales.xls workbook, insert a module sheet. Name the module sheet VideoSales.

    In Microsoft Excel 97 or Microsoft Excel 98 follow these steps to insert a new module sheet and name the module sheet:

    a. In the CDSales.xls workbook, point to Macro on the Tools menu, and

          then click Visual Basic Editor.
    

    b. On the Insert menu, click Module.

    c. In Microsoft Excel 97, you can rename a module by activating the

          module, clicking to the right of "(Name)" in the Properties window 
          of the Visual Basic Editor, typing a new module name, and pressing 
          ENTER.
    

  6. In the VideoSales module sheet, type the following code:

          Public SalesPrice As Integer
          Public UnitsSold As Integer
          Public CostPerUnit As Integer
    

          Sub VideoSales()
             SalesPrice = CDSales.SalesPrice * 1.05
             UnitsSold = CDSales.UnitsSold * 1.456
             CostPerUnit = CDSales.CostPerUnit * 1.75
             MsgBox "The Projected Gross Profit for video sales is $" & _
                (SalesPrice * UnitsSold) - (UnitsSold * CostPerUnit)
             ' Displays the value of 5824 as the projected gross profit.
          End Sub
    
    
To Run the Sample Macros in Microsoft Excel 5.0, 7.0

  1. To Create a reference from the workbook VideoSales.xls to CDSales.xls, select the VideoSales module sheet in VideoSales.xls, and then click References on the Tools menu.

  2. In the References dialog box, select the CDSales.xls check box, and click the OK button.

  3. Run the CDSales macro and then run the VideoSales macro.

To run the sample macros in Microsoft Excel 97, 98

  1. Rename the project name of the two workbooks so that they are unique by following these steps:

    a. In the Project Explorer pane in the Visual Basic Editor, look at the

          projects that are listed. You should see entries similar to the
          following:
    

                <VBAProject> (VideoSales.xls)
                <VBAProject> (CDSales.xls)
                <VBAProject> (Personal.xls)
    
          where <VBAProject> is the name of the project.
    
       b. Click the entry for VideoSales.xls.
    
       c. In the Properties pane, in the box to the right of "(Name)", type a 
          new, unique project name and press Enter.
    
          Note: Do not use a project name that you use in any other workbook.
    
       d. On the File menu, click Save <bookname>, where <bookname> is the 
          name of the workbook you modified.
    
    

  2. To Create a reference from the workbook VideoSales.xls to CDSales.xls, select the VideoSales module sheet in VideoSales.xls, and then click References on the Tools menu.

  3. In the References dialog box, select the check box of the project name specified in step 1c, and then click OK.

  4. Run the CDSales macro and then run the VideoSales macro.

Note that the VideoSales macro uses the public variables declared in the CDSales module of CDSales.xls.

Example of Macro Failure When You Try to Access Local Variable

The following example tries to use the module-level variable, CDSales.Markup or the local variable CDSales.X in the VideoSales module sheet:

   Sub VideoSales2()
       MsgBox CDSales.Markup
   End Sub

   Sub VideoSales3()
       MsgBox CDSales.X
   End Sub

In Microsoft Excel 5.0 or 7.0, the following error message appears when you run either of these procedures:

   Member not defined

In Microsoft Excel 97 or Microsoft Excel 98, the following error message appears when you run either of these procedures:

   Compile error:
   Method or data member not found

REFERENCES

For more information about scope, click the Index tab in Microsoft Excel 7.0 Help, type the following text

   scope

and then double-click the selected text to go to the "Understanding scope" topic.

For more information about how long the value of a variable is retained, click the Index tab in Microsoft Excel 7.0 Help, type the following text

   variables, lifetime

and then double-click the selected text to go to the "Lifetime of variables" topic.

"Visual Basic User's Guide," version 5.0, Chapter 6, "Making Your Variables Available Within Procedures, Modules, or Publicly"


Additional query words: 7.00 5.00 5.00a 5.00c global
Keywords : kbcode kbprg PgmOthr
Version : WINDOWS:5.0,5.0a,5.0c,7.0; MACINTOSH:5.0,98
Platform : MACINTOSH WINDOWS
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 3, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.