Jumpstart Creating a Simple VBA Program

Organize with Office 365 Course

Organize With Office 365

Get Instant Access

In This Chapter

^ Starting out with a standard module ^ Creating procedures ^ Getting help with VBA keywords ^ Editing existing code m Jisual Basic for Applications (VBA) is a programming language for writing yr instructions that tell Office applications — in this book, that means Access — the steps needed to perform a task. You store code in Access modules. The tool that you use to create and edit VBA code is the Visual Basic Editor, which opens automatically whenever you open an Access module. (If you need a refresher on the basics of using the Visual Basic Editor, hop back to Chapter 2.)

In this chapter, we get into some of the nitty-gritty of what's really involved in writing VBA code within Access. You discover how to create a module in Access and how to create procedures within a module. You also read about VBA syntax, which defines the rules you need to follow when writing a VBA statement. Finally, this chapter shows you how to use preexisting code in your own modules.

Creating a Standard Module

Before you start writing code, you need a place to put it. Putting your code in standard modules is always a good bet because code in standard modules is accessible to all objects within a database. Creating a new standard module is easy. Just follow these steps:

1. With your database open in Access, click the Create tab on the Ribbon.

2. Click the Macro button and select Module from the drop-down list that appears, as shown in Figure 3-1.

Figure 3-1:

Begin by creating a new standard module.

Click the Create tab...

Click the Create tab...

Figure 3-1:

Begin by creating a new standard module.

...and then select Module.

The new module opens in the VBA Editor. Most likely, it's empty except for the words Option Compare Database at the top. That line, a module-level declaration, just tells VBA that when comparing values, it should use the same rules as the rest of the database. The module might also contain the words Option Explicit on the second line. That line tells VBA to require you to declare variables before using them. If Option Explicit appears, just highlight and delete that line. We talk more about that later on.

As we discuss in Chapter 1, a module contains VBA code that's organized into one or more procedures. A procedure is simply the set of steps needed to perform some task. A new standard module contains no procedures because it's empty. Thus, the first step to writing code is to create a procedure.

Creating a Procedure

Adding a procedure to a module is a fairly simple task. The procedure that you create can be either a Sub procedure or a Function procedure. For now, it's sufficient to know that a Sub procedure is like a command on a menu: When called, it just does its job and doesn't return anything. A Function procedure, on the other hand, is more like a built-in function in that it returns a value. However, the steps for creating either type of procedure are the same:

1. In the Visual Basic Editor, choose InsertOProcedure.

The Add Procedure dialog box appears.

2. Type a name for your procedure in the Name text box.

The name must begin with a letter and cannot contain any blank spaces or punctuation marks. To create a practice procedure, enter a simple name, like mySub.

3. Choose the type of procedure you want to create (Sub or Function) by selecting the Sub or Function option button in the Type group.

For your first practice procedure, choose Sub. You can ignore the rest of the options in the Add Procedure dialog box; the default settings are fine.

The Add Procedure dialog box closes. Your module contains a new procedure with the name that you provided in Step 2.

The two lines of VBA code that are needed to define the new procedure are entered into your module as soon as you click OK. The first line begins with Public Sub or Public Function, followed by the procedure name and a pair of closed parentheses. For example, if you create (in the Add Procedure dialog box) a Sub procedure named mySub, the following VBA lines are added to your module:

Public Sub mySub() End Sub

The Public keyword at the start of each procedure defines the scope of each procedure. Because procedures in a standard module are public by default, they're visible to all objects in the current database. In a standard module, you can omit the Public keyword and just begin the line with the Sub or Function keyword. Either way, the procedure is public (visible to all objects in the database).

In the module, the procedure name always ends in a pair of closed parentheses, as in mySub() or myFunc(). The parentheses are required, so they're added automatically when you click OK in the Add Procedure dialog box. Each procedure ends with an End Sub or End Function statement.

Figure 3-2 shows an example where we used the Add Procedure dialog box (twice) to create a Sub procedure named mySub and a Function procedure named myFunc. The module is visible in the Visual Basic Editor's Code window.

Figure 3-2:

Sub and Function procedures in a standard module.

f VBA Practice - Modulel (Code)

Option Compare Database

Public Sub mySub() End Sub _

Public Function iwyFunc () End Function

-Procedure menu

~mySub() procedure

-myFunc() procedure -Code window

Any code that you type into the procedure must be typed between the two lines that define the procedure. You can easily position the cursor within any procedure by clicking within that procedure. You can also move the cursor into a procedure just by choosing the procedure's name from the Procedure menu in the Code window.

Understanding Syntax

Writing code is the art of programming the computer to perform a specific procedure by defining each step in the procedure as a single VBA statement. For the code to work, every VBA statement must conform to rules of syntax, which define exactly how the code is written. The syntax of a VBA statement is the set of rules that define the exact order of words and where spaces, commas, parentheses, and other punctuation marks are required.

Like any written language, the VBA language consists of words (keywords), punctuation marks (for example, commas), and blank spaces. Keywords are plentiful, and each has its own specific rules of syntax. The syntax rules are so rigid that you'd never be able to figure them out by guessing. You have to know how to get the information you need, when you need it.

The Visual Basic Editor provides several tools to help with syntax. For example, you use the MsgBox() function in VBA to display a custom message onscreen. Imagine that you already know about the MsgBox() function and were about to use it in a program, and you type the following line into a procedure:

As soon as the Visual Basic Editor sees the MsgBox( part, it shows a Quick Info screen tip for the MsgBox keyword, as shown in the example at the top of Figure 3-3. The Quick Info tip is a small syntax chart showing you the rules for using MsgBox correctly. Within the Quick Info, the bold-italic word Prompt means that you're expected to type a prompt next.

For example, you might type "Hello World" (with the quotation marks) and a comma on the line:

x = MsgBox("Hello World",

The comma lets the Visual Basic Editor see that you typed a valid first argument and are now ready to type the second argument. The second argument in the syntax chart ([Buttons As vbMsgBoxStyle = vbOKOnly]) is then boldfaced to indicate that you now should type the second argument. Also, a list of meaningless-looking names (called constants) appears, as shown in the bottom half of Figure 3-3.

Okay, you gotta trust us on this one: The Quick Info and list of constants are there to help. Unfortunately, they're helpful only to those people who've used the MsgBox() function a zillion times and need only brief reminders on syntax and available constants. For someone who's just starting out, more in-depth information is needed. Fortunately, it's always easy to get.

Quick Info

Constants

Quick Info

Constants

Getting keyword help

Whether you're typing your own code or trying to modify someone else's, you can get information on any keyword at any time. Just select (doubleclick) the keyword right in the Code window where it appears. Then press the Help key (F1) on your keyboard. The Help window that opens describes the command and its syntax.

After you type a keyword into a procedure, you can use the Help window to get detailed information. Just select (double-click) the keyword and press the Help key (F1). Using the Help window is also an excellent way to find out more about code other people have written because you can determine what each line of code does.

When you press F1, the Help window that opens describes whatever keyword you selected in your module. For example, if you double-click MsgBox in a procedure (to select it) and then press F1, the Help page for the MsgBox keyword opens, as shown in the example in Figure 3-4.

Selected keyword

Figure 3-4:

Help for the

MsgBox keyword.

Selected keyword

Figure 3-4:

Help for the

MsgBox keyword.

Help for MsgBox keyword

The Help window shows a ton of information about using the MsgBox keyword. The first paragraph describes what the keyword does. Under the heading, the syntax chart shows the same information that the Quick Info screen tip does (namely, the arguments that you can use with the keyboard), as well as the order in which you must use them. For example, the syntax chart for MsgBox looks like this:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The first word (MsgBox, in this example) is the keyword. The text and symbols enclosed in parentheses represent arguments that you can use with the MsgBox keyword. An argument is a piece of information that you give to the keyword to use for something. (More on that in the upcoming section "Help with arguments.") The syntax chart uses square brackets, boldface, and italics as described here:

i Bold: Represents a required keyword.

i Italic or bold italic: Represents an argument.

i [ ]: Indicates that the argument is optional and can be omitted.

Beneath the syntax chart is a description of each argument that the keyword supports. For example, scrolling down a short way through this Help page reveals a description of each of the argument names that MsgBox supports, as shown in Figure 3-5.

Figure 3-5:

Find argument info in a keyword Help window.

t^l Access Help * x ffl '.J Aft , characters, depending on the width of the characters used. If prompt consists of mare than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10>), or carriage return-linefeed character combination (Chr<13) 8Chr(10)) between each line.

Optional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message bon. If omitted, the defauIt value for/HJftows is 0.

Optional. String expression displayed in the title bar of the dialog box. If you omit title, the application name is pi: Optional. String expression that identities the Help file to use to provide context-sensitive Help for the dialog box. canied must also be provided.

title bar. s provided, context Optional. Numeric expression that is the Help context number assigi provided, fie/pfi/e must also be provided.

Settings

The buttons argument settings e appropriate Help topic by the Help author, ifconfexfis

Constant

Description

vbOKOniy

0

Display OK button only.

vtoOKCancel

1

Display OK and Cancel buttons.

vtiAbortRetrytcjnore

2

Display Abort, Retry, and Ignore buttons.

vtiYesNoCancel

3

Display Yes, No, and Cancel buttons.

vtoYesNo

4

Display Yes and No buttons.

vtfRetryCancel

5

Display Retiy and Cancel buttons.

vfaCrilical

16

Display Critical Message icon.

vbQuestion

32

Display Warning Query icon.

vtiExclarnation

48

Display Warning Messaije icon.

Developer Reference

Developer Reference

The description of an argument tells you whether the argument is required or optional. If an argument is required, you must type an acceptable value for that argument into your code (always within the parentheses that follow the keyword). If an argument is optional, you can either type an acceptable value for the argument or just not use the argument.

Never type square brackets into your VBA code: The square brackets in the syntax chart are just there to indicate the optional arguments. If you type the square brackets in your code, the code doesn't work.

The argument acts as a placeholder for some actual value that you'll later pass to the procedure. If you have any experience in using Access expressions, you're familiar with arguments. For example, in the expression Sqr(81), 81 is the value being passed to the Sqr() (square root) function. When executed, the function returns 9 because 9 is the square root of 81.

What constitutes an acceptable value for an argument is usually the second item listed in the Help chart. Typically, it's one of these types of expressions:

¡^ String: Can be literal text enclosed in quotation marks, as in "Hello World", or an expression that results in text.

¡^ Numeric: Can be a number, like 1 or 10, or an expression that results in a number.

That's a lot to try to understand. Take it one step at a time, though, with an example to try to make sense of it all. First, understand that the arguments of a keyword are typed within parentheses, after the keyword. And multiple arguments are always separated by commas. So the most general view of any keyword that accepts three arguments would be keyword (argument1, argument2, argument3)

In other words, you don't start typing the first argument until you've typed the keyword and opening parenthesis. After you type the first argument, you have to type a comma before you start typing the second argument, and so forth. The Visual Basic Editor doesn't know that you're ready to type the next argument until you type that comma. Finally, you have to type the closing parenthesis at the end of the statement. If you mess it up, you get a compile error as soon as you press the Enter key. All you can do is click OK and try again (or delete the whole line and start over).

Getting back to the MsgBox() keyword and its arguments, you can see at the top of the first Help page (refer to Figure 3-4) that MsgBox() is a function that returns a value. Although it's not specifically stated in the syntax, to be able to use the command properly in a procedure, you need to use this syntax:

x = Msgbox(prompt[, buttons ][, title][,helpfile, context])

You can see on the Help page that the prompt argument is required and must be a string expression. So if you want the message box to display Hello World, you would type those words (remember to enclose them in quotation marks) as the first argument, as in the following example. Because the remaining arguments are optional, you could omit them and just end the whole line with a closing parenthesis, like this:

x = MsgBox("Hello World")

The Immediate window, which we discuss in Chapter 2, provides a handy means of testing a VBA statement on the fly to see whether it will work when it's executed in your code. For example, if you type (exactly) x=MsgBox("Hello World") into the Immediate window and press Enter, VBA executes the statement. The result is a message box containing the words Hello World, as shown in Figure 3-6. (You have to click the OK button in the message box to get back to working in the Visual Basic Editor.)

Statement entered in Result of executing the Immediate window the statement

Help with arguments

Refer to Figure 3-5 (of the Help page for the MsgBox keyword) to see the Settings section (below the argument descriptions) that provides some specific info on using the buttons argument. You can use either the constant or the value in the command. For example, if you want the MsgBox statement to show both an OK button and a Cancel button (rather than just an OK button), use either the value 1 or the constant vbOKCancel as the second argument in the MsgBox statement. Arguments are always separated by commas, so the correct syntax is either x = MsgBox("Hello World",!)

or x = MsgBox("Hello World",vbOKCancel)

A constant is a special word in VBA that has been assigned a value that never changes. For example, the constant vbOKOnly is always the same as the value 0. You can use vbOKOnly (which is easier to remember) in place of 0 in a MsgBox statement.

As instructed on the Help page, you can combine values (by using a + sign) in the buttons argument to use multiple options. For example, the vbYesNo setting (value = 4) displays Yes and No buttons in the message box. The vbQuestion setting (value = 3 2) setting displays a question mark icon in the message box. Thus, if you want to display a message box that displays the question Are you there?, a question mark icon, and Yes and No buttons, you can type any of the following statements. (The 3 6 is allowed because the sum of the two settings' values, 4 and 3 2, equals 36.)

x

= MsgBox(

"Are

you

there?'

' ,vbQuestion+vbYesNo)

x

= MsgBox(

"Are

you

there?'

' ,32+4)

x

= MsgBox(

"Are

you

there?'

' ,36)

You can test any of these VBA statements by typing one into the Immediate window and pressing Enter. Because all three statements produce the same result, you see a message box with the prompt Are you there?, a question mark icon, and Yes and No buttons, as shown in Figure 3-7.

Figure 3-7:

Test a MsgBox statement in the Immediate window.

>: = MsgBox ( "Are you thsre , vt'Cuss" icn+vljYssMC j

>: = MsgBox ( "Are you thsre , vt'Cuss" icn+vljYssMC j

Microsoft Office Access

Are you frere?

Yes | No

The third optional argument in the MsgBox keyword, title, allows you to specify a title to display in the dialog box. If you omit that argument in Access, the default title for all Access message boxes — Microsoft Office

Access — appears in the message box. If you include a title (as text in quotation marks), that title replaces the default title. For example, if you test the following command in the Immediate window:

x = MsgBox("Are you there?",vbQuestion+vbYesNo,"Howdy")

the message box opens with the word Howdy, rather than Microsoft Office Access, on its title bar.

jjtîM^ The order of arguments in a VBA statement is critical. For example, the title for a MsgBox must be the third argument in the statement. If you want to use a title argument but not a buttons argument, you have to still include a placeholder comma for the buttons argument and include a similar comma for the title argument, as in the following example:

x = MsgBox("Hello World", ,"Howdy")

In this statement, the first argument (prompt) is "Hello World", and the second argument — which acts as a placeholder for the buttons argument — is empty. Because you omitted the argument, Access uses the default value for that argument, which is vbOKOnly (0). Thus, when the statement executes, the message box appears with only the default OK button. The third argument is "Howdy", which appears on the message box title bar.

About named arguments

Named arguments provide an alternative to putting arguments in a specific order. With named arguments, you can just type the argument name followed by a colon and an equal sign ( :=) and then the value you want for that argument. For example, the following statement is equivalent to x =

MsgBox("Hello World", ,"Howdy"), but it uses argument names rather than commas to specify which argument is receiving which value.

x=MsgBox(prompt:="Hello World", title:="Howdy")

Unfortunately, you can't always easily tell whether a statement supports named arguments. The Quick Info screen tip doesn't provide any clues, and the Help doesn't often show the syntax with the optional names in place. About the only clue you get to whether a statement supports named arguments is from the sentence above the argument descriptions in Help. For example, refer to the Help for the MsgBox function in Figure 3-4: namely, the sentence The MsgBox function syntax has these named arguments, just below the syntax chart for MsgBox(). But because named arguments are entirely optional, you don't have to worry about accidentally excluding them when writing your own code.

Was this article helpful?

0 0

Post a comment