Learn Access Now! Chapter 19 Next Section in Chapter 20 Chapter 21

Chapter 20: Using Visual Basic for Applications

(This is section 1 of 6 in this chapter)

As you work with Access, there may be times when you can't write a macro that's capable of performing the operations you need. In such cases, you can turn to Visual Basic for Applications, a programming language that's built into Access. In short, using Visual Basic for Applications, you specify a list of instructions you want Access to perform.

In the previous two chapters, you have learned a bit about programming; after all, that's what macros effectively let you do. Macros, however, don't give you all the capabilities of a programming language. In this chapter, you will plunge head-first into the world of programming.

By the time you finish this chapter, you will understand the following key concepts:

What Visual Basic for Applications is
How Access uses procedures, functions, and subroutines
The parts of a Visual Basic for Applications program
How to use statements, variables, operators, and functions within your program
How to address database objects in Visual Basic for Applications
How to use the VBA Editor
How to develop a Visual Basic for Applications procedure
How to test your procedures
How to use a procedure from an Access form

Note: This chapter is not a substitute for a good book on programming. There are books available on the market which can help you with either Visual Basic for Applications or Visual Basic (they are very similar). This chapter will provide you with a quick introduction to Access programming using Visual Basic for Applications.

What is Visual Basic for Applications?

Visual Basic for Applications (VBA) is a programming language built right into Access. 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 know how to program in Visual Basic, you will feel immediately comfortable programming in VBA. 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.

These reasons are either a side-effect of using VBA (such as the faster speed) or will be covered to one degree or another in this chapter. 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.

These reasons involve using VBA at a level that this chapter will not cover. They are still valid reasons, however. If you find you need to perform any of these advanced operations, purchase a good programming reference and any other VBA programming information you can find.

Getting Help for VBA

Throughout this book, you have learned how you can use the online Help system. When you program in VBA, you will probably use the Help system quite a bit. This is because of all the rules that you must follow when you use VBA.

As you use the programming section of the online Help system, you may find these guidelines helpful:

Use the Help tool to select the programming statement you need help with.

Use the search feature to find information on a specific topic, function, or statement.

Only use the Help system table of contents if you can't find help any other way.

Learn Access Now! Chapter 19 Next Section in Chapter 20 Chapter 21