|

Purpose
In this project, a database management system will be implemented to make the self-ordering easier. The users for this program will be used for customers in various restaurants. It will take a customer’s order, total it, and give them an amount. The system will instruct the customer to enter the correct amount. If the amount is not enough, the system will instruct them to enter more money. If it is too less, however, the system will them issue out change.
Use of Visual Basics
Visual Basic for Applications (VBA) will be used because it is a programming language built right into many Microsoft programs. This language is built on the Basic programming language made popular by PCs over the past 25 years. VBA shares similarities with other Windows implementations of the Basic language, such as Visual Basic. If you are familiar with programming in a different language (including using macros), you can quickly get up to speed with VBA.
There are a few terms you should know before you start programming in VBA: * Procedure. A section of programming code, designed to accomplish a specific task, which your program statements can use throughout your program. There are two types of procedures: functions and subroutines. * Function. A procedure that returns a value after it completes its task. When called, functions typically appear on the right side of an equal sign in an expression. * Subroutine. A procedure that does not return a value after it completes its task. * Module. A named collection of procedures. You typically attach modules to specific forms or reports.
You can accomplish many tasks simply by using macros. This implies--and correctly so--that not everyone needs to use VBA. As a general rule, if you can accomplish a task by using macros, do so. Macros are simpler to implement and easier to maintain. However, there are certain tasks which you cannot do with macros or which you can better implement through VBA. You should use VBA instead of macros when you: * Want to create your own functions. * Need to handle events that pass parameters or accept return values. * Need to perform error handling. * Need to process information on a record-by-record basis. * Want your application to run as quickly as possible.

There are other instances when you should use VBA, as well: * When you don't want to manually create or manipulate database objects. * If you want to create a library. * If you are writing your own Wizards. * When you need to perform system-level operations such as DDE and OLE. * When you want to access Windows API functions or DLL files for other applications.
Parts of a Program
In VBA, a program consists of procedures. These procedures are made up of individual program lines, which you must put together according to VBA rules. These rules are referred to as syntax. There are many elements that go into a program, including the following: * Statements * Variables * Operators * Functions * Database objects and collections
Understanding Variables
Your VBA programs use variables to store information. Just like you can store data in a field within a table, you can store information in a variable for use in a program. Variables are so named because their contents can vary, meaning your program can change their values.
Variables can be very powerful. They let you create general-purpose routines that can act and make decisions based on the value within the variable. For example, consider the following logical sentence:
If the temperature is high enough, I will go to the beach.
In this instance, the temperature is the variable. If it is high enough, you will do something; if not, you will not. Variables serve the same purpose in a program. Later in this chapter, you will learn how VBA uses variables to make decisions within your programs.
Data Types
When you define a table, you need to define what type of information will be stored in each field of the table. You need to be aware of what information your program is going to store in the variable, and then you need to use the correct data type so VBA knows how to handle the value. VBA supports eleven different data types, as indicated below: Data Type | | Type of Values | Boolean | | Either logical True or logical False | Byte | | Numbers between 0 and 255 | Integer | | Numbers between -32,768 and 32,767 | Long | | Numbers between -2,147,483,648 and 2,147,483,647 | Single | | Numbers between -3.402823E38 and 3.402823E38 | Double | | Numbers between -1.79769313486232D308 and 1.79769313486232D308 | Currency | | Numbers between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 | Date | | Date between January 1, 100 and December 31, 9999 | String | | Up to 65,535 characters (approximately) | Object | | Reference to a database object | Variant | | Varies based on the characteristics of the information |
Notice that some of the data types shown above are numeric data types, meaning they hold number values. When you take into account the setting of the Field Size property for the field, VBA data types correspond to field data types. The String data type is comparable to the text data type for fields.
String type variables can contain any characters--letters, numbers, or symbols--which you can think of.
The Date data type is not really a date, as you might expect. For instance, humans represent a date such as June 11, 1999. VBA, however, works with date serial numbers. These are a numeric representation of a date, in the form of a number. The appearance of that number (for instance, June 11, 1999) is the format of the number, not the date serial number itself. Access uses the special Date data type to contain serial numbers which represent any date between January 1, 100 and December 31, 9999.
Another interesting data type is Object. This data type is actually a reference to an object tracked by Access. Many of the actions you perform in VBA are upon objects, such as tables, forms, reports, and the pieces thereof. Each of these are objects, and can be addressed within VBA. Later in this chapter you will learn more about VBA objects in Access.
The last VBA data type is not supported directly in setting data types for fields. The Variant data type automatically adapts to the type of information it contains. If you store a number in a variant, it behaves like a numeric data type. If you store characters in it, it behaves like a string. In addition, you can store special values in a variant, such as dates and times. There are nine types of data you can store in variants: * Empty * Null * Integer * Long * Single * Double * Currency * Date * String
Most of these types of data should look familiar by now. The first two, however, may not. A variant is empty if your program has never assigned it a value. Typically, a variant is empty at the beginning of your program, before you ever use it. A variant is Null if your program has initialized it but it currently contains no data.
Declaring Variables
Before you can use a variable within your program, you need to declare it. This simply means that you need to let VBA know what name you are going to use for the variable and what data type it will store. There are two ways you can declare a variable--either implicitly or explicitly.
Implicit declaration means you simply start using the variable. For example, if VBA encounters the following line in your program, and you have never used the variable “lc” before, then VBA assumes you want to define a variable by the name of “lc”. lc = 123.456
Because you are assigning a single precision value to the variable, you have implied the variable's type as single.
Explicit declaration means you declare the variable before actually using it. You explicitly declare using one of the following declaration statements: * Dim. Dimensions (sets aside space for) a variable. You can access the variable within the current procedure only. Each time your program uses the procedure, VBA reinitializes the variable. * Global. Same as Dim, except the variable is accessible in any procedure. * Static. Same as Dim, except VBA does not reinitialize the variable every time the procedure is used.
Assigning Values to Variables
To inform VBA that a particular variable should contain a particular value, you need to assign the value to the variable. To assign a value to a variable, use the assignment operator (the equal sign) in an equation, as shown here:
Dim sl As Integer sl = 65
After you program this assignment, you can use the variable “sl” anywhere else in your program and VBA will understand you actually mean the number 65.
Understanding Operators
An operator is a symbol that informs VBA about the operation you want to perform. For instance, the expression 2 + 2 uses an operator--the plus sign. There are 4 general categories of VBA operators: * Math operators. Used to put together mathematical equations. Examples include addition, subtraction, multiplication, and division. * Comparison operators. Used to determine how two operands relate to each other. The result of a comparison operation is always either True or False. * String operators. Used to combine the characters in two strings. * Logical operators. Used to test conditions. Examples include AND, OR, and NOT. The result of a logical operation is always either True or False.
There are 20 different operators spread among these 4 categories: Operator | | Category | | Meaning | + | | Math | | Addition | - | | Math | | Subtraction | * | | Math | | Multiplication | / | | Math | | Division | ^ | | Math | | Exponentation | Operator \ | | CategoryMath | | MeaningInteger division | Mod | | Math | | Modulus | = | | Comparison | | Equal | < | | Comparison | | Less than | > | | Comparison | | Greater than | <= | | Comparison | | Less than or equal | >= | | Comparison | | Greater than or equal | <> | | Comparison | | Not equal | & | | String | | Concatenation | AND | | Logical | | And | EQV | | Logical | | Equivalent | IMP | | Logical | | Implication | NOT | | Logical | | Not (or the logical opposite of) | OR | | Logical | | Or | XOR | | Logical | | Exclusive Or |
Understanding Functions
Technically, a function is a procedure that returns a value. VBA has a wide variety of built-in functions which you can use in your programs. These functions are important because they keep you from having to "reinvent the wheel" for common tasks.
There are many, many functions built into VBA. These functions are categorized by the type of work they do. The categories are: * Math Functions * Financial Functions * Data Conversion Functions * String Functions * Date and Time Functions * Transcendental Functions * Miscellaneous Functions
Creating a VBA Module
Now that you know how to put together a program, you are ready to give it a try. To create a VBA procedure, you follow many of the same steps you follow when you created macros. The general steps in VBA programming are as follows: 1. Identify the task you want to accomplish. 2. Plan the steps needed to accomplish that task. 3. Create the programming code necessary to implement the steps. 4. Test the program. 5. Refine the program. 6. Repeat steps 4 and 5 until the program works correctly.
You create VBA programming code by using the VBA Editor, which is described in the following section.
What is the VBA Editor?
You create VBA programs using the VBA Editor. To start the VBA Editor, first click your mouse on the Modules button in the Database window. Then, click your mouse on the New button. Access, in turn, displays the VBA Editor.
Notice that there are several different parts to the VBA Editor. In the upper left corner is what the editor refers to as the Project window. This is where you can see the different elements of your project and any modules that have been defined in the workbook. Just below the Project window is the Properties window. Here you can specify different attributes of whatever you have selected in the Project window. For most simple development needs, you will never do much with the Properties window. To the right of the Properties window, and at the very bottom of the screen, is the Immediate window. This is where you can either test parts of your procedures during development or you can find the immediate results of various commands. The Immediate window comes in very handy during testing and debugging, when they are necessary.
The Module window, which is the largest window on the screen, is where you do your programming. At the top of the Module window are two drop-down lists. The one on the left is called the Object box. The one on the right is the Procedure box. You use the Object box to select which object you want to work with. When you first create a module, the object is set to the word General, meaning you are working on a general module, not on one associated with a particular object in a form or report.
The Procedure box is where you indicate the name of the procedure on which you want to work. If you choose or specify a different procedure in this box, the information Access shows in the Module window changes to reflect the VBA statements you have assigned to that procedure.
The top level of a module is the Declarations section. It begins with the procedure name you indicate in the Procedure box when you first create a module. Take a look at the Module window. It contains the programming code already defined for the declarations section.
To enter programming statements into a procedure, you type them in the Module window. As you enter information, Access checks to make sure it can understand what you type. In other words, Access checks the syntax of what you enter. You use the correct syntax when you follow the VBA rules of grammar.
You can cut, copy, and paste sections of code using standard Windows mouse or keyboard techniques. You can perform these operations either in the same procedure or between different procedures.
Creating a New Procedure
Assume for a moment that you want to create a procedure that will step through the records in the Business Customers table, which you developed earlier in this book. The procedure will check the ZIP Code of every record. If the ZIP Code equals a specific value, the procedure will change the record's salesperson.
Your first step is to create a new procedure in the Module window. To do this, you must first decide if your procedure will be a subroutine or a function. Your choice will depend on the task you want the procedure to perform. Remember, if you want your procedure to return a value, you need to make it a function. You also need to determine a name for your procedure. Unlike the names you use for fields, tables, and other database objects, this procedure name cannot contain spaces. In this case, let's say you decide to use the name ChangeSalesByZip, which is quite descriptive
Specifying Parameters
Take a look at the parentheses after the function name (on the first line of the procedure). Inside these parenthesis, you specify arguments (parameters) for your function. Position the cursor between the parentheses, and change the line so it looks like this:
Function ChangeSalesByZip(ZipWanted As String, NewSales As Long)
This statement tells VBA that your function requires two arguments: ZipWanted and NewSales. The first parameter is a string, and the second is a long integer. Your function will use these arguments to determine which records to change and what to change them to.
There is one other thing you need to do to this function's first line. You already know that you use functions when you want to return information to whoever called it. In this case, you want to return the number of records the function changes. You need to specify, on this first line, what type of value your function will return. Add As Long to the end of the line, so it looks like this:
Function ChangeSalesByZip(ZipWanted As String, NewSales As Long) As Long
The As Long tells VBA that this function returns a long integer to the calling routine. With these declarations out of the way, you are ready to enter the body of your routine in between the first and last lines on your screen.
Saving Your Module
After you have finished your first procedure, you can create other procedures in this same module, if you desire. Otherwise, your next step is to save your module and close it. You save the module by clicking your mouse on the Save tool on the toolbar, or by choosing Save from the File menu. Access then asks you for the name you want to use for your module. Enter a name, such as MyModule, and then press ENTER or click your mouse on OK. Access will save the module as a part of the database.
Now you can close your module by clicking your mouse on the Close icon (the X) in the upper-right corner of the VBA Editor program window.
Testing Your Procedure
Once you have created a procedure, you need to test it out. VBA provides several different tools you can use to do this. These tools include: * The Immediate window * Debug.Print * Breakpoints
Using the Immediate Window
Perhaps the most important tool you can use to test your procedures is the Immediate window. This window lets you see the results of your procedures right away. Using the window, you can uncover problems before they get buried among dozens of different procedures you may develop. For example, you can test your functions to see what values they are returning.
Remember that the Immediate window appears at the bottom of the VBA Editor workspace. (If it is not visible for some reason, you can display it by choosing Immediate Window from the View menu.) You can select the window by clicking your mouse in the window. Access displays a cursor and then waits for your command. You can switch to another VBA Editor window by simply clicking the mouse in the desired window. Later, you can hop back to the Immediate window by clicking your mouse within it.
You should note that before a procedure is executed, the value of all variables is undefined, meaning you cannot print them. In addition, after a procedure is executed, VBA wipes out the contents of all variables. This means that the only time you can meaningfully print the value assigned to a variable is while a procedure is executing. Typically, this is done after a breakpoint is reached, as discussed shortly.
Using Debug.Print
Another way you can use the Immediate window is with Debug.Print. VBA lets you use Debug.Print to display the contents of any variable, as your program runs. Using Debug.Print, you can watch closely a variable's value to see how your code changes the variable. By tracking a variable's value in this way, you may detect errors in your code.
To display a variable value in this way, you include Debug.Print in a line of code. For example, if you want to determine the value of the TempStore variable, you can use the following line of code:
Debug.Print "Value of TempStore is " & TempStore
If TempStore contains a value of 4, when VBA executes this line, Access will display the following in the Immediate window:
Value of TempStore is 4
Debug.Print has no other effect on your program; everything else remains the same. Debug.Print is particularly useful when you're debugging, since you don't have to bother with stopping and restarting your program.
Setting Breakpoints
Another testing tool which VBA provides for you is a breakpoint--a place you specify within your program where you want the program's execution to stop. Once your program stops, you can use the Immediate window to display information about variables or to test out other procedures, if you desire. VBA lets you set breakpoints on any line of code in your program or within any procedure of your program.
To set a breakpoint, position the cursor on the program line where you want VBA to stop. Then, select the Toggle Breakpoint option from the Debug menu or click your mouse on the toolbar Breakpoint tool.
When you later run the program and VBA encounters the line at which you have set the breakpoint, VBA will do three things: * Pause the program. * Display your program (with the line containing the breakpoint) in the program window. * Wait for your command.
Normally, you set breakpoints so you can inspect the status of the program while it is executing. This is the time when you can print out the contents of variables, as discussed earlier in this chapter. You can also use other VBA menu commands (those under the the Debug menu) to step through your procedure one instruction at a time. If you are done inspecting the program, you can also choose the Run command from the Run menu. This causes the procedure to continue executing as if no breakpoint had been encountered.
To later remove a breakpoint, stop your program and position the cursor on the line that contains the breakpoint. Then, perform any of the actions you used to first set the breakpoint: select the Toggle Breakpoint option from the Debug menu or click your mouse on the toolbar Breakpoint tool. This removes the breakpoint, which you can see because VBA removes the highlight from the program line.

Bibliography
Harbour, J. S. (2008). Visual Basic. Net Programming for the Absolute Beginner. Cincinatti, OH: Premier Press.
Petroutsos, E., & Cirtin, T. (2002). Mastering Visual Basics.Net. Alameda, CA: SYBEX.
Sigon, T. (2009, October). What is Visual Basics? Retrieved from Visual Basics: http://visualbasic.about.com/od/standalonevb6/l/bllearnvba.htm
Valiev, S. (2008). Microsoft(r) Visual Basics.Net Programming Fundamentals. Huntington, NY: Frontenac Books.