When a parameter is passed by value using the ByVal keyword, the called parameter obtains its own separate copy of the variable. The value of the variable can therefore be changed in the called procedure without affecting its original value in the calling procedure. This means that as in the function GetMaxSalesInfo, when it changes the values of the parameters on lines 8 and 9, these changes also take place in the calling procedure. So in line 5 of TestMaxSalesInfo, the two actual parameters theAddress and theRepname are also changed, when the function is called. The syntax for declaring a ByVal parameter is to insert the keyword ByVal before the parameter list is
declared in the parentheses following the function name. If we had a function called thisFunct that used a string parameter called myStr, and we wanted to pass this parameter by value, then the function declaration would be:
Function thisFunct (ByVal myStr as String)
Worked example
To see how passing parameters by value and by reference work in practice, lets look at an example of what happens when we pass parameters by reference and by value.
Consider the function, sqNum, which squares an integer, and then the sub, testSqNum, which makes a call to sqNum. Listing 9.9 displays the sub testSqNum code. Notice that an Integer variable called passVal is declared and assigned the value 10 in the sub. The call to the pass by value function is sandwiched between the two message box displays: the first displays passVal before it is passed to the function, the second displays passVal after being passed to the function. Notice from Figure 9.6 that the value of passVal passed back to the sub after the function call is 10. Thats because it has been passed by value and that means that it cannot change when passed to the function.
Now consider what happens when the parameter is passed by reference as shown in Listing 9.10. When the code is run, the passVal passed back to the sub after the function is called has the value 20 (see Figure 9.7). This is because it has been passed by reference and that means that it can be changed when passed to the function.
Listing 9.9
Sub testSqNum ()
Dim passVal As Integer PassVal = 10
MsgBox "Number passed into square function is = " & passVal MsgBox "Function value is = " & sqNum (passVal) & vbCrLf & _ "Number passed back to square function is = " & passVal End Sub
Listing 9.10 Passing a parameter by reference Function sqNum (ByRef passVal As Integer)
SqNum = passVal * passVal PassVal = 20
End Function
Now let us see what happens when we pass the parameter by value instead.
You can see from Figure 9.6 that the first message box shows the number going into the function is 10, as expected, but the second message box shows that the value passed back to the calling procedure is also 10 not 20. This is because the parameter has been passed by value giving a result as expected.
Listing 9.11 Passing a parameter by value
Function sqNum (ByVal passVal As Integer) SqNum = passVal * passVal
PassVal = 20 End Function
Figure 9.6 Screenshot with MsgBox displays with pass by value
Comparison of ByRef and ByVal
When passing parameters to procedures ByRef is faster than ByVal because passing by reference gives instant access to the parameters value. However, the VBA programmer has to decide whether passing a parameter by reference is likely to have any harmful side effects on the parameter in the calling procedure. If this possibility exists then passing by value will be the sensible choice.
Figure 9.7 Screenshot of the second MsgBox display after value passed by reference
Exercises
1 Open a new workbook using a new module, write the VBA code in Listings 9.4 and 9.5. Run the subprogram to make sure it works correctly.
2 Write a function called TrebleMe that takes a integer value and multiplies it by 3. Test this function procedure by using a blank worksheet and enter
=TrebleMe (30) in cell B3.
3 Write a function called AddTwo that will take two integer parameters and return their sum.
4 Write a function called AddTwo that will take two parameters as string values and return their sum as a string value.
5 Open the weeklysales sheet of the SALESMAN workbook. Create a function called IsValidRepName that will take a repName as a parameter and returns True if the repName exists in the range on the worksheet. Test your function using =IsRepName (Jack), and =IsRepName (Rhiannon) using any blank cells on the worksheet.
Write the following sub procedure for testing the function.
Sub TestIsValidRepName () Dim tryRep As String
TryRep = InputBox "enter a repName for testing"
MsgBox "the repName is valid: " & IsValidRepName (tryRep) End Sub
Amend your function from (3) so that it has an extra parameter to pass back the address of the cell in which that repName is found. Test your function.
Amend your function from (4) so that it has a third extra parameter to pass back the value of the miles to date for that RepName. Test your function.
Try out the pass ByRef and ByVal example in 5.4 and 5.5.
6 Study the following sub procedure and try to find out what it does.
Sub IsActiveCellEmpty()
Dim stFunctionName As String, stCellReference As String stFunctionName = "isblank"
StCellReference = ActiveCell.Address
MsgBox Evaluate (stFunctionName & "(" & stCellReference & ")") End Sub