Table of Contents
Acknowledgments xi
Introduction xiii
Chapter 1 Getting Started with Access VBA 1
Understanding VBA Modules and Procedure Types 1
Writing Procedures in a Standard Module 4
Executing Your Procedures 7
Understanding Class Modules 10
Events, Event Properties, and Event Procedures 12
Why Use Events? 14
Walking Through an Event Procedure 14
Compiling Your Procedures 19
Placing a Database in a Trusted Location 20
Summary 23
Chapter 2 Getting to Know Visual Basic Editor (VBE) 25
Understanding the Project Explorer Window 25
Understanding the Properties Window 27
Understanding the Code Window 29
Other Windows in the VBE 31
Assigning a Name to the VBA Project 32
Renaming a Module 33
Syntax and Programming Assistance 33
List Properties/Methods 34
Parameter Info 35
List Constants 36
Quick Info 36
Complete Word 37
Indent/Outdent 37
Comment Block/Uncomment Block 39
Using the Object Browser 39
Using the VBA Object Library 41
Using the Immediate Window 43
Summary 45
Chapter 3 Access VBA Fundamentals 47
Introduction to Data Types 47
Understanding and Using Variables 50
Declaring Variables 50
Specifying the Data Type of a Variable 54
Using Type Declaration Characters 55
Assigning Values to Variables 56
Forcing Declaration of Variables 59
Understanding the Scope of Variables 61
Procedure-Level (Local) Variables 61
Module-Level Variables 62
Project-Level Variables 64
Understanding the Lifetime of Variables 65
Using Temporary Variables 66
Creating a Temporary Variable with a TempVars Collection Object 66
Retrieving Names and Values of TempVar Objects 67
Using Temporary Global Variables in Expressions 68
Removing a Temporary Variable from a TempVars Collection Object 68
Using Static Variables 69
Using Object Variables 70
Disposing of Object Variables 73
Finding a Variable Definition 73
Determining the Data Type of a Variable 73
Using Constants in VBA Procedures 74
Intrinsic Constants 76
Summary 77
Chapter 4 Access VBA Built-in and Custom Functions 79
Writing Function Procedures 79
Running Function Procedures 80
Data Types and Functions 82
Passing Arguments By Reference and By Value 83
Using Optional Arguments 85
Using the IsMissing Function 87
VBA Built-in Functions for User Interaction 87
Using the MsgBox Function 88
Returning Values from the MsgBox Function 95
Using the InputBox Function 96
Converting Data Types 99
Summary 101
Chapter 5 Adding Decisions to Your Access VBA Programs 103
Relational and Logical Operators 103
If…Then Statement 104
Multiline If…Then Statement 106
Decisions Based on More than One Condition 108
If…Then…Else Statement 109
If…Then…Elself Statement 112
Nested If…Then Statements 113
Select Case Statement 116
Using Is with the Case Clause 118
Specifying a Range of Values in a Case Clause 119
Specifying Multiple Expressions in a Case Clause 121
Summary 121
Chapter 6 Adding Repeating Actions to Your Access VBA Programs 123
Using the Do…While Statement 123
Another Approach to the Do…While Statement 125
Using the Do…Until Statement 127
Another Approach to the Do…Until Statement 128
Using the For…Next Statement 129
Using the For Each…Next Statement 132
Exiting Loops Early 133
Nested Loops 134
Summary 135
Chapter 7 Keeping Track of Multiple Values Using Arrays 137
Understanding Arrays 137
Declaring Arrays 139
Array Upper and Lower Bounds 141
Initializing and Filling an Array 141
Filling an Array Using Individual Assignment Statements 141
Filling an Array Using the Array Function 142
Filling an Array Using the For…Next Loop 142
Using a One-Dimensional Array 143
Arrays and Looping Statements 145
Using a Two-Dimensional Array 148
Static and Dynamic Arrays 150
Array Functions 152
The Array Function 152
The IsArray Function 153
The Erase Function 153
The LBound and UBound Functions 156
Errors in Arrays 157
Parameter Arrays 160
Passing Arrays to Function Procedures 161
Sorting an Array 162
Summary 164
Chapter 8 Keeping Track of Multiple Values Using Collections 165
Creating Your Own Collection 165
Adding Items to Your Collection 166
Determine the Number of Items in Your Collection 167
Accessing Items in a Collection 167
Removing Items from a Collection 168
Updating Items in a Collection 169
Returning a Collection from a Function 171
Collections vs. Arrays 173
Watching the Execution of Your VBA Procedures 174
Summary 179
Chapter 9 Getting to Know Built-in Tools for Testing and Debugging 181
Syntax, Runtime, and Logic Errors 181
Stopping a Procedure 183
Using Breakpoints 184
Removing Breakpoints 188
Using the Immediate Window in Break Mode 188
Using the Stop Statement 190
Using the Assert Statement 191
Using the Add Watch Window 192
Removing Watch Expressions 195
Using Quick Watch 195
Using the Locals Window 197
Using the Call Stack Dialog Box 198
Stepping Through VBA Procedures 198
Stepping Over a Procedure 200
Stepping Out of a Procedure 201
Running a Procedure to Cursor 201
Setting the Next Statement 201
Showing the Next Statement 202
Navigating with Bookmarks 202
Stopping and Resetting VBA Procedures 203
Trapping Errors 203
Using the Err Object 204
Procedure Testing 208
Setting Error-Trapping Options 210
Summary 211
Index 213
