XL: Passing Variables in Visual Basic for Applications Macros

ID: Q140033

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh, version 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

SUMMARY

When you use a Microsoft Visual Basic for Applications macro or procedure, you can retain the value of a variable by doing any of the following:

Pass the variable to another workbook. (This way you can retain the value of the variable, even if the original workbook is closed.)
Pass the variable as an argument within the same workbook. (You can do this without making the variable public.)
Define the variable as a public variable.

Each of these methods is discussed in more detail in the "More Information" section of this article.

MORE INFORMATION

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 Pass a Variable to Another Workbook

1. Type this code in a module sheet and save the workbook as Book1.xls:

      Sub PassVarValues()

         ' Declare variables.
         Dim PassVar1 as Integer
         Dim PassVar2 as Integer

         ' Set the variable PassVar1 to equal 1234.
         PassVar1 = 1234

         ' Set the variable PassVar2 to equal 5678.
         PassVar2 = 5678

         ' Run the macro Receiver and pass the variables to the subroutine.
         ' On a Macintosh computer, you may need to omit the .xls file
         ' extension.
         Application.Run "Book2.xls!Receiver", PassVar1, PassVar2

      End Sub

2. Open a new workbook and type this code in a module sheet:

      Sub Receiver(PassVar1 As Integer, PassVar2 As Integer)

         ' Declare variable.
         Dim Result1 as Integer

         ' Manipulate the variables.
         Result1 = PassVar1 + PassVar2

         ' Displays the value of the variable PassVar1 in a message box.
         MsgBox PassVar1

         ' Displays the value of the variable PassVar2 in a message box.
         MsgBox PassVar2

         ' Displays the value of the variable Result1 in a message box.
         MsgBox Result1

      End Sub

3. Save this workbook as Book2.xls.

4. To close Book1.xls and retain the values passed to Book2.xls, make the

   following changes:

   a. In Book1.xls, modify the PassVarValues macro to read as follows:

         Sub PassVarValues()

            ' Declare variables.
            Dim PassVar1 as Integer
            Dim PassVar2 as Integer

            ' Set the variable PassVar1 to equal 1234.
            PassVar1 = 1234

            ' Set the variable PassVar2 to equal 5678.
            PassVar2 = 5678

            ' Run the macro Receiver and pass the variables to the
            ' subroutine.
            ' On a Macintosh computer, you may need to omit the .xls file
            ' extension.
            Application.Run "Book2.xls!Receiver", PassVar1, PassVar2

            ' The following line is new:
            ActiveWorkbook.Close    ' Closes the workbook Book1.xls.

         End Sub

   b. In Book2.xls, modify the Receiver macro to read as follows:

         ' Set the passed variables to Public so MacroDisp macro can
         ' display the values passed to Receiver
         Public NewVar1, NewVar2, Result2

         Sub Receiver(PassVar1 As Integer, PassVar2 As Integer)

            ' Declare variable.
            Dim Result1 as Integer

            ' The following 2 code lines are new:

            ' Set NewVar1 equal to PassVar1, so we can retain the passed
            ' variable value.
            NewVar1 = PassVar1

            ' Set NewVar2 equal to PassVar2, so we can retain the passed
            ' variable value.
            NewVar2 = PassVar2

            ' The variables in the following four code lines have been
            ' changed:

            ' Manipulate the variables.
            Result2 = NewVar1 + NewVar2

            ' Displays the value of the variable NewVar1 in a message box.
            MsgBox NewVar1

            ' Displays the value of the variable NewVar2 in a message box.
            MsgBox NewVar2

            ' Displays the value of the variable Result2 in a message box.
            MsgBox Result2

         End Sub

5. In Book2.xls, add the following macro to the module sheet:

      Sub MacroDisp()

         ' Displays the value of variable NewVar1 in a message box.
         MsgBox NewVar1

         ' Displays the value of variable NewVar2 in a message box.
         MsgBox NewVar2

         ' Displays the value of variable Result2 in a message box.
         MsgBox Result2

      End Sub

6. On the Tools menu, click Macro, and run the PassVarValues macro.

   When you run the MacroDisp macro, the variables previously passed to the
   Receiver macro will be displayed by MacroDisp. This behavior occurs
   because the variables are being retained by the public variables.

To Pass the Variable as an Argument Within the Same Workbook

This example shows variable PassVar1 being passed to another module without declaring it as Public. In this example it will be passed to a macro called RecVar2 on Module2 macro sheet.

1. Type this code on a module sheet named Module1 in a new workbook.

      Sub PassVar2()

         ' Declare variable.
         Dim PassVar1 as Integer

         ' Set the variable PassVar1 to equal 14.
         PassVar1 = 14

         ' Run the macro RecVar2 in Module2 and pass it the variable
         ' PassVar1.
         Module2.RecVar2(PassVar1)

      End Sub

2. Type this code on a module sheet named Module2 in the same workbook

   where the code above resides, and then save the workbook as Book4.xls.

      Sub RecVar2(PassVar1)

         ' Display the value of variable PassVar1 in a message box.
         MsgBox PassVar1

      End Sub

To Define the Variable as a Public Variable

This example shows how PublicVar1 declared as Public can be accessed without actually passing it into the macro RecVar in Module2. In this example, a call to the macro RecVar in the Module2 macro sheet is made and the value of PublicVar1 is displayed.

1. Type this code on a module sheet named Module1 in a new workbook:

      ' Set the variable PublicVar1 public, so module2 will have access to
      ' it.
      Public PublicVar1

      Sub PublicVar()

         ' Set the variable PublicVar1 to equal 12.
         PublicVar1 = 12

         ' Run the macro RecVar on Module2 macro sheet.
         Module2.RecVar

      End Sub

2. Type this code on a module sheet named Module2 in the same workbook

   where the code above resides, then save the workbook as Book3.xls

      Sub RecVar()

         ' Display the value of variable PublicVar1 in a message box
         MsgBox PublicVar1

      End Sub

Additional query words: 5.00 5.00a 5.00c 7.00

Keywords          : kbprg kbdta kbdtacode PgmHowto KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999