Variables and Data type

In this chapter you will learn how to declare a variable and different type of data type in VBA.


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

Options Window in Visual Basic Editor
Options Window in Visual Basic Editor

Tick on Require Variable Declaration check box.

Now if you insert a new module, Option Explicit will be there automatically.

Option Explicit in module
Option Explicit in module

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 SubtypeRequired MemoryRange
Integer2 bytes–32,768 to 32,767
Long Integer4 bytes–2,147,483,648 to 2,147,486,647
Single4 bytes–3402823E38 to –1.401298E–45 or 1.401298E–45 to 3.402823E38
Double8 bytes–1.79769313486232E308 to –4.94065645841247E–324 or 1.79769313486232E308 to 4.94065645841247E–324
Currency8 bytes–922,337,203,477.5808 to 922,337,203,685,477.5807
Date8 bytesJanuary 1, 100 to December 31, 9999
StringString's length1 to 65,400 characters
Object4 bytesAny Access object, ActiveX component or Class object
Boolean2 bytes–1 or 0
Variant16 bytesSame as Double
Byte1 byte0 to 255

Next Chapter>>Comments