You are currently offline, waiting for your internet to reconnect

Scope of variables in Visual Basic for Applications

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q141693
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 forvariables are procedure, module, and public. The "More Information" sectionof this article describes each scope in detail.
MORE INFORMATION

Procedure (local) scope

A local variable with procedure scope is recognized only within theprocedure in which it is declared. A local variable can be declared with aDim or Static statement.
Dim				
When a local variable is declared with the Dim statement, the variableremains in existence only as long as the procedure in which it is declaredis running. Usually, when the procedure is finished running, the values ofthe procedure's local variables are not preserved, and the memory allocatedto 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," thevariable X is declared in each of the modules. Each variable X isindependent of the other--the variable is only recognized within itsrespective 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 existencethe entire time Visual Basic is running. The variable is reset when any ofthe 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 retainsits value every time it is executed. The first time the module is run, ifyou 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 sheetis called a "module-level" variable. A module-level variable is availableto all of the procedures in that module, but it is not available toprocedures in other modules. A module-level variable remains in existencewhile Visual Basic is running until the module in which it is declared isedited. Module-level variables can be declared with a Dim or Privatestatement at the top of the module above the first procedure definition.

At the module level, there is no difference between Dim and Private. Notethat 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 themodule level. These two variables are available to any of the procedures onthe module sheet. The third variable, C, which is declared in the Example3macro, is a local variable and is only available to that procedure.

Note that in Example4, when the macro tries to use the variable C, themessage box is empty. The message box is empty because C is a localvariable 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 publicvariable is recognized by every module in the active workbook. To make apublic variable available to other workbooks, from a new workbook selectthe workbook containing the public variable in the Available References boxof 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 ofthe module, above the first procedure definition. A public variable cannotbe 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 thesame name. To avoid confusion and possible errors, it is a good idea to useunique names or to precede each variable name with a module qualifier (forexample, in a module named "Feb_Sales" you may want to precede all publicvariables with the letters "FS").

To create the macros, follow these steps:
  1. Create a new workbook and name it CDSales.xls.

    Note In Excel 2007, save the file as an Excel Macro-Enabled Workbook. The file name will be CDSales.xlsm.
  2. In the CDSales workbook, insert a module sheet. Name the module sheet CDSales.

    In Microsoft Excel 97 and in later versions of Excel, follow these steps to insert a new module sheet and to name the module sheet:
    1. In the CDSales workbook, press ALT+F11 to open the visual Basic Editor.
    2. On the Insert menu, click Module.
    3. In Microsoft Excel for Windows (version 97 and later), you can rename a module by activating the module, clicking to the right of "(Name)" in the Properties window of the Visual Basic Editor, type a new module name, and then press 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.

    Note In Excel 2007, save the file as an Excel Macro-Enabled Workbook. The file name will be VideoSales.xlsm.
  5. In the VideoSales workbook, insert a module sheet. Name the module sheet VideoSales.

    In Microsoft Excel 97 or later follow these steps to insert a new module sheet and name the module sheet:
    1. In the CDSales workbook, press ALT+F11 to open the Visual Basic Editor.
    2. On the Insert menu, click Module.
    3. In Microsoft Excel for Windows (version 97 and later), you can rename a module by activating the module, clicking to the right of "(Name)" in the Properties window of the Visual Basic Editor, type a new module name, and then press 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						

Run the sample macros in Microsoft Excel 97 and in later versions of Excel

  1. Rename the project name of the two workbooks so that they are unique by following these steps:
    1. 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.
    2. Click the entry for CDSales.xls. In Excel 2007, click the entry for CDSales.xlsm.
    3. In the Properties pane, in the box to the right of "(Name)", type a new, unique project name, and then press Enter.

      Note Do not use a project name that you use in any other workbook.
    4. 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 to CDSales, select the VideoSales module sheet in VideoSales, 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 theCDSales 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 modulesheet:
   Sub VideoSales2()       MsgBox CDSales.Markup   End Sub   Sub VideoSales3()       MsgBox CDSales.X   End Sub				
In Microsoft Excel 97 or in later versions of Excel, the following error messageappears 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 Excel7.0 Help, typescope,and then double-click the selected text to go to the "Understanding scope"topic.
5.00a 5.00c global xl97 xl2000 xl2002 XL2003 Excel2007 XL2007
Properties

Article ID: 141693 - Last Review: 02/08/2007 17:58:17 - Revision: 4.0

  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 5.0c
  • Microsoft Excel 5.0 for Macintosh
  • Microsoft Excel 5.0a for Macintosh
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • kbcode kbinfo kbprogramming KB141693
Feedback