Microsoft Office 2000/Visual Basic Programmer's Guide

Declaring Variables

The following points provide suggestions for ways to streamline your memory requirements and speed up performance when you are using variables:

• To conserve memory resources, always declare all your variables with specific data types. When you declare a variable without a specific data type, VBA creates a variable of type Variant, which requires more memory than any of the other data types.
  
  
• Be aware of how much memory each data type requires and what range of values it can store. Always use a smaller data type if possible, except in the case where using a smaller data type will force an implicit conversion. For example, since variables of type Integer are converted to variables of type Long, it makes sense to declare variables that will store integer values as type Long instead of as type Integer.
  
  
• Avoid using floating-point data types unless you really need them. Even though it's larger, the Currency data type is faster than the Single data type because the Currency data type doesn't use the floating-point processor.
  
  
• If you refer to an object more than once within a procedure, create an object variable and assign to it a reference to the object. Because the object variable stores the object's location in memory, VBA won't have to look up the location again.
  
  
• Declare object variables as specific types rather than as type Object so that you can take advantage of early binding.