VBA for Excel 2007 tutorial-Passing Parameters


Passing Parameters


You can pass values to the sub-procedure or function through its parameters.
Example: In this example two integer values will be passed to the sum
 sub-procedure through x and y parameters.
  Sub sum(Byval x As Integer, ByVal y As Integer)
     Dim s As Integer
     s=x+y
     Msgbox "Total=" & s
  End Sub
 
To call the sub-procedure to sum the values (e.g. 10 and 20) for you, you
can write as shown below:
   Call sum(10,20)

-Passing parameters by value and by reference


To pass values to the procedure, you have two choices—passing by
value (see example above) and passing by reference. By passing the
parameters by value, the values of variables to be passed as the parameters
can’t be changed by the procedures that call them. This contrasts to passing
parameters by reference. When you pass parameters by reference, you
pass the addresses(memory location) of the variables, not the values of
the variables. Therefore, the values of the variables can be changed by the
procedures that call it.
 
Example: Passing by value
 
  Private Sub Command0_Click()
      Dim x  As Integer
      x = 10
      Call square(x)
      MsgBox "The value of x is " & x
  End Sub
 
  Sub square(ByVal x As Integer)
    x = x * x 'Try to change the value of x
    MsgBox "Square=" & x
  End Sub
 
If you run this code, the program will show “The value of x is 10”. This
indicates that the original value of x(10) can’t be changed by the square
sub-procedure.
In some cases, you may want to let the program modify the values of the
variables to be passed to the procedure, you can pass them by reference.
Example: Passing by reference
Now let modify the example code above by replacing Byval keyword with Byref keyword.
 
  Private Sub Command0_Click()
       Dim x  As Integer
       x = 10
       Call square(x)
       MsgBox "The value of x is " & x
  End Sub
 
  Sub square(ByRef x As Variant)
    x = x * x 'Try to change the value of x
    MsgBox "Square=" & x
  End Sub
 
If you run this modified code, the program will show “The value of x is
100”. This indicates that the original value of x(10) can be changed by the
square sub-procedure and the new value is 100. Note: We need to change the data type of the parameter from Integer
type to Variant to store the address value of the x variable.


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.