Tip:
Highlight text to annotate it
X
In this lesson, we will discuss how to pass arrays to Sub and Function procedures.
You can pass arrays to Sub and Function procedures in a similar manner as passing variables to Sub and Function procedures.
When passing arrays, you only need to write the array name in the argument list.
Inside the procedure, all arrays in the argument list are automatically declared.
As we will see in the following example, it is often useful to pass the number of rows and columns in the array as well.
In this example, multiple arrays are passed down to a Sub procedure named addarrays and these arrays are used to calculate the
values stored in another array.
Option Base 1 forces the lowest index number of all array dimensions to be 1 instead of the default value of 0.
Inside the macro named main, two constants are created named nrow and ncol.
nrow will store the number of rows in the arrays A, B and C.
ncol will store the number of columns in the arrays A, B and C.
Each array is given a Double data type and will have 2 rows and 2 columns.
Next, all four elements and A and all four elements of B are assigned values.
Then a Sub procedure addarrays is called.
This Sub procedure will take two arrays, A and B, and add them together.
The resulting array will be passed back up to main and stored in the array C.
Notice that we only need to include the array name in the argument list.
We also pass down nrow and ncol as they will be useful in determining the array C.
It is good programming practice to protect all variables and arrays from being accidentally changed if you do not intend for them to be
changed in the Sub procedure.
The contents of array A in main are passed to array W in addarrays.
The contents of array B in main are passed to array X in addarrays.
nrow and ncol in main are passed to nr and nc in addarrays.
The array C in main will be linked with the array Y in addarrays since there are no parentheses around C.
Any changes made to Y in addarrays will affect the array C in main.
We do not need to declare W, X, nr, nc, or Y in addarrays since they are in the argument list.
W, X, and Y are automatically given the same dimensions as A, B, and C; they are all 2 x 2 arrays.
In this Sub procedure, we will be using the variables i and j.
They need to be declared since they are not in the argument list.
When adding the two arrays together element W(i,j) will be added to element X(i,j).
The resulting value will be stored in element Y(i,j).
We need to execute this statement for all combinations of i and j.
This task can be accomplished using nested For loops with i as the variable for row number and j as the variable for column number.
The values of i and j will be: first 1 and 1, then 1 and 2, then 2 and 1, and finally 2 and 2.
When addarrays is terminated, the values stored in Y will be passed back up to the array C.
Then a message box appears that displays the values stored in C(1,1) and C(2,2).
When this macro is run, 5 and -80 are displayed since A(1,1) + B(1,1) is 2+3, or 5, and A(2,2) + B(2,2) is 20+(-100), or -80.
Notice that this sub procedure will work with arrays of any size as long as we specify the
number of rows and number of columns of those arrays.