XL2000: How to Pass Variables in Visual Basic for Applications Macros

In Microsoft Excel, 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.

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 a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

To Pass a Variable to Another Workbook

1. Start Excel, and then press ALT+F11 to start the Visual Basic Editor.
2. On the Insert menu, click Module.
3. Type the following code into the module sheet:

   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
   					

4. Press ALT+F11 to return to Excel.
5. On the File menu, click Save.
6. In the File name box, type Book1.xls, and then click Save.
7. Click New on the standard toolbar to open a new workbook.
8. Switch to the Visual Basic Editor.
9. On the Insert menu, click Module, and then type the following code into the new 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
   					

10. Switch to Excel and then save this workbook as Book2.xls.
11. To close Book1.xls and retain the values passed to Book2.xls, follow these steps:
    
    
    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
       							

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

13. On the Tools menu, point to Macro, and then click Macros.
14. In the Macro name list, click PassVarValues, and then click Run. When you run the MacroDisp macro, the variables previously passed to the Receiver macro are displayed by MacroDisp. This behavior occurs because the variables are being retained by the public variables.

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 is passed to a macro called RecVar2 on Module2 macro sheet.

1. Start Excel, and then press ALT+F11 to start the Visual Basic Editor.
2. On the Insert menu, click Module.
3. On the Module sheet (Module1), type the following code:

   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
   					

4. On the Insert menu, click Module.
5. Type the following code into the new module sheet (Module2):

   Sub RecVar2(PassVar1)
      ' Display the value of variable PassVar1 in a message box.
      MsgBox PassVar1
   End Sub
   					

6. Switch to Excel, and then save the workbook as Book4.xls.

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. Open a new Excel workbook.
2. Switch to the Visual Basic Editor.
3. On the Insert menu, click Module.
4. Type the following code in a module sheet named Module1 in the 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
   					

5. On the Insert menu, click Module (Module2).
6. Type the following code in Module2:

   Sub RecVar()
      ' Display the value of variable PublicVar1 in a message box
      MsgBox PublicVar1
   End Sub

7. Switch to Excel, and then save the workbook as Book3.xls.