Important: Most of the time we will be passing parameters by reference, which is default. Because it is default there is no need for ByRef notation.
We will look at passing parameters by value and by reference. Passing parameters is defined as passing the parameter values of the called subroutine or function to the variables from the main program.
Where parameters are passed by reference, any changes that are made to a variable in the called subroutine will be passed to the main program.
Where parameters 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 par1)
Notice that a single quote also acts as a REM or remark. Remember that when using copy and paste the quote must be typed over in the code window, if you have an error message after running the code.
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)
'd, e, f, myArray are par1, par2, par3, parArray
MsgBox "Sum f = " & f 'f = 11
MsgBox "myArray(3) = " & myArray(3) 'myArray(3) = 17
End Sub
Sub subAdd(par1,par2,par3,parArray)
Dim myVar As Long 'myVar is declared a local variable
par3 = par1 + par2
myVar = 20 + 21
MsgBox "myVar from sub = " & myVar 'myVar from sub = 41
parArray(3) = parArray(1) + parArray(2)
End Sub
The program SimpleAddition() calls a subroutine subAdd(par1,par2,par3,parArray) with variables and an array for parameters. Variables d, e, f, and the array myArray are substituted for par1, par2, par3, and array parArray. In subAdd() the variable myVar equals 41, par3 equals 11, and parArray(3) equals 17. myArray(1), myArray(2), and myArray(3) are members of the array myArray. When parArray is passed to myArray, it carries the values of all its elements, including the elements parArray(1), parArray(2), and parArray(3). In the comments I have described the substitutions. It is important to understand that the values d, e, f, myArray are substituted into par1, par2, par3, parArray. Likewise, after calculations in subAdd(par1,par2,par3,parArray) the values of par1, par2, par3, parArray 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 par1, par2, par3, parArray in the called subroutine or you will get the error message Duplicate declaration in current scope.
Note that all the functions and subroutines written in this tutorial are in a single module.
Variables that are declared in a function or a subroutine are called local variables. A local variable is declared within a function or subroutine. You can only use the value of that variable in the function or subroutine. Each time the function is called, the values generated previously are not available.
Sub Test()
Dim MyValue As Integer
' ……
End Sub
To keep the previous values, you must define the variable as Static.
Sub Test()
Static MyValue As Integer
' ……
End Sub
On the other hand, public and global variables are available to all procedures (functions and subroutines) in a module. And to all modules in a library. 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 in the header section of the module.
Public a As Integer
Sub Test()
myAdd
mySubtract
End Sub
Sub myAdd()
a = a +1
End Sub
Sub mySubtract()
a = a -1
End Sub
The value of a is increased by one in the myAdd subroutine, and decreased by one in the mySubtract subroutine. After the associated macro is executed, the variable a is reset.
Global variables are similar to Public variables, except that their values are retained even after the associated macro has executed.
Global a As Integer
Sub Test()
'…..
End Sub
Private Variables are only available for the module in which they are defined. There is a seperate occurrence for the variable in each module.
Private a As Integer
Sub Main()
'…...
End Sub
When Option Explicit appears in a module header, you must explicitly declare all variables by using the Dim, Static, Public, Global, or Private statements. If you attempt to use an undeclared variable name, an error occurs at compile time.
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 below.
Please note that in the following Bubble Sort program, I appear to have broken my rules for use of parameters in subroutines and functions. Sub ReadList and sub Write_List do not have parameters for input and output variables. Sub Myswap(i, j, list) 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 parameters and section 12.2 Program Calling a Subroutine with Variables and an Array as parameters.
You will need to enter the following variables as Public: i, j, n, list(20)
' BUBBLE SORT PROGRAM
'Because the variables i, j, n, list(20) are used in several subroutines,
'they are declared Public as shown.
'Main Program for sorting a list of numbers
Public i As Long
Public j As Long
Public n As Long
Public list(20) As Long
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()
oSheet = ThisComponent.CurrentController.ActiveSheet
Dim r As Long
For r = 0 To 20
list(r) = oSheet.GetCellByPosition(9,r).value
If list(r) = 999 Then Exit For
n = r 'n is the number of values in array list to sort
Next r
MsgBox r
End Sub
Sub Write_List() 'Write_List() subroutine for main()
Dim r As Long
oSheet = ThisComponent.CurrentController.ActiveSheet
For r = 0 To n 'n is the number of values in array
oSheet.GetCellByPosition(10,r).value = list(r)
Next r
MsgBox 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
We will now analyze the Bubble Sort Program. First we declare the Public variables: i, j, n, list(20). Next we call the ReadList() subroutine to read in the list of values to be sorted. We then step through two loops to sort the values. The heart of the swap is the call to the Myswap(i, j, list) subroutine. If value of list(i) is greater than list(j) then we swap the value of list(i) and list(j) in our list of values. When the sort is finished, we call sub Write_List() to print out the sorted values.
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-WS Functions
Ch15-Calc Help Page
