CHAPTER 3
INTRODUCTION TO ACCESS VBA
Like many professional RDBMS (Relational Database Management Systems), Microsoft Access comes with its own programming language called VBA or Visual Basic for Applications. Though VBA supports the look and feel of Microsoft’s Visual Basic, it is not Visual Basic nor is it Visual Basic .NET. Access VBA is specifically designed for Microsoft Access. Meaning, it has knowledge of and support for the Microsoft Access object model. The concept of an object model is different for each Microsoft Office application. For example, both Microsoft Excel and Microsoft Word support VBA, but each application has its own object model.
THE EVENT-DRIVEN PARADIGM
The event-driven paradigm is a powerful programming model that allows programmers to build applications that respond to actions initiated by the user or system. Access VBA includes a number of events that are categorized by the objects they represent. VBA programmers write code in event procedures to respond to user actions (such as clicking a command button) or system actions (such as a form loading).
To demonstrate the event-driven model, consider a form, which has a corresponding Form object that contains many events such as Click, Load, and MouseUp. As seen next, both Click and MouseUp events are triggered by the user performing an action with the mouse on the form.
Private Sub Form_Click()
‘write code here to respond to the user clicking the form
End Sub
Private Sub Form_MouseUp(Button As Integer, _
Shift As Integer, X As Single, Y As Single)
‘write code here to respond to the user releasing a mouse button
End Sub
You, the VBA programmer, write code in these event procedures to respond to user actions. Moreover, events can be triggered by the system or the program itself. For example, the Load event shown next is triggered when a form’s Form object is first loaded into memory.
Private Sub Form_Load() ‘write code here to respond to the form loading into memory End Sub
If you’re new to event-driven programming, this may seem a bit awkward at first. I promise you however, it is really not that difficult. In fact, VBA does a great job of providing much of the detail for you. By the end of this chapter, you will be writing your first Access VBA event-driven programs with ease.
OBJECT-BASED PROGRAMMING
The key to programming in VBA is using objects. Objects have properties that describe the object and methods that perform actions. For example, say I have an object called Person. The Person object contains properties called HairColor, Weight, Height, and Age that describe the object. The Person object also contains methods that describe an action the object can perform such as Run, Walk, Sleep, and Eat. As you can see, understanding the concept of objects is really quite simple!
Many Access VBA objects also contain data structures called collections. In a nutshell, collections are groupings of objects.
Access VBA supports many objects such as the Form object, which is simply a window or dialog box. The Form object contains many properties such as Caption, Moveable, and Visible. Each of these properties describes the Form object and allows VBA programmers to set characteristics of a user’s interface. Like the object Person, the Form object contains methods such as Move and Refresh.
Many objects share common characteristics such as properties and methods. To demonstrate, the Label object (which implements a label control) shares many of the Form properties, such as Caption and Visible.
Properties and methods of objects are accessed using the dot operator (.), as demonstrated in the next two VBA statements.
Label1.ForeColor = vbBlue Label1.Caption = “Hello World”
Realize that properties such as ForeColor and Caption belong to the Label1 object and they are accessed using the dot operator. I discuss this in more detail in sections to come.
THE VBA IDE
If you’ve written programs in Visual Basic or VBA before, the Access 2010 VBA integrated development environment (IDE) should feel very familiar to you. If not, don’t worry—the VBA IDE is user friendly and easy to learn. For ease of use, I will refer to the VBA integrated development environment simply as the Visual Basic Editor, or VBE, from now on.
The VBE contains a suite of windows, toolbars, and menu items that provide support for text editing, debugging, file management, and help. Two common ways for accessing the VBE is with forms and code modules.
After adding and saving a form to your database, make sure your form is highlighted (selected) in Design view and then click the Visual Basic icon in the Macro area of the Database Tools tab of the Ribbon, as shown in Figure 3.1.
An easy shortcut to opening the VBE and alternating between Access and the VBE is by pressing Alt+F11. You can also right-click an object, such as the form itself, and choose Build Event, then Code Builder from the Choose Builder window to access the VBE.
FIGURE 3.1 Selecting the Visual Basic Editor or VBE.
After selecting the Visual Basic icon, the VBE should open up in a separate window, similar to the one shown in Figure 3.2.
FIGURE 3.2 Opening the Visual Basic Editor or VBE for the first time.
The first time you open the VBE, the Project Explorer and Properties windows may not be visible, but they can be accessed from the View menu.
There are a few VBE components you should familiarize yourself with right away. Each is described here and shown in Figure 3.2.
• Toolbars: Toolbars contain shortcuts to many common functions used throughout your VBA development, such as saving, inserting modules, and running your program code. Additional toolbars can be added from the View menu item.
• Menus: Menus in the VBE provide you with many development features, such as file management, editing, debugging, and help.
• Project Explorer window: The Project Explorer window provides you with a bird’s-eye view of all files and components that build your Access VBA programming environment. Notice in Figure 3.2 that my form’s name (Form_Form2) appears under the Microsoft Office Access Class Objects heading. If I had multiple forms in my database, there would be multiple form names in this folder. Remember, Microsoft Access stores all components including forms, queries, reports, and modules in a single .accdb file.
• Properties window: The Properties window shows all available properties for the object selected in the list box. Most importantly, the Properties window allows you to change the values of an object’s property during design-time development.
• Code window: The Code window is where you enter your VBA code and find procedures and event procedures for objects using the two list boxes at the top of the window.
If you haven’t done so yet, explore each of the previously mentioned components and windows so that you are comfortable navigating the VBE environment.
Introduction to Event Procedures
Procedures are simply containers for VBA code. Access VBA contains four types of procedures:
• Subprocedures
• Function procedures
• Property procedures
• Event procedures
Each type of procedure is designed to accomplish specific tasks. For example, event procedures are designed to catch and respond to user-initiated events such as a mouse click on a command button or a system-initiated event such as a form loading. In this section I concentrate on event procedures, because they are the foundation for an event-driven language such as VBA. In subsequent chapters, you learn about other types of procedures in detail.
As mentioned, objects such as the Form object contain methods and properties. They also contain specialized events that are automatically provided after the object has been added to your database. VBA takes care of naming your object’s events for you. The naming convention follows.
ObjectName_EventName
For example, a form added to your Access database called Form2 has a number of events, including the following:
Private Sub Form_Load() End Sub Private Sub Form_Unload(Cancel As Integer) End Sub
Notice the naming convention used for each event procedure: object name followed by the event name with an underscore in between. The objects and their events in Figure 3.3 are accessed from the VBE Code window.
FIGURE 3.3 Accessing an object and its associated events in the VBE.
The leftmost list box in the Code window identifies available objects. The rightmost list box contains all available events for the object selected in the left list box. Each time you select an event, VBA creates the event shell for you automatically. This saves you from having to manually type each event’s beginning and ending procedure statements.
Each procedure in the VBE Code window is separated by a horizontal line.
Keep in mind that empty event procedures serve no purpose until you write code in them using VBA statements. More importantly is that VBA procedures will not run if Access has blocked VBA code and macros from executing. You may have already seen such a security warning such as the one in Figure 3.4.
FIGURE 3.4 Access 2010 security setting that prevents VBA code from running
To run your VBA code, you must first change the security setting to allow VBA Macro content, which is accomplished by clicking the Enable Content button in the Security Warning banner seen in Figure 3.4. Type the keywords “security” or “trust center” in the Access 2010 Help window for more information about Office 2010 security settings.
Introduction to VBA Statements
VBA statements include variables, keywords, operators, and expressions that comprise an instruction to the computer. Every VBA statement falls into one of three categories:
• Declaration statement: Creates variables, data types, and procedures.
• Assignment statement: Assigns data or values to variables or properties.
• Executable statement: Initiates an action such as a method or function.
Most VBA statements fit on one line, but sometimes it is appropriate to continue a VBA statement onto a second (or more) line for readability. To split a single VBA statement into multiple lines, VBA programmers use the concatenation character (&) and the line continuation character (_) separated by a space. To demonstrate, the following assignment statement uses the concatenation and continuation characters to extend a statement across two lines.
Private Sub Label3_Click() Label3.Caption = “This is a single VBA assignment ” & _ “ statement split onto two lines.” End Sub
The term concatenation means to glue or put one or more items together.
One of the best ways to provide understandable VBA statements is with comments. Comments provide you and other programmers a brief description of how and why your program code does something. In Access VBA, comments are created by placing a single quote (’), sometimes called a tick mark, to the left side of a statement. Comments are also created by placing the keyword REM (short for remark) at the left side of a statement. The following statements demonstrate both ways of creating VBA comments.
‘ This is a VBA comment using the single quote character. REM This is a VBA comment using the REM keyword.
When a computer encounters a comment, it is ignored and not processed as a VBA statement.
ACCESSING OBJECTS AND THEIR PROPERTIES
Besides the Properties window, Microsoft Access provides a number of ways to access objects and their properties. Each way provides a level of intricacy and detail while providing specific performance characteristics. In its simplest form, programmers can directly call the name of an object (such as the Form object) or the name of a control (such as a command button). This is only applicable when accessing objects and controls that belong to the current scope of a code module. For example, the next VBA assignment statement updates the form’s Caption property during the form’s Load event.
Private Sub Form_Load() Form.Caption = “Chapter 3” End Sub
In addition to forms, controls belonging to the current form and scope can be referenced by simply calling their name.
Private Sub Form_Load() lblSalary.Caption = “Enter Salary” txtSalary.Value = “50000.00” cmdIncrease.Caption = “Increase Salary” End Sub
There are times, however, when you need to go beyond the current scope and access forms and controls that do not belong to the current object. There are a number of other reasons for being more specific about what controls you are referencing, including performance considerations and advanced control access techniques such as enumerating. To accomplish these goals, I will show you how to access forms and controls using common VBA techniques with the Me keyword prefix and collections such as the Forms collection.
The Forms Collection
Properties of the Form object can be accessed in the VBE Code window by simply supplying the form’s Access class name.
Form_Form1.Caption = “updating the form’s caption property”
Notice the naming convention used in the keyword Form_Form1. When an Access form is created and saved, Microsoft Access refers to it in the VBE as a Microsoft Office Access Class Object with the name Form representing the standard object name with a trailing underscore (_) followed by the individual form’s name. Moreover, you can use the form’s Access class name to not only access its own properties, but controls contained on the form. For example, the following VBA assignment statement uses the Access form class name to modify a label’s Caption property.
Form_Form1.Label1.Caption = “update the label’s caption property”
If your form name contains spaces, you must surround the Access form class name using brackets.
[Form_Light Switch].Label1.Caption = “Light Switch”
This approach is convenient when working with small VBA projects. At times, however, you want to use a more advanced feature (such as the Forms collection) when working with multiple forms or with multiple controls on a form. Access provides the Forms collection for specifying which form’s Caption property you are referencing.
The Forms collection contains all open forms in your Access database. To access individual forms in the Forms collection, simply supply the Forms collection an index or form name, as shown in the next statements.
‘ Using an index to refer to a form in the collection. Forms(0).Caption = “Chapter 1” ‘ Using a form name to reference a form in the collection. Forms(“Form1”).Caption = “Chapter 1”
Because form indexes can change, it is considered safer to use the form name when accessing forms in the Forms collection.
Notice when passing the name of the form to the Forms collection, you must surround the form name in double quotes. If the form’s name contains one or more spaces, you must use brackets ([]) to surround the name. After specifying a form in the Forms collection, you can use the dot operator to reference the individual form’s properties, such as Caption.
The Me Keyword
To make things more interesting, Access provides the Me keyword, which refers to the current object or form within the scope of the VBE code module. More specifically, I can use the Me keyword in place of the Access form class name to access the current form’s properties, methods, and controls.
Me.Caption = “updating the form’s caption property” Me.lblSalary.Caption = “updating the label’s caption property”
The Me keyword provides a self-documenting feature for VBA programmers in that it explicitly tells the reader what object, property, or form you are referring to.
In addition to the dot operator (.), Microsoft VBA provides the exclamation point (!) identifier for identifying what type of item or object immediately follows.
Me!lblSalary.Caption = “updating the label’s caption property”
VBA supports two operators, the dot and exclamation mark, for accessing object properties and collection items. Because the dot and exclamation mark operators can often be interchanged, it can be confusing to remember which serves what purpose and when to use what. As a general rule of thumb, use the exclamation mark operator prior to accessing an item in a collection, and use the dot operator when referencing a property of a form or control. To keep things simple however, I use the dot operator to reference both items in collections and properties of forms and controls.
Assignment Statements
You can assign data to object properties, such as the form’s Caption property, using an assignment operator in a VBA assignment statement. The assignment operator is really a fancy term for the equals (=) sign. However, it’s really more important, as you will soon see. To demonstrate, evaluate the next lines of VBA code, which assign the text “Ouch!” to the Caption property of the Form1 control.
Form.Caption = “Chapter 1”
Or
Forms(“Form1”).Caption = “Ouch!”
Or
Forms(0).Caption = “Chapter 1”
Or
Me.Caption = “Ouch!”
Or
Form_Form1.Caption = “Ouch!”
A core concept in most programming languages is to understand the difference between data assignment and equality testing. This is especially important in programming languages such as VBA, which use the same operator.
Specifically, the next assignment statement reads: “The Caption property takes the literal value ouch or the Caption property gets the literal value ouch.”
Me.Caption = “Ouch!”
Either way, the equals sign in an assignment statement is not testing for equality. In other words, you never want to read the previous assignment as “the Caption property equals Ouch!”
In the next chapter, I discuss how the equals sign can be used in testing for equality.
Command and Label Objects
I’ll now show you how to put your knowledge of event procedures, VBA statements, objects, and their properties to work by building two small programs with VBA. Let’s start by building a program that allows a user to turn off and on a light switch. Begin by adding a new form to an Access database and naming it Light Switch. Next, add one label control to the form and assign the following property values to it:
• Name: lblCaption
• Caption: Lights are on
• Font Size: 12
Now I add three image controls to the form, but only one of them is visible during the form’s runtime. (You’ll see why shortly.) Add the following property values to the image controls:
• Name: imgMain
• Picture: LIGHTON.ICO (Image located on companion website)
• Visible: Yes
• Name: imgOn
• Picture: LIGHTON.ICO (Image located on companion website)
• Visible: No
• Name: imgOff
• Picture: LIGHTOFF.ICO (Image located on companion website)
• Visible: No
Now add two command buttons to the form, which allows the user to turn off and on the light switch. Do not use the wizard while adding these command buttons.
• Name: cmdOn
• Caption: On
• Name: cmdOff
• Caption: Off
You can turn off and on the control wizards (command button control wizard) by clicking the Control Wizards icon in the Controls section of the Design tab, as shown in Figure 3.5. Note, you will need to click the More button to see the Control Wizards icon.
FIGURE 3.5 Viewing the Light Switch program in Design view.
When a graphic’s path and filename are assigned to an Image control’s Picture property, Microsoft Access does not include the image as part of its .accdb file. To use the Light Switch program located on this book’s companion website, you must first change the Picture property’s value to a location on your PC. This caution applies to all programs on the companion website that have references to images.
A depiction of my completed form in Design view is revealed in Figure 3.5. Sample code from the Light Switch form is shown next.
Private Sub cmdOff_Click() Me.lblCaption.Caption = “Lights are off“ Me.imgMain.Picture = Me.imgOff.Picture End Sub
Private Sub cmdOn_Click() Me.lblCaption.Caption = “Lights are on” Me.imgMain.Picture = Me.imgOn.Picture End Sub
I use only one image control (imgMain) to display one or the other lightbulb image. This is why I set the other two image control’s Visible properties to No. The final output of my Light Switch form in runtime mode is seen in Figure 3.6.
FIGURE 3.6 The completed Light Switch form in Form view.
You can get rid of the lines and scrollbars on a form in runtime by setting the following form property values to No:
• DividingLines: Used to separate sections on a form.
• NavigationButtons: Provides access to navigation buttons and a record number box.
• RecordSelectors: Record selectors display the unsaved record indicator when a record is being edited in Form view.
I now create a Colors program that allows a user to change the color of a label control and exit the Access application without the assistance of a control wizard. First, I create my Colors form and set the following form properties:
• Dividing Lines: No
• Navigation Buttons: No
• Record Selectors: No
I add four command buttons (three to change colors and one to exit the application) and one label control that displays the color selected by the user:
• Caption: E&xit
• Name: cmdRed
• Caption: Red
• Name: cmdWhite
• Caption: White
• Name: cmdBlue
• Caption: Blue
• Name: lblDisplay
• Caption: colors
• Font Weight: Bold
• Back Style: Normal
The label’s BackColor property cannot be changed unless the corresponding label’s BackStyle property is set to Normal.
A picture of the Colors form in design time should look similar to that in Figure 3.7. The VBA code for each button’s Click event is shown next.
FIGURE 3.7 The completed Colors form in Design view.
Private Sub cmdBlue_Click() Me.lblDisplay.BackColor = vbBlue End Sub
Private Sub cmdRed_Click()
Me.lblDisplay.BackColor = vbRed
End Sub
Private Sub cmdWhite_Click()
Me.lblDisplay.BackColor = vbWhite
End Sub
The way I used the Me keyword to access the label and its corresponding properties should not be new to you. What should have caught your attention were the values I used in assigning BackColor properties. Specifically, VBA provides you access to eight color constants:
• vbBlack
• vbRed
• vbGreen
• vbYellow
• vbBlue
• vbMagenta
• vbCyan
• vbWhite
It’s important to note that BackColor and ForeColor properties actually take a number value, which each color constant stores representatively. In addition to using VBA color constants, you can assign numbers representing a multitude of colors using either the RGB function or by viewing the BackColor or ForeColor properties in design time using the Properties window.
To terminate an Access application, use the DoCmd object, which runs Microsoft Access functionality from VBA code, and access its built-in Quit method as shown in the next command button Click event procedure.
Private Sub cmdExit_Click()
DoCmd.Quit
End Sub
The VBA code in the cmdExit_Click() event procedure is similar to the code generated by the Access Control Wizard to quit an Access application using the DoCmd object and its Quit method.
The ampersand (&) character creates keyboard shortcuts with the Alt key when placed in the Caption property of certain controls such as command buttons.
Getting User Input with Text Boxes
Text box controls receive all types of input from users such as dates, time, text, and numbers. VBA programmers (that’s you) write code in procedures to collect the user input and process it. This may seem trivial, but it’s not.
Consider a simple application that requests a user to enter two numbers, after which the user clicks a command button to add the two numbers. After adding the numbers, the program should display its output in a label control.
Private Sub cmdAdd_Click()
Me.lblOutput.Caption = Me.txtNum1.Value + Me.txtNum2.Value
End Sub
I can use a VBA assignment statement to add the value of both text boxes and assign the result to the label control’s Caption property. Given this fact, why is the output of this VBA statement 55, as revealed in Figure 3.8, instead of 10?
FIGURE 3.8 Concatenating two numbers instead of adding them.
This is an excellent question and best answered by examining the Value property of a text box. The text box’s Value property returns or sets the text box’s Text property (more on this in a moment). Because the Text property returns a string (a textual description of what’s inside the text box), the output seen in Figure 3.8 is generated because I’ve added two strings together (“5” and “5” makes “55”). In other words, I concatenated them.
To accurately process numbers retrieved from text boxes, you use a built-in VBA function called Val. The Val function is simple to use. It takes a string as input and returns a numeric value. The next set of VBA code uses the Val function to correct the previous program’s output.
Private Sub cmdAdd_Click()
Me.lblOutput.Caption = Val(Me.txtNum1.Value) + _
Val(Me.txtNum2.Value)
End Sub
Notice in this example that each Val function takes a string as input. Specifically, I use two separate Val functions to convert each text box’s Value property, one at a time, on both sides of the addition operation. The strings contained in the Value property are converted to numeric values prior to performing mathematical operations.
Now back to the relationship between the text box’s Value and Text properties. If the Text property already contains the contents of the text box, then why use the Value property? Another excellent question! Before I answer, look at the following updated code that uses the Text property to add two numbers; the output is shown in Figure 3.9.
Private Sub cmdAdd_Click()
Me.lblOutput.Caption = Val(Me.txtNum1.Text) + _
Val(Me.txtNum2.Text)
End Sub
As Figure 3.9 depicts, VBA does not allow this approach. Why? The Text property of a text box is only accessible once the text box has focus. In other words, the Text property is only current or valid once the text box has the focus. The Value property, however, is the saved value of the text box control regardless of its focus.
To clear the text box of all contents, simply assign an empty string, also known as empty quotes, to the text box’s Value property.
Me.Text1.Value = “”
In subsequent chapters I’ll show you how to validate user input with validation programming patterns and text box events.
FIGURE 3.9 Attempting to use the Text property to retrieve user input from text boxes.
VARIABLES AND BEGINNING DATA TYPES
Paramount in any programming language is the concept of variables. In a nutshell, variables are pointers to storage locations in memory that contain data. You often hear variables referred to as containers for data. In reality they are pointers that represent a memory address pointing to a memory location.
Though every variable created is unique (unique memory address), all variables share some common characteristics:
• Every variable has a name.
• Each variable has an associated memory address (hidden in modern programming languages such as VBA).
• Variables have a data type such as String, Integer, or Boolean.
Variables in Access VBA must begin with a letter and cannot be longer than 255 characters, nor can they contain periods or spaces. When created, variable names point to a location in memory that can be managed during the execution of your program. Demonstrated next, VBA programmers use the Dim keyword (short for dimension) to declare a new variable in what’s called a declaration statement:
Dim myVariable
Once a variable has been declared, VBA reserves space in memory so you can store and retrieve data from its memory location using VBA statements. Simply declaring variables is not the end of the road. It is good programming practice to tell VBA what kind of variable, the data type, you are creating. When creating variables, you should ask yourself whether your variable stores numbers, strings, Boolean, dates, or object type data.
VBA provides a number of data types for declaring variables. The more common are listed in Table 3.1.
TABLE 3.1 COMMON DATA TYPES IN VBA
By default, VBA initializes your declared variables for you. Specifically, all number-based variables are initialized to zero (0), strings are initialized to empty string (“”), and Boolean variables are initialized to False. This may seem trivial, but it is a nice feature that is not offered in every programming language.
To assign a data type to a variable, simply supply a data type name in the variable declaration using the As clause.
Dim myName As String
With this declaration statement, I’ve created one variable of String data type called myName. I can now use the myName variable in VBA statements to get and set data inside reserved memory, to which the variable myName points. This concept is demonstrated in the following statement.
myName = “Emily Elizabeth”
Notice when assigning data to string variables that the data on the right side must be enclosed with double quotes. Moreover, VBA programmers can use the concatenation operator (&) to glue two or more strings together. The next few VBA statements reveal VBA string concatenation.
Dim myTitle As String myTitle = “Access VBA ” & “Programming for the ” & “Absolute Beginner” Me.Caption = myTitle
In the preceding example, I successfully assigned the contents of the myTitle variable to the Caption property of the form, which works because both the String variable and Caption property store string data types.
Numbers however, do not require double quotes when used in assignment statements.
Dim mySalary As Double mySalary = 50000.55 myBalance = -457.23
Understanding the difference between string data and string variables is an important concept in beginning programming. Beginning programmers often forget to surround text with double quotes when assigning data to string-based variables or properties. Forgetting to do so can cause compile-time errors.
Study the next program statement and see if anything strikes you as weird.
Dim mySalary As Double Me.Caption = mySalary
It’s intriguing that I can assign the variable mySalary (a Double) to a property such as Caption, which holds String data types. After executing, the value in the Caption property is now “50000.55” and not 50000.55.
Many languages such as the C language would not like the preceding assignment statement one bit. This is because many languages require you to convert or cast data of one data type prior to assigning the value to a container of a different data type.
Do not count on VBA to always convert data successfully for you. In fact, it is good programming practice to always use the Val function to convert strings to numbers when performing numeric calculations on string variables or properties.
In addition to variables, most programming languages, including VBA, provide support for constants. Unlike variables, constants retain their data values throughout their scope or lifetime.
Constants are useful for declaring and holding data values that will not change during the life of your application. Unless they are declared in a standard code module using the Public keyword, constants cannot not be changed once declared.
In VBA, you must use the Const statement to declare a constant as revealed in the next statement, which creates a constant to hold the value of PI.
Const PI = 3.14
For readability, I like to capitalize the entire constant name when declaring constants in my VBA code. This way, they really stick out for you and other programmers when seeing their name among other variable names in program code.
IN THE REAL WORLD
At the lowest computer architecture level, data is represented by electrical states in digital circuits. These states can be translated into binary representations of 0s and 1s, which modern day computing systems can understand as machine language. Understanding how data is converted to and from binary codes is beyond the scope of this book. But, it is worth noting that depending on interpretation, binary codes can represent both a character and an Integer number. To demonstrate this concept, study Table 3.2.
TABLE 3.2 EXAMPLE BINARY REPRESENTATIONS
Interesting! The information in Table 3.2 should trigger a question in your head, which goes something like this. “If binary codes can represent both characters and numbers, how do I know what type of data I’m working with?” The notion and application of variables help to answer this question. Variables provide a storage mechanism that accurately manages the binary representations for us. For example, if I store data in an Integer variable, I can feel pretty good VBA will give me back an Integer number. And, if I store data in a String variable, I feel pretty good VBA will give me back characters and not a number. Using built-in VBA functions, it is possible to convert numbers to strings (characters) and strings to numbers.
With the knowledge of how data is represented, consider that data can be stored in varying types of media such as volatile memory (also known as random access memory or RAM) and nonvolatile areas such as disk drives. Programmers can easily manage volatile memory areas using variables with languages like VBA. Nonvolatile memory areas such as hard drives are generally managed (stored) in systems such as files or databases like Microsoft Access.
Variable Naming Conventions
Depending on the programmer and programming language, there are a number of popular naming conventions for variables. I like to use a single prefix that denotes data type, followed by a meaningful name with the first letters capitalized for each word comprising the variable name. For constants, it is advisable to capitalize the entire name so that it stands out from the rest of the code. Table 3.3 lists some common data types with a sample variable name and purpose.
TABLE 3.3 SAMPLE NAMING CONVENTIONS
As you program more and observe more programming, you will notice many other popular naming conventions. The important note is to use a naming convention and stick with it throughout your code!
Variable Scope
Variable scope is a fancy way of describing how long a variable will hold its data, or in other words, how long will it live. VBA supports three types of variable scope.
• Procedure-level scope
• Module-level scope
• Public scope
To create a variable with procedure-level scope, simply declare a variable inside of a procedure.
Private Sub Form_Load() Dim dProfit As Double dProfit = 700.21 End Sub
In the preceding form Load event procedure, I declared a Double variable called dProfit that will hold its value as long as the current scope of execution is inside the procedure. More specifically, once program execution has left the form Load procedure, the dProfit variable is initialized back to 0.
If you need to maintain the value of dProfit for a longer period of time, consider using a module-level or public variable. Module-level variables are only available to the current module from where they are declared, but are available to all procedures contained within the same module. Moreover, module-level variables are considered private and can be declared either with the keyword Dim or Private in the general declarations area, as demonstrated next.
Dim dRunningTotal As Double ‘ module-level variable Private iScore As Integer ‘ module-level variable
You can create public variables that are available to the entire project (all code modules) by declaring a variable using the Public keyword in the general declarations area of a code module.
Public bLoggedIn As Boolean
The general declarations area is Located at the top of a code module and is considered an area that is outside of any procedure.
Determining variable scope is part of application development—during which you define all needed variables, their storage type, and scope.
Option Statements
VBA has a few module-level utility statements known as options that are used for naming conventions, string comparisons, and other internal settings. First off, you may have already noticed the Option Compare Database statement located in the general declarations area of the VBE Code window.
Per Microsoft, the Option Compare Database statement is: “performs string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.” This statement can be modified to either Option Compare Binary or Option Compare Text instead of Option Compare Database. If your VBE code module does not include an Option Compare statement, VBA will default to Option Compare Binary, which results in string comparisons based on a character’s internal binary representation.
The next option statement, Option Explicit, is more important to beginning VBA programmers as it forces you to explicitly declare all variables before you can use them. This is a huge, I mean HUGE service to even seasoned VBA programmers. By forcing the explicit declaration of variables, you are saved an often painful process of misspelling or misrepresenting variables that ultimately lead to program or compile errors.
Unless you tell Microsoft Access to make it so, the Option Explicit statement may not appear by default in your VBE code module. To have this statement provided in each of your code modules, simply access the Options window from the VBE Tools menu and select the Require Variable Declaration setting, as demonstrated in Figure 3.10.
FIGURE 3.10 Requiring variable declaration in the Options window.
The next option clause is the Option Base statement, which is manually typed into the general declarations area of each code module. In a nutshell, the Option Base statement defines the lower bounds for arrays. VBA arrays are by default 0-based arrays, but can start at 1 using an Option Base statement as seen next.
Option Base 1
I discuss arrays and their upper and lower bounds in more detail in subsequent chapters.
VBA ARITHMETIC AND ORDER OF OPERATIONS
It’s no secret, programming in any language involves some level of math. Though it’s not necessary to be a mathematical wiz in calculus, algebra, or trigonometry, it is useful to understand the essential arithmetic operators and order of precedence offered in a given programming language. For basic mathematical operations, VBA supports the operators shown in Table 3.4.
TABLE 3.4 COMMON MATHEMATICAL OPERATORS
In addition to basic math operations, VBA supports what’s known as order of operations using parentheses. Without parentheses, VBA determines order of operations in the following order.
1. Exponents
2. Multiplication and division
3. Addition and subtraction
When VBA encounters a tie between operators, it performs calculations starting from the leftmost operation. To get a better handle of the importance of operator precedence and order of operations, consider the following equation, which calculates a profit.
Profit = (price * quantity) – (fixed cost + total variable cost)
The next VBA assignment statement implements the preceding equation without parentheses: In other words, without a well-defined order of operations.
dProfit = 19.99 * 704 - 406.21 + 203.85
The result of this calculation is 13870.6. Now study the next VBA statement, which implements the same equation; this time using parentheses to build a well-defined order of operations.
dProfit = (19.99 * 704) - (406.21 + 203.85)
Using parentheses to guide my order of operations, my new profit is 13462.9. That’s a difference of $407.70 that might have been recorded as inflated profits!
CHAPTER PROGRAM: FRUIT STAND
The Fruit Stand program is a simplified data entry system for a small fruit vendor. It implements many chapter-based concepts such as variables, constants, and VBA statements.
To build the Fruit Stand program, you’ll need to create a form in Design view, as shown in Figure 3.11.
FIGURE 3.11 Building the Fruit Stand program in Design view.
Controls and properties of the Fruit Stand program are described in Table 3.5.
TABLE 3.5 CONTROLS AND PROPERTIES OF THE FRUIT STAND PROGRAM
All of the code required to build the Fruit Stand program is shown here.
Option Compare Database Option Explicit ‘ declare module level variable and constants Dim dRunningTotal As Currency Const TAXRATE As Currency = 0.07 Const PRICEPERAPPLE As Currency = 0.1 Const PRICEPERORANGE As Currency = 0.2 Const PRICEPERBANANA = 0.3
Private Sub cmdCalculateTotals_Click()
‘ declare procedure-level variables
Dim dSubTotal As Currency
Dim dTotal As Currency
Dim dTax As Currency
‘ calculate and apply sub total
dSubTotal = (PRICEPERAPPLE * Val(Me.txtApples.Value)) + _
(PRICEPERORANGE * Val(Me.txtOranges.Value)) + _
(PRICEPERBANANA * Val(txtBananas.Value))
Me.lblSubTotal.Caption = “$” & FormatNumber(dSubTotal, 2)
‘ calculate and apply tax
dTax = (TAXRATE * dSubTotal)
Me.lblTax.Caption = ”$” & FormatNumber(dTax, 2)
‘ calculate and apply total cost
dTotal = dTax + dSubTotal
Me.lblTotal.Caption = ”$” & FormatNumber(dTotal, 2)
‘ build and apply running total using module-level variable
dRunningTotal = dRunningTotal + dTotal
Me.lblRunningTotal.Caption = ”$” & FormatNumber(dRunningTotal, 2) End Sub
Private Sub cmdExit_Click() DoCmd.Quit ‘ terminates the application End Sub
Private Sub cmdResetFields_Click() ‘ reset application fields Me.txtApples.Value = “0” Me.txtOranges.Value = “0” Me.txtBananas.Value = “0” Me.lblSubTotal.Caption = “$0.00” Me.lblTax.Caption = “$0.00” Me.lblTotal.Caption = “$0.00” End Sub
Private Sub cmdResetRunningTotal_Click() ‘ reset running total variable and application field dRunningTotal = 0 Me.lblRunningTotal.Caption = “$0.00” End Sub
Privte Sub Form_Load()
‘ set focus to first text box
Me.txtApples.SetFocus
‘set default quantities when the form first loads
Me.txtApples.Value = 0
Me.txtBananas.Value = 0
Me.txtOranges.Value = 0
End Sub
SUMMARY
• The event-driven paradigm allows programmers to build applications that respond to actions initiated by the user or system.
• Access VBA includes a number of events that are categorized by the objects they represent.
• Objects are nouns such as a person, place, or thing.
• Objects have properties that describe the object and methods, which perform actions.
• Properties and methods of objects are accessed using the dot operator (period).
• The VBE (Visual Basic Environment) contains a suite of windows, toolbars, and menu items that provide support for text editing, debugging, file management, and help.
• Procedures are containers for VBA code.
• VBA statements are comprised of variables, keywords, operators, and expressions that build a complete instruction to the computer.
• Comment statements are ignored and not processed as a VBA statement by the computer.
• The Forms collection contains all open forms in an Access database.
• The Me keyword refers to the current object or form within the scope of the VBE code module.
• The DoCmd object runs Microsoft Access functionality from VBA code.
• Use the Val function to accurately process numbers retrieved from text boxes.
• Variables are declared using the keyword Dim.
• Variables are pointers to storage locations in memory that contain data.
• All number-based variables are initialized to zero (0), string variables are initialized to empty string (“”), and Boolean variables are initialized to False.
• Constants are useful for declaring and holding data values that will not change during the life of your application.
• The Option Explicit statement forces an explicit declaration before a variable can be used.
• VBA supports order of operations using parentheses.
1. Create a simple word processor that allows a user to enter text into a large text box. (Hint: Set the Enter Key Behavior property of a text box to New Line in Field.) The user should be able to change the foreground and background colors of the text box using three command buttons representing three different colors. Also, the user should be able to change the font size of the text box using up to three command buttons representing three different font sizes.
2. Build a simple calculator program with an Access form that allows a user to enter numbers in two separate text boxes. The Access form should have four separate command buttons for adding, subtracting, multiplying, and dividing. Write code in each command button’s Click event to output the result in a label control.
3. Create a discount book program that allows a user to enter an initial book price, a discount rate (e.g., 10% off), and a sales tax percentage. The program should display, in labels, the derived discount price, sales tax amount, and final cost of the book.