Worked example

To see how passing parameters by value and by reference work in practice, let's 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 calledpassVal 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 v alue ofpassVal passed back to the sub after the function call is 10. That's b ecause it has b een 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 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 Inte SqNum = passVal * passVal PassVal = 20 End Function ger)

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 b ox shows that the value passed back to the calling procedure is also 10 not 20. This is because the parameter has b een 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 [Passing by Value

SALESMAN

Number p<

To maintain we«

to calculate sales to for each representat n & month total

îe Monthly Total the Bonus Rate

Function value is = 100

Number passed back to square function is = 10

ïlumn for the beginning of each

Andy

Bindi

Katie

Misba

Amy jajxj g Microsoft Encel - SALESMAN

S vile small Workbook Data

Workbook Name

System Purpose:

End of Month Sales

Monthly Bonus

Sales To Date

Month No:

Bonus Rate

Month No:

Weekly Sales

End of Month S!a

Rep Name sales To Date

Monthly Total

Monthly Bonus

385X10

680X10

1,860X10

900X10

785X10

965X10

Figure 9.6 Screenshot with MsgBox displays with pass by value Figure 9.7 Screenshot of the second MsgBox display after value passed by reference
0 0