Named ranges with UDF's

[TickTock]

This is a repeat of an old thread which ended with an "it's impossible" verdict. I really can't accept that there is no solution so I am re-asking the question.

http://www.mrexcel.com/forum/showthread.php?t=55657&highlight=named+array+udf

Essentially I like to name rows and columns to make the formulas easier to read and audit. The problem is if you use these names as parameters of a UDF (and some excel built-in functions like SUM()), the UDF receives the entire column/row as a list instead of just the corresponding entry as a scalar.

Take the following UDF:

Function test(a As Integer, b As Integer)
test = a + b
End Function

Then create two named ranges cla=A2:A9 and clb=B2:B9
If I sum in a formula (column C below "=cla+clb"), it behaves as desired. Similarly if I use my udf without the named values, it behaves as desired (column D below "=test(A2,B2)"). However, if I try to feed the names to the udf (column E "=test(cla,clb)"), it doesn't work.

A B C D E
1 cla clb "=cla+clb" "=test(A2,B2)" "=test(cla,clb)"
2 1 9 10 10 #VALUE!
3 2 10 12 12 #VALUE!
4 3 11 14 14 #VALUE!
5 4 12 16 16 #VALUE!
6 5 13 18 18 #VALUE!
7 6 14 20 20 #VALUE!
8 7 15 22 22 #VALUE!
9 8 16 24 24 #VALUE!

If I could figure out the row number of the cell calling the udf, I could index the array. However adding a third integer parameter and passing ROW() [=test(cla,clb,ROW()] is a very ugly solution in the actual use model. I have many UDF's being called in some rather long formulas in the cells. I have a small library of UDFs for many missing mathematical functions and really don't want to have to add the ",row()" hack every time I call one of these functions with a named range. Anyone have any ideas how to do this?

Thanks!

 

[ZVI]

Hi,

What values are you expecting instead of #VALUE! in your example?

Vladimir

 

[jindon]

Of-course,

Function test(a As Integer, b As Integer)

but what you are passing Range for those arguments.
try

Function test(a As Range, b As Range)
   test = Evaluate(a.Address & "+" & b.Address)
End Function

 

[ZVI]

If you expect the same values in column E as in column D, try it:

<font face=Courier New>
Function test(a As Range, b As Range)
  Dim i As Long
  i = Application.Caller.Row
  test = a.Cells(i - a.Row + 1, 1) + b.Cells(i - b.Row + 1, 1)
End Function</FONT>

 

[TickTock]

If you expect the same values in column E as in column D, try it:

<font face=Courier New>
Function test(a As Range, b As Range)
  Dim i As Long
  i = Application.Caller.Row
  test = a.Cells(i - a.Row + 1, 1) + b.Cells(i - b.Row + 1, 1)
End Function</FONT>

Sweet! Thanks ZVI, this works. That "i=Application.Caller.Row" was the key.

 

[ZVI]

Glad it helped, kind regards!