Using Integer Data Type in VBA

There are many data types in the VBA that you can use to store values in a variable and a constant. In this tutorial, we will walk through the Integer Data Type.

What is the Integer Data Type in VBA?

Integer is a data Type in VBA that stores numbers ranging from -32768 to 32767. It can store whole numbers (numbers without decimals). Integer consumes less storage (2 Bytes) than other data types, which store large numbers. So, if you are sure that the number you will store will be in the range, you can use the Integer data type to save the memory and make your code run faster.

How to Declare Integer Data Type in VBA

To declare a variable or a constant with an integer data type, follow the same steps you use to declare a variable with any other data type.

1. First, you need to use the DIM keyword. DIM stands for declare, which means you are declaring a variable.
2. After that, enter the name “myINT” or use any name you want.
3. Now, type the keyword “As”. When you enter As, it shows you a list of data types, methods, and properties.
4. Finally, select “Integer” from the list or type it from the keyboard.

Sub integer_varable()
Dim myINT As Integer
End Sub

In the above code, we have declared myINT variable with the Integer Data type. Now you can store any number in it between -32768 and 32767. Check out the code below:

Sub integer_varable()
Dim myINT As Integer
myINT = 34
Debug.Print myINT
End Sub

You can see by using Debug.Print, in the immediate window, we have the value you assigned to the variable.

Now, there’s one more thing you need to know: when you assign a value to a variable declared as an integer, VBA will round that number. Let’s understand it with the below example:

Sub integer_varable()
Dim myINT As Integer
myINT = 34.23
Debug.Print myINT
End Sub

In this code, the value assigned to the variable is 34.23, and when you print that value in the immediate window, it rounds it down to 34.

If you change that 34.23 to 34.56 in the code, run the code to get the value in the immediate window.

Sub integer_varable()
Dim myINT As Integer
myINT = 34.56
Debug.Print myINT
End Sub

The example below shows that when we run the code to get the variable value in the immediate window, it returns 35.

For all the fractional numbers with a decimal below or equal to .50, VBA will round it down; if the decimal value is .51 or above, VBA will round it up.

Error While using Integer Data Type

As we have learned, you can store numbers in the integer data type from -32768 to 32767. And if you store a number out of this range, you will get an Overflow error.

The best way to handle this error is to thoroughly understand the VBA Data Types you need to use while declaring a variable.

Benefits of using Integer Data Type

• One of the biggest benefits of Integers is it can round numbers. So, if you don’t want to work with decimal numbers, you will always get whole numbers with Integer.
• Integers have a smaller range of numbers to store, so they consume less memory and are easy to understand for anyone who writes the code.

Using Integer Data Type with Constants

Just like variables, you can use Integers with a constant as well. See the example below. We have declared a constant with the name myConstIN and assigned it a value (2000).

Sub integer_varable()
Const myConstIN As Integer = 2000
MsgBox myConstIN
End Sub

In the above code, we have used a message box to get the values from the constant. If there is a number you want to use within the code, and that number is not going to change, it’s better to use a constant.