1. 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. 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. 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.

4. 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.

5. Excel Shorthand...VBA

boo,

Consider the following UDFs

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

Public Function b()
b = [b1] + [b2] + [b3]
End Function```
boo1.png

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

Code:
```=Index(A1:B10,Match(lookup, A1:A10,0),2)
Public Function a(lookup)
a = WorksheetFunction.Index(Range("A1:B10"), WorksheetFunction.Match(lookup, Range("A1:A10"), 0), 2)
End Function

=Index(D1:E10,Match(lookup, D1:D10,0),2)
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))```
Boo2.png

Would this be short enough for you?

HTH,
Maud

Posting Permissions

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