VBA can take care of data types automatically, but it does it at a cost of speed and memory. When your application grows, sometimes you will need to explicitly define the best data type for the particular variable.
The following list shows the grammgcdata types you can use in Excel VBA.
Contents
Boolean
It holds only one of two logical values: True or False. It is often used in logical statements.
| Bytes used | Range | Example |
| 2 | True or False | Dim checkVal As Boolean
checkVal = True |
Byte
You can use this type when you work with small values (e.g. people’s ages).
| Bytes used | Range | Example |
| 1 | 0 to 255 | Dim age As Byte Age = 56 |
Currency
The currency format allows you to store numbers as the decimal fraction that has 15 digits to the left and 4 digits to the right.
| Bytes used | Range | Example 1 |
| 8 | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | Dim money AS Currency
money = 123.456789
result: 123.4568
|
| Example 2 | ||
| Dim money
Dim value As Double
value = 123.456789 money = CCur(value) MsgBox (money)
description: Ccur function converts a value to the currency. |
Date
The date format is useful when you need to perform some kind of operations on dates (e.g. calculate the number of days between two dates). Each date or time need to be placed between two „#” marks.
| Bytes used | Range | Example |
| 8 | 1 January 100 to
31 December 9999 |
Dim today As Date
Const myDate As Date myDate = #12/1/2014#
Dim myTime As Date myTime = #12:45:12# |
Caution
VBA displays your time and date according to your system’s date and time format.
Caution
When you write VBA code you have to remember that you must use the U.S. date formats (e.g. mm/dd/yyyy) even if your system’s time format is set differently.
Decimal
The Dim value as Decimal notation is not correct. You cannot declare a variable to be a Decimal. But you can declare it as Variant first and then use the CDec function to convert it to Decimal.
| Bytes used | Range | Example |
| 12 | Largest possible value: +/-79,228,162,514,264,337,593,543,950,335 Largest/smallest value with 28 decimal places: +/-7.9228162514264337593543950335 Smallest non-zero value: +/-0.0000000000000000000000000001 |
Dim value
value = 123.456789 value = CDec(value)
|
Double
The Double (double-precision floating-point) variables are used to store large floating point numbers.
| Bytes used | Range | Example |
| 8 | -1.79769313486231E+308 to
-4.94065645841247E-324 |
Dim value As Double value = 123.456789 |
Integer
The Integer is used to store whole numbers. Floating point numbers are rounded to the nearest integer.
| Bytes used | Range | Example |
| 2 | -32,768 to 32,767 | Dim value As Integer
value = 3.5 MsgBox(value)
result: 4 |
Long
If you need to store larger numbers and the Integer type is not enough, try using Long.
| Bytes used | Range | Example |
| 4 | -2,147,483,648 to 2,147,483,647 | Dim value As Long
value = 32800 |
LongLong
If you need to store even bigger numbers (for example to show the world population), you can use the LongLong format.
| Bytes used | Range | Example |
| 8 | -2,147,483,648 to 2,147,483,647 | Dim value As LongLong
value = 7000000000# |
NOTICE
If you enter a number that is larger than Long, VBA will add # mark at the end of the number.
Single
Single (single-precision floating-point) is the smallest data type which you can use to store decimal fractions.
| Bytes used | Range | Example |
| 4 | -3.402823E+38 to -1.401298E-45 | Dim value As Single value = 123.456 |
String
Use this data type to store text.
| Bytes used | Range | Example 1 |
| 1 per character | A variable-length string:
up to 2 billion characters A fixed-length string: 1 to 65,526 characters |
Dim myText As String
myText = “Some text”
|
| Example 2 | ||
| Dim myText As String * 6
myText = “Some text” MsgBox(myText)
result: Some t |
Variant
The Variant type is assumed if you don’t declare data type explicitly.
| Bytes used | Range | Example |
| may vary | For negative values:
-1.797693134862315E308 to -4.94066E-324 For positive values: 4.94066E-324 to 1.797693134862315E308 |
Dim myVar1 As Variant
myVar = 123.456
Dim myVar2 myVar2 = “This is simple text” |
