alias for the INDIRECT function

T

Takeadoe

Gang - I'm using the INDIRECT function lots of times in my cell formula
and it is taking up a lot of space. Is there a way to assign an alias
"IND" e.g., to a function and use that in a cell reference instead of
the actual function name?

Regards,

Mike
 
G

Gary''s Student

Maybe for simple cases. If A1 contains 123 and Z100 contains
=INDIRECT("A1") it will also display 123

The tiny UDF

Function ind(s As String)
ind = Range(s).Value
End Function

will do the same thing. If the expression is complex, it may need EVALUATION
 
H

Harlan Grove

Gary''s Student wrote...
....
The tiny UDF

Function ind(s As String)
ind = Range(s).Value
End Function

will do the same thing. If the expression is complex, it may need EVALUATION
Click to expand...
....

But serves no purpose. UDFs *always* recalculate more slowly than
built-in functions, and they take more memory in RAM and storage space
on disk. Excel doesn't store formulas as text. Excel converts formulas
into a binary form when you enter them, and built-in functions are
represented as particular byte codes. UDFs are also represented as byte
codes. The end result is no difference in RAM or disk storage used for
the cell formulas, additional RAM and disk storage used for the VBA
module containing the udf's code, and much slower recalculation time.

Why would anyone who knows what s/he's doing use udfs for this?
 
G

Gary''s Student

Harlan:

You are correct in all regards. Using a UDF in this instance is silly.
However, you must admit that:
1. INDIRECT has 8 letters and IND only 3
2. IND is much easier to spell
 
G

Gord Dibben

GS

Which just happens to be what the OP wanted.

A shorter spelling of INDIRECT.

In that respect, your UDF "serves a purpose".


Gord



Harlan:

You are correct in all regards. Using a UDF in this instance is silly.
However, you must admit that:
1. INDIRECT has 8 letters and IND only 3
2. IND is much easier to spell
Click to expand...

Gord Dibben MS Excel MVP
 
T

Tushar Mehta

In that case, wouldn't it make more sense to set up a "auto correct" entry
that maps IND into INDIRECT?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
R

Ragdyer

The OP mentions about Indirect "taking up a lot of space".

That *doesn't* sound like it's a typing issue, but perhaps a formula length
issue, where the formula bar may be covering some of the cells.
 
B

Biff

Ragdyer said:
The OP mentions about Indirect "taking up a lot of space".

That *doesn't* sound like it's a typing issue, but perhaps a formula
length issue, where the formula bar may be covering some of the cells.
Click to expand...

Yep, sounds like a monster formula.

Biff
 
H

Harlan Grove

Biff wrote...
....
Yep, sounds like a monster formula.
Click to expand...

In which case replacing INDIRECT with IND would only be a bandaid (and
since function names can be as short as 1 character, wouldn't I be even
better than IND?). If a udf were useful, it could do a lot more than
just replace INDIRECT.

Which gets to the core of the matter. There could be better ways to
rewrite the OPs formula(s), but if the OP won't show us the actual
formula(s), then all we can do is idly speculate.
 
B

Biff

Harlan Grove said:
Biff wrote...
...

Which gets to the core of the matter. There could be better ways to
rewrite the OPs formula(s), but if the OP won't show us the actual
formula(s), then all we can do is idly speculate.
Click to expand...

Exactly what I was thinking.

Biff
 
Top