Alias within Formula

[Roderick_E]

While I have been working with Excel for several decades, I still a lot of blind spots.
I was wondering if I missed that there is a way to alias a segment of a formula within the entire formula so that within the formula you can refer to the alias result? My example below is very simplistic and perhaps even if aliasing were possible, it wouldn't be worth the effort. Also, before someone suggests, I already know that "helper cells" are often used as aliases or /and named ranges

EXAMPLE AS IS:

=if(len(a1)<len(b1),len(a1),len(b1))[ code]

[B]EXAMPLE OF ALIAS:[/B]
[CODE]=if(alias("res1",len(a1))<alias("res2",len(b1)),aliasres("res1"),aliasres("res2"))[ code]<="" html=""></alias("res2",len(b1)),aliasres("res1"),aliasres("res2"))[></len(b1),len(a1),len(b1))[>

 

[Oaktree]

The GETV and SETV functions that are part of the morefunc.dll add-in might be what you're looking for.

 

[Roderick_E]

The GETV and SETV functions that are part of the morefunc.dll add-in might be what you're looking for.

Thanks for the direction. I found this rather than loading an entire add-in

[COLOR=#333333]Public Function V(Optional vrnt As Variant) As Variant[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]' Stephen Dunn[/COLOR]
[COLOR=#333333]' 2002-09-12[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]Static vrntV As Variant[/COLOR]
[COLOR=#333333]If Not IsMissing(vrnt) Then vrntV = vrnt[/COLOR]
[COLOR=#333333]V = vrntV[/COLOR]
[COLOR=#333333]End Function[/COLOR]

Usage example:

[COLOR=#333333]=IF(ISNA(v(VLOOKUP("q",F2:G5,2,0))),0,v())[/COLOR]

 

[Joe4]

=IF(ISNA(v(VLOOKUP("q",F2:G5,2,0))),0,v())

Also note that the IFERROR function may suffice here, i.e.

[COLOR=#333333]=IFERROR(VLOOKUP("q",F2:G5,2,0),0)[/COLOR]

 

[Roderick_E]

For my purposes, I'm going to use =v() to save the result of match(criteria, range,0) so that I need not repeat it it further in a complex formula. Yes, I could use a named range to store the match() but wanted something I could use quickly. Would be cool to modify v() to allow for saving multiples like:

=v(1,value or text)
=v(1)

It could distinguish with the comma. If not comma in args then user wants return result otherwise save result.

 

[Roderick_E]

For my purposes, I'm going to use =v() to save the result of match(criteria, range,0) so that I need not repeat it it further in a complex formula. Yes, I could use a named range to store the match() but wanted something I could use quickly. Would be cool to modify v() to allow for saving multiples like:

=v(1,value or text)
=v(1)

It could distinguish with the comma. If not comma in args then user wants return result otherwise save result.

Further on my thinking here, if this function is able to save a STATIC variant, what keeps it from saving an array of variants?

{151,"test variant2",5111,"test variant4"}

Then returning them with

=v(1) -- result = 151
=v(4) -- result = "test variant4"

Can anyone show me how to modify the original function to do this?

 

[Joe4]

Can anyone show me how to modify the original function to do this?

I am afraid I cannot help with that.

I need not repeat it it further in a complex formula.

If you have a "complex" function, you may want to consider creating your own function in Excel to do it (create a User Defined Function).
That is usually what I try to do instead of trying to build or maintain some complex Excel formula.

 

[Oaktree]

Further on my thinking here, if this function is able to save a STATIC variant, what keeps it from saving an array of variants?

{151,"test variant2",5111,"test variant4"}

Then returning them with

=v(1) -- result = 151
=v(4) -- result = "test variant4"

Can anyone show me how to modify the original function to do this?

See Chip's write-up here: http://www.cpearson.com/excel/returningarraysfromvba.aspx

 

[Roderick_E]

I think I'm dense. So, suppose I want to initialize an array with the first item being the word "default"

{"default"}

And after that, using my UDF I want to be able to add more items...

=doarray(1,"new item")

Which becomes:

{"default","new item"}

and if I use my UDF without a comma it returns the item of the index such as:

=doarray(1)

result = "new item"

Further, if I do:

=doarray(1,"change item") it updates array.

Further, if I do:

=doarray(5,"next item")

it would count current items length and automatically put next item as index 2 since there are not 5 items.

Any ideas how to do this AND make it static?

 

[Roderick_E]

I think I'm getting close with something like this:

Public Function Vget(idx As Integer, Optional vrnt As Variant) As Variant
Static strunbound() As String
If IsMissing(vrnt) Then
Vget = strunbound(idx)
Exit Function
End If
strunbound = Split(vrnt, ",")
ReDim Preserve strunbound(LBound(strunbound) To UBound(strunbound))
For i = LBound(strunbound) To UBound(strunbound)
MsgBox strunbound(i)
Next i
End Function