Passing Variables in Visual Basic for Applications Macros

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.

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:
   
   
   1. 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
      								

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