Results 1 to 9 of 9

Thread: To integer or not to integer

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    To integer or not to integer

    Hello,

    Does anyone have definitive proof of what VBA does with integer type numbers please?

    Do they get converted internally to long or not?


    TIA
    Lisa

  2. #2
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    Re: To integer or not to integer

    Internal to whom or what?
    I assume VBA Integers are the same as VB6 and earlier Integers and are 16-bits.
    But since VBA has continued to evolve, perhaps at some point in recent versions Integers may now be 32-bit.
    At what point are you concerned about an Internal representation, and what the implications are?

    And if it is an "Internal" representation, that implies some type of conversion process from external to internal, so it shouldn't matter to you. If you declare something as Integer, and then try to assign 60 thousand to it you should get an overflow exception whether the value will be converted to a Long before it is used or not. After it is used, the result will still cause an exception (I assume software based) because the range can exceed the type definition even if it doesn't exceed the hardware implementation.

    According to Microsoft sources, it is 32-bit internally, but at what point is the external to internal conversion done, I don't know. I don't do VBA generally, but I might test some things myself, if I get bored enough.

    It would seem to me that it would be best to use a long (32-bit) value in recent versions of VBA just to avoid the process of conversion to the internal representation and to avoid the compiler generated software range test that must be generated to insure the longs value stays in the range of the Integer type when calculations and assignments are done.
    Last edited by passel; Apr 19th, 2018 at 10:52 AM.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: To integer or not to integer

    Since there is no SizeOf-Function in VB/VBA, you could declare an array (1 To 2) As Integer, and then use the VarPtr-API to get the Addresses of both Elements.
    The Difference between both Addresses should be the allocated memory in Bytes for an Element
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: To integer or not to integer

    hmmmmmm.... ok

    As far as I can tell with my limited knowledge and restricting things to VBA not VB.... Integers do seem to be converted to longs... but... VBA remembers that they were originally longs and applies long "restrictions" to them.... as you implied.

    All of this should IMHO slow things down a bit but I've not been able to prove this so far.

    Yes it does matter to me because I can stop declaring Integer and do everything as Long.

    Microsoft sources are IMHO a bit vague about what goes on.

    My Question is here because it's VBA but you say you don't do VBA generally.

    I'd just like to know what actually... really... happens.

    Then I could declare types as Integer/Long whatever fits the bill and is more appropriate.

    You say " just to avoid the the process of conversion to the internal representation and to avoid the compiler generated software range test that must be generated to insure the longs value stays in the range of the Integer type when calculations and assignments are done."

    That *seems* to imply you are *sure* there is an internal conversion to long.

    I could be wrong and am willing to admit it but are you positive that is the case? That's what my question is about.

    Do you know 100% or not?

    How it matters to me? In the editing of code in the VBE.

    > I assume VBA Integers are the same as VB6 and earlier Integers and are 16-bits.
    Assumptions are odious.

    > But since VBA has continued to evolve..
    It hasn't for a long time. My information... which could very likely be wrong is that the last update was in 1998.


    So...
    Is there an internal conversion or not?
    Is it ever worth... in VBA... typing anything as anything but long?

    How do you know and is this definitive????

    Lisa

  5. #5
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    Re: To integer or not to integer

    I can only base my opinion based on what has been reported by Microsoft and others, and perhaps some bit of empirical testing based on what Zvoni mentioned.
    Quote Originally Posted by lisagreen View Post
    ....
    Yes it does matter to me because I can stop declaring Integer and do everything as Long.
    ...
    You could have always declared everything as Long (assuming you're not passing to a function or sub that expects an Integer), so the fact that they may be using a Long at some point internally has never mattered logically.

    VBA has been updated since 1998 (VB6 language development pretty much stopped then, but not VBA).
    Apparently starting with Office 2010 it has been modified to work better on 64-bit systems, so I'm assuming that is probably when they would have switched to using a Long (32-bit) internally. I'm saying that based on what I've read since you've asked the question. I assume that is also when they added the LongLong type (64-bit Integer value) and the LongPtr type.

    Again, as an end user, I don't see that it would usually matter. When you declare the value as an Integer, it will still take up two bytes of memory of storage and be limited to the range of a signed integer. When VBA accesses the memory the value will probably be extended to 32-bits, and operated on, and then converted back to 16-bit (assuming it fits the range) and stored again in memory. You can save some bit of time (but probably hard to measure) if you used 32-bit values to start with, but they will take up more storage in memory so it is up to you whether you consider using a bit less memory is worth a small bit of overhead when accessing the values on 64-bit processors.

    The two byte in memory representation of the data storage is based on the test that Zvoni mentioned trying.
    I have Office 2016 installed on this computer so tried creating a two element Integer array as Zvoni suggested and get the difference in memory addresses of the two elements and the difference returned was 2. The longPtr value returned is 8, so in memory an Integer is a 2 byte value (i.e. 16-bit) and a longPtr is an 8 byte value (64-bit).

    The code, which I ran in an Excel UserForm to test follows.
    Code:
    Private Declare PtrSafe Function VarPtrArray Lib "VBE7" Alias _
        "VarPtr" (Var() As Any) As LongPtr
        
      Dim a(1) As Integer
      Dim b(1) As LongPtr
      
    Private Sub CommandButton2_Click()
    End Sub
    
    Private Sub CommandButton1_Click()
      Dim b1 As LongPtr, b2 As LongPtr
      
      b1 = VarPtr(a(0))
      b2 = VarPtr(a(1))
     ' b = LenB(a(0))
      Debug.Print "Integer size: "; b2 - b1
      b1 = VarPtr(b(0))
      b2 = VarPtr(b(1))
      Debug.Print "longPtr size: "; b2 - b1
    
    End Sub
    You just need to display the Immediate window in the Visual Basic for Applications IDE to see the debug print out.
    Last edited by passel; Apr 19th, 2018 at 08:30 PM.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: To integer or not to integer

    i learned somewhere (no idea now where) that while longs took more memory than integer, they were faster in calculations, so i am more likely to use longs anyway, that amount of memory difference seems irrelevant these days, and do not have to worry about overflow error on things like rows.count
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: To integer or not to integer

    Personally, I'd be shocked if an Integer were anything other than two bytes. It'd just make the math much more difficult to navigate at the machine language level. Also, here's some VBA macro code that fairly conclusively shows that they're two bytes:

    Code:
    
    Option Explicit
    
    Private Type TestType
        i1 As Integer
        i2 As Integer
        i3 As Integer
        i4 As Integer
    End Type
    
    Private Sub Testing()
        Dim udt As TestType
        '
        MsgBox VarPtr(udt.i1)   ' <--- shows 35320780
        MsgBox VarPtr(udt.i2)   ' <--- shows 35320782
        MsgBox VarPtr(udt.i3)   ' <--- shows 35320784
        MsgBox VarPtr(udt.i4)   ' <--- shows 35320786
    End Sub
    
    
    However, on another note, it's been my standard practice for years to declare all integer-type-numbers as Long unless I have a compelling reason to declare them as Integer.

    Take Care,
    Elroy

    EDIT1: Just to elaborate a bit more, here are the general purpose registers of something like an i7 Intel chip:

    Name:  regs.png
Views: 432
Size:  79.2 KB

    Given that it's trivial to address these as either a 64 bit, 32 bit, 16 bit, or even 8 bit register, there's absolutely no reason that we'd want to scale up a two-byte Integer into a four-byte Long. The math (at the machine language level) is quite simple regarding any of these number of bits/bytes.
    Last edited by Elroy; Apr 20th, 2018 at 11:19 AM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Oct 2013
    Posts
    30

    Re: To integer or not to integer

    Ok everyone... What I'm hearing is lots about the merits of Integer Vs Long and.

    I just wanted to know what actually happens.

    Thank you all for replying anyway.

    Lisa

  9. #9
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: To integer or not to integer

    Quote Originally Posted by lisagreen View Post
    merits of Integer Vs Long
    For me, I'd bet the raw speeds of the two (doing various math operations) is extremely similar. Also, anytime I do use an Integer, I find that I'm often shoving it into a Long somewhere along the way. Therefore, why not just always use a Long. Also, Enumerations are always Long values, so, if we use those with our variable, we'll wind up stuffing a Long into an Integer.

    Quote Originally Posted by lisagreen View Post
    I just wanted to know what actually happens.
    I haven't tested every possible version of the VBA, but I feel very confident saying that an Integer is always two-bytes (so long as it stays an Integer), and that a Long is always four-bytes, and a LongLong is always eight-bytes.

    Best Regards,
    Elroy

    EDIT1: And just to be complete, a LongPtr will be four-bytes on 32-bit Office, and it will be eight-bytes on 64-bit Office.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

Posting Permissions

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



Click Here to Expand Forum to Full Width