Dec 10, 2015

When you design a procedure in VBA you may want to pass some data values to it for processing. You pass data through arguments; like a and h below on lines 6 to 8;

6      Function TriangleArea(Base as single, Height as single) as single

7       TriangleArea = 0.5 * Base * Height

8      End Function

Now you call the function from another procedure using something like shown on line 4 below;

1     Sub Triangle()

2       b = 25

3       h = 8

4       MsgBox TriangleArea(b, h) & “------“ & b

5      End Sub

When we run the Triangle() procedure we get 100-----25 in the message box since 0.5 * 25 * 8 is 100 and b is 25. This is what happens internally;

• At line 2, VBA allocates a memory location at a specific memory address let’s say 110011 and gives it an alias name b and then puts number 25 in it.
• At line 3, VBA allocates another memory location at a memory address, let’s say 220022 and gives it an alias name h and then puts number 8 in it.
• At line 4 it jumps to executes line 6 because we have made a call to TriangleArea() function. At this point VBA gives another alias name to the above memory locations i.e. calls 110011 Base and 220022 Height, the two memory locations have now got two aliases each.
• At line 7 the mathematical expression is evaluated to 100
• At line 8 the control is now back to line 4 where it displays the calculated value in the message box.

Let’s now add a new line to our Function so that it reads like this;

6      Function TriangleArea( Base as single, Height as single) as single

7       Base = 50

8       TriangleArea = 0.5 * Base * Height

9      End Function

Of course when we execute the Triangle() now we get the value 200------50 because 0.5 * 50 * 8 is now 200, and because base has changed to 50 in the function therefore b should also have changed to 50 since b and Base were just two aliases referencing the same memory address 110011.

If you don’t want this to happen then you need to add the ByVal keyword to the declaration of Base like this;

6      Function TriangleArea( ByVal Base as single, Height as single) as single

7       Base = 50

8       TriangleArea = 0.5 * Base * Height

9      End Function

Now here’s what happens:

1. At line 2, VBA allocates a memory location at a specific memory address let’s say 110011 and gives it an alias name b and then puts number 25 in it.
2. At line 3, VBA allocates another memory location at a memory address, let’s say 220022 and gives it an alias name h and then puts number 8 in it.
3. At line 4 it jumps to executes line 6 because we have made a call to TriangleArea() function. At this point since we have used the keyword ByVal, VBA creates another separate memory location for Baselet’s say at address 330033 and calls it of course Baseand copies the value from 110011 (or b) in it. So at this point b and Baseare NOT aliases for the same memory location but two aliases each for separate memories but the value of both is 25.
4. At line 7 Base(address 330033) is now changed to 50 but, of course, b (address 110011) is not.
5. At line 8 the mathematical expression is evaluated to 200
6. At line 9 the control is now back to line 4 where it displays the calculated value in the message box. Now it will display the calculated value along the value in b, i.e. 200------25 and not 200------50 since b and Base are two different values.

So as you can see ByVal creates a new address reference and copies the value into it so that if the value in the called procedure changes it does not impact on the value in the caller, whereas ByRef keyword allows the called procedure to access the same memory reference as in the caller. By the way as you may have guessed ByRef is the default behaviour, so you may not need to mention it.