Read an Excerpt
Lesson 5: Write Your Own Functions
Watching small children learn to speak is fascinating. Long before they're aware of grammar or sentence structure, they manage to build sentences. Without any understanding of the rules of language, they nevertheless communicate. Of course, before you can read or write, you need to learn something about the language-still, you can get by pretty well with a rudimentary understanding of grammar and a limited vocabulary.Although a far cry from anyone's native tongue, computer programming languages share many characteristics with human language. Both have an extensive vocabulary and complex rules of grammar to follow. But as you've seen in previous lessons, you can learn to get things done using Visual Basic, even create and customize applications, without bothering to learn much about the programming language. So far, you've been speaking the language without knowing the underlying rules.
You can get a good start with a language before studying any grammar, but you won't be likely to write War and Peace. In order to use a language with confidence, you need to understand how it works-the basic rules and components, and how to combine them effectively. Now that you've gotten a taste of what you can accomplish by programming Microsoft Access, it's time to discover the bigger picture.
In this lesson, you'll learn about the fundamental building blocks of Visual Basic for Applications-modules and procedures-and how you fit them together to build flexible, well-organized applications. You'll pick up new vocabulary along the way, and you'll try using it in several different contexts. Then, throughout Part2, you'll continue to explore the underlying rules of Visual Basic programming and try out all the tools Microsoft Access provides for crafting your modules.
Start the lesson
Start Microsoft Access and open the 05Contac database in the practice files folder.
Understanding Modules and Procedures
In Part 1, you worked exclusively with event procedures on existing forms. Because forms are so central to Microsoft Access applications, this type of procedure is probably where you'll add most of the code you write. However, there is another important type of procedure you'll create in this lesson, called a general procedure. General procedures don't run automatically in response to events - instead,, you have to run them yourself, either by referring to them in an expression on a form or report or by using them in Visual Basic code. General procedures come in two types: Sub procedures and Function procedures. Event procedures, by contrast, are all Sub procedures.
Why Create General Procedures?
You can think of general procedures as having a supporting role in your applications - while event procedures are the central force in Microsoft Access programming, general procedures are helpers along the way. You could accomplish most anything in Microsoft Access with event procedures alone, but using general procedures provides much better solutions as the complexity of your application increases. There are several ways you can use general procedures:Perform complex operations that don't fit in an expression. If you've used expressions as property settings on forms and reports, you know that they can quickly become unwieldy. You can hide this complexity by creating your own function to perform the operation you need, and then referring to it in your expressions. Additionally, Visual Basic code can perform complex operations that aren't possible using expressions.
Reuse program code to repeat a task. Your event procedures often perform the same or similar actions at various times. For example, you might often display a message to the user. While you could copy the same code into every event procedure that uses it, this would be extremely inefficient. Instead , you can create a general procedure that performs a common task, and reuse it in every procedure that performs that task.
Break up programming tasks into manageable units. If you were to put all your code into event procedures, they could become very long, complicated, and difficult to understand. By creating separate procedures to perform each part of a large task, you can simplify your work and make each individual procedure easier to read and understand.
Standard Modules and Form Modules
You can put general procedures in one of two places: in a form or report module, where they share space with any event procedures you write; or in a standard module. Standard modules are separate database objects containing one or many procedures. They most often contain "utility" functions, so named because they are useful in many different circumstances. For example, one standard module might contain several general functions for working with dates in Visual Basic-you can copy such a module to any application in which you use dates.If you add a general procedure to a form or report module, it belongs to that form or report. Most often, you'll use this type of procedure only in the form or report itself, either in expressions on the form or in other procedures in the same module. Create this type of procedure to perform a task that applies directly to the form-for example, to work with information in the fields on that form.
General procedures that you add to standard modules usually belong to the application as a whole. Create this type of procedure to perform a task that applies to more than one form or report-often, a task that could apply to any form or report.
Creating General Procedures in a Standard Module
A database can contain one or more standard modules, each of which can hold many procedures. You'll usually group all the procedures for a specific purpose into one module. In this section, you'll create a module to store two procedures that are used to display messages to the user.Create a new standard module
- In the Database window, click the Modules tab.
Standard modules are regular objects in the Database window, just like tables and forms.
- Click the New button.
Microsoft Access creates a new standard module and displays it in the Module window. From here, you can enter Visual Basic code and create general procedures.
(Unlabeled illustration)
Using the Declarations Section
A new module doesn't contain any procedures-however, like all modules, it has a special section called the Declarations section. In this section, you declare variables and constants, and enter other preliminary code that applies to the entire module. In a standard module, code in the Declarations section can even apply to all modules in the application.As you can see, Microsoft Access automatically added two statements to the Declarations section of your module:
Option Compare Database
Option Explicit
These statements use the Option keyword to set options for the whole module. They are two of several available statements that begin with the Option keyword.
The Compare option determines the method Microsoft Access uses for comparing strings in your module, and can be set to Database, Text, or Binary. Using the Database option tells Microsoft Access to compare strings using the sort order set in your database, as opposed to the traditional Visual Basic sort order, which distinguishes between uppercase and lowercase letters. Because Option Compare Database makes sense for most applications in Microsoft Access, you should just leave this statement alone in your modules.
The Explicit option tells Microsoft Access to check all the variable names you use in the module, to make sure you've declared them. If you remove this option from the Declarations section of a module, Microsoft Access lets you enter just about anything in code-but doing this makes your code much more prone to errors, because a misspelling won't be caught before you run your code. Having the Option Explicit statement in your modules will help you avoid many headaches down the road....