VBA Data Types
You might have defined different variable during your VBA journey till now. Have you ever given a thought on the data types those variables have? A Data Type of a variable is something that tells your system about the type of data you store with the help of some variables. It’s a way to let your compiler know how the data is being stored and what is the size it should be of (Different data types has different storing capacity in their own). This seems to be the most basic part you may come up with. But believe me, it’s equally important to have a hands on. Moreover, it’s easy to grasp as well.
Data Type = Type of your data.
There are roughly more than 15 data types present under VBA. However, in this article, we will be discussing the most commonly used data types (almost surely used more than 90% of the time).
Please see below table where you will get the list of data types which are widely used and are always good to have as a reference:
We will see step by step, how each of these data types gets used in defining a variable and storing data.
When you define a variable, you actually are reserving some memory in your computer to be assigned to some specific data type with a name. In that way, a variable can be considered as a token/ticket/voucher which can be redeemed while a value is actually being assigned to the variable.
As in most conventional languages like C, C++, you have to declare a variable before you assign a value of the specific type to it. It can be done as below:
Dim VariableName As DataType
Here, Dim stands for a dimension of the variable.
Let’s see how different data types are being stored using variables.
How to Use Excel VBA Data Types?
We will learn how to use VBA Data Types with a few examples in Excel.
Example #1 – VBA String Data Type
A string is the data types which can be stored as texts in the computer’s memory.
Follow the below steps to use String data type in Excel VBA.
Step 1: Open a new module and insert sub-procedure to access the macro.
Sub Ex1() End Sub
Step 2: Declare a new variable with String as a data type so that it can hold a string value in it.
Sub Ex1() Dim stringVar As String End Sub
Step 3: Use assignment operator to assign a text “Hello VBA Programmer!” to a variable named “stringVar”.
Sub Ex1() Dim stringVar As String stringVar = "Hello VBA Programmer!" End Sub
Step 4: Use MsgBox function from VBA to be able to see the output of this macro code.
Sub Ex1() Dim stringVar As String stringVar = "Hello VBA Programmer!" MsgBox stringVar End Sub
Step 5: Hit F5 or Run button under VBE to run this code and see the output.
Example #2 – VBA Boolean Data Type
Boolean data type consists either True or False as values. It can be mostly used to check whether certain logical conditions are meeting or not.
Follow the below steps to use Boolean data type in Excel VBA.
Step 1: Define a new variable as Boolean under new sub-procedure in a module.
Sub Ex2() Dim boolVar As Boolean End Sub
Step 2: Assign a value either True or False (True = -1 and False = 0 can also be used instead) to variable “boolVar”.
Sub Ex2() Dim boolVar As Boolean boolVar = False End Sub
Step 3: Use If-Then… Else loop to decide what happens when boolVar = True and False.
Sub Ex2() Dim boolVar As Boolean boolVar = False If boolVar = True Then Sheets("Data_Type").Range("A1") = "Bulls Eye! You Rock" Else Sheets("Data_Type").Range("A1") = "Sorry Mate!" End If End Sub
Here in this loop, if boolVar = True, the value that should get printed in cell A1 of sheet Data_Type is “Bulls Eye! You Rock”. If boolVar =False, the value that should get printed is “Sorry Mate!”
Now, logically speaking, what do you think will get printed under cell A1?
You probably have guessed correctly. The text gets printed will be “Sorry Mate!”.
Step 4: Run this code and see the output.
Example #3 – VBA Integer Data Type
An integer data type can store numbers as a whole. If you provide any number with fraction in it, it gets rounded to the nearest integer.
Follow the below steps to use Integer data type in Excel VBA.
Step 1: Define a new variable with data type as an integer so that it can store integer value under a new sub-procedure.
Sub Ex3() Dim intVar As Integer End Sub
Step 2: Assign value as 5.7 to variable named “intVar” using assignment operator.
Sub Ex3() Dim intVar As Integer intVar = 5.7 End Sub
Step 3: Use VBA MsgBox function to see the value of variable “intVar” as a message box.
Sub Ex3() Dim intVar As Integer intVar = 5.7 MsgBox intVar End Sub
Step 4: Now, run this code using F5 or Run button simultaneously to see the output. You can see an output message box as below.
Hold on a sec! have you noticed something fishy here in output?
You have assigned 5.7 as a value to variable “intVar”. How does it print 6 under the message box? It happened due to the data type of the variable you defined. The integer data type can only hold whole numbers. As then number assigned is not a whole number, it has been rounded to the nearest integer (i.e. 6).
Example #4 – VBA Double Data Type
The above example leads to the inclusion of data type double under VBA. This data type stores any number with fractions as well as the whole number as a value.
Follow the below steps to use Double data type in Excel VBA.
Step 1: Declare a variable as double under new sub-procedure.
Sub Ex4() Dim doubVar As Double End Sub
Step 2: Assign a value to the variable “doubVar” using an assignment operator.
Sub Ex4() Dim doubVar As Double doubVar = 3.7 End Sub
Step 3: Use VBA MsgBox function to be able to see the output as a message box in VBA.
Sub Ex4() Dim doubVar As Double doubVar = 3.7 MsgBox doubVar End Sub
Step 4: Run this code by hitting F5 or Run button and see the output.
Not surprisingly, a double variable can store an integer value as well. Because in the backend that value will automatically be stored as double. Ex. If you assign 6 as a value to variable doubVar, it will get stored as 6.0 in backend due to a variable data type as double.
Example #5 – VBA Date Data Type
The date is a data type which can store any date value within the range 01 January 100 12:00:00 AM to 31 December 9999 11:59:59 PM.
Follow the below steps to use Date data type in Excel VBA.
Step 1: Define a variable with data type as a date in a new sub-procedure.
Sub Ex5() Dim dateVar As Date End Sub
Step 2: Assign a date value to this variable using a combination of assignment operator and VBA NOW () function.
Sub Ex5() Dim dateVar As Date dateVar = Now() End Sub
Step 3: Use MsgBox to be able to see this date value as a message.
Sub Ex5() Dim dateVar As Date dateVar = Now() MsgBox "Today's date is: " & dateVar End Sub
Step 4: Hit F5 or Run button to see the output of this code.
These are the data types which 9 out of 10 times you will be using while working in VBA. Let’s wrap the things up with some points to be remembered.
Things to Remember
- If you assign any numeric/Boolean/Date value to a variable with data type as string, it will automatically convert that value into string (thanks to type conversion functions available under VBA) and store under that variable. However, vice versa is not always possible. You can’t assign any string value to a variable with an integer or double data type.
- The default value for a string is empty string “”.
- Default value for Integer and double is zero.
- When numeric date values are converted into dates, values left to the decimal points represent dates and values to the right of decimal point represent time.
- Negative whole numbers represent dates before 30 December 1899.
This has been a guide to VBA Data Types. Here we discussed Top 5 different Data Types (i.e. String, Boolean, Integer, Double, Date) in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –