VBA Integer

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

Excel VBA Integer

Data types are so important in any coding language because all the variable declaration should be followed by the data type assigning to those variables. We have several data types of working with, and each data type has its own advantages and disadvantages associated with it. When we are declaring variables is important to know details about the particular data type. This is the article dedicated 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, and zero but not a fractional number. 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, which is half of 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 datatype i.e., 4 bytes.

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)

Examples of Excel VBA Integer Data Type

Below are the 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.

Example #1

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

As I said, an integer can only hold of 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

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

Code:

Sub Integer_Example1()

  Dim k As Integer

  k = 500

End Sub

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

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

Example #2

Now I will assign the value as -500 to the variable “k.”

Code:

Sub Integer_Example2()

  Dim k As Integer

  k = -500

  MsgBox k

End Sub

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

Example #3

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

However, I 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

I have assigned 85.456 to the variable “k.” I 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 what the result is.

• It has returned the result as 85, even though I have assigned the value of the fraction number. This is because of VBA roundVBA RoundRound function in VBA is a mathematical function that rounds up or down the given number to the specific set of decimal places specified by the user to ease calculation.read more the fraction numbers to the nearest integer.
• All the fraction numbers, which are less than 0.5, will be rounded down to the nearest integer. For an example 2.456 = 2, 45.475 = 45.
• All the fraction numbers, which are greater than 0.5, will be rounded up to the nearest integer. For an example 10.56 = 11, 14.789 = 15.

To have another 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

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

Limitations of Integer Data Type in Excel VBA

Overflow Error: Integer data type should work fine as long as the assigned value is between -32768 to 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

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

Since I have complete knowledge of Integer Data Type for sure, I know it doesn’t work because integer data type cannot hold the value anything more than 32767.

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

Oops !!!

I got the error as “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 I will try to assign text or string values to it.  In the below example code, I have assigned the value as “Hello.”

Code:

Sub Integer_Example4()

    Dim k As Integer

    k = "Hello"

    MsgBox k

End Sub

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

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

Recommended Articles

This has been a guide to VBA Integer Data type in excel. Here we discussed how to use the VBA Integer data type in Excel and its limitations along with the examples and downloadable excel template.

• RoundUp in VBA
• 2 Types of Data Types in VBA
• VBA Function IsEmpty
• How to Use Paste Special in VBA?