Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Passing Arguments in Functions

    This seems so simple. I do this in coding languages; there's got to be a way to do this in Excel.

    Suppose I have a simple function in a cell, like =if(). And, a chunk of the code in my arguments is repeated. Is there a shortcut to reduce typing.

    Some pseudocode might look like this. I would normally write this in a cell:

    =if(A1>0,A1,0)

    Is there a way to make the middle argument reference all or part of the first argument. As in:

    =if(A1>0,LHS of $1,0)

    In that pseudocode, LHS of $1 is "left hand side of the first argument".

    Obviously, this is pointless for this simple example, but it could be very useful if the first argument is lengthy, and much of it is repeated in the second argument. OR ... am I missing the point, and the preferred methodology for Excel is to put that lengthy argument in its own cell, and then call that address twice?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    re: Is there a shortcut to reduce typing.

    You can use the Excel AutoCorrect feature:
    1.After you click the File>Excel Options item, you will see the Excel Options Window;
    2.Click the Proofing button in left panel
    3.Click the [AutoCorrect Options...] button
    4.Select the [AutoCorrect] tab
    5.Scroll to bottom of the 'Replace: With:' section

    rz-autocorrect.GIF

    Now, I prefix my personal text shortcuts with a z (so I always find them at the bottom of the list) and I use a combination that is unlikely to occur during 'normal' typing.
    For example, I might set
    Replace:
    zz1
    With:
    This is a long function that I use a lot

    Now, whenever you type zz1 it will be replaced with whatever you set as the text shortcut.

    zeddy

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The short answer is no, I'm afraid. The only function that does anything similar is IFERROR. For other functions you need to use a helper column.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    We can call this one solved.

    Zeddy: that's not a bad idea. I have some of those, but I don't think I have any for portions of formulas.

    Rory: Oh well. I am OK with a helper column. Sometimes I like that Excel is not a programming language, and using my whitespace is something I shouldn't be afraid of. Other times (like this) I want it to work like a language.

    EOM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •