Excel VBA Variable Types
Variables in VBA are used to assign a small amount of memory spaces which is then used to define the script. In the process of declaring variables in VBA, we eventually assign different data types to them. And each data type in VBA has a different role and process of execution.
Variable, as the name says, does not store the fixed values. When we define any variable, we always store the values in different ranges. Although we can start with defining one value to it. But even the small byte of memory can store up large amounts of data. Every variable has its own memory size by which is also their limit of storing the data. To make this happen we have different types of Data Types in VBA. Suppose a data type Integer is used for storing whole numbers integers. But it can only retain the values up to 32768. For the numbers greater than can be stored using data type Long. Below is the syntax of Variable declaration;
Dim (Variable Name) As (Variable Data Type)
Types of Data Types
There are two types of Data Types in VBA:
- Numerical Data Type
- Non-Numerical Data Type
Numerical Data Type
Numerical Data types are used where we need to store the numbers. Numbers can be of any type such as integers, decimal numbers, currency, date and time. And for each of these, we have different data types depending on type of number we want to feed.
- Byte: It has a very small capacity. It can hold values from 0 to 255. This is very rarely used.
- Integer: Integer can hold the values more than a Byte could. But it also has some limitations as Byte. The range of Integer is from -32768 to 32768. Beyond this, it will give an error. If we try to use decimal values in Integers, then it will again convert them into the nearest possible whole number.
- Long : The values or numbers which cannot be held by Integer data type, for those we have a Long data type. This can hold values that are greater than 32768. And the range Long is from -2,147,483,648 to 2,147,483,648.
- Single: Single data type is used for storing the decimal values. But it can only hold the values with two-digit of decimals. The range of Single is from -3.402823E+38 to -1.401298E-45 for negative values and 1.401298E-45 to 3.402823E+38 for positive values.
- Double: Whereas Single can store the values to 2 digits of decimal, there Double data type stores the values more than 2 digits of decimals. Same as other data types, Double is also having the range from -1.79769313486232e+308 to -4.94065645841247E-324 for negative values and 4.94065645841247E-324 to 1.79769313486232e+308 for positive values, up to 14 decimal places.
- Decimal: Decimal can hold the values up to 28 digits which is way more than that Single and Double data types. So the size of Decimal is also greater than the rest of the variables. The range of Decimal data type is from +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335.
- Date: It is used for data type values.
- Currency: Storing range of Currency data type is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
Non-Numerical Data Type
This considers the values which are not taken and considered by Numerical data types. Such as Boolean, Text, Alphabets.
- String: It is used for text. It has 2 types, Fixed and Variable-length.
- Boolean: When we need to get the answer as TRUE or FALSE then this logical data type is used.
- Object: Object variables such as Worksheets, Workbooks, Sheets, Range which are there in Excel comes under it. Other Objects are MS Word, PowerPoint, and Outlook.
- Variant: It is used for both numerical and non-numerical values.
How to Declare Types of Variable in Excel VBA?
We will learn how to declare different types of variable in Excel by using the VBA Code.
VBA Variable Types – Example #1
We will first see the very basic example of Variable and data types. For this, follow the below steps:
4.7 (2,676 ratings)
Step 1: For this, we need to open a module from the Insert menu as shown below.
Step 2: Write the subprocedure of VBA Variable. Or we can choose any name to define our code.
Sub VBA_Variable() End Sub
Step 3: Define a variable with any name, let say “A” and if we want to use text then we could use popular data type String as shown below.
Sub VBA_Variable() Dim A As String End Sub
Step 4: Let’s assign any text to variable A. Let it be Testing.
Sub VBA_Variable() Dim A As String A = "Testing" End Sub
Step 5: And to get the output, we would use MsgBox as shown below.
Sub VBA_Variable() Dim A As String A = "Testing" MsgBox A End Sub
Step 6: Now compile the code and run by clicking on the Play button Or F5 function key. We will get the message box with output Testing.
VBA Variable Types – Example #2
In a similar way, we would apply another data type Integer. Follow the below steps to use Excel VBA Variable Types.
Step 1: We would use the same code which we have seen above and use Integer in place of String.
Sub VBA_Variable1() Dim A As Integer MsgBox A End Sub
Step 2: Assign a number to variable A. Let say it be 10.
Sub VBA_Variable1() Dim A As Integer A = 10 MsgBox A End Sub
Step 3: Run the code by pressing the F5 key or by clicking on the Play button, we would get the message box with value 10.
Step 4: Let’ try to change the value from 10 to some higher number, such as 50000 in variable A.
Sub VBA_Variable1() Dim A As Integer A = 50000 MsgBox A End Sub
Step 5: Now again if we try to run this code, we would get the error with the message as Run-time error Overflow.
This means we have exceeded the capacity limit of data type Integer.
Pros of Excel VBA Variable Type
- With the help of variables, we can write any structured VBA Code.
- Variables help us to use different types of data types as per our needs.
- Each variable has its own capacity limit and property of the application.
Things to Remember
- Always define a variable using the DIM word.
- Make sure that it is fixed for which type of variable we need to define so as per that required data type can also be selected.
- If we assign the value more than the capacity of any data type, then we will end up getting the error message of OVERFLOW as we got in example-2.
This is a guide to VBA Variable Types. Here we discuss how to declare different types of Variable in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –