Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional arguments
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional arguments
"KD" wrote:
"Optional ByVal LB As Long = -1&, _" What does the "&" after the -1 mean? 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional arguments
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 "KD" wrote in message 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional arguments
joeu2004 wrote:
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). 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). -- He'd like you to think he's got a heart of stone, but really, he's got a heart of marshmallow. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional arguments
"Auric__" wrote:
joeu2004 wrote: Dim n As Long n = 30000 + 1000 [....] 30000 + 1000 won't (normally) result in overflow [....] The overflow *does* occur with 32000 + 1000 Yes, and that was my intent. A typo. Thanks for catching it. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional arguments
Thks guys!
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional arguments
Thanks guys!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Optional Arguments in Custom Fuctions | Excel Programming | |||
Optional arguments and IsMissing | Excel Programming | |||
optional args in UDF | Excel Programming | |||
Optional Linking | Excel Discussion (Misc queries) | |||
Representation of optional parameters in Function arguments window | Excel Programming |