Integers to long in VBA

[gol4]

I found this info on MSDN today. Was anyone else aware that VBA converted integers to long. I have used integers in my code where ever possible. Internal functions like on dblclick(cancel as integer) still use integers. I started programing when memory was tight now it seems my practice actually hurts performance. I have no plans to convert my existing codes but looks like I need to just use long from now on.

The Integer and Long data types can both hold positive or negative values. The difference between them is their size: Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647. 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 are declared as type Integer. Therefore, there is no longer a performance advantage to using Integer variables; in fact, Long variables might be slightly faster because VBA does not have to convert them.

 

[dhookom]

I would like to see a link to the actual article. I just ran a test in Access 2010 vba and got an error if I attempted to store a larger number in an integer variable:

Public Sub IntTest()
    Dim intValue As Integer
    intValue = 320000  [COLOR=#4E9A06]'<=== generates an error[/color]
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[gol4]

here is the link.

http://msdn.microsoft.com/en-us/library/office/aa164754(v=office.10).aspx

 

[dhookom]

Apparently behind the scene VBA is actually storing the value as a long. The type declaration of Integer will still force the value to be within the limits of integer. I expect this is for compatibility reasons.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

In [highlight]recent[/highlight] versions, however VBA...

[highlight]Rather imprecise, it seems to me![/highlight]

My VBA 6.5 returns an OVERFLOW. Maybe it's not [highlight]recent[/highlight] enough.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gol4]

So I'm wasting my time using an integer. I had the same experience as you with an overflow if declared as an integer. But the only reason I declared it as an integer was to reduce memory usage. Since it converts it to a long any way I really don't save any memory I only run the risk of and oveflow error. Am I correct in my interpretation of this?

 

[SkipVought]

I had the same experience as you with an overflow if declared as an integer.

[highlight]Since it converts it to a long any way[/highlight] I really don't save any memory I only run the risk of and oveflow error.

[highlight]But it does not 'convert' in your version of VBA, as well as mine and Duane's, since we all get errors![/highlight]

Your interpretation is not correct.

If you have any whole number that is expected to exceed the Integer data type limits, I'd declare that as Long, until your version of VBA becomes recent.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

The article does state "Office XP" in the subtitle. I expect the only reason you would want/need to dim as integer rather than long would be to generate an error for larger or smaller numbers. Any performance increase would be imperceptible.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Let's assume the memory storage is a parking space and the declaration is the entry to the space. The article might be stating the parking space accommodates a bus but the entry to the space only accepts a car ;-)

Duane
Hook'D on Access
MS Access MVP

 

[gol4]

Thanks for the input guys. I think I will just declare everything as long from now on. Unless I can get away with a byte.

 

[strongm]

>Was anyone else aware that VBA converted integers to long

Yep. Been aware of it for some time. This isn't new ('recent' when referring to Office XP means at least 12 years ago). It's for memory access performance reasons (faster for a CPU to pull 4 bytes from memory into a 32-bit register and then mask than to only read 2 bytes). In other words, dhookom's analogy is pretty much right: underneath the covers VBA reserves 4 bytes of memory for storage, but only the top two bytes are used to actually store the integer. You can see this if you dim a few longs and an integer and examine their memory locations with VarPtr, e.g.

Public Sub example()
Dim a As Long, b As Long, c As Integer, d As Long
Debug.Print VarPtr(a), VarPtr(b), VarPtr(c), VarPtr(d)
End Sub

You'll find that VarPtr(d) isn't quite where you expect it to be if integer c had only reserved 2 bytes of memory

 

[gol4]

Way to clear it up Strongm!
I have not used the VARPTR command since Qbasic when I was poking and peeking pixels. I did not realize it was supported in VBA
I just tried VARSEG but VBA ejects that.

 

[strongm]

VARSEG wouldn't make much sense now that we no longer have the memory restrictions that affected Qbasic

In addition to VARPTR, VB/VBA includes:

StrPtr - Returns the address of the UNICODE string buffer.
ObjPtr - Returns the pointer to the interface referenced by an object variable.
VarPtrArray - Returns the address of an array.
VarPtrStringArray - Returns the address of an array of strings.

Admittedly the latter two are not directly available in the commands exposed by VBA: VarPtrArray requires an API call into the VB runtime, and VarPtrStringArray is even more esoteric, requiring the creation of a type library