How to pass one cell in a named range to a UDF?

[JenniferMurphy]

I make use of a lot of named ranges so as to make my formulas more readable and more resistant to errors due to moving cells around. One of my favorite methods is to name entire columns and then use those names in formulas. Those formulas will then use the cells in the names ranges that are in the same row as the formulas. Here's a simple example. Column C has been assigned the name "A", column D has been assigned the name "B".

View attachment 4086

Column E combines the values in columns C & D using absolute addressing. Column G obtains the same result using the named ranges.

This works well for in-sheet formulas. It doesn't work nearly as well if I try to pass these named ranges to a UDF.

Column I passes the values to simple UDFs using absolute addressing. Here's the UDF code:

Function AddEm(V1, V2) As Double
  AddEm = V1 + V2
End Function

Function JoinEm(V1, V2) As String
  JoinEm = V1 & V2
End Function

Column K tries it with the named ranges and gets a Value error.

I discovered a way to get around this design failure by performing an arithmetic operation on the named range parameters, which is pretty silly for the character values in rows 7 & 8.

Here's my question: Is there some code I can put in the UDFs so that they will work whether they are passed absolute addresses or named ranges?

PS: I hope I got the images right. The image inserting mechanism is a little flakey or maybe I just don;t know how to operate it.

 

[JenniferMurphy]

Why does my first image show as "View attachment 4086" but the second one shows as an actual image?

 

[shg]

The first instance works because of an implied intersection..

You could make that explicit if myRow referred to the row in which it appears, and then use

=addem(myRow A, myRow B)

 

[Tetra201]

... I discovered a way to get around this design failure by performing an arithmetic operation on the named range parameters, which is pretty silly for the character values in rows 7 & 8...

This is because when you use =joinem(+A,+B), you are passing individual cell references/values to the UDF, whereas when you use =joinem(A,B), you are passing ranges/arrays.

... Here's my question: Is there some code I can put in the UDFs so that they will work whether they are passed absolute addresses or named ranges?..

Here is a crude UDF (Note: entire columns must be named):

Function JoinEm(V1, V2) As String
    Dim x1, x2
    If VarType(V1) > 8000 Then x1 = V1(Application.Caller.Row) Else x1 = V1
    If VarType(V2) > 8000 Then x2 = V2(Application.Caller.Row) Else x2 = V2
    JoinEm = x1 & x2
End Function

 

[JenniferMurphy]

The first instance works because of an implied intersection..

Ok, but if that syntax implies an intersection operation in that situation, why doesn't it also imply one in the other? I guess expressions are inherently scalar operations, whereas function calls can pass any kind of parameter. This seems like another design flaw to me. A syntax should work the same way everywhere. (sigh)

You could make that explicit if myRow referred to the row in which it appears, and then use

=addem(myRow A, myRow B)

That would require that I name every row, which would be a bit of a problem if I have 500 rows. It also defeats the whole purpose of having a syntax that works on any row.

I did try this, but Excel wouldn't accept it. Why not?
=addem(row() A, row() B)

 

[JenniferMurphy]

This is because when you use =joinem(+A,+B), you are passing individual cell references/values to the UDF, whereas when you use =joinem(A,B), you are passing ranges/arrays.

Here is a crude UDF (Note: entire columns must be named):

Function JoinEm(V1, V2) As String
    Dim x1, x2
    If VarType(V1) > 8000 Then x1 = V1(Application.Caller.Row) Else x1 = V1
    If VarType(V2) > 8000 Then x2 = V2(Application.Caller.Row) Else x2 = V2
    JoinEm = x1 & x2
End Function

Hey, that works!

I did a little testing. When the UDF detects a range (VarType > 8000), these two can be obtained:

V1.Rows.Count V2.Columns.Count
I would imagine that there is a way to use these values along with some others to obtain the value in the cell that is the intersection of the column named by V1 and the row of the caller. If there is, I would put that code in another UDF and call it from any UDF that needs it. That way, I could pass an absolute address, a named range that is an entire column, or a named range that is a piece of a column.

 

[shg]

That would require that I name every row

No it doesn't.

Select A1, Name Manager, myRow Refers to: =1:1 (no dollar signs). myRow always refers to the row in which it appears.

You can do the same thing for myCol.