XL: Passing Variables in Visual Basic for Applications Macros

• 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.

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 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 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/default.asp

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.