Optional arguments

Discussion in 'Microsoft Excel Programming' started by KD, Aug 21, 2012.

  1. KD

    KD Guest

    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, Aug 21, 2012
    #1
    1. Advertisements

  2. KD

    joeu2004 Guest

    "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.
     
    joeu2004, Aug 21, 2012
    #2
    1. Advertisements

  3. KD

    Peter T Guest

    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?
     
    Peter T, Aug 21, 2012
    #3
  4. KD

    Auric__ Guest

    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.
     
    Auric__, Aug 21, 2012
    #4
  5. KD

    joeu2004 Guest

    "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.
     
    joeu2004, Aug 21, 2012
    #5
  6. KD

    KD Guest

    Thks guys!
     
    KD, Aug 22, 2012
    #6
  7. KD

    KD Guest

    Thanks guys!
     
    KD, Aug 22, 2012
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dan

    Making GIFs from anything, with optional resizing

    Dan, Jan 30, 2004, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    246
    David McRitchie
    Jan 30, 2004
  2. ExcelMonkey

    Optional Arguements for Functions

    ExcelMonkey, Feb 4, 2004, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    239
    Rob van Gelder
    Feb 4, 2004
  3. compound

    Representation of optional parameters in Function arguments window

    compound, Jan 17, 2006, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    176
    compound
    Jan 17, 2006
  4. pinkfloydfan

    Optional arguments and IsMissing

    pinkfloydfan, Mar 18, 2007, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    131
    pinkfloydfan
    Mar 18, 2007
  5. Guest

    Optional Arguments in Custom Fuctions

    Guest, Sep 27, 2007, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    169
    Guest
    Sep 27, 2007
Loading...

Share This Page