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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,518
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
 

KerryFSA

New Member
Joined
Dec 5, 2018
Messages
49
So it would be more efficient to define as integer if the value of the variable were <= 32767?
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,582
Office Version
2010
Platform
Windows
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,507
Office Version
2010
Platform
Windows
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
 

KerryFSA

New Member
Joined
Dec 5, 2018
Messages
49
Thank you all for your replies.
I have always used type long for integers. Now I know why!
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,582
Office Version
2010
Platform
Windows
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:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,582
Office Version
2010
Platform
Windows
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:

Forum statistics

Threads
1,082,259
Messages
5,364,100
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top