VBA Data Type

What Are VBA Data Types?

VBA data types show the variable’s compiler storage size. Data types are built-in VBA. In VBA, we often need to use variables to store various kinds of data. Before we store a value to any variable, we need to be sure of what kind of value a variable can hold.

The kind of data a variable can hold depends on the data type assigned to the variable while declaring the variable. For example, suppose you want the variable to hold integer numbers. In that case, we can assign the data type for the variable as “Integer” and similarly, if we want the variable to hold text value, then we can assign the “String” data type.

We will see the various kinds of data types available in VBA now.

Types Of Data Types In VBA

In VBA, we have many kinds of variables. Now, we have divided them into two parts i.e., Numerical Data Types and Non-Numerical Data Types for better understanding,.

#1 – Numerical Data Types

This category contains 7 types of data types that can hold various kinds of numerical data. The following are the numerical data types.

• Byte – A byte variable data type can hold values from 0 to 255 only, hence taking only one byte of the memory. No negative values are allowed to assign to this data type and positive numbers that are greater than 255 are also not allowed to be assigned. The following is the example code for assigning the byte data type.
  • Dim X as Byte
• Integer – This data type allows us to assign both positive and negative numbers ranging from -32768 to 32767. Even zero can be assigned to this data type. The memory consumed for this data type is 2 types. The following is the example code for assigning the Integer data type.
  • Dim X as Integer
• Long (Longer Integer) – This is the advanced data type compared to the integer data type and hence consumes more memory than the integer data type i.e., 4 bytes.  This data type can hold values from -2,147,483,648 to 2,147,483,648.
  • Dim X as Long
• Decimal – This is the numerical data type used for its precision. The total number of digits to the right of the decimal is 28, and it is called as the scaling factor. This data type consumes 14 bytes of memory.
  • Dim X as Decimal
• Single – This data type is used to store numerical values with a single precision floating point. This can hold two kinds of values, and those are:
  • 3.402823E38 to -1.401298E-45 for negative values.
  • 1.401298E-45 to 3.402823E38 for positive values.

This data type takes 4 bytes of memory. We can assign the data type to the variable in two ways:

• Dim X as Single

OR

•  Dim X!

As we can see we have added the exclamation (!) mark after the variable name “X” and it denotes the data type “Single”.

• Double – This data type is used to store numerical values with a double-precision floating point. This can hold two kinds of values such as:
  • -1.79769313486231E308 to -4.94065645841247E-324 for negative values.
  •  4.94065645841247E-324 to 1.79769313486232E308 for positive values.

This data type takes 8 bytes of memory. We can assign the data type to the variable in two ways:

• Dim X as Double

OR

•  Dim X#

As we can see, we have added the Hash (#) symbol after the variable name “X,” and it denotes the data type “Double”.

• Currency – This data type is used to store 15-digit numbers to the right and 4 digits after the decimal point. This data type accepts both positive and negative numbers. This consumes 8 bytes of memory.
  • -922,337,203,685,477.5808 to 922,337,203,685,477.5807.

Now, we can declare the variable in two ways:

• – Dim X as Currency

OR

•  Dim X@

Here, we have used at (@) symbol after the variable name to indicate it as a Currency data type.

#2 – Non-Numerical Data Types

This category also contains 7 different types of non-numerical values. These comprise Text, Date, String, etc.

• Boolean – This data type holds only logical values i.e., TRUE or FALSE. Here, instead of TRUE, we can give 1, and instead of FALSE, we can give 0. This data type takes 2 bytes of memory.

• Date – This data type is used to assign date and time values to the variables. Remember, the date values range from Jan 01, 100 to December 31, 9999, and time values range from 00:00:00 to 23:59:59.
  • Dim X as Date

• String (Fixed Length) – This data type is used to store a sequence of characters. This sequence can be of text, numbers, special characters, etc.… But the string length is limited to the range of 1 to approximately 65,400 characters.

• String (Variable Length) – This data type is used to store a string value in the range of 0 to 2 billion. Meanwhile, this takes 10 bytes of memory.
  • Note: The string value should be given in double quotes only.
  • Dim X as String

• Object – This data type takes the object references like worksheets, workbooks, cells, ranges, charts, etc.… The following code is the worksheet object data type.
  • Dim X as Worksheet

• Variant – This is a universal data type. Also, we can use it when we are not sure of what kind of data our variable will be holding. This data type takes more memory storage space than any other data type discussed so far. Meanwhile, when we do not assign a data type it takes variant as the data type by default.
  • Dim X as Variant
  • Dim X

Declaring Variable Data Types In VBA

In VBA, there are two ways to declare a variable i.e., Implicitly, and Explicitly.

• Implicitly: In this method, we need not have to define variables and assign a data type to them. Now, we simply enter the variable name and assign any value to it. For example, look at the following code.

Next, for the variable “Myvalue”, we have assigned the value “Hi”. Likewise, we can assign any value to it because we have not limited the value assigned to any one data type.

So, the implicit way of declaring a variable is nothing but no declaration and no data type will be applied to the variable.

• Explicitly: In this method, before we assign a value to the variable, first, we define and assign a data type to this variable. For example, look at the following code.

Now, we have defined the variable using the “Dim” keyword and then assigned the data type “Date” to it. This means we can store only date values to this variable unlike in the previous methods where we could have assigned any type of value.

However, if we assign any value apart from the date, we will get the following error.

Now, we have assigned the value “Hi” to the variable; hence, we are getting the error “Type mismatch”.

Making Variable Declaration Mandatory (Option Explicit)

Option Explicit in VBA is the statement we can use at the top of the module to make the variable declaration mandatory i.e., forcing the coder to declare the variable explicitly, so no implicit way of a variable declaration is allowed.

Now, to get the “Option Explicit” statement by default, we need to change the default settings in the VBA window.

First, go to the Tools bar and then, click on Options.

Now, this will bring the following Options window.

Next, check the Require Variable Declaration box.

Then, click on OK to close the Options window.

This will add the line Option Explicit at the top, and we have to write the codes after that line only, and we have to declare the variable mandatorily now.

Note: This will not add Option Explicit to the existing module. Only new modules will have this statement. All the existing modules need to be added manually.

For example, let us not declare the variable explicitly and try to assign the value to the variable implicitly.

In the above code, we have not defined the variable “Myvalue” explicitly but assigned value to it, and we have the statement “Option Explicit” at the top.

While assigning the value to the variable, we will not get this error, but at the time of execution we get the “Compile Error” stating “Variable not defined”.

Declaring Constants In Excel VBA

In VBA, we can declare the variable as constant by using the word “Const”. Using the constant, we can fix the value of the variable, and we cannot really change the value of the variable during the code.

For example, look at the following VBA code.

1. We have used the word Const to declare the variable instead of the word Dim.
2. We have given the name to the variable.
3. We have assigned the data type Date to the variable.
4. We have entered the equal sign to assign a value to the variable.
5. We have assigned the value to the variable based on the data type assigned.

Once the value is assigned to the variable, we cannot change the value of the variable throughout the code.

Examples

Example #1 – Overflow Error With Data Type

As we have learned so far, we have many data types, and according to the requirement, we can assign the data types to the variables. However, when the data type and assigned values are not correct, we will end up getting various kinds of errors, and one such error is Overflow Error.

For instance, look at the following code.

Sub Example1()

Dim k As Byte

k = 300

MsgBox k

End Sub

Next, we have assigned the data type Byte to the variable k. Now, for this variable, we have stored the value 300.

Now, let’s run the code and see what happens.

Clearly, we have got the Overflow error.

This is because, the data type Byte can hold values only from 0 to 255, and the stored value is 300 which is beyond the capacity of the byte data type, hence getting the overflow error.

Example #2 – Boolean Data Type

As we know, the Boolean data type is used to store either TRUE (1) or FALSE (0). Now, let us look at the following code to understand more about the Boolean data type.

Sub Example2()

Dim k As Boolean

k = 20

MsgBox k

End Sub

Now, let’s run this code and see what happens.

Clearly, we can see the result as TRUE.

Meanwhile, the reason for this is in Boolean data types; anything more than 0 is treated as TRUE, and 0 is treated as FALSE.

Example #3 – Object Data Type

Object data types are “Worksheets, Workbooks, Charts, Cells, etc.…”. Now, we can use these object variables to reference that object.

For example, look at the following code.

Sub Example3()

Dim Ws As Worksheet

Set Ws = Worksheets("Basic")

Ws.Select

End Sub

Now, we have defined the variable “Ws” and assigned the object data type “Worksheet” to it.

Next, whenever we use the object variable, we need to use the “Set” keyword to assign an object to it.

Then, by using the “Set” keyword, we have assigned the worksheet “Basic” to it.

Now, whenever we want to do something with the worksheet “Basic,” we can simply use this variable and perform actions.

Here, we have written the code “Ws.Select” to select the worksheet “Basic”.

Important Things To Note

• We need to define the variable using the keyword “Dim” and “Dim” stands for “Dimension”.
• The option explicit statement should be at the top of the module, and after this statement, only the codes should follow.
• Existing modules will not get the option explicit statement as soon as we make changes, so need to add the word “Option Explicit” statement at the top manually.
• Object variables require the “Set” keyword to assign object values to the variables.
• Remember, we cannot use special characters while naming the variable.
• Also, the first character of the variable name should be an alphabet.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA Data Type. Here we explain Excel VBA data types, their types, how to declare variables & constants, along with examples. You can learn more from the following articles –

• VBA Boolean
• VBA Arrays Function in Excel
• VBA Input Box