VBA Variable Olympics…. Integer vs Long Vs Byte vs Double vs Undeclared

When using variables in VBA (or any language) eventually every coder will have to make some choices on which type of variable to use for numbers.

The common practice is to either use Integer, Long, or Double (I never bother with single). A quick recap of each of these is that:

Integer uses less memory (2 bytes) but has a range of numbers of -32,768 to 32,767, and does not allow decimals (by definition this is what “integer” means in all programming languages).

Long uses double the memory (4 bytes) but the range of numbers is exponentially–2,147,483,648 to 2,147,486,647 (that’s 2.1 billion vs. 32 thousand on integer). Whole numbers only.

Double is the go-to variable if you may have something over 2.1 billion or if you have decimals. However its overhead is 8 bytes. The range of numbers is massive but Excel can’t handle anything over 15 digits, so it’s always seemed irrelevant to me (in case you care, here’s the supposed range: – 1.79769313486232E308 to –4.94065645841247E–324 or 1.79769313486232E308 to 4.94065645841247E–324

One other variable worth mention is:

Byte is a hardly used variable that can return a whole number from 0 to 255, and only uses 1 byte. Interestingly, this is less memory than a Boolean variable (true or false), which uses the same memory as Integer. It’s tempting to consider using Byte for small numbers to save memory. However, it’s not recommended for reasons I’m unclear about.

What brought this to my attention was an old StackOverFlow post that details how  Microsoft claims that they shift all VBA variables from INTEGER to LONG, which essentially nullifies the benefit of EVER using INTEGER.

“In recent versions, however, VBA converts all integer values to type Long, even if they’re declared as type Integer. So there’s no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them.”

I decided to conduct the VARIABLE OLYMPICS by timing how fast a basic macro ran, but with different variable types.

The results align with what Microsoft says, in that it probably makes sense to just use LONG instead of INTEGER in pretty much every case.


Leave a Reply