Results 1 to 9 of 9

Thread: Using variable as Integer or Long
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Location
    London, Ontario, Canada
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using variable as Integer or Long

    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

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,248
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Using variable as Integer or Long

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Dec 2018
    Location
    London, Ontario, Canada
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using variable as Integer or Long

    So it would be more efficient to define as integer if the value of the variable were <= 32767?

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,431
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using variable as Integer or Long

    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 by joeu2004; Feb 23rd, 2019 at 05:46 PM.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Using variable as Integer or Long

    Quote Originally Posted by KerryFSA View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Dec 2018
    Location
    London, Ontario, Canada
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using variable as Integer or Long

    Thank you all for your replies.
    I have always used type long for integers. Now I know why!

  7. #7
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,154
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Using variable as Integer or Long

    one google hit, https://stackoverflow.com/questions/...nstead-of-long
    basically integers are converted to long anyway

    unless you really want an integer, contrary to older ideas use long not integer
    To receive a better answer, put more work into asking the question.


  8. #8
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,431
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using variable as Integer or Long

    Quote Originally Posted by Fazza View Post
    one google hit, https://stackoverflow.com/questions/...nstead-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 by joeu2004; Feb 24th, 2019 at 06:13 PM.

  9. #9
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,431
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using variable as Integer or Long

    Quote Originally Posted by joeu2004 View Post
    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 by joeu2004; Feb 24th, 2019 at 07:47 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •