Before variables are used in a procedure, it’s recommended to declare them — that is, tell VBA each variable’s data type. Declaring variables makes macro run faster and use memory more efficiently. The default data type, Variant, causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary.
VBA’s Built-In Data Types
|
Byte |
1 |
0 to 255 |
|
Boolean |
2 |
True or False |
|
Integer |
2 |
–32,768 to 32,767 |
|
Long |
4 |
–2,147,483,648 to 2,147,483,647 |
|
LongLong |
8 |
–9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
|
Single |
4 |
–3.40E38 to –1.40E-45 for negative values; 1.40E-45 to 3.40E38 for positive values |
|
Double |
8 |
–1.79E308 to –4.94E-324 for negative values; 4.94E-324 to 1.79E308 for positive values |
|
Currency |
8 |
–922,337,203,685,477 to 922,337,203,685,477 |
|
Date |
8 |
1/1/0100 to 12/31/9999 |
|
Object |
4 |
Any object reference |
|
String |
1 per character |
Varies |
|
Variant |
Varies |
Varies |
- Static
- Public
- Private
| Scope | How the Variable Is Declared |
| Procedure only | By using Dim or Static inside the procedure that uses the variable |
| Module only | By using Dim or Private before the first Sub or Function statement in the module |
| All procedures in all modules | By using Public before the first Sub or Function statement in any module |
