VBA Integer

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

An integer is a data type in VBA given to any variable to hold integer values. The limitations or the bracket for the number of an integer variable can hold similar in VBA to those of other languages. Using the DIM statement or keyword in VBA, one can define any variable as an integer variable.

Excel VBA Integer

Data types are important in any coding language because all the variable declarations should follow the data type assigned to those variables. We have several data types to work with, and each data type has its advantages and disadvantages associated with it. When declaring variables, it is important to know the particular data type. We dedicate this article to the “Integer” data type in VBA. We will show you the complete picture of the “Integer” data type.

What is the Integer Data Type?

Integers are whole numbers, which could be positive, negative, or zero but not fractional numbers. In the VBA context, “Integer” is a data type we assign to the variables. It is a numerical data type that can hold whole numbers without decimal positions. Integer data type 2 bytes of storage is half the VBA LONGVBA LONGLong is a data type in VBA that is used to store numeric values. We know that integers also store numeric values, but Long differs from integers in that the range for data storage is much larger in the case of long data type.read more data type, i.e., 4 bytes.

VBA Integer

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Integer (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples of Excel VBA Integer Data Type

Below are examples of the VBAExamples Of The VBAHere's a list of the top examples of VBA Macro code in Excel: Print All Sheet Names, Insert Different Color Index in VBA, Insert Worksheets as Much as You want, Insert Blank Row After Every Other Row Highlight Spelling Mistakes.read more Integer Data type.

You can download this VBA Integer Data Type Template here – VBA Integer Data Type Template

Example #1

When we declare a variable, it is necessary to assign a data type, and an integer is one of them, which all the users commonly use based on the requirements.

As we said, an integer can only hold whole numbers, not any fractional numbers. Follow the below steps to see the example of a VBA Integer data type.

Step 1: Declare the variable as Integer.

Code:

Sub Integer_Example()

  Dim k As Integer

End Sub
VBA Integer Example 1

Step 2: Assign the value of 500 to the variable “k.”

Code:

Sub Integer_Example1()

  Dim k As Integer

  k = 500

End Sub
VBA Integer Example 1-1

Step 3: Show the value in the VBA message boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

Code:

Sub Integer_Example1()

  Dim k As Integer

  k = 500

  MsgBox k

End Sub
VBA Integer Example 1-2

When we run the code using the F5 key or manually, we can see 500 in the message box.

VBA Integer Example 1-3

Example #2

Now, we will assign the value -500 to the variable “k.”

Code:

Sub Integer_Example2()

  Dim k As Integer

  k = -500

  MsgBox k

End Sub
VBA Integer Example 2

Run this code manually or press F5. Then, it will also show the value of -500 in the message box.

VBA Integer Example 2-1

Example #3

As we told VBA, the Integer data type can hold only whole numbers, not fraction numbers like 25.655 or 47.145.

However, we will try to assign the fraction number to a VBA Integer data type. For example, look at the below code.

Code:

Sub Integer_Example3()

    Dim k As Integer

    k = 85.456

    MsgBox k

End Sub
Example 3

We have assigned 85.456 to the variable “k.” Next, we will run this VBA codeThis VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more to see the result.

VBA Integer Example 3-1

To look at the roundup integer lets, the value of “k” to 85.58.

Code:

Sub Integer_Example3()

    Dim k As Integer

    k = 85.58

    MsgBox k

End Sub
Example 3-2

When we run this code using the F5 key or manually, it will return 86 because it will round up anything more than 0.5 to the next integer number.

VBA Integer Example 3-3

Limitations of Integer Data Type in Excel VBA

Overflow Error: The Integer data type should work fine if the assigned value is between -32768 and 32767. The moment it crosses the limit on either side, it will cause you an error.

For example, look at the below code.

Code:

Sub Integer_Example4()

    Dim k As Integer

    k = 40000

    MsgBox k

End Sub
Example 4

We have assigned the value of 40000 to the variable “k.”

Since we have complete knowledge of Integer data types, we know it does not work because integer data types cannot hold the value of anything more than 32767.

Let us run the code manually or through the F5 key and see what happens.

VBA Integer Example 4-1

We got the error “Overflow” because the Integer data type cannot hold anything more than 32767 for positive numbers and -32768 for negative numbers.

Type Mismatch Error: Integer data can only hold numerical values between -32768 to 32767. Suppose any number assigned more than these numbers will show an Overflow errorOverflow ErrorVBA Overflow Error or "Run Time Error 6: Overflow" occurs when the user inserts a value that exceeds the capacity of a specific variable's data type. Thus, it is an error that results from the overloading of data beyond the desired data type limit of the variable.read more.

Now, we will try to assign text or string values to it. In the below example code, we have assigned the value “Hello.”

Code:

Sub Integer_Example4()

    Dim k As Integer

    k = "Hello"

    MsgBox k

End Sub
Example 4-2

We will run this code through the run option or manually and see what happens.

VBA Integer Example 4-3

It shows the error as “Type mismatch” because we cannot assign a text value to the variable “integer data type.”

Recommended Articles

This article has been a guide to VBA Integer data type in Excel. Here, we discussed using the VBA Integer data type in Excel, its limitations, and the examples and downloadable Excel template.