Why Use Integer Instead of Long?
Solution 1
Integer variables are stored as 16-bit (2-byte) numbers
Long (long integer) variables are stored as signed 32-bit (4-byte) numbers
So, the benefit is in reduced memory space. An Integer takes up half the memory that a Long does. Now, we are talking about 2 bytes, so it's not going to make a real difference for individual integers, it's only a concern when you are dealing with TONS of integers (e.g large arrays) and memory usage is critical.
BUT on a 32 bit system, the halved memory usage comes at a performance cost. When the processor actually performs some computation with a 16 bit integer (e.g. incrementing a loop counter), the value silently gets converted to a temporary Long without the benefit of the larger range of numbers to work with. Overflows still happen, and the register that the processor uses to store the values for the calculation will take the same amount of memory (32 bits) either way. Performance may even be hurt because the datatype has to be converted (at a very low level).
Not the reference I was looking for but....
My understanding is that the underlying VB engine converts integers to long even if its declared as an integer. Therefore a slight speed decrease can be noted. I have believed this for some time and perhaps thats also why the above statement was made, I didnt ask for reasoning.
This is the reference I was looking for.
Short answer, in 32-bit systems 2 byte integers are converted to 4 byte Longs. There really is no other way so that respective bits correctly line up for any form of processing. Consider the following
MsgBox Hex(-1) = Hex(65535) ' = True
Obviously -1 does not equal 65535 yet the computer is returning the correct answer, namely "FFFF" = "FFFF"
However had we coerced the -1 to a long first we would have got the right answer (the 65535 being greater than 32k is automatically a long)
MsgBox Hex(-1&) = Hex(65535) ' = False
"FFFFFFFF" = "FFFF"
Generally there is no point in VBA to declare "As Integer" in modern systems, except perhaps for some legacy API's that expect to receive an Integer.
And at long last I found the msdn documentation I was really truly looking for.
Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. In recent versions, however, VBA converts all integer values to type Long, even if they're declared as type Integer. So there's no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them.
To clarify based on the comments: Integers still require less memory to store - a large array of integers will need significantly less RAM than an Long array with the same dimensions. But because the processor needs to work with 32 bit chunks of memory, VBA converts Integers to Longs temporarily when it performs calculations
So, in summary, there's almost no good reason to use an Integer
type these days. Unless you need to Interop with an old API call that expects a 16 bit int, or you are working with large arrays of small integers and memory is at a premium.
One thing worth pointing out is that some old API functions may be expecting parameters that are 16-bit (2-byte) Integers and if you are on a 32 bit and trying to pass an Integer (that is already a 4-byte long) by reference it will not work due to difference in length of bytes.
Thanks to Vba4All for pointing that out.
Solution 2
As noted in other answers, the real difference between int and long is the size of its memory space and therefore the size of the number it can hold.
here is the full documentation on these datatypes http://msdn.microsoft.com/en-us/library/office/ms474284(v=office.14).aspx
an Integer is 16 bits and can represent a value between -32,768 and 32,767
a Long is 32 bits and can represent -2,147,483,648 to 2,147,483,647
and there is a LongLong which is 64 bits and can handle like 9 pentilion
One of the most important things to remember on this is that datatypes differ by both language and operating system / platform. In your world of VBA a long is 32 bits, but in c# on a 64 bit processor a long is 64 bits. This can introduce significant confusion.
Although VBA does not have support for it, when you move to any other language in .net or java or other, I much prefer to use the system datatypes of int16, int32 and int64 which allows me to b much more transparent about the values that can be held in these datatypes.
Solution 3
Even though this post is four years old, I was curious about this and ran some tests. The most important thing to note is that a coder should ALWAYS declare a variable as SOMETHING. Undeclared variables clearly performed the worst (undeclared are technically Variant
)
Long
did perform the fastest, so I have to think that Microsoft's recommendation to always use Long
instead of Integer
makes sense. I'm guessing the same as true with Byte
, but most coders don't use this.
RESULTS ON 64 BIT WINDOWS 10 LAPTOP
Code Used:
Sub VariableOlymics()
'Run this macro as many times as you'd like, with an activesheet ready for data
'in cells B2 to D6
Dim beginTIME As Double, trials As Long, i As Long, p As Long
trials = 1000000000
p = 0
beginTIME = Now
For i = 1 To trials
Call boomBYTE
Next i
Call Finished(p, Now - beginTIME, CDbl(trials))
p = p + 1
beginTIME = Now
For i = 1 To trials
Call boomINTEGER
Next i
Call Finished(p, Now - beginTIME, CDbl(trials))
p = p + 1
beginTIME = Now
For i = 1 To trials
Call boomLONG
Next i
Call Finished(p, Now - beginTIME, CDbl(trials))
p = p + 1
beginTIME = Now
For i = 1 To trials
Call boomDOUBLE
Next i
Call Finished(p, Now - beginTIME, CDbl(trials))
p = p + 1
beginTIME = Now
For i = 1 To trials
Call boomUNDECLARED
Next i
Call Finished(p, Now - beginTIME, CDbl(trials))
p = p + 1
End Sub
Private Sub boomBYTE()
Dim a As Byte, b As Byte, c As Byte
a = 1
b = 1 + a
c = 1 + b
c = c + 1
End Sub
Private Sub boomINTEGER()
Dim a As Integer, b As Integer, c As Integer
a = 1
b = 1 + a
c = 1 + b
c = c + 1
End Sub
Private Sub boomLONG()
Dim a As Long, b As Long, c As Long
a = 1
b = 1 + a
c = 1 + b
c = c + 1
End Sub
Private Sub boomDOUBLE()
Dim a As Double, b As Double, c As Double
a = 1
b = 1 + a
c = 1 + b
c = c + 1
End Sub
Private Sub boomUNDECLARED()
a = 1
b = 1 + a
c = 1 + b
c = c + 1
End Sub
Private Sub Finished(i As Long, timeUSED As Double, trials As Double)
With Range("B2").Offset(i, 0)
.Value = .Value + trials
.Offset(0, 1).Value = .Offset(0, 1).Value + timeUSED
.Offset(0, 2).FormulaR1C1 = "=ROUND(RC[-1]*3600*24,0)"
End With
End Sub
Solution 4
VBA has a lot of historical baggage.
An Integer
is 16 bits wide and was a good default numeric type back when 16 bit architecture/word sizes were prevalent.
A Long
is 32 bits wide and (IMO) should be used wherever possible.
Solution 5
I have taken @PGSystemTester's method and updated it to remove some potential variability. By placing the loop in the routines, this removes the time taken to call the routine (which is a lot of time). I have also turned off screen updating to remove any delays this may cause.
Long
still performed the best, and as these results are more closely limited to the impacts of the variable types alone, the magnitude of variation is worth noting.
My results (desktop, Windows 7, Excel 2010):
Code used:
Option Explicit
Sub VariableOlympics()
'Run this macro as many times as you'd like, with an activesheet ready for data
'in cells B2 to D6
Dim beginTIME As Double, trials As Long, i As Long, p As Long
Dim chosenWorksheet As Worksheet
Set chosenWorksheet = ThisWorkbook.Sheets("TimeTrialInfo")
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
trials = 1000000000 ' 1,000,000,000 - not 10,000,000,000 as used by @PGSystemTester
p = 0
beginTIME = Now
boomBYTE trials
Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
p = p + 1
beginTIME = Now
boomINTEGER trials
Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
p = p + 1
beginTIME = Now
boomLONG trials
Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
p = p + 1
beginTIME = Now
boomDOUBLE trials
Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
p = p + 1
beginTIME = Now
boomUNDECLARED trials
Finished p, Now - beginTIME, CDbl(trials), chosenWorksheet.Range("B2")
p = p + 1
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
chosenWorksheet.Calculate
End Sub
Private Sub boomBYTE(numTrials As Long)
Dim a As Byte, b As Byte, c As Byte
Dim i As Long
For i = 1 To numTrials
a = 1
b = 1 + a
c = 1 + b
c = c + 1
Next i
End Sub
Private Sub boomINTEGER(numTrials As Long)
Dim a As Integer, b As Integer, c As Integer
Dim i As Long
For i = 1 To numTrials
a = 1
b = 1 + a
c = 1 + b
c = c + 1
Next i
End Sub
Private Sub boomLONG(numTrials As Long)
Dim a As Long, b As Long, c As Long
Dim i As Long
For i = 1 To numTrials
a = 1
b = 1 + a
c = 1 + b
c = c + 1
Next i
End Sub
Private Sub boomDOUBLE(numTrials As Long)
Dim a As Double, b As Double, c As Double
Dim i As Long
For i = 1 To numTrials
a = 1
b = 1 + a
c = 1 + b
c = c + 1
Next i
End Sub
Private Sub boomUNDECLARED(numTrials As Long)
Dim a As Variant, b As Variant, c As Variant
Dim i As Long
For i = 1 To numTrials
a = 1
b = 1 + a
c = 1 + b
c = c + 1
Next i
End Sub
Private Sub Finished(i As Long, timeUSED As Double, trials As Double, initialCell As Range)
With initialCell.Offset(i, 0)
.Value = trials
.Offset(0, 1).Value = timeUSED
.Offset(0, 2).FormulaR1C1 = "=ROUND(RC[-1]*3600*24,2)"
End With
End Sub
Related videos on Youtube
![Gareth](https://i.stack.imgur.com/ErAC5.jpg?s=256&g=1)
Gareth
Personal and professional interest in programming. #SOreadytohelp
Updated on July 05, 2022Comments
-
Gareth almost 2 years
I often see questions relating to
Overflow
errors with vba.My question is why use the
integer
variable declaration instead of just defining all numerical variables (excludingdouble
etc.) aslong
?Unless you're performing an operation like in a for loop where you can guarantee that the value won't exceed the 32,767 limit, is there an impact on performance or something else that would dictate not using
long
? -
Admin over 9 yearsOne thing worth pointing out is that some old API functions may be expecting parameters that are 16-bit (2-byte) Integers and if you are on a 32 bit and trying to pass an Integer (that is already a 4-byte long) by reference it will not work due to difference in length of bytes.
-
Admin about 9 years@It'sbeenapleasure I'don't understand. If you pass a Long by ref to a function that expects an Integer by ref, it will use the first two bytes, and since numbers are stored in little-endian, it will work (provided that only the two lower bytes are meaningful, but it's likely if this Long is simply an Integer stored in 32 bits).
-
ThunderFrame about 7 yearsThe other time that you must use
Integer
is when declaring aType
, where the type's layout and size is important, either because you're passing the type to an API, or you're serializing/deserializing a file, or you're copying bytes withLSet
/Rset
. -
ChrisB almost 7 yearsFor an array of numbers between 1 and 10, you could use a BYTE array instead, but I understand the point you are making.
-
aelveborn almost 7 yearsThat MSDN article is wrong and/or misleading. It might refer to e.g.
Integer
s being internally processed asLong
s inside a processor, but for all observable purposes anInteger
still takes two bytes. See answers and comments under stackoverflow.com/q/26717148/11683 for extended discussion. -
RubberDuck almost 7 years@GSerg I explicitly state that in my answer. Do you have a suggestion for how to make that more clear?
-
aelveborn almost 7 yearsYou don't seem to state anywhere in your answer that msdn.microsoft.com/en-us/library/office/… might be wrong or misleading, rather, you are presenting it as
the msdn documentation I was really truly looking for.
However my comment was mostly directed towards future readers rather than yourself, because you have participated in that other question, even though your answer there in my opinion should not have been accepted (this one I believe should have been). -
Vityata almost 6 yearsThe undeclared variable is a variant.
Dim a as Variant, b as Variant, c As Variant
should give the same result as not "dimming" at all. -
RubberDuck almost 6 yearsI just noticed someone had edited this answer with incorrect links to the VB.Net documentation back in July. Please don't do that. That documentation is flat out wrong as far as VBA is concerned.
-
Vityata almost 6 yearsThis was probably true some 15 years ago, but now the answer is really outdated.
-
Alter almost 6 yearsHow is this outdated? Problems in pandas where you have millions of rows (big data/machine learning), or in networking when transferring large amounts of data (ex. physics experiments)
-
pgSystemTester almost 6 years@Vityata, I was aware that "undeclared" SHOULD mean variant, but keep in mind the backstory to this whole discussion was microsoft automatically converting integer to Long despite being defined! So I wasn't sure what to expect with whole numbers without being defined. Seeing the result, it seems most likely to truly be Variants, but that's why I gave the name "Undeclared" vs. 'Variant." Plus that's probably a lot more useful to lower level coders. However, I'll go ahead and add that in now. Thanks for feedback.
-
Vityata almost 6 years@PGCodeRider - it would be probably interesting to create a dll library with c++ and to add it to VBA and to benchmark it there as well. The results would be probably interesting. I have done something similar here - vitoshacademy.com/…
-
Vityata almost 6 years@Alter - I guess you missed the point of the original question. Take a look at the accepted answer.
-
Alter almost 6 yearsLooks like I understood it. Also the accepted answer points out it's talking about 32 bit systems when it says there's no difference.
-
Greedo about 4 yearsI think get rid of that last MSDN excerpt, it seems to imply VBA used to use less memory to store integers than longs, and now it doesn't. That's a bit misleading - declare two equal dimension arrays of long and integer, look in task manager, the long array takes twice as much memory. The reason for the performance loss is because VBA converts integers to longs temporarily whenever it does processing on them, but that will be one integer at a time. So generally if memory usage is an issue, converting all the longs to integers will help, but don't do it in a tight loop
-
Greedo about 4 yearsHmmm I just made a fairly wordy edit to your answer - it was only meant to be suggested but I guess I have enough rep now that edits are just carried through. From this comment I think this is in keeping with the point you wanted to make, and for me at least the emphasis is much more clear (feel free to adjust wording ofc).
-
Greedo about 4 yearsAlso I'm now slightly sceptical about the last point on old APIs - the pointer ByRef points to the 2 byte Integer in memory, the Long conversion is only temporary during a calculation so that's definitely not being passed. So I really don't know if you should have that in the answer, unless it can be reproduced. Finally, I'm probably missing something really obvious, but could you clarify what is meant by that
Hex
stuff - I don't really get why it's there -
RubberDuck about 4 yearsThanks Greedo. I think that’s the first edit to this answer I didn’t have to rollback.
-
pgSystemTester about 3 yearsI actually like this approach better than mine.