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 Type | Memory Allocation | Range of Values |
|---|---|---|
| Byte | 1 byte | 0 to 255 |
| Integer | 2 bytes | -32,768 to 32,767 |
| Long | 4 bytes | -2,147,483,648 to 2,147,483,648 |
| Single | 4 bytes | -3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values. |
| Double | 8 bytes | -1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values. |
| Currency | 8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| Decimal | 12 bytes | +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places) |
Storing Non-Numerical Values
There are other Data Types that store non-numerical values like date, text string, a boolean value, variant or Objects.
| String (fixed Length) | Length of string | 1 to 65,400 characters |
| String (Variable Length) | Length + 10 bytes | 0 to 2 billion characters |
| Boolean | 2 bytes | True or False, 1 or 0, Yes or No |
| Date | 8 bytes | January 1, 100 to December 31, 9999 |
| Object | 4 bytes | Any embedded object |
| Variant(numeric) | 16 bytes | Any value as large as Double |
| Variant(text) | Length+22 bytes | Same as variable-length string |
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.
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.