Function Parameter ByVal and ByRef Usage ~ LEARN MS-ACCESS TIPS AND TRICKS

Function Parameter ByVal and ByRef Usage.

Before exploring this topic, let us review some fundamental concepts of variables for the benefit of beginners.

When we define a variable in VBA or any other programming language, the computer allocates memory cells (let us skip the technical aspect and stick to the basics) to store a value there.

In a layman's analogy, we can imagine a variable as a box with the name 'Apple' or whatever name we give to the box, and use that name to pick the value stored in it. Assume that you have put 5 Apples in the box named A. We can give these apples to someone in two ways.

Make a copy of the apples (the number) from box A, and put the copy into another box B. The owner of box B can work with his apples, adding more Apples to box B or removing a few from B, and so on. There will not be any change to the contents of box A.
We can tell the other person (or Function) which area of the room (location) you kept the original Apples box A, and it can take control of box A and work with it directly. No need for a new Box B. But if you would like to preserve the original Box contents for some other purpose, then make a copy into some other box and work.

Making Copies of Original Values.

In the first method explained above, unlike the physical box, you can make copies of the original value and store them in different Variables. The Function that receives the value has no access to the first Variable. The Function can work with the copy of the value received.

In the second case, you can tell the location of the Apple_Box1 to the other Function so that it can go there and find its contents and do whatever the Function wants to do with them (add, subtract, multiply, etc., or use it as part of other calculations) or whatever operations you would like to do with them.

To prove the first point above, let us take a closer look at the next two example functions, Test_1A() (the calling function) and Test_1B() (the called function with the copy of the value).

Method-1 Examples:

Public Function Test_1A()

'Define two variables(boxes)to hold values of
'Long Integer type
Dim Apple_Box1 As Long, ApplesReceived As Long

'Put an initial value of 10

'into the variable

Apple_Box1 = 10

'sending a copy of Apple_Box1 Value to Apple_Box2

'Whatever value changes happened in Apple_Box2 in Test_1B()
'Received back into the third box:ApplesReceived

ApplesReceived = Test_1B(Apple_Box1)

'call funcction Test_1B() with the value
'Display the result in MsgBox.
MsgBox "Apple_Box1: " & Apple_Box1 & vbLf & "Apple_Box2 contents: " & ApplesReceived

End Function

In the code above, we define two variables, Apple_Box1 and ApplesReceived, both of which can hold long integer values. Here, we need not bother about what a long or short integer is, and what range of values can be stored in them, etc.

Next line: Apple_Box1 = 10; the value 10 is stored in Apple_Box1.

The next three lines are remarks explaining what we do in the next line.

We are calling the Test_1B() Function and passing the value of Apple_Box1 to the function to do something with the value received in a Variable (ByVal Apple_Box2). The ByVal before Apple_Box2 given in Test_1B() function states that receive a copy from Apple_Box1 value. The ‘As Long’ appearing after the closing parenthesis indicates that the second function does some calculations with the received value and returns the result to the first function. The new value received is stored in the ApplesReceived Variable.

The Next line, the MsgBox() function, displays the Apple_Box1 contents and the result value (after modifications done to the copy) returned from the Test_1B() function.

The Test_1B Function.

Public Function Test_1B(ByVal Apple_Box2 As Long) As Long

'Take Note of the Key-Word 'ByVal' (says take a Copy of the Passed Value)
'Return the value back into the first function Test_1A
'After adding 15 to the original value of 10
'copied into Apple_Box2

Test_1B = Apple_Box2 + 15

End Function

Only one executable statement in the above function. Immediately after the function definition, four remark lines indicate what happens in the function.

We will take a closer look at the next statement. This statement has two parts – the first part appears left of the equal (=) sign and the second part on the right side,

In this expression, the left side of the equal sign will be a Variable or a Function name. By now you may be asking yourself why a function name is there. That is explained in a moment.

The expression on the right side of the equal sign evaluates first, and the result is returned in the left-side variable. Any existing value in the variable will be lost. You can write that expression in two lines to arrive at the same result as below:

Apple_Box2 = Apple_Box2 + 15

Test_1B = Apple_Box2

In the first expression, you can see that the Apple_Box2 variable is used on both the left and right sides of the = sign. As mentioned earlier, the expression on the right side of the equal sign is evaluated first. Therefore, the existing value of 10 stored in Apple_Box2 is used in the calculation, and 15 is added to it, producing a result of 25. This resulting value is then assigned to the Apple_Box2 variable, replacing its previous value of 10.

If the Function name (the function name given in the first line of Code) appears on the left side of the equal sign, then the meaning of the statement is that the final result of the expression must be returned to the Calling Function. Here, the Function Name acts as a Variable with the Data Type (As Long) specified immediately after the closing brackets on the first line.

This function name appears on the calling statement in the calling function to the right side of the = sign, and a Variable Name on the left side of the = sign that saves the received result value (ApplesReceived = Test_1B(Apple_Box1).

Method-2 Examples:

In this method, we defined only one variable, Apple_Box1, as a Long Integer Type. In the next line, the Variable is loaded with an initial value of 10. The next two lines are remarks explaining the next line of code, which calls the second function Test_1D().

Compare this statement with the statement that calls Test_1B. Unlike the statement that calls Test_1B(), the Function Test_1D and the function parameter, Variable Apple_Box1, only appear here. The opening and closing brackets are omitted from the function name. The parameter variable is the second item. Test_1D() function returns no value to the calling function Test_1C. Therefore, we don't need to write this line of code in the form of an expression as we did in the Test_1A Function. But you cannot write the statement as:

Test_1D(Apple_Box1).,

Once we use the parentheses with the function name around the parameter variable, then VBA assumes that some value is returned from the called function, and you are forced to write it like we did in Function Test_1A:

x = Test_1D(Apple_Box1) 'Expression
'OR use Call statement 
Call Test_1D(Apple_Box1)

There will not be any value in the variable x because no value is returned from the called function.

If you are comfortable with this approach, then you may stick with that. You need to define one more variable for this purpose, and your program needs more memory.

The Call statement needs parentheses around the parameter variable. If there are no parameters to pass to the called function, then it requires the opening and closing parentheses at the end of the function name: Call Test_1D().

When control returns from Test_1D(), the next line displays the changed value in the Apple_Box1 Variable.

Public Function Test_1C()
Dim Apple_Box1 As Long

'put 10 into Variable
Apple_Box1 = 10

'here Test_1D function takes the
'location address of Apple_Box1

Test_1D Apple_Box1 'compare this statement with Test_1A function

MsgBox "Apple_Box1: " & Apple_Box1 & vbLf & "Apple_Box2 contents: " & ApplesReceived

End Function

Test_1D() function takes the parameter's location address. It works directly with the Apple_Box1 value.

Public Function Test_1D(ByRef Apple_Box2 As Long)

Apple_Box2 = Apple_Box2 + 15

End Function

Test_1D() takes the location address of variable Apple_Box1 into variable Apple_Box2, defined in the parameter area of the Test_1D() Function.

Now, look at the expression:

Apple_Box2 = Apple_Box2 + 15

Apple_Box2 contains the location address, not the contents of the Apple_Box1 variable. But, no need to make any change in the expression to do any kind of calculations. The expression uses the location address, picks the value from there, and uses it in calculations.

Although we have explicitly used ByRef to specify that the parameter is passed by reference, allowing the function to access the variable's memory address, the rest of the parameter declaration follows the same syntax as any other variable declaration, including the matching data type specification (As Long). Compare the Test_1D() function definition with Test_1B(). The Test_1D() function does not include As Long at the end of its declaration to specify a return data type because it does not return a value. Instead, it directly modifies the original variable's value at its memory location.

You may omit the keyword ByRef from the Function declaration. By default, VBA assumes that the Function Parameter declaration is ByRef (By Reference) if you have not explicitly defined the parameter as ByVal, like:

Public Function Test_1D(Apple_Box2 As Long)

Each Variable Type, like Byte, Integer, Long (integer), Double, String, etc., gets allocated enough memory cells, besides its own address, to hold its maximum range of values. This is different between Programming Languages: VBA, C, C++, C#, etc.

In Conclusion.

We don't have to go too deep, but it does help to know the basics.

If you get in touch with the C language and its variants, like the examples given above, you need to deal with these things, sooner or later.

Our discussion here was on a single variable and its value. How we can work with an Array of Variables and Values. We will explore its methods in the coming weeks.