Long vs Integer vs Double (1 Viewer)

[tmyers]

I have a friend who is also learning VBA and Access (more Excel than Access, but still) who asked me last night what datatype is best to use and surprisingly I didn't really know the answer.

All I know is Long can go out to like 2 billion but doesn't do decimals, double I am not sure on its range but I know it does decimals and I was told early on to never bother with integer but at this point I don't remember by who nor why so have just always used long.

Aside from the needing decimals, what datatype should be used and why? Are there performance gains for one over the other? I am now curious as I honestly don't know why each is used aside from the obvious.

 

[CJ_London]

you use whatever is best for the job in hand. It may be it has to be big enough - so integer may be too small for range required, so use long or will never exceed 255 in which case you can use byte. Or there are size/performance constraints - technically bigger number types can take longer to process.

see this link

Data type summary

Office VBA reference topic

learn.microsoft.com

As a default, use long if no decimals required, currency or decimal if there are

 

[ebs17]

Performance: Move as few bytes as possible. Less bytes, less work.

In VBA, however, you have no advantage with an Integer (2 bytes) compared to a Long (4 bytes), which is due to the size of the available memory blocks.

The question of integer (Long) versus decimal (Double, Decimal, Currency) doesn't really arise, either I need decimals or I don't.

 

[tmyers]

Performance: Move as few bytes as possible. Less bytes, less work.

In VBA, however, you have no advantage with an Integer (2 bytes) compared to a Long (4 bytes), which is due to the size of the available memory blocks.

The question of integer (Long) versus decimal (Double, Decimal, Currency) doesn't really arise, either I need decimals or I don't.

That may have been why I was told early on to never bother with Integer as there is no reason to use it over Long.

 

[GPGeorge]

Integers are whole numbers with a range of values from -32,768 to +32,767. That is a little over 65,000 total possible values.
Long Integers are whole numbers with a range of values from -2,147,483,648 to +2,147,483,647. That's about 4.3 billion possible values.
Long Integers do require four bytes of storage, while Integers require two bytes.

Decimals, of course, include fractional numbers with a range of values from -9.999... x 1027 to +9.999... x 1027 Decimals require 12 bytes of storage. A key potential problem with decimals is in the fact that they are not exact, as indicated by the ellipses in the range.

As others have noted, there is no subjective "best to use" consideration. There can be an objective evaluation of the requirement at hand which leads to a choice that satisfies that requirement.

Ultimately, then, and given the factor Eberhard pointed out, you primarily choose between Long Integer and Decimal on the basis of one requirement:

Do I need fractional, or decimal, values, or do I need only whole numbers?

Other factors can, of course, come into play, but that is situational.

 

[The_Doc_Man]

Think of this another way: If going to a doctor who only has scalpels in his drawer, what do you think he will use on you?

Data types are like tools in a tool box. INTEGER is limited, but makes sense for subscripts and loop counters where you know it's a short loop. BYTE is an integer that usually is good for things like state variables. For instance, I had about 20 states that a particular thing could be in, such as PENDING, APPLIED, WAITING FOR DOWNTIME, WAITING FOR VENDOR, etc. The range of a byte is -128 to +127, so it was perfect. LONG is what you use for integers up to +/- 2 billion - and for autonumbers. On a 64-bit system and 64-bit version of Access, and on newer versions of 32-bit Access, you can use data type QUAD, a 64-bit integer with a +/- 8 quintillion range (10^18). Ac2010 doesn't have that but I had a newer version with the Navy that did support it. The integers support scaling, which is to say that they diddle with the decimal point internally so it LOOKS like you have one or two decimal places but you pay for that by reducing the range of the numbers you are working with. For LONG, that might be OK. For INTEGER (WORD) and BYTE, not so much.

DOUBLE handles fractions with 53 bits of precision, which turns out to be about 15 or 16 digits, and it handles exponents in the 10^-38 to 10^+37 range. SINGLE gives you about 6 or 7 digits. Most of the scientific math you ever wanted to do can fit in a DOUBLE. SINGLE is good for individual readings taken from imprecise sources in science, and there are some simple-minded real-world cases where it is OK.

Then there are the typecast data types. BOOLEAN or YES/NO uses a BYTE integer. DATE uses a DOUBLE. CURRENCY is an automatically scaled very long integer. TEXT (both SHORT and LONG) are BYTE arrays treated as a non-array unit. They are each specialized in their purpose and interpreted according to the data type used.

You use things according to need. One size almost never fits all. But you should know every tool in the tool box.

Given modern systems with high-speed, wide-path memory channels, you need to worry about data type sizes for one and only one reason. Access places a size limit on individual fields and also on records as a whole. LONG TEXT is so abnormal that it is treated specially. There is such a data type as LARGE BINARY OBJECT (think "attachment" as one example) that is also treated specially. Everything else has to fit into a 4,000 byte block (or smaller) for a single record.

 

[CJ_London]

That may have been why I was told early on to never bother with Integer as there is no reason to use it over Long.

Appreciate this is mainly about Excel but just to emphasis ebs17 said 'in VBA'. If you are talking about a field in an access table the integer will take 2 bytes whilst the long will take 4

 

[Gasman]

That may have been why I was told early on to never bother with Integer as there is no reason to use it over Long.

Just as I feel it is wrong to copy a file every day when it has not changed, as opposed to just copying it when it has changed, I would use an Integer over Long where it is suitable and Long is overkill.

 

[ebs17]

Solved: "Integer" vs "Long" in For...Next Loops

In his terrific article on making macros faster and shorter, johnske mentioned that the index variable in a For...Next loop should be dimensioned as "Long". I have always had the habit of making the variable "Integer" because it takes less memory. That's probably not a big issue, but why...

www.vbaexpress.com

=>

...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.

 

[Gasman]

And I do not have a recent version?

 

[strive4peace]

I have a friend who is also learning VBA and Access (more Excel than Access, but still) who asked me last night what datatype is best to use and surprisingly I didn't really know the answer.

All I know is Long can go out to like 2 billion but doesn't do decimals, double I am not sure on its range but I know it does decimals and I was told early on to never bother with integer but at this point I don't remember by who nor why so have just always used long.

Aside from the needing decimals, what datatype should be used and why? Are there performance gains for one over the other? I am now curious as I honestly don't know why each is used aside from the obvious.

not just performance, but also accuracy. If a field is a primary key, it should NOT be a floating point number (single or double) because they aren't precise and can't reliably be compared unless you use a tolerance

Integers are limited to 32K, so long integer is a better choice for a key field. Another consideration is math on the field -- if you will calculate statistics and the sum could be greater than 32K, then better to use a long integer for the field data type. Plus, as @ebs17 noted, integers will be stored in a long integer space anyway.

Sometimes when decimal places are needed, I use Currency (4, and only 4, decimal places) because the decimal data type isn't recognized by VBA. In those cases, I change format to not show the currency symbol.