Access 2003 VBA Programmer's Reference

8.3. Using Variables

When using variables in your VBA code, there are several things to remember to ensure that your code runs smoothly. Choosing the appropriate data type for each variable is critical, and it's also important to use global variables correctly.

Naming conventions for variables are very important. In Appendix G, "Naming Conventions," the Reddick naming conventions for variables are described. If you get into the habit of consistently naming your variables, your code will be easier to maintain over time, faster to debug, and will look more professional.

8.3.1. Using Appropriate Data Types and Sizes

First, make sure that your variable types will handle the size of data they are expected to store. Many overflow errors occur because an AutoNumber key value from a table was stored in a variable defined as an Integer. This may work fine during testing, because an integer can store numbers with values up to 32,767. Then, when a user starts adding more data, the application breaks on an overflow error.

It's a good idea to define variables with the maximum size that is possible to occur. AutoNumber fields should be stored in variables defined as Long(which is the same as the Long Integer in Access tables). Defining a field as String will allow it to store very long strings, whether they are defined as Text or Memo in a table.

If a variable can possibly contain a Null, then you must define it as a Variant, in which case it will be able to store just about anything that ...