Important: Most of the time we will be passing arguments by reference, which is default. Because it is default there is no need for ByRef notation.
We will look at passing arguments by value, and by reference. Passing arguments is defined as passing the argument values of the called subroutine or function to the variables from the main program.
Where arguments are passed by reference, any changes that are made to a variable in the called subroutine will be passed to the main program.
Where arguments are passed ByVal, changes made to variables in the called subroutine do not affect the values in the main subroutine. In other words, if you need to keep the main program data intact, you must pass the data by value.
ByVal can be coded as follows in the called subroutine:
Sub Add(ByVal Arg1)
Program calling a subroutine with variables and an array as arguments.
Sub SimpleAddition()
Dim d As Long 'First we declare d, e, f, and myArray(5)
Dim e As Long
Dim f As Long
Dim myArray(5) As Long 'myArray(5) is a six element array
d = 9
e = 2
myArray(1) = 10 'element numbered 1 is set equal to 10
myArray(2) = 7 'element numbered 2 is set equal to 7
Call subAdd(d, e, f, myArray) 'The variables d, e, f, myArray are substituted for arg1,
MsgBox "Sum f = " & f 'f = 11 'arg2, arg3, argArray
MsgBox "myArray(3) = " & myArray(3) 'myArray(3) = 17
End Sub
Sub subAdd(arg1,arg2,arg3,argArray)
Dim myVar As Long 'myVar is declared a local variable
arg3 = arg1 + arg2 'Because of substitutions this is actually f = e + d
myVar = 20 + 21
MsgBox "myVar from sub = " & myVar 'myVar from sub = 41
argArray(3) = argArray(1) + argArray(2) 'Because of substitutions this is actually
End Sub 'myArray(3) = myArray(1) + myArray(2)
The program SimpleAddition() calls a subroutine subAdd(arg1,arg2,arg3,argArray) with variables and an array for arguments. Variables d, e, f, and the array myArray are substituted for arg1, arg2, arg3, and array argArray. In subAdd() the variable myVar equals 41, arg3 equals 11, and argArray(3) equals 17. myArray(1), myArray(2), and myArray(3) are members of the array myArray. When argArray is passed to myArray, it carries the values of all its elements, including the elements argArray(1), argArray(2), and argArray(3). In the comments I have described the substitutions. It is important to understand that the values d, e, f, myArray are substituted into arg1, arg2, arg3, argArray. Likewise, after calculations in subAdd(arg1,arg2,arg3,argArray) the values of arg1, arg2, arg3, argArray are passed by reference back to the main program into d, e, f, myArray.
Notice that myVar in the called subroutine must be declared or you will get the error message Variable not defined. Do not declare arg1, arg2, arg3, argArray in the called subroutine or you will get the error message Duplicate declaration in current scope.
A local variable is declared within a function or subroutine. You can only use the value of that variable in the function or subroutine. On the other hand, a public variable is available to all procedures (functions and subroutines) in a module. All variables we have declared so far with the Dim statement have been local variables. To create a public variable we must declare with the Public assignment. To use Public we must declare in the header of the code window.
Open the Code Window and be sure that at the top of the code window it says (General) and (Declarations). Declare the public variables. The following screenshot is an example.
To prepare for this program, enter 10 random values into Column J, starting with J1. The last value is 999, and marks the end of the input values. Then run the program from the Macro dialog box. The list will then be sorted and printed out to Column K. The program is on the next page.
Please note that in the following Bubble Sort program, I appear to have broken my rules for use of arguments in subroutines and functions. Sub ReadList and sub Write_List do not have arguments for input and output variables. Sub Myswap(i, j, list) repeats its arguments. It uses i, j, list in the main program and in the subroutine. We can break these rules because we declared i, j, n, list(20) as Public variables. However it is still the best policy to write your programs as described in Example programs in section 5.2 Example Program Calling a Subroutine and Function with Arguments and section 12.2 Program Calling a Subroutine with Variables and an Array as Arguments.
As described in Chapter 12 Section 3, you will need to enter the following variables as Public: i, j, n, list(20)
Public i As Long
Public j As Long
Public n As Long
Public list(20) As Long
' BUBBLE SORT PROGRAM
'Because the variables i, j, n, list(20) are used in several different subroutines,
'they are declared Public as shown above.
Sub Main()
Call ReadList 'Reads in a list of values
For i = 1 To n 'Starts the sort
For j = i + 1 To n
If list(i) > list(j) Then Call Myswap(i, j, list)
Next j
Next i
Call Write_List 'Writes out the sorted values
MsgBox "End of sort"
End Sub
Sub ReadList() 'ReadList subroutine for main()
Dim r As Long
For r = 1 To 20
list(r) = Cells(r, 10) 'Read list from Column J into the array
If Cells(r, 10) = 999 Then Exit For 'Exit when last value is 999
n = r 'n is the number of values in array list to sort
Next r
End Sub
Sub Write_List() 'Write_List subroutine for main()
Dim r As Long
For r = 1 To n 'n is the number of values in array
Cells(r, 11) = list(r) 'Write sorted list to Column K
Next r
End Sub
Sub Myswap(i, j, list) 'Swap() subroutine for main()
Dim temp As Long
temp = list(i) 'Swaps the values of list(i) and list(j)
list(i) = list(j)
list(j) = temp
End Sub
Table of Contents
Ch1-Introduction
Ch2-Loops
Ch3-If Statements
Ch4-Functions
Ch5-Subs & Functions
Ch6-Read & Write
Ch7-Operators
Ch8-Built-in Functions
Ch9-Built-in Examples
Ch10-Debugging
Ch11-Running Subs
Ch12-Sample Programs
Ch13-WS Formulas
Ch14-Questions
