Function Parameter Array Passing ~ LEARN MS-ACCESS TIPS AND TRICKS

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:

How to pass the location address of a single element of an Array to the called Function and change its value?
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.