Data Types of Variables in VBA

In the previous blog, we learned about the usage and declaration of variables in VBA Programming. Today we will cover different data types of the variables available in Visual Basic for Applications.

Let us start learning now.

Data Types of Variables in VBA

When we declare a variable, we need to specify the data type of the variable. The data type of a variable defines what type of value the variable can store and how much space would be allocated to it in the main memory of the computer.

For instance, we have data types to store numerical values, dates, currency, text strings as well as boolean numbers. The different data types have different amounts of storage space allocated in the computer’s main memory in bytes. This is so because saving storage space is very important for performance when we want to perform large tasks.

You can also see the usage of different data types of variables through this workbook.

Storing Numbers

When we want to store a numerical value in VBA, there are different types of data types. We use these data types on the basis of the range of value and memory allocation. Range refers to the value within which the variable value must lie.

Data TypeMemory AllocationRange of Values
Byte1 byte0 to 255
Integer2 bytes-32,768 to 32,767
Long4 bytes-2,147,483,648 to 2,147,483,648
Single4 bytes-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
Double8 bytes-1.79769313486232e+308 to -4.94065645841247E-324 for negative values
4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
Currency8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal12 bytes+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places)
Data Types storing Numerical values in VBA

Storing Non-Numerical Values

There are other Data Types that store non-numerical values like date, text string, a boolean value, variant or Objects.

Data Type
Memory Allocation
String (fixed Length)Length of string1 to 65,400 characters
String (Variable Length)Length + 10 bytes0 to 2 billion characters
Boolean2 bytesTrue or False, 1 or 0, Yes or No
Date8 bytesJanuary 1, 100 to December 31, 9999
Object4 bytesAny embedded object
Variant(numeric)16 bytesAny value as large as Double
Variant(text)Length+22 bytesSame as variable-length string
Data Types to store Nonnumerical values in VBA

Example to Use Different Types of Variables

We would now use this Sub Procedure example to use variables of different data types.

Sub Variables_Datatypes()
    Dim name As String
    Dim roll As Integer
    Dim birth As Date                   'declaring variables
    Dim re_appear As Boolean
    Dim marks As Byte
    Dim percentage As Single
    Dim annual_fee As Currency
    name = "Chetna Dua"
    roll = 54
    birth = "10-10-2002"                'assigning values to variables
    re_appear = False
    marks = 65
    percentage = 56.543
    annual_fee = 3000000
    MsgBox "Name:=" & name
    MsgBox "roll:=" & roll
    MsgBox "birth:=" & birth             'printing the assigned values
    MsgBox "marks:=" & marks
    MsgBox "percentage:=" & percentage
    MsgBox "annual fees of student:=" & Format(annual_fee, "$#,##")
End Sub
  • Open an Excel workbook and press the Alt and F11 keys to open the VBA editor.
  • Select the VBA project from the Project explorer.
  • Go to the Insert tab on the ribbon and insert a new module. Paste the above code into the workbook
  • Close the Visual Basic editor.
  • Press the Alt and F8 keys to open the list of available macros. Run the Sub Procedure named Variable_Datatypes from there.
different types of data types available in vba in excel example

As a result. message boxes pop up showing the assigned values of the variables.

You can see that the text string and date are enclosed in double quotes.

Learn more about static variables from here.

This brings us to end.

Thank you for reading.

Leave a Comment