VBA Integer

Excel VBA Integer Data Type

In mathematics, Integers are the numbers which are complete as a whole. They do not contain any decimal values. Numbers such as 1, 10, 11, 234, etc. are the whole number called as Integers. The same concept of Integers is used in any programming language. In most of the programming language, Integers contains numbers or set of numbers which are complete whole numbers. Integers can be positive or negatives. But number with decimal digits are not integers. They are considered are Double in VBA coding.

How to Use VBA Integer Data Type in Excel?

Below are the examples to use VBA Integer Data Type in Excel.

VBA Integer – Example #1

Let’s see a very simple example of VBA Integer.

Follow the below steps to use VBA Integer data type in Excel.

Step 1: Go to the VBA window and open a new module by selecting Module from the Insert menu tab as shown below.

Step 2: After that, we will get a white blank page of Module. In that, write Subcategory for VBA integer or in any other name.

Code:

Sub VBAInteger1()

End Sub

Step 3: Now use dimension DIM and assign it a name. It can be any letter or word. Here we are using “A” for it.

Code:

Sub VBAInteger1()

  Dim A

End Sub

Step 4: After that assign the Integer function to it as shown below.

Code:

Sub VBAInteger1()

  Dim A As Integer

End Sub

Step 5: Now DIM A can only store numbers in it. After that we can assign any numerical value to A. Here we are giving 10 to A.

Code:

Sub VBAInteger1()

  Dim A As Integer

  A = 10

End Sub

Step 6: This completes the assigning of a number to defined dimension A. Now we need to see this value somewhere so we will use the message box to print the value assign to Integer A as shown below.

Code:

Sub VBAInteger1()

  Dim A As Integer

  A = 10

  MsgBox A

End Sub

Step 7: Once done, compile and run the complete code by clicking on the play button which is located just below to the menu bar as shown below.

And then we will get a message box which has number 10, which was our assign integer value to dimension A.

VBA Integer – Example #2

In another example of VBA Integer, we will see if the concept and logic of Integers are still true about negative numbers. To demonstrate this, follow the below steps to use VBA Integer data type in Excel.

Step 1: Open a module in VBA and give it Subcategory in the name of VBA Integer or any other name as per own choice. We are giving a sequence to it.

Code:

Sub VBAInteger2()

End Sub

Step 2: Now in a similar way, define a dimension DIM with any name, let’s says “A”.

Code:

Sub VBAInteger2()

  Dim A

End Sub

Step 3: And now assign the dimension A as Integer as shown below.

Code:

Sub VBAInteger2()

  Dim A As Integer

End Sub

Step 4: Now assign a negative value of 10 or any other number to A.

Code:

Sub VBAInteger2()

  Dim A As Integer

  A = -10

End Sub

Step 5: To get this value, we will use a message box to print it as a pop-up.

Code:

Sub VBAInteger2()

  Dim A As Integer

  A = -10

  MsgBox A

End Sub

Step 6: Now compile the code if there is any error or not. Then run. We will see as per definition, Integer can store value in negative as well.

VBA Integer – Example #3

We have also discussed that in VBA Integer decimal digits are not considered. Let’s see if this is applicable in reality too or not.

Follow the below steps to use VBA Integer data type in Excel.

Step 1: For this open a new module in VBA and start writing subcategory of VBA Integer in it. Here give it a proper sequence as well as shown below.

Code:

Sub VBAInteger3()

End Sub

Step 2: Again define and choose a DIM dimension as any alphabet as per your choice. We consider the same alphabet A as taken in the above examples as well.

Code:

Sub VBAInteger3()

  Dim A

End Sub

Step 3: Now assign the Integer function to Dim A.

Code:

Sub VBAInteger3()

  Dim A As Integer

End Sub

Step 4: Now assign the selected dimension “A” a decimal values. We have assigned it 10.123 as shown below.

Code:

Sub VBAInteger3()

  Dim A As Integer

  A = 10.123

End Sub

Step 5: Now select a message box for A to see the value stored in dimension A.

Code:

Sub VBAInteger3()

  Dim A As Integer

  A = 10.123

  MsgBox A

End Sub

Step 6: Now compile and run the written code. We will see Integer function as returned the values as the whole number and decimal digits are being ignored if Integer is used.

If instead of Integer, we use Double function then we would get the complete decimal values.

VBA Integer – Example #4

We have seen whole numbers, negative number and decimal number with Integers. Integer function in VBA has the limit of storing the data numbers. We can store any number in Integer but there are some constraints of choosing the length of numbers. To demonstrate, Follow the below steps to use VBA Integer data type in Excel.

Step 1: Insert a new module in VBA and give it a Subcategory with the name of VBA Integer or any other name.

Code:

Sub VBAInteger4()

End Sub

Step 2: Now use DIM for defining any dimension. Let’s consider the same alphabet used in the above examples as assign Integer function to it as shown below.

Code:

Sub VBAInteger4()

  Dim A As Integer

End Sub

Step 3: Now let’s assign a numeric value to Integer A have 6-8 digit. Here we are assigning 1012312 number as shown below.

Code:

Sub VBAInteger4()

  Dim A As Integer

  A = 1012312

End Sub

Step 4: And give Integer A a message box so that we will see the outcome to value stored.

Code:

Sub VBAInteger4()

  Dim A As Integer

  A = 1012312

  MsgBox A

End Sub

Step 5: Now compile and run the above code.

Here we got an error message which says “Run-time error 6 – Overflow” which means that numeric value of 7 digits which we entered has crossed the limit of storage.

VBA Integer is 16bit size file which can only store values from –32768 to +32768. Beyond this, it will show the error as shown above.

VBA Integer – Example #5

We have seen all type of numbers in Integers. Now let’s consider what happens when we store any text or alphabet in Integer.

Follow the below steps to use VBA Integer data type in Excel.

Step 1: For this open a module and enter subcategory, if possible in sequence as shown below.

Code:

Sub VBAInteger5()

End Sub

Step 2: Now define a dimension DIM as A and assign it with Integer.

Code:

Sub VBAInteger5()

  Dim A As Integer

End Sub

Step 3: And now in defined Integer A, assign a text. Here we have assigned it “VBA Integer” along with message box.

Code:

Sub VBAInteger5()

  Dim A As Integer

  A = "VBA Integer"

  MsgBox A

End Sub

Step 4: Now run the code. We will get “Run-time error 13 – Type Mismatch” error which means used function and its value don’t match.

Pros of Excel VBA Integer

• We can use any type of number with an Integer data type.
• Keeping the limit of numbers will give a positive outcome using Integer data type.

Things to Remember

• Integers cannot be used for texts and decimal numbers.
• For numbers beyond the limit of –32768 to +32768 use a LONG function instead of Integers.
• Use Double function for decimal values.
• No need to compile data step-by-step if your code is small.
• Save the file in the Macro Enable format to avoid losing written code.

Recommended Articles

This has been a guide to Excel VBA Integer. Here we have discussed how to use VBA Integer data types in Excel along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA IsNumeric
2. VBA RGB
3. VBA String
4. VBA XML