Variable Number Types in VBA

VBA is the programming language that enables a person to customize Microsoft Office products to suit specific and individual needs. Visual Basic for Applications comes standard within the Microsoft Office Suite of products. It's used in Excel to create graphs or perform repeat calculations, and is used in Word, PowerPoint and others to customize formatting or prompt users before performing an action. Within the VBA language, number types include bytes, singles, doubles, integers, currency and dates.

1. Bytes, Singles and Doubles

   • A byte Is a positive number that ranges from 0 to 255. When working with applications where you know any calculations will not result in any number larger than 255, declaring your number variable type as a byte will be OK. Bytes represent 1 space for each number in memory. Singles represent 2 storage spaces in memory and have a range from approximately -3.4 to -1.4 and from 1.4 to 3.4. If you know the results of your calculations or items you are referencing will not fall outside of these ranges, then use the single number type. Doubles hold longer decimal points within memory and range approximately from -1.79 to -4.9 or from 1.79 to 4.9.

   Integer

   • The integer number type represents 2 spaces in memory and will hold whole numbers ranging from -32,767 to 32,767. This number type is helpful for rounding off calculations or creating forms that only are in need of whole numbers within this specified range.

   Currency

   • The currency number type is used when performing calculations involving money or calculations where there is a need for specified accuracy. The range on this number type is approximately from -900 trillion to 900 trillion. Using the currency number type has a wider range than the rest of the number types and will prevent accidental errors in coding by allowing enough room for the calculation result to display and to be stored.

   Date

   • The date number types represents 8 spaces in memory storage. It stores dates and times as a "real" number in the format of "DATE.TIME." The way the date format is displayed can also be customized to display "dd/mm/year" or "dd/mm/time" and others.