Optional arguments

  • Thread starter Thread starter KD
  • Start date Start date
K

KD

I found some code by Chip Pearson which is mostly self-explanatory
except this portion:

"Optional ByVal LB As Long = -1&, _"

What does the "&" after the -1 mean?
 
KD said:
"Optional ByVal LB As Long = -1&, _"
What does the "&" after the -1 mean?
Click to expand...

It is completely unnecessary in this context.

For every data type, there is a character that implicitly assigns a type to
a variable name or number when used as a prefix.

-1& says -1 should be treated as type Long. It is unnecessary here because
you are simply assigning -1 to a type Long variable.

It is also unnecessary if you specify a number larger than 32767, e.g.
33000. VBA knows to treat that as Long since it is larger than type
Integer.

The "&" suffix becomes important when the data type of an expression is
ambiguous. For example:

Dim n As Long
n = 30000 + 1000

results in an overflow error since 30000 and 1000 are both interpreted as
type Integer. Consequently, VBA tries to perform type Integer arithmetic
(addition). The following fixes the problem:

n = 30000& + 1000

In contrast, the following works without the use of "&":

n = 33000 + 1000

because 33000 is type Long; ergo, 1000 is "converted" to type Long, and type
Long arithmetic is performed.
 
As joeu2004 has explained the & defines the value -1 as a Long.

Also as explained in this context it's not necessary, however predefining as
a Long reserves matching 32bit/4-byte spaces in memory which means
ultimately no coercion is required in assigning the value to the variable.

In theory it makes the code more efficient though in practice unlikely to be
noticeable in modern machines. Even if only by convention it's normal to
fully define constants, eg

Const cNum as Long = 123&

Chip Pearson is a stickler for detail :-)

Regards,
Peter T
 
joeu2004 said:
The "&" suffix becomes important when the data type of an expression is
ambiguous. For example:

Dim n As Long
n = 30000 + 1000

results in an overflow error since 30000 and 1000 are both interpreted
as type Integer. Consequently, VBA tries to perform type Integer
arithmetic (addition).
Click to expand...

Minor detail: 30000 + 1000 won't (normally) result in overflow (unless you're
using a data type that can't handle the result, i.e. Byte, and then only when
assigning the result to the variable, not during the actual addition). Max
value of an Integer is 32767, and 32767 > (30000 + 1000). The overflow *does*
occur with 32000 + 1000 (or any equation that adds up to more than &h7FFF).
 
Back
Top