In VBA, there are a few numeric data types that you may use to handle numbers in your applications.
Out of these, Integer and Long deal with signed numbers.
Difference between Integer and Long types
Integer Type | Long Type |
Integer takes two bytes or 16 bits in memory | Long takes four bytes or 32 bits in memory |
The default value of the integer is 0. | The default value of Long is also 0. |
The negative limit that an Integer variable can hold is -32,768. | The negative limit for the Long type is -2,147,483,648 |
The positive possible value is 32,768. | The positive limit is 2,147,483,648 |
Integer provides optimal performance on a 32-bit processor. | |
If your variables are supposed to store less than the maximum positive/negative limits, then use the integer type. | You should use Long types when numbers are too big than the maximum limit of the Integer type. |
An example of declaring and using an integer-type variable
In the example below, we will declare an Integer type variable using the Dim keyword.
We will not assign any value to the Integer variable and display it in a message box. Let us see the output:
Sub int_long_ex() Dim num As Integer MsgBox num End Sub
Output:
So, we got zero i.e. the default value.
Assigning values to Integer variables and multiply
We have three integer-type variables.
Assigned values to first two variables and then multiplied both and assigned its result to third Integer variable:
Sub int_long_ex() Dim num1 As Integer Dim num2 As Integer Dim num3 As Integer num1 = 5 num2 = 100 num3 = num1 * num2 MsgBox "num1 * num2 = " & num3 End Sub
Result:
Assigning a value greater than the Integer limit
As the limit of the Integer is 32,768, we assigned 17,000 to the Integer variable and multiplied it by 2, and assigned the result to another Integer variable.
See what happens:
Sub int_long_ex() Dim num1 As Integer Dim num2 As Integer num1 = 17000 num2 = num1 * 2 MsgBox num2 End Sub
Result:
As result is 34000 which is beyond the Integer limit, so it produced an Overflow error.
Getting the memory size of the Integer variable by Len() function
If you provide a variable to Len() function, it returns its memory size:
Sub int_long_ex() Dim num1 As Integer num1 = 10500 MsgBox "Size of num1 = " & Len(num1) End Sub
Output:
You saw, it displayed 2 which is the memory size of the Integer variable.
An example of Long variable without assigning a value
We declared a variable and just displayed it in a message box to see the default value of Long type:
Sub int_long_ex() Dim lng As Long MsgBox "Default value of Long = " & lng End Sub
Output:
Using Long in multiplication example
Sub int_long_ex() Dim lng1 As Long, lng2 As Long, lng3 As Long lng1 = 500000 lng2 = 10 lng3 = lng1 * lng2 MsgBox "Result after Multiply = " & lng3 End Sub
Output:
Checking the limit of Long variable example
We just assigned one number which is greater than the Long limit to the program below and as executed – it produced the Overflow error:
Sub int_long_ex() Dim lng1 As Long, lng2 As Long lng1 = -2147483648# lng2 = -2147483649# End Sub
Getting memory size of Long variable
Again, by using Len() function, we may also get the size of the long type variable.
Code:
Sub int_long_ex() Dim lng1 As Long lng1 = 350000 MsgBox "Size of Long Variable: " & Len(lng1) End Sub
Result: