CHAPTER 7
CODE REUSE AND DATA STRUCTURES
In this chapter I show you how to increase your programming productivity by building your own procedures for reuse throughout an Access program. I also show you how to build collections of related information using data structures such as arrays and user-defined types.
CODE REUSE
Remember that Visual Basic and VBA are event-driven programming languages. This means VBA programmers could easily duplicate work when writing code in two or more event procedures. For example, consider a bookstore program that contains three separate graphical interfaces (forms or windows) a user could use to search for a book by entering a book title and clicking a command button. As a VBA programmer, you could easily write the same code in three separate control events. This approach is demonstrated in the next three event procedures.
Private Sub cmdSearchFromMainWindow_Click(BookTitle As String) ’ Common code to search for a book based on book title. End Sub
Private Sub cmdSearchFromHelpWindow_Click(BookTitle As String) ’ Common code to search for a book based on book title. End Sub
Private Sub cmdSearchFromBookWindow_Click(BookTitle As String) ’ Common code to search for a book based on book title. End Sub
The program statements required to search for a book could be many lines long and needlessly duplicated in each event procedure. To solve this problem, you could build your own user-defined procedure called SearchForBook, which implements all the required code only once to search for a book. Then each event procedure need only call SearchForBook and pass in a book title as a parameter.
To remove duplicate code, I must first build the SearchForBook user-defined procedure.
Public Sub SearchForBook(sBookTitle As String) ’ Search for a book based on book title. End Sub
Instead of duplicating the Search statements in each Click event, I simply call the SearchForBook subprocedure, passing it a book title.
Private Sub cmdSearchFromMainWindow_Click() SearchForBook(txtBookTitle.Value) End Sub
Private Sub cmdSearchFromHelpWindow_Click() SearchForBook(txtBookTitle.Value) End Sub
Private Sub cmdSearchFromBookWindow_Click() SearchForBook(txtBookTitle.Value) End Sub
This new approach eliminates duplicate code and logic by creating what’s known as code reuse. Specifically, code reuse is the process by which programmers pull out commonly used functionality (code statements), and put them into unique procedures or functions, which can be referenced from other locations in the application.
Code reuse makes your life as a programmer much easier, and is an easy concept to grasp. In the world of VBA, code reusability is implemented as subprocedures and function procedures. Programmers create user-defined procedures for problems that need frequently used solutions.
Introduction to User-Defined Procedures
In previous chapters, you learned how to use built-in VBA functions (also known as procedures) such as MsgBox and InputBox. You may have wondered how those functions were implemented. In this section, you learn how to build your own functions using user-defined procedures.
Access VBA supports three types of procedures: subprocedures, function procedures, and property procedures. I specifically discuss subprocedures and function procedures in this chapter, and save property procedures for Chapter 11, “Object-Oriented Programming with Access VBA,” when I discuss object-oriented programming, also known as OOP!
The main difference between subprocedures and function procedures is that subprocedures do not return values. Other programming languages such as C or Java, simply refer to a procedure that returns no value as a void function.
Though different in implementation and use, both subprocedures and function procedures share some characteristics, such as beginning and ending statements, executable statements, and incoming arguments. The main difference between the two revolves around a return value. Specifically, subprocedures do not return a value, whereas function procedures do.
User-defined procedures are added to your VBA code modules manually, or with a little help from the Add Procedure dialog box. To access the Add Procedure dialog box, open the VBE and make sure the Code window portion has the focus. Select Insert, Procedure from the menu.
The Procedure menu item appears unavailable (disabled) if the Code window in the VBE does not have the focus.
The Add Procedure dialog box in Figure 7.1 allows you to name your procedure and select a procedure type and scope.
FIGURE 7.1 Adding a procedure with the Add Procedure dialog box.
If you select the All Local Variables as Statics check box, your procedure-level variables maintain their values through your program’s execution.
After creating your procedure, the Add Procedure dialog box tells VBA to create a procedure shell with Public Sub and End Sub statements, as shown in Figure 7.2.
FIGURE 7.2 An empty procedure created with the Add Procedure dialog box.
Subprocedures
Subprocedures must have a Sub statement and corresponding End Sub statement. They can contain executable VBA statements such as declaration and assignment statements. Subprocedures can take arguments such as variables, constants, and expressions. If no arguments are provided, the beginning Sub statement must contain an empty set of parentheses:
Public Sub DisplayCurrentTime() MsgBox “The time is ” & Time End Sub
The AddTwoNumbers procedure implements the addition of two numbers using Integer-based parameters, as seen in the next procedure declaration.
Public Sub AddTwoNumbers(iNumber1 As Integer, iNumber2 As Integer)
MsgBox “The result of ” & iNumber1 & “ and ” & iNumber2 & _
“ is ” & iNumber1 + iNumber2
End Sub
When executed by itself, the AddTwoNumbers procedure requires no parentheses to surround its arguments list:
When used in an assignment statement, however, the comma-separated arguments list must be enclosed by parentheses:
lblOutput.Caption = AddTwoNumbers(4, 6)
Note again that subprocedures only execute statements and do not return a value to the calling procedure. If a return value is required, consider using a function procedure (discussed next).
Function Procedures
Function procedures are very much like subprocedures in that they consist of VBA statements and take arguments. Unlike subprocedures, function procedures begin with a Function statement, and end with an End Function statement. Function procedures return values to the calling procedure by assigning a value to the function name:
Public Function MultiplyTwoNumbers(dNumber1 As Double, dNumber2 As Double) MultiplyTwoNumbers = dNumber1 * dNumber2 End Function
The MultiplyTwoNumbers function procedure takes two Double parameters, and assigns the result of their multiplication to the function name, thereby returning the result to the calling function.
lblResult.Caption = MultiplyTwoNumbers(6, 9)
To be more dynamic, I could pass the Value property of two text boxes as arguments.
lblResult.Caption = MultiplyTwoNumbers(Val(txtNumber1.Value), _ Val(txtNumber2.Value))
To ensure that the MultiplyTwoNumbers function receives number values (specifically Doubles), I use the Val function inside the argument list to convert strings to numbers.
Arguments and Parameters
The words arguments and parameters are often used interchangeably, but they differ in purpose and definition. Specifically, parameters are variables that hold the arguments in the procedure’s declaration, whereas Arguments are the values passed to the procedure. If this is confusing, don’t worry, a picture often helps—take a look now at Figure 7.3.
FIGURE 7.3 Distinguishing between arguments and parameters.
Many programming languages, including VBA, allow arguments to be passed either by value or by reference. When arguments are passed by value, VBA makes a copy of the original variable’s contents and passes the copy to the procedure. This means the procedure can’t modify the original contents of the argument, only the copy.
To pass arguments by value, you need to preface the parameter name with the ByVal keyword in the procedure’s declaration as shown in the Increment procedure.
Private Sub cmdProcess_Click() Dim iNumber As Integer iNumber = 1 Increment iNumber MsgBox “The value of iNumber is ” & iNumber End Sub
Public Sub Increment(ByVal x As Integer) x = x + 5 End Sub
What do you suppose the value of iNumber would be after being passed to, and processed by the Increment procedure? If you guessed 1, you would be correct because the variable was passed by value, which means the Increment procedure cannot modify the variable’s original content. Also worth noting is that it is not required to give the argument (variable) the same name as the parameter as demonstrated in Figure 7.4.
FIGURE 7.4 Argument names need not be the same as the variable passed in.
When not used in assignment statements, argument lists can’t be enclosed in parentheses. Here is an example:
Increment iNumber
Keep in mind that VBA does not always produce a runtime error when parentheses are used but not required. Instead, VBA may simply pass the argument incorrectly, producing unexpected results.
Arguments that are passed by reference send the procedure a reference to the argument’s memory location. In a nutshell, a memory address is sent to the procedure when arguments are passed by reference. This means the procedure is able to modify the original data value. Passing arguments by reference is the default argument behavior in VBA. Moreover, passing arguments by reference is the most efficient means of passing arguments to procedures because only a reference (memory address) to the argument is passed, not the data itself.
To pass arguments by reference, simply preface the argument name using the ByRef keyword or use no preface keyword at all.
Private Sub cmdProcess_Click() Dim iNumber As Integer iNumber = 1 Increment iNumber MsgBox “The value of iNumber is ” & iNumber End Sub
Public Sub Increment(ByRef x As Integer) x = x + 5 End Sub
Arguments are passed by reference automatically. It is not necessary to preface the argument name with the ByRef keyword.
Passing the iNumber variable by reference will now allow the Increment procedure to modify the argument’s value directly. What do you now suppose the value of iNumber is after being passed to and processed by the Increment procedure? If you guessed 6, you would be correct. Since the variable iNumber was passed by reference, the Increment procedure was able to modify the variable’s original content.
STANDARD MODULES
Access VBA supports two types of modules: class and standard. Class modules are directly related to an object, such as a form or report. Form class modules contain event procedures for the associated controls and objects. Standard modules, however, have no association with an object. They store a collection of variables and user-defined procedures, which can be shared among your Access programs.
You can add a standard module from the VBA environment by selecting Insert, Module from the menu.
To see how you could utilize a standard module, I’ve revised the Secret Message program from Chapter 6, “Common Formatting and Conversion Functions.” Specifically, I added one standard module and two public functions called Encrypt and Decrypt. Using public functions allows me to reuse the code in these functions from anywhere in my application.
To move the Encrypt and Decrypt functionality from event procedures to functions, I first create the function shells using the Add Procedure dialog box. Next, I add a string parameter to both functions. This argument is passed into each function when called. Moreover, the parameter called sMessage replaces the hard-coded text-box value from the previous version of Secret Message. All occurrences of the text-box name are replaced with the parameter name. This is truly code reuse, as I can now call these functions and pass my message from anywhere in my Access application. Last but not least, I assign the function’s output to the function’s name.
The new standard module code from the enhanced Secret Message program is shown next.
Option Compare Database Option Explicit
Public Function Decrypt(sMessage As String)
Dim sDecryptedMessage As String
Dim sDecryptedCharacter As String
Dim iCounter As Integer
For iCounter = 1 To Len(sMessage)
sDecryptedCharacter = Asc(Mid(sMessage, iCounter, 1))
sDecryptedCharacter = Chr(sDecryptedCharacter - 1)
sDecryptedMessage = sDecryptedMessage + sDecryptedCharacter
Next iCounter
’ Assign decrypted message to function name.
Decrypt = sDecryptedMessage
End Function
Public Function Encrypt(sMessage As String)
Dim sEncryptedMessage As String
Dim sEncryptedCharacter As String
Dim iCounter As Integer
For iCounter = 1 To Len(sMessage)
sEncryptedCharacter = Asc(Mid(sMessage, iCounter, 1))
sEncryptedCharacter = Chr(sEncryptedCharacter + 1)
sEncryptedMessage = sEncryptedMessage + sEncryptedCharacter
Next iCounter
’ Assign encrypted message to function name.
Encrypt = sEncryptedMessage
End Function
With my Encrypt and Decrypt functions implemented in a standard module, I simply need to call them and pass the Value property from the text box. After the function call is executed, the function’s return value is assigned back to the text box’s Value property.
Option Compare Database Option Explicit
Private Sub cmdEncrypt_Click()
’ Call the Decrypt function passing the encrypted
’ message as an argument. Assign function’s result
’ to the text box’s Value property.
If txtMessage.Value <> “” Then
txtMessage.Value = Decrypt(txtMessage.Value)
End If
End Sub
Private Sub cmdDecrypt_Click()
’ Call the Decrypt function passing the encrypted
’ message as an argument. Assign function’s result
’ to the text box’s Value property.
If txtMessage.Value <> “” Then
txtMessage.Value = Decrypt(txtMessage.Value)
End If
End Sub
You should understand that the changes made to the Secret Message program are transparent 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 modular program, which implements code reuse through user-defined procedures and modules. The enhanced Secret Message program can be found on the companion website.
ARRAYS
Arrays are one of the first data structures learned by beginning programmers. Not only common as a teaching tool, arrays are frequently used by professional programmers to store like data types as one variable. In short, 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 7.5.
FIGURE 7.5 A five-element array.
Notice the five-element array in Figure 7.5 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 7.5, starting with index 0 and ending with index 4.
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 seven 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)
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 lower and upper 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)
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 example, a two-dimensional array containing four rows and five columns creates 20 elements, as shown in Figure 7.6.
Dim x(3, 4) As Integer ’ Two dimensional array with 20 elements.
FIGURE 7.6 A 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()
’ 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 “should” hold the number of elements required; however, I don’t recommend this. 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 either wasting memory or running the risk of not having enough. 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, or other structures. You will be relieved to learn that VBA makes building and working with dynamic arrays very easy.
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 subscripts or indexes in the array declaration.
Option Compare Database Option Explicit Dim iDynamicArray() As Integer ’ Dynamic array.
Leaving the parentheses empty tells VBA that 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
I can set my array size after the program is running using the ReDim keyword. However, there is a problem with this approach; specifically, each time the ReDim statement is executed, all previous element data is lost. To solve 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.
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 elements to the array’s existing upper bound. This can be accomplished using the UBound function, as demonstrated in the previous Click event procedure cmdIncreaseDynamicArray.
The Preserve keyword allows you to change a dynamic array’s upper bound only. You cannot change a dynamic array’s lower bound with the Preserve keyword.
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 as an argument with no parentheses. You will also need to ensure that the procedure’s definition includes the array’s parameter name with an empty set of parentheses. 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
To pass a single element in an array to a procedure, it is not necessary to specify an array as a parameter in the procedure definition. Instead, list the data type required in the procedure definition (a nonarray variable), and simply pass the array element as a single variable argument as demonstrated next.
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 standard 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 End Type ’ ...is the same as Public Type EmployeeData Dim EmployeeLastName As String Dim EmployeeFirstName As String Dim EmployeeID As Integer Dim EmployeeSalary As Currency Dim EmployeeHireDate As Date End Type
It is not necessary to use the Dim keyword when declaring variables (members) inside a user-defined type.
Note that declaring a user-defined type does not instantiate a variable, nor does it reserve any space in memory. The declaration of a user-defined type simply provides VBA with a blueprint, or in other words, a template to be used when variables of your user-defined type are created.
By default, user-defined types are public, though they can be declared using the keyword Private, which makes them available only to the current module from where they are created.
’ Available only in the current module. Private Type BookData Title As String ISBN As String Author As String Publisher As String PublishDate As Date Price As Currency End Type
Declaring Variables of User-Defined Type
As mentioned earlier, declaring a user-defined type does not create a variable, but rather defines a template for VBA programmers to use later. To create variables of your user-defined types, define a user-defined type in a standard module.
Option Compare Database Option Explicit ’ Define user defined type in a standard module. Type BookData Title As String ISBN As String Author As String Publisher As String PublishDate As Date Price As Currency End Type
With the structure defined, you can now create variables of your user-defined type at a module level.
’ Declare 5 element array of BookData Type Dim myFavoriteBook As BookData
Because user-defined types are public by default, you can create type variables in other modules, such as form class modules:
Private Sub cmdEnterBookData_Click() ’ Declare one variable of BookData Type Dim myCookingBook As BookData End Sub
Managing Elements
Once a variable has been declared as a user-defined type, you can use it much like any other variable. To access elements within type variables, simply use the dot notation (myBook.Title) to assign and retrieve data, as the next program demonstrates.
Private Sub cmdEnterBookData_Click() Dim myBook As BookData ’ Declare one variable of BookData Type myBook.Title = txtTitle.Value myBook.ISBN = txtISBN.Value myBook.Author = txtAuthor.Value myBook.Publisher = txtPublisher.Value myBook.PublishDate = txtPublishDate.Value myBook.Price = txtPrice.Value MsgBox myBook.Title & ” has been entered.” End Sub
Note that a public user-defined type must have already been declared in a standard module.
Remember that the grouping of elements (i.e., Title, ISBN, Author, Publisher, PublishDate, and
Price) in a user-defined type can be thought of as rows in a database table: Both table rows and user-defined types maintain a grouping of like elements of one or more data types. So far, you have only seen how to create a single variable of user-defined type (analogous to a single row in a database). To create multiple variables of the same user-defined type (much like multiple rows in a database), simply create an array of user-defined type, as shown in the next program.
Option Compare Database Option Explicit
Dim myBooks() As BookData ’ Declare dynamic array of BookData Type Dim currentIndex As Integer
Private Sub cmdAddNewBook_Click() ’ Add one array element to the dynamic array. ReDim Preserve myBooks(UBound(myBooks) + 1) ’ Clear text boxes txtTitle.Value = “” txtISBN.Value = “” txtAuthor.Value = “” txtPublisher.Value = “” txtPublishDate.Value = “” txtPrice.Value = “” End Sub
Private Sub cmdEnterBookData_Click() myBooks(UBound(myBooks)).Title = txtTitle.Value myBooks(UBound(myBooks)).ISBN = txtISBN.Value myBooks(UBound(myBooks)).Author = txtAuthor.Value myBooks(UBound(myBooks)).Publisher = txtPublisher.Value myBooks(UBound(myBooks)).PublishDate = txtPublishDate.Value myBooks(UBound(myBooks)).Price = txtPrice.Value MsgBox myBooks(UBound(myBooks)).Title & ” has been entered.” End Sub
Private Sub cmdNext_Click() If currentIndex <= UBound(myBooks) Then
If currentIndex < UBound(myBooks) Then
’ Increment index.
currentIndex = currentIndex + 1
End If
txtTitle.Value = myBooks(currentIndex).Title
txtAuthor.Value = myBooks(currentIndex).Author
txtISBN.Value = myBooks(currentIndex).ISBN
txtPublisher.Value = myBooks(currentIndex).Publisher
txtPublishDate.Value = myBooks(currentIndex).PublishDate
txtPrice.Value = myBooks(currentIndex).Price
End If
End Sub
Private Sub cmdPrevious_Click()
If currentIndex >= 1 Then
If currentIndex > 1 Then
’ Decrement index.
currentIndex = currentIndex - 1
End If
txtTitle.Value = myBooks(currentIndex).Title
txtAuthor.Value = myBooks(currentIndex).Author
txtISBN.Value = myBooks(currentIndex).ISBN
txtPublisher.Value = myBooks(currentIndex).Publisher
txtPublishDate.Value = myBooks(currentIndex).PublishDate
txtPrice.Value = myBooks(currentIndex).Price
End If
End Sub
Private Sub Form_Load() ’ Add one array element to the dynamic array. ReDim myBooks(1) currentIndex = 1 End Sub
The Integer variable—called currentIndex in the previous Form_Load event procedure—was declared in the general declarations section. It therefore can be used throughout the form’s class module. I use this variable to maintain the current index of the array as I navigate through the elements in the array. Also note that I used dynamic array techniques previously discussed in this chapter to add elements of BookData type to my array variable in the cmdAddNewBook_Click event procedure.
CHAPTER PROGRAM: DICE
The chapter program Dice, shown in Figure 7.7, is an easy-to-build and fun game that mimics basic poker rules. The player rolls the dice by clicking a command button and hopes for either three of a kind (worth 10 points) or, better yet, four of a kind (worth 25 points).
FIGURE 7.7 Using chapter-based concepts to build the Dice program.
The Dice program implements code reuse by leveraging chapter-based concepts such as arrays, user-defined procedures, and standard code modules. In addition to chapter-based concepts, the Dice program uses random number techniques to simulate a roll. (This is discussed in Chapter 5, “Looping Structures.”)
Controls and properties that build the Dice program are described in Table 7.1, with a look at the form in Design View in Figure 7.8.
FIGURE 7.8 The Dice program in Design View.
TABLE 7.1 CONTROLS AND PROPERTIES OF THE DICE PROGRAM
All of the code required to implement the form class module in the Dice program is seen next.
Option Compare Database Option Explicit
Private Sub cmdQuit_Click()
DoCmd.Quit
End Sub
Private Sub cmdRoll_Click() ’ Roll the Dice! RollTheDice ’ Check the player’s hand. DetermineCurrentHand iCurrentHand End Sub
Private Sub cmdHowToPlay_Click()
MsgBox “Dice! Version 1.0” & Chr(13) & _
“Developed by Michael Vine.” & Chr(13) & Chr(13) & _
“Roll the dice and win points with four of a kind (25 points), ” _
& Chr(13) & _
“and three of a kind (10 points).”, , “Chapter 7 - Dice!”
End Sub
Private Sub Form_Load() Randomize lblScore.Caption = “Your score is ” & iScore End Sub
The algorithm to roll the dice and check the player’ s module, which is shown next.
Option Compare Database Option Explicit ’ Public variables available to all procedures in ’ all modules Public iScore As Integer Public iCurrentHand(3) As Integer
Public Sub DetermineCurrentHand(a() As Integer)
’ Look for valid hands worth points.
’ Valid hands with points are:
’ 3 of a kind - 10 points
’ 4 of a kind - 25 points
Dim iCounter As Integer
’ Holds possibilities of a win
Dim iNumbers(1 To 6) As Integer
’ Count the number of occurrences for each die
For iCounter = 0 To 3
Select Case a(iCounter)
Case 1
iNumbers(1) = iNumbers(1) + 1
Case 2
iNumbers(2) = iNumbers(2) + 1
Case 3
iNumbers(3) = iNumbers(3) + 1
Case 4
iNumbers(4) = iNumbers(4) + 1
Case 5
iNumbers(5) = iNumbers(5) + 1
Case 6
iNumbers(6) = iNumbers(6) + 1
End Select
Next iCounter
’ Determine if player has four of a kind
If iNumbers(1) = 4 Or iNumbers(2) = 4 Or iNumbers(3) = 4 Or _
iNumbers(4) = 4 Or iNumbers(5) = 4 Or iNumbers(6) = 4 Then
MsgBox “Four of a kind! 25 points!”
iScore = iScore + 25
Forms(“ChapterProgram”).lblScore.Caption = _
“Your score is ” & iScore
Exit Sub
End If
’ Player did not have a four of a kind, see if they
’ have three of a kind.
If (iNumbers(1) = 3 Or iNumbers(2) = 3 Or iNumbers(3) = 3 Or _
iNumbers(4) = 3 Or iNumbers(5) = 3 Or iNumbers(6) = 3) Then
MsgBox “Three of a kind! 10 points!”
iScore = iScore + 10
Forms(“ChapterProgram”).lblScore.Caption = _
“Your score is ” & iScore
Exit Sub
End If
End Sub
Public Sub RollTheDice()
Dim iCounter As Integer
’ Reset current hand
For iCounter = 0 To 3
iCurrentHand(iCounter) = Int((6 * Rnd) + 1)
Next iCounter
’ Assign a die to the first slot
Select Case iCurrentHand(0)
Case 1
Forms(“ChapterProgram”).imgSlot1.Picture =
Forms(“ChapterProgram”).imgDice1.Picture
Case 2
Forms(“ChapterProgram”).imgSlot1.Picture =
Forms(“ChapterProgram”).imgDice2.Picture
Case 3
Forms(“ChapterProgram”).imgSlot1.Picture =
Forms(“ChapterProgram”).imgDice3.Picture
Case 4
Forms(“ChapterProgram”).imgSlot1.Picture =
Forms(“ChapterProgram”).imgDice4.Picture
Case 5
Forms(“ChapterProgram”).imgSlot1.Picture =
Forms(“ChapterProgram”).imgDice5.Picture
Case 6
Forms(“ChapterProgram”).imgSlot1.Picture =
Forms(“ChapterProgram”).imgDice6.Picture
End Select
’ Assign a die to the second slot
Select Case iCurrentHand(1)
Case 1
Forms(“ChapterProgram”).imgSlot2.Picture =
Forms(“ChapterProgram”).imgDice1.Picture
Case 2
Forms(“ChapterProgram”).imgSlot2.Picture =
Forms(“ChapterProgram”).imgDice2.Picture
Case 3
Forms(“ChapterProgram”).imgSlot2.Picture =
Forms(“ChapterProgram”).imgDice3.Picture
Case 4
Forms(“ChapterProgram”).imgSlot2.Picture =
Forms(“ChapterProgram”).imgDice4.Picture
Case 5
Forms(“ChapterProgram”).imgSlot2.Picture =
Forms(“ChapterProgram”).imgDice5.Picture
Case 6
Forms(“ChapterProgram”).imgSlot2.Picture =
Forms(“ChapterProgram”).imgDice6.Picture
End Select
’ Assign a die to the third slot Select Case iCurrentHand(2) Case 1 Forms(“ChapterProgram”).imgSlot3.Picture = Forms(“ChapterProgram”).imgDice1.Picture Case 2 Forms(“ChapterProgram”).imgSlot3.Picture = Forms(“ChapterProgram”).imgDice2.Picture Case 3 Forms(“ChapterProgram”).imgSlot3.Picture = Forms(“ChapterProgram”).imgDice3.Picture Case 4 Forms(“ChapterProgram”).imgSlot3.Picture = Forms(“ChapterProgram”).imgDice4.Picture Case 5 Forms(“ChapterProgram”).imgSlot3.Picture = Forms(“ChapterProgram”).imgDice5.Picture Case 6 Forms(“ChapterProgram”).imgSlot3.Picture = Forms(“ChapterProgram”).imgDice6.Picture End Select ’ Assign a die to the fourth slot Select Case iCurrentHand(3) Case 1 Forms(“ChapterProgram”).imgSlot4.Picture = Forms(“ChapterProgram”).imgDice1.Picture Case 2 Forms(“ChapterProgram”).imgSlot4.Picture = Forms(“ChapterProgram”).imgDice2.Picture Case 3 Forms(“ChapterProgram”).imgSlot4.Picture = (“ChapterProgram”).imgDice3.Picture Case 4 Forms(“ChapterProgram”).imgSlot4.Picture = (“ChapterProgram”).imgDice4.Picture Case 5 Forms(“ChapterProgram”).imgSlot4.Picture = (“ChapterProgram”).imgDice5.Picture Case 6 Forms(“ChapterProgram”).imgSlot4.Picture = Forms(“ChapterProgram”).imgDice6.Picture End Select End Sub
SUMMARY
• Code reuse can be implemented in VBA through user-defined subprocedures and function procedures.
• Function procedures return a value; subprocedures do not.
• Both subprocedures and function procedures can take one or more arguments.
• Arguments are the data passed into procedures. Parameters are the variables defined in the procedure definition.
• Arguments can be passed by value and by reference.
• Arguments passed by value contain a copy of the original data. This prevents the procedure from modifying the original value.
• Arguments passed by reference contain a reference to the variable’s memory address. The procedure can modify the original value.
• Arguments are passed by reference automatically in VBA.
• Standard modules are used to group commonly referenced user-defined procedures together.
• Arrays are used to store groupings of like data types as one variable.
• An array is a grouping of contiguous memory segments.
• Variables in an array are called elements.
• Each variable in an array shares the same name.
• Elements in an array are accessed via an index.
• VBA arrays are zero based by default.
• Arrays are created just like other variables using the keywords Dim, Static, Public, and Private.
• Two-dimensional arrays are often thought of in terms of a table or matrix.
• Two looping structures (one of which is nested) are required to iterate through each element in a two-dimensional array.
• Dynamic arrays can be created and managed using the ReDim and Preserve keywords.
• Arrays can be passed as arguments to procedures.
• User-defined types are commonly referred to as structures.
• User-defined types are groupings of like information, which can be of different data types.
• User-defined types are created using the Type and End Type statements.
• User-defined types must be declared in a standard module in the general declarations area (outside of any procedure).
• A grouping of variables of user-defined type is analogous to a row in a database.
CHALLENGES
1. Build a form with two text boxes that receive numbers as input and one command button that displays a message box containing the larger of the two numbers. To accomplish this, write code in the Click event of the command to call a user-defined function named FindLargestNumber. Pass it two arguments (text box values) and display the result in a message box. Write code in the FindLargestNumber function to determine the larger number and return the result to the calling procedure (command button Click event in this case).
2. Create a one-dimensional string-based array with five elements. Assign five different names to the array. Use a For loop to iterate through each of the array elements, displaying the names in a message box.
3. Declare a user-defined type called HomeData with elements StreetAddress, City, State, SquareFootage, LotSize, and SalePrice in a standard module. Create a form with six text boxes to add values to each variable type element. In the general declarations area of the form, create a single variable of HomeType to store the user-entered value. Add two command buttons to the form, one called cmdAddHome and the other cmdDisplayHomeData. In cmdAddHome Click event, store the data entered by the user into your user-defined type variable. In cmdDisplayHomeData Click event, display each element’s value in a message box.
4. Update the chapter program Dice to check for two pair.