The Floating-Point Data Types
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.
VBA provides two floating-point data types, Single and Double. The Single data type requires 4 bytes of memory and can store negative values between -3.402823 x 1038 and -1.401298 x 10-45 and positive values between 1.401298 x 10-45 and 3.402823 x 1038. The Double data type requires 8 bytes of memory and can store negative values between -1.79769313486232 x 10308 and -4.94065645841247 x 10-324 and positive values between 4.94065645841247 x 10-324 and 1.79769313486232 x 10308.
The Single and Double data types are very precise—that is, they allow you to specify extremely small or large numbers. However, these data types are not very accurate because they use floating-point mathematics. Floating-point mathematics has an inherent limitation in that it uses binary digits to represent decimals. Not all the numbers within the range available to the Single or Double data type can be represented exactly in binary form, so they are rounded. Also, some numbers can't be represented exactly with any finite number of digits—pi, for example, or the decimal resulting from 1/3.
Because of these limitations to floating-point mathematics, you may encounter rounding errors when you perform operations on floating-point numbers. Compared to the size of the value you're working with, the rounding error will be very small. If you don't require absolute accuracy and can afford relatively small rounding errors, the floating-point data types are ideal for representing very small or very large values. On the other hand, if your values must be accurate—for example, if you're working with money values—you should consider one of the scaled integer data types.