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 (excluding
double etc.) as
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
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
“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
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.
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
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
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
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
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.