Microsoft KB Archive/140033

= Passing Variables in Visual Basic for Applications Macros =

Article ID: 140033

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q140033





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. 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
  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   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  Save this workbook as Book2.xls. To close Book1.xls and retain the values passed to Book2.xls, make the following changes:

  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   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 </li></ol> </li>  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 </li> 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.</li></ol>

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.

<ol>  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 </li>  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 </li></ol>

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.

<ol>  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 </li>  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 </li></ol>

Additional query words: 5.00a 5.00c XL

Keywords: kbdtacode kbhowto kbprogramming KB140033

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.