39
157
Chapter 6 • Code Reuse and Data Structures
Private Sub cmdEncrypt_Click()
‘ Call the Encrypt function passing the plain text
‘ message as an argument. Assign function’s result
‘ to the text box’s Value property.
If txtMessage.Value <> “” Then
txtMessage.Value = Encrypt(txtMessage.Value)
End If
End Sub
You should understand that the changes made to the
Secret Message
program are transpar-
ent to the user. In other words, the use of user-defined functions and standard modules does
not change the way the user interacts with the program, nor does it change the program’s
functionality. The important concept is that the changes were made to provide a more mod-
ular program, which implements code reuse through user-defined procedures and modules.
Arrays
Arrays are one of the first data structures learned by beginning programmers. Not only com-
mon as a teaching tool, arrays are frequently used by professional programmers to store like
data types as one variable. In a nutshell, arrays can be thought of as a single variable that
contains many elements. Moreover, VBA arrays share many common characteristics:
• Elements in an array share the same variable name.
• Elements in an array share the same data type.
• Elements in an array are accessed with an index number.
As noted, elements in an array share the same variable name and data type. Individual members
in an array are called elements and are accessed via an index. Just like any other variable,
arrays occupy memory space. To explain further, an array is a grouping of contiguous memory
segments, as demonstrated in Figure 6.4.
Notice the five-element array in Figure 6.4 starts with
index 0
. This is an important concept
to remember, so it’s worth repeating in italics: Unless otherwise stated, elements in an array begin
with index number zero. With that said, there are five array elements in Figure 6.1, starting
with
index 0
and ending with
index 4
.
50
158
A common programming error is not accounting for the zero-based index in
arrays. This programming error is often called the off-by-one error. Errors like
this are generally not caught during compile time, but rather at runtime when a
user or your program attempts to access an element number in an array that
does not exist. For example, if you have a five-element array and your program
tries to access the fifth element with index number
5
, a runtime program error
ensues. This is because the last index in a five-element array is
index 4
!
Single-Dimension Arrays
Using the keywords
Dim
,
Static
,
Public
, and
Private
, arrays are created just like any other
variable.
Unless
Option Base 1
is specified, or dimensioned, with an explicit range, arrays
by default begin with a zero base index.
• Dim myIntegerArray(5) As Integer ‘ Creates six Integer elements
• Dim myVariantArray(10) ‘ Creates eleven Variant elements
• Dim myStringArray(1 to 7) As String ‘ Creates 7 String elements
In the preceding declarations, the number of elements in an array is determined during
array declaration using either a number or a range of numbers surrounded by parentheses.
A nice feature of VBA is its ability to initialize variables for use. Specifically, VBA initializes
number-based array elements to
0
and string-based array elements to
“”
(indicating an
empty string).
Individual elements in an array are accessed via an index:
lblArrayValue.Caption = myStringArray(3)
TRICK
TRAP
Access VBA Programming for the Absolute Beginner, Second Edition
Figure 6.4
A five-element
array.
Element’s data value
Variable Name
Index Number
44
The next
Click
event procedure initializes a
String
array using a
For
loop and adds the array
contents to a list box.
Private Sub cmdPopulateListBox_Click()
‘ Declare a seven element String array.
Dim myStringArray(1 To 7) As String
Dim x As Integer
‘ Initialize array elements.
For x = 1 To 7
myStringArray(x) = “The value of myStringArray is “ & x
Next x
‘ Add array contents to a list box.
For x = 1 To 7
lstMyListBox.AddItem myStringArray(x)
Next x
End Sub
VBA provides two array-based functions called
LBound
and
UBound
for determining an array’s
upper and lower bounds. The
LBound
function takes an array name and returns the array’s lower
bound. Conversely, the
UBound
function takes an array name and returns the array’s upper
bound. These functions are demonstrated in this
Click
event procedure.
Private Sub cmdPopulateListBox_Click()
‘ Declare an eleven element Integer array.
Dim myIntegerArray(10) As Integer
Dim x As Integer
‘ Initialize array elements using LBound and UBound functions
‘ to determine lower and upper bounds.
For x = LBound(myIntegerArray) To UBound(myIntegerArray)
159
Chapter 6 • Code Reuse and Data Structures
26
160
myIntegerArray(x) = x
Next x
‘ Add array contents to a list box.
For x = LBound(myIntegerArray) To UBound(myIntegerArray)
lstMyListBox.AddItem myIntegerArray(x)
Next x
End Sub
Two-Dimensional Arrays
Two-dimensional arrays are most often thought of in terms of a table or matrix. For exam-
ple, a two-dimensional array containing four rows and five columns creates 20 elements, as
seen in Figure 6.5.
Dim x(3,4) As Integer ‘ Two dimensional array with 20 elements.
The first index (also known as a subscript) in a two-dimensional array represents the row in
a table. The second index represents the table’s column. Together, both subscripts specify a
single element within an array.
A nested looping structure is required to iterate through all elements in a two-dimensional
array.
Private Sub cmdInitializeArray_Click()
Access VBA Programming for the Absolute Beginner, Second Edition
Figure 6.5
A two-
dimensional array
with 20 elements.
Column index
Row index
38
‘ Create a 20 element two dimensional array.
Dim x(3, 4) As Integer
Dim iRow As Integer
Dim iColumn As Integer
‘ Loop through one row at a time.
For iRow = 0 To 3
‘ Loop through each column in the row.
For iColumn = 0 To 4
‘ Populate each element with the result of
‘ multiplying the row and column.
x(iRow, iColumn) = iRow * iColumn
Next iColumn
Next iRow
End Sub
As shown in the previous
Click
event, the outer
For
loop iterates through one column at a
time. Each time the outer loop is executed, a nested
For
loop is executed five times. The
inner loop represents each column (in this case five columns) in a row. After each column
in a row has been referenced, the outer loop executes again, which moves the array position
to the next row and the inner loop to the next set of columns.
Dynamic Arrays
Arrays are useful when you know how many elements you need. What if you don’t know
how many array elements your program requires? One way to circumvent this problem is by
creating a huge array that most definitely holds any number of elements you throw at it. I
don’t recommend this, however. When arrays are declared (created), VBA reserves enough
memory to hold data for each element. If you’re guessing on the number of elements
required, you’re most certainly wasting memory! A more professional way of solving this
dilemma is with dynamic arrays.
If you’ve worked in other programming languages such as C, you might be cringing about
the thought of dynamic arrays implemented with linked lists. You will be relieved to learn
that VBA makes building and working with dynamic arrays very easy.
161
Chapter 6 • Code Reuse and Data Structures
37
162
When your program logic uses dynamic arrays, it can size and resize your array while the
application is running. To create a dynamic array, simply eliminate any references to sub-
scripts or indexes in the array declaration.
Option Compare Database
Option Explicit
Dim iDynamicArray() As Integer ‘ Dynamic array.
Leaving the parentheses empty tells VBA your array is dynamic. I will be able to use my
dynamic array in all subsequent form-level procedures by dimensioning the dynamic array
in the general declaration area. To set the number of elements in a dynamic array, use the
ReDim
keyword.
Private Sub cmdDynamicArray_Click()
Dim sUserResponse As String
sUserResponse = InputBox(“Enter number of elements:”)
‘ Set number of array elements dynamically.
ReDim iDynamicArray(sUserResponse)
MsgBox “Number of elements in iDynamicArray is “ _
& UBound(iDynamicArray) + 1
End Sub
Using the
ReDim
keyword, I can set my array size after the program is running. The only prob-
lem with this approach is that each time the
ReDim
statement is executed, all previous element
data is lost. To correct this, use the
Preserve
keyword in the
ReDim
statement, as follows.
Private Sub cmdIncreaseDynamicArray_Click()
Dim sUserResponse As String
sUserResponse = InputBox(“Increase number of elements by:”)
‘ Set number of array elements dynamically, while
‘ preserving existing elements.
Access VBA Programming for the Absolute Beginner, Second Edition
37
ReDim Preserve iDynamicArray(UBound(iDynamicArray) _
+ sUserResponse)
MsgBox “Number of elements in iDynamicArray is now “ _
& UBound(iDynamicArray) + 1
End Sub
To preserve current elements while increasing a dynamic array, you must tell VBA to add ele-
ments to the array’s existing upper bound. This can be accomplished using the
UBound
func-
tion, as demonstrated in the previous
Click
event procedure
cmdIncreaseDynamicArray
.
The
Preserve
keyword allows to you to change a dynamic array’s upper bound
only. You cannot change a dynamic array’s lower bound with the
Preserve
key-
word.
Passing Arrays as Arguments
Passing an array to a function or subprocedure is not as difficult in VBA as one might think.
You must follow a couple of rules, however, to ensure a valid argument pass.
To pass all elements in an array to a procedure, simply pass the array name with no paren-
theses. Next, you must define the parameter name with an empty set of parentheses, as the
next two procedures demonstrate.
Private Sub cmdPassEntireArray_Click()
Dim myArray(5) As Integer
HowMany myArray
End Sub
Private Sub HowMany(x() As Integer)
MsgBox “There are “ & UBound(x) & “ elements in this array.”
End Sub
TRAP
163
Chapter 6 • Code Reuse and Data Structures
34
164
To pass a single element in an array, it is not necessary to define the parameter name as an
array. Rather, simply pass one array element as a normal variable argument:
Private Sub cmdPassArrayElement_Click()
Dim myArray(5) As Integer
CheckItOut myArray(3)
End Sub
Private Sub CheckItOut(x As Integer)
MsgBox “The parameter’s value is “ & x & “.”
End Sub
Passing arrays and elements of arrays as arguments is that easy!
User-Defined Types
In other programming languages such as C, user-defined types are commonly referred to as
structures. User-defined types are collections of one or more related elements, which can be of
different data types. User-defined types must be declared at the module level (also known as
the general declarations area) in a standard module. Programmers can leverage user-defined
types to group like variables as one, much as a record in a database does.
Type and End Type Statements
User-defined types are created with the
Type
and
End Type
statements at the module level.
More specifically, user-defined types must be declared outside of any procedure in a stan-
dard module. To demonstrate, I created a user-defined type called
EmployeeData
.
Type EmployeeData
EmployeeLastName As String
EmployeeFirstName As String
EmployeeID As Integer
EmployeeSalary As Currency
EmployeeHireDate As Date
Access VBA Programming for the Absolute Beginner, Second Edition
Documents you may be interested
Documents you may be interested
