In this chapter you will learn how to declare a variable and different type of data type in VBA.
Variables
Variables are used to store the information. We can re-assign a value on a variable.
See the below example
Sub Use_Variable()
Dim my_age As Integer
my_age = 30
MsgBox my_age
End Sub
Dim keyword is used to declare a variable. Integer is a data type.
Declaring these variables is not absolutely necessary, but it is recommended. We should get in the habit of declaring variables correctly. Try to keep your variable name properly.
Option Explicit
We can make our variable declaration mandatory by using Option Explicit in the starting of the module.
To add automatically Option Explicit while adding a new module-
Go to Visual Basic Editor Window >>Tools>>Options>>check the Require Variable Declaration
Tick on Require Variable Declaration check box.
Now if you insert a new module, Option Explicit will be there automatically.
Data Type
As in above example we have taken my_age as an Integer. Basically Integer is a data type, which is used for numbers.
Below is the list of data type in VBA.
| Data Type or Subtype | Required Memory | Range |
|---|---|---|
| Integer | 2 bytes | –32,768 to 32,767 |
| Long Integer | 4 bytes | –2,147,483,648 to 2,147,486,647 |
| Single | 4 bytes | –3402823E38 to –1.401298E–45 or 1.401298E–45 to 3.402823E38 |
| Double | 8 bytes | –1.79769313486232E308 to –4.94065645841247E–324 or 1.79769313486232E308 to 4.94065645841247E–324 |
| Currency | 8 bytes | –922,337,203,477.5808 to 922,337,203,685,477.5807 |
| Date | 8 bytes | January 1, 100 to December 31, 9999 |
| String | String's length | 1 to 65,400 characters |
| Object | 4 bytes | Any Access object, ActiveX component or Class object |
| Boolean | 2 bytes | –1 or 0 |
| Variant | 16 bytes | Same as Double |
| Byte | 1 byte | 0 to 255 |