Passing Arguments in Functions

Topic

New Reply

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?

Reply | Quote

Replies

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

45994-rz-autocorrect

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

boo,

Consider the following UDFs

Code:

Public Function a()
a = [a1] + [a2] + [a3]
End Function


Public Function b()
b = [b1] + [b2] + [b3]
End Function

46212-boo1

Better yet, let’s look at the following pair of index/match functions converted to UDFs:

Code:

[COLOR=”#0000FF”]=Index(A1:B10,Match([I]lookup[/I], A1:A10,0),2)[/COLOR]
Public Function a(lookup)
a = WorksheetFunction.Index(Range(“A1:B10”), WorksheetFunction.Match(lookup, Range(“A1:A10″), 0), 2)
End Function

[COLOR=”#0000FF”]=Index(D1:E10,Match([I]lookup[/I], D1:D10,0),2)[/COLOR]
Public Function b(lookup)
b = WorksheetFunction.Index(Range(“D1:E10”), WorksheetFunction.Match(lookup, Range(“D1:D10”), 0), 2)
End Function

The following formula uses 5 repetitive Index/Match formulas. With a bit of shorthand (UDFs), it can be greatly simplified:

Code:

=IF(INDEX(A1:B10,MATCH(8, A1:A10,0),2)=”deck”,INDEX(A1:B10,MATCH(3, A1:A10,0),2)&INDEX(D1:E10,MATCH(7, D1:D10,0),2),INDEX(A1:B10,MATCH(1, A1:A10,0),2)&INDEX(D1:E10,MATCH(9, D1:D10,0),2))

To

=IF(a(8)=”deck”,a(3)&b(7),a(1)&b(9))

46215-Boo2

Would this be short enough for you?

HTH,
Maud

Reply | Quote