Programming Fundamentals Using VBA - Creating Procedures
(Page 2 of 9 )
You will recall from Chapter 5 that most VBA code is contained in blocks called procedures. Further, these procedures are divided into two categories: sub procedures (sometimes called subs) or function procedures. A sub just performs a task but does not return a value, while a function does return a value.
Most of the time, you will see the following two lines of code at the top of the module:
Option Compare Database
Option Explicit
These are called the general declarations of the module. Any code put into this section will affect all the procedures in the module.
The Option Compare line has three possible settings for sorting strings of characters within the code:
- Option Compare Database This uses the same sort order as the database itself and is the most common setting.
- Option Compare Binary This sorts a string of characters based upon the binary values of the characters. Thus, uppercase letters would be recognized before lowercase.
- Option Compare Text This makes the sort case sensitive with regard to the local language.
Throughout this text, we will keep the Option Compare Database setting, which is the default.
The Option Explicit line is, in my opinion, an important one. As you shall see momentarily, that line can keep you from making a lot of coding mistakes by forcing you to formally declare all variables before using them.
If this option does not come on automatically when you create the module, force it to do so by selecting Tools | Options and selecting Require Variable Declaration on the Editor tab, as shown in Figure 6-6.
Think of a procedure as a miniprogram to do one specialized job. For instance, a procedure may be used to add two numbers together. Every time those numbers need to be added, you can just call this prewritten procedure. Without the procedure, you might need to write redundant code every time you needed to do that particular job.
Within the procedure, you declare variables, have loops and If statements, and even call other procedures. Let’s build a simple procedure that adds two numbers together. We will discuss each step along the way.
Figure 6-6. The Require Variable Declaration option
A procedure is first declared with the following syntax. Of course, the name is anything that you choose it to be.
Sub addNumbers()
End Sub
Notice that the VBA Editor draws a horizontal line between the general declaration area and the new procedure. This is to help delineate the procedures. It has no bearing on how the procedure works.
When you declare a procedure, it is important that you begin it with the word Sub and complete the declaration with the parenthesis. I say it is important because it is worthwhile to use proper syntax from the beginning. If you don’t put the parenthesis in, the VBA Editor will put it in for you as soon as you press ENTER.
Normally you would place any arguments the procedure would be expecting to receive within the parenthesis. Even if no arguments are expected, you need to have the empty parentheses.
As soon as you press ENTER, the VBA Editor adds the End Sub line to close the procedure. All of your code will go between the opening and closing statements.
As we move through this book, I will relate what I feel are good programming habits, or practices. While these are certainly not rules, they are accepted by many programmers and are industry standards.
The first such practice is to name your procedures with a name that reflects its job, for instance, addNumbers. Any other programmers looking at it would easily discern what it does. In addition, even though VBA is not case sensitive, it will sometimes need to interact with programs that are. A common naming convention, as mentioned in Chapter 4, is to use lowercase letters, with the exception of midword capitalization, no spaces, and to begin with a letter rather than a number. In the case of beginning with a letter rather than a number, this is more than just a naming convention; for many programs, including VBA, it is a rule.
The second practice is to indent the code in the procedure. That way, it is easy to spot where the procedure begins and ends. I usually just press the SPACEBAR three times. The VBA Editor will preserve the indenting for subsequent lines.
The third practice is to carefully comment your code. You can easily do that by using the single quote before a line. If you do that, VBA will ignore that line as a comment.
The following example shows the indent and comment:
Sub addNumbers()
'Declare the variables
End Sub
Notice the color the VBA Editor uses to indicate the comment. Understanding the color coding in the VBA Editor can help you track what is going on with the code. As an example, code colored red indicates a syntax error.
You can see what the various colors denote by selecting Tools | Options. Once you are in the Options dialogue box, the Editor Format tab will show you what the various colors mean and, in addition, allow you to change the scheme.
Next: Declaring Variables >>
More Visual Basic.NET Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter six of the book Access VBA Programming, written by Charles E. Brown and Ron Petusha (McGraw-Hill/Osborne, 2004; ISBN: 0072231971). Check it out at your favorite bookstore today. Buy this book now.
|
|
AT&T devCentral & BlackBerry(r) Webcast Series: BlackBerry and GPS -Build Location Awareness into your BlackBerry Applications, July 10th -1:00PM EST. 
