Declaring Variables

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

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, Microsoft® Visual Basic® for Applications (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, because 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 must have them. Although it is larger, the Currency data type is faster than the Single data type, because the Currency data type does not 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 will not have to look up the location again.
• Declare object variables as specific types rather than as type Object, so you can take advantage of early binding.

See Also

Optimizing VBA Code | Mathematical Operations | String Operations | Loops