VBA code optimization - why using long instead of integer?

  • Thread starter Thread starter Thao
  • Start date Start date
T

Thao

Can anyone explain to me that why VBA converts all integer values to long to
make the program run faster? Mean that Integer is meaningless?

"VBA converts all integer values to type Long, even if they are declared as
type Integer. In fact, Long variables might be slightly faster because VBA
does not have to convert them."
 
Short answer, in 32-bit systems 2 byte integers are converted to 4 byte
Longs. There really is no other way so that respective bits correctly line
up for any form of processing. Consider the following

MsgBox Hex(-1) = Hex(65535) ' = True

Obviously -1 does not equal 65535 yet the computer is returning the correct
answer, namely
"FFFF" = "FFFF"

However had we coerced the -1 to a long first we would have got the right
answer (the 65535 being greater than 32k is automatically a long)

MsgBox Hex(-1&) = Hex(65535) ' = False
"FFFFFFFF" = "FFFF"

Generally there is no point in VBA to declare "As Integer" in modern
systems, except perhaps for some legacy API's that expect to receive an
Integer.

Regards,
Peter T
 
But if the compiler converts integer variable to long, it means that
declaration variables as integer or long is not important anymore since at
run-time, all of them are long.
Eg: 2 same VBA codes, one is using Integer, the other is using Long. At
run-time,using the same computer will these two codes finish running at the
same time?

Why do they still recommend that we should use Long when writing the VBA
code instead of Long since both will be Long after compiling?

Thanks and best regards,
Thao Vo
 
AFAIK the compiler does not convert a number from one data type to another.
However an Integer gets converted to a long at the moment it is used, in
effect an extra process.

In practise I don't suppose you'd notice the difference between calculating
with Integers vs Longs (assuming numbers within 32k). However it does not
serve any useful purpose to declare Integer variables.

A common catch is when an Integer variable is used to refer to a cell row,
then wonder why the code fails when it tries to refer to any row over 32767

Regards,
Peter T
 
If the integer is automatically converted to a long, why do we still get the
overflow message when we use itegers and try to execute a procedure that
exceeds the integer capacity?
 
If the integer is automatically converted to a long, why do we still get the
overflow message when we use itegers and try to execute a procedure that
exceeds the integer capacity?
Click to expand...

Maybe because you *told* the compiler it was an integer:

Oh, how I hate this dumb machine,
I wish the boss would sell it.
It won't do what I want it to,
But only what I tell it.

More seriously, Peter has mentioned the problem of communicating with
legacy APIs which might be expecting a 16 bit. Garbage might occur if
silent overflows happened in that context.

Maybe not very elegant, but I have exploited the overflow message for
crude debugging purposes. If I have a while loop which increments a
counter with each pass and I don't know ahead of time how many passes
should occur but I know that it is in the hundreds or thousands rather
than in the tens of thousands, I'll declare the counter an integer so
that the program crashes quickly if the loop is executing too many
times
 
Although behind the scenes the 16-bit value is converted to a 32 bit for
processing (an Integer to a Long) that has nothing to do with the data type
of the named variable (or return type of say a function) that "points" to
the value in memory. Also the value remains in memory as its original data
type. So you still need to be careful with not only data types of variables
but even the data types of simple numbers in some cases, to illustrate

dim n as Long
n = 32767 + 1 ' overflow

It fails because of the attempt to create 32768 as an integer, even though
"n" is declared as a Long. To fix need to convert one of those numbers to a
Long before doing the addition, by appending with a & or with the cLng
function.

Regards,
Peter T





Regards,
Peter T
 
Back
Top