Using variable as Integer or Long

KerryFSA

New Member
Joined
Dec 5, 2018
Messages
49
I have read that it is useful to define a variable taking on integer values as "Long".
Yet most examples I see define such a variable as "Integer" which, of course, it is.
Can you provide clarification or the thinking behind an integer being defined as "Long"?

Thanks, Kerry
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
The difference is in the range of values that each can reference.
Integer: -32,768 to 32,767
Long: -2,147,483,648 to 2,147,483,647
 
Upvote 0
On a moderate computer, type Integer is no more "efficient" (faster execution) than type Long. Theoretically, type Integer uses half the space. But as a practical matter, the savings is only worthwhile when using humongous arrays. What is more important is for algorithms (VBA expressions) to work as intended. For example, x = a + 1 - b might overflow if a and b are type Integer and a is 32767, even though the result (x) might be within the numerical range of type Integer.

Bottom line: I would use type Long "all" the time, using type Integer or Byte only when I'm working with humongous arrays. And even then, I might not bother, since most moderate computers have lots of memory.

BTW, "always" use type Double instead of type Single. Although the latter has the same space-saving ability, the loss of precision is often significant, especially when storing the arithmetic result into an Excel cell.

-----
PS.... You might see type Integer used out of ignorance; that is the most likely explanation. But also, for old geezers like me, the use of the "tightest" data type was important in the distant past because in early computers, especially PCs, memory was a premium (a homebrew computer with 8K of memory was "huge", and it was used for code as well as data), type Byte operations were faster than type Integer, and type Long operations were implemented in software.
 
Last edited:
Upvote 0
So it would be more efficient to define as integer if the value of the variable were <= 32767?
No, not really... basically, modern computer will use the same amount of memory to store and Integer as a Long (given the size of each memory unit). Another reason to not use Integers is unintended math problems. Execute this in the Immediate Window (note both numbers would fit comfortably in an Integer variable)...

? 1234 * 9876
 
Upvote 0
one google hit, https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long
basically integers are converted to long anyway

That is incorrect. In fact, there are a lot of incorrect statements in that article. The fact is: Intel-compatible CPUs loads 16-bit values (type Integer) into 32-bit or 64-bit registers. But it performs 16-bit arithmetic if indicated to do so by the machine language instructions.

Whether or not a computer language treats integers as 16-bit or 32-bit values (or even 64-bit values) is a property of the definition of the computer language.

As Rick demonstrated, obviously VBA uses 16-bit arithmetic for values that are implicitly or explicitly type Integer. We could write 1234# * 5678# in order to force VBA to do 32-bit (type Long) arithmetic. But 1234 * 5678 performs 16-bit arithmetic by default because each of 1234 and 5678 can be represented as 16-bit values.

In contrast, 1234 * 56789 does indeed perform 32-bit arithmetic, only becuase 56789 requires 32 bits (type Long) to be represented, and VBA coerces the left-hand expression (1234) to type Long automagically for the purpose of that arithmetic.

The flaw in the cited article (one of many) is that it chose 65535 to prove its point. Again, that value requires 32 bits to be represented.

I might add that the type coercion is performed based on the types of values of each pairwise operation, generally left-to-right, but subject to rules of operator percedence.

So 32767 + 123 + 45678 fails because 32767 + 123 is evaluated first with 16-bit arithmetic, resulting in overflow. On the other hand, 32767 + (123 + 45678) succeeds because 123 + 45678 is evaluated first with 32-bit arithmetic, which causes 32767 to be coerced to a 32-bit value for the second evaluation.

PS.... Generally, Rick is incorrect in stating that ``modern computer will use the same amount of memory to store and Integer as a Long``. That really depends on the computer language compiler or interpreter. I don't know about VBA per se. And of course, we are talking about the explicit type Integer and Long, not integers stored in type Variant variables.
 
Last edited:
Upvote 0
PS.... Generally, Rick is incorrect in stating that ``modern computer will use the same amount of memory to store and Integer as a Long``. That really depends on the computer language compiler or interpreter. I don't know about VBA per se. And of course, we are talking about the explicit type Integer and Long, not integers stored in type Variant variables.

Try the following on your computer:

Code:
Sub doit()
Dim x As Integer, y As Integer, z As Integer
Debug.Print vbNewLine & "====="
Debug.Print Hex(VarPtr(x)) & "  x" & _
    vbNewLine & Hex(VarPtr(y)) & "  y" & _
    vbNewLine & Hex(VarPtr(z)) & "  z"
End Sub

On my computer (one time), the results were:

2BEF32 x
2BEF30 y
2BEF2E z

Note that the addresses of x, y and z differ by only 2 bytes. Thus, VBA allocates only 16 bits (2 bytes) for type Integer, not 32 bits (4 bytes) as it does for type Long.

I might note that the separation between variables depends on the order of declaration -- or more precisely, how the computer language allocates variables. An optimizing compiler or interpreter might gather variables of the same type; so their order of declaration does not matter. But typically, if we alternate type Integer and type Long declarations, that might result is wasted memory in between because on Intel-compatible (and most) computers, the address of variables must be divisible by the size of their type. So type Long variables must be aligned on 4-byte addresses, whereas type Integer variables can be aligned on 2-byte addresses.

[EDIT] Nitpick: Actually, recent Intel-compatible CPUs do not require size-specific aligned addresses, generally (although some instructions do). But it is more efficient to align variables on their "natural boundaries" (Intel's terminology).

Nevertheless, even though there is extra memory in between, a type Integer variable is still treated as if it consumes only 2 bytes.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top