Function Parameter Array Passing

Introduction

Last week, we explored the use of ByVal (By Value) and ByRef (By Reference) in function parameters. These keywords determine how data is passed from the calling function to the called function—either as the value stored in a variable (ByVal) or as the memory address (reference) of the variable (ByRef). If you have not yet read that article, you can find the link below:

• Function Parameter ByVal and ByRef usage.

Now, we will learn:

1. How to pass the location address of a single element of an Array to the called Function and change its value?

2. How to pass the Location Address of an array and sort the values in the array in Descending Order.

Two Test Programs.

Function ArrayArg_Test1().

First, let us write two small programs for the first example. The first Program Code is given below.

Public Function ArrayArg_Test1()
Dim NumArray(1 To 5) As Integer, j As Integer

'load array with numbers 1 to 5
For j = 1 To 5
   NumArray(j) = j
Next

'pass a single element value
Call ArrayArg_Test2(NumArray(4))

'Print the Array values after change
For j = 1 To 5
  Debug.Print j, NumArray(j)
Next

End Function

'Result:
1
2
3
20
5

In the first line of code, we define an array variable (NumArray()) with five elements to store integer values. We also declare a control variable j for use in the For…Next loop.

Inside the For…Next loop, the array is populated with values from 1 to 5 — for example, NumArray(1) = 1, NumArray(2) = 2, and so on, up to the fifth element.

Next, we call the ArrayArg_Test2() function, passing NumArray(4) as a parameter. The number 4 inside the brackets refers to the index position of the element being passed—not its value. In this case, the element at index 4 happens to hold the value 4. The ArrayArg_Test2() function receives this argument either as the value itself or as a reference to its memory location, depending on how the function’s parameter is defined.

If the parameter is defined with ByVal, the value of the element is copied into a local variable within the function, leaving the original array element unchanged. If the parameter is defined with ByRef, the function works directly on the original element, without creating a copy. If neither ByVal nor ByRef is explicitly specified, ByRef is used by default. In our example, we will use this default behavior and omit the ByRef keyword in the function’s parameter definition.

In the next For … Next loop, the contents of the array are printed in the Debug window. If the ArrayArg_Test2() function has made any changes to the Array, those changes will be reflected in this printed list. Since we already know that the array elements 1 to 5 initially contain the values 1, 2, 3, 4, and 5, we did not print them before calling the second function.

Function ArrayArg_Test2().

The ArrayArg_Test2() Function VBA Code is given below:

Public Function ArrayArg_Test2(NA As Integer) 'The word ByRef is omited
'multiply NumArray(4) value * 5 = 20
    NA = NA * 5
End Function

The Variable NA is assigned to the NumArray’s 4th element location address.  The ArrayArg_Test2() picks the value from NumArray(4) itself, multiplies it by 5, and stores the result back in the same location.

This was working with a single element of an Array.  What about passing the full Array’s location address and working with hundreds of elements of this array in the called Function? 

Get the Array Sorted and Print.

We will now pass the same array used in the previous example as a parameter to a sorting function, which will arrange its values in descending order.

The modified version of the First Function Code is given below. 

Public Function ArrayArg_Test3()
Dim NumArray(1 To 5) As Integer, j As Integer

For j = 1 To 5
   NumArray(j) = j
Next

'Pass the array to the called function
Call ArrayArg_Test4(NumArray())

'Print the Sorted Array
For j = 1 To 5
  Debug.Print j, NumArray(j)
Next

End Function

Check the function call statement. NumArray() is passed without the element number, as we did in the earlier example. The opening and closing brackets are required along with the array variable name to indicate that the parameter passed is an array, not a single variable.

When control is returned from the ArrayArg_Test4() function, the sorted list of numbers is printed in the debug window. The value printed on the left side is the array element number, and the right-side value is the array value itself,  sorted in descending order.

Sort the Array in Descending (Z-A) Order.

The Data Sorting Program is given below:

Public Function ArrayArg_Test4(apple() As Integer)
Dim j As Integer, k As Integer, Temp As Integer

'Bubble Sort the Array in Descending order
' 1st loop runs maximum array elements minus 1 times

For j = 1 To 5 - 1 ' in place of 5-1 you may use Ubound(apple)-1

   ' inner loop starts with outer loop's current value + 1
   ' and runs to the maximum number of array elements times

      For k = j + 1 To 5 ' replace 5 with Ubound(apple)

     If apple(k) > apple(j) Then 'if second value is greater
     
        Temp = apple(j) 'copy 1st value to Temp Variable
        
        apple(j) = apple(k) 'move greater value up
        
        apple(k) = Temp ' move the smaller value down
        
     End If
    Next k ' compare next two elements
Next j
End Function

To sort the values in ascending order, the only change needed in the program is to replace the greater-than (>) comparison operator with the less-than (<) operator. In our example, the numbers were already loaded into the array in ascending order.

To make the program work with arrays of any size, replace the fixed constant 5 - 1 in the first For… Next loop with UBound(apple) - 1, and replace 5 in the second loop with UBound(apple). This way, the program can handle arrays with any number of elements without requiring further changes.

Also, note that we omitted the ByRef keyword in the parameter definition of the called ArrayArg_Test4() function. Since ByRef is the default in VBA, the parameter is automatically treated as a ByRef variable.

• Autonumber with Date and Sequence Number
• Overlaying Sub-Forms in Real-Time
• Opening Multiple Instances of Form
• Conditional Formatting in Continuous Form
• Control Setfocus on Tab-Page Click

Function Parameter ByVal and ByRef Usage

Introduction.

Before taking up the above subject, let us look at some fundamentals of the variables for the benefit of novices.

When we define a variable in VBA or in any other programming language, the computer reserves some memory location and allocates some memory cells (the number of cells allocated depends on the declared variable type, like Integer, Double, String, etc.) to store values passed to it. 

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

1. Make copies of the apples (the number) from the box ourselves and put them into another box and pass it.  The target box's name will be different.  The recipient of the new box can work with his copy of the apples, like adding more apples in his box or removing some of them, etc.  There will not be any change in the first box's contents. 
2. We can tell the other person (or Function), which area of the room (location) you have kept the original box of apples, get the box contents from there, and work with it.  In this room (or within the function body) there may be other boxes (Variables) with different names and contents.

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 original value will not change in the first Variable.  The Function that gets the new Variable with the copy has no access to the first Variable.  He can do whatever he wants to do with the copy he has.

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 above program, we are defining two Variables Apple_Box1 and ApplesReceived both to store Long Integer type values. Here, we are not going to bother about what is Long Integer or Short Integer and the range of values that we can store 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 are doing in the next line. 

We are calling 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 take a copy of the value from Apple_Box1.  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 ApplesReceived Variable. 

Next line MsgBox() function displays the Apple_Box1 contents and the result value (after modification done to the original copy of the value) received from 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

There is only one executable statement in the above function.  Immediately after the function definition, four lines of remarks indicate what happens in the function.

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

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

The computer always evaluates the expression given on the right side of the equal sign first and arrives at a single value and moves that result in the Variable given on the left side of the equal sign.  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 we have used the Apple_Box2 variable on the left side and right side of the = sign. As I said earlier the expression on the right side of the equal sign is evaluated first. So it takes the existing value of 10 from Apple_Box2 for calculations and Adds 15, arriving at the single result value of 25, and moves that value into Variable Apple_Box2, replacing the earlier 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 have 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 what is happening in the next line that 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 Name 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 is not returning any value back into 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 Test_1A Function.  But, you cannot write the statement as:

Test_1D(Apple_Box1).,

Once we use the parenthesis (normally used 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 it 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 feel comfortable with this method, then you may do so. You will be defining one more variable for this purpose and your program takes up more memory.

The usage of the Call statement requires the parenthesis around the parameter variable. If no parameters to pass to the called function even then you should use the opening and closing parenthesis at the end of Function Name like Call Test_1D().

When control returns from Test_1D() the next line displays the changed value in 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 location address of the parameter variable passed to it. It works directly with the value stored in the variable Apple_Box1's own location.

Public Function Test_1D(ByRef Apple_Box2 As Long)

Apple_Box2 = Apple_Box2 + 15

End Function

 Test_1D() takes the location address (this is the memory location, number of the variable) of variable Apple_Box1 into variable Apple_Box2, defined in the parameter area of 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 computer uses the location address to pick the value from there and use the value in calculations.

Even though we have used ByRef to the Parameter Variable, to accept the location address of the variable passed to it (always a number irrespective of different variable types), the rest of the parameter definition is like any other variable with variable Type (as Long) specification.  Compare Test_1D() function definition with the Test_1B().  Test_1D doesn't have the As Long at the end of the line because it is not returning any value from the function but it changes the original value at its location directly. 

You may omit the usage 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 with enough memory cells, besides its own address, to hold their maximum range of values. This is different between Programming Languages: VBA, C, C++, C#, etc.

In Conclusion.

We don't have to bother about going too deep into all those things, but it does no harm to have a basic understanding of them.

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.

• Useful Report Functions
• Join Split Array Functions
• Join Split Array Functions-2
• Macros and Temporary Variables
• Sub-query in Query Column Expressions