Long Vs Integer

**Reafidy** · Nov 16th 2007

With respect to excel vba on another site - a member quoted:

"As a rule of thumb never use integer".

With no disrespect to the member I at first thought this was a slightly bold statement. But perhaps its not?

My understanding is that the underlying VB engine converts integers to long even if its declared as an integer. Therefore a slight speed decrease can be noted. I have believed this for some time and perhaps thats also why the above statement was made, I didnt ask for reasoning.

So we should be using long right? Unless anyone has a good reason why we should'nt?

Also always using long will prevent rare integer overflow errors. Like when programmers make the classic mistake of dimensioning rows as integers.

Try the variable speed test attached. Credit to the original author.

Dave Hawley · Nov 16th 2007

Re: Long Vs Integer

Reafidy, that is my understanding too, that is, Excel converts to a Long BUT still throws an Overflow error is the Integer limits are exceeded.

Basically, good practice is to forget about Integers and always use Longs.

GeorgS · Nov 16th 2007

Re: Long Vs Integer

I hadn't heard of that "rule of thumb"

My only small objection to this is that avoiding using integers removes a level of validation and code readability. Nowadays speed is less of an issue than readability.

I'd be (mildly) confused if I came across some code that dimensioned (struggling to come up with a good example here) the seats in a 20,000 person stadium as Long.

I guess as the number of rows in an Excel worksheet grows the Integer becomes less useful.

G.

Dave Hawley · Nov 16th 2007

Re: Long Vs Integer

George, why do you believe readability is sacrificed? So long (no pun intended ) as good variable naming conventions are adhered to, I don't see it as an issue. While a Long variable may have an Integer passed to it in one instance, it may have a larger value passed in another.

shg · Nov 16th 2007

Re: Long Vs Integer

On a related note, VBA stores ASCII (single-byte) strings not as a series of Bytes, but as a series of Integers, so that it can handle Unicode (two-byte) strings in the same data structure. The first (Long) word stores the string length, which is why

Code

If Len(str)=0 Then

should be used in preference to

Code

If str = "" Then

, which invokes a string comparison.

There's a function (I forget which) that converts VBA strings to Windows null-terminated strings for Windows API functions that expect them.

Dave Hawley · Nov 16th 2007

Re: Long Vs Integer

VBNullString is better than "" in VBA, apparently.

Wouldn't the Len Function cause Excel to use a String, where-as;

Code

If IsEmpty(str) Then

wouldn't?

My bad, IsEmpty always returns False after the variable has been initialized.

shg · Nov 16th 2007

Re: Long Vs Integer

Quote

VBNullString is better than "" in VBA, apparently.

Perhaps it cues the compiler to perform a zero-length check. One hopes, for example (but this one does not know), that x^2 compiles as x*x rather than as a general exponentiation. I do know that in days of yore in real-time programming, people were fastidious about coding it explicitly as the latter.

IsEmpty is applicable only to Variants and single-cell ranges, right?

Dave Hawley · Nov 16th 2007

Re: Long Vs Integer

IsEmpty is great for checking single cells to see if they are empty. It can be used with any variable type.

shg · Nov 16th 2007

Re: Long Vs Integer

You sure? According to Help,

Quote

IsEmpty only returns meaningful information for [COLOR="Blue"]variants[/COLOR]

.

GeorgS · Nov 16th 2007

Re: Long Vs Integer

Dave,

Quote

George, why do you believe readability is sacrificed? So long (no pun intended) as good variable naming conventions are adhered to

I should have worded that better. From:

Quote

avoiding using integers removes a level of validation and code readability

to

Quote

avoiding using integers removes a level of validation and a level of code readability

I'm guessing that you are talking about Hungarian notation?
Without wanting to side track into a naming convention debate, I found that I can fit 95% of my functions into one screen, and so, after using it for about 10 years, no longer needed Hungarian to tell me the type of the variable.
(I droned on a bit here)

So, readability bit comes in here:
(Once again, not an example of which I am proud.)

Code

Dim seatCount as Integer
Redim stadiumSeating(seatCount)

I know that we are talking about the WACA and not the Camp Nou.
This example is getting more ludicrous as I go a long, so think that I will stop.

G

Dave Hawley · Nov 16th 2007

Re: Long Vs Integer

Quote from shg

You sure? According to Help,
.

It works as advertised with any variable type on my testing.

**Reafidy** · Nov 17th 2007

Re: Long Vs Integer

Interesting stuff.

Yes the help file is a little confusing in regard to IsEmpty I think when they say "IsEmpty only returns meaningful information for variants" they are meaning that a variant can be any "expression that can evaluate to numeric, string, or date data, as well as the special values Empty and Null" but not nessecarily a variable dimensioned as variant. Maybe?

Oh by the way: Ive given away my old habbit of Dimensioning strings as Str. Due to it overiding the str function in vba. I now use s to designate a string so as an example: Dim sPass As String. Just a thought for those who dont know of the Str function.

carlmack · Nov 19th 2007

Re: Long Vs Integer

Quote

My understanding is that the underlying VB engine converts integers to long even if its declared as an integer

I didn't know that. Well Long it is then. For now anyway ...

In VB.NET an "Integer" is the old Long also known as Int32. Something to do with Int32 being faster on a 32bit machine so the decided to change the definition. They also have Int16 (old integer) and Int64 (very long ?). MS SQL now has Int, bigInt, smallInt and tinyint. SSIS even has unsigned and signed varieties of all these but calls then 2 bit, 4 bit .... Confused ? Me too.

and when we all move to 64bit machines will they change it again ?

Dave Hawley · Nov 22nd 2007

Re: Long Vs Integer

Integers no Longer It doesn't get any cornier than that folks!

Anonymous · Nov 24th 2007

Re: Long Vs Integer

Interesting discussion. I ran the speed test on my Macintosh and found a couple of things. First, Timer only measures in seconds. Second, Integer appears faster than Long.
Byte 16.0000
Integer 14.0000
Long 16.0000
Single 13.0000
Double 15.0000
Variant 32.0000

Dave Hawley · Nov 25th 2007

Re: Long Vs Integer

Derk, what was the test and how & what order did you run it? What stands out to me is Single is the fastest?

shg · Nov 25th 2007

Re: Long Vs Integer

Quote

First, Timer only measures in seconds.

On Mac, yes; in Windows, fractional seconds.

Want to post your benchmark so we can try?

Anonymous · Nov 25th 2007

Re: Long Vs Integer

Quote from Dave Hawley

Derk, what was the test and how & what order did you run it? What stands out to me is Single is the fastest?

I used the speed test Reafidy attached in the first post of this thread to get my posted results. The VBA for the Mac must process things differently with respect to integer and long variables. I know VBA is slower on a Mac than a PC for comparable clock speeds (I'm running a 1.8 G5 single processor).

shg · Nov 25th 2007

Re: Long Vs Integer

Here are my numbers:
[ss=
---A--- -------B--------
3 Byte 2.0117
4 Integer 2.2422
5 Long 1.8242
6 Single 2.1055
7 Double 2.1328
8 Variant 9.2930 ]*[/ss]
1.6GHz M Pentium.

Dave Hawley · Nov 25th 2007

Re: Long Vs Integer

I hate to be a kill joy, but it's not quite a level playing field. Try the attached version and before running any macros, Compile the Module.

My times
[ss=
---A--- -------B--------
3 Byte 1.6483
4 Integer 1.5556
5 Long 1.5240
6 Single 1.7223
7 Double 1.7369
]*[/ss]

Long Vs Integer

Participate now!

Share