Can a UDF access named ranges

[JenniferMurphy]

Is there a way a UDF can access cells in a named range in the row just above the row from where it was called?

In this example, The "Parm1" is assigned "$D:$D" and "Parm2" is assigned "$E:$E". I would like a UDF that would add up the values in Parm1 and Parm2 in the row one up from the calling row.

For example, if called from C8 (as shown in F8), it would add D7 + E7 = 5 + 9 and return 14.

My actual UDF is much more complicated. But if I can get this simple one to work, I can handle the rest.

I already have it working passing the exact cells as parameters as in G8, but the actual UDF takes several more parameters and I want to minimize the chances for typos.

 

[MARK858]

Probably a much shorter way of doing it but as a quick thought maybe...

Function Jennifer(Parm1 As Range, Parm2 As Range, xrow As Long)
Jennifer = Application.Sum(Intersect(Parm1, Rows(ActiveCell.Offset(xrow).Row)), Intersect(Parm2, Rows(ActiveCell.Offset(xrow).Row)))
End Function

Book2
	A	B	C
1	Result	Parm1	Parm 2
2		18	1
3	19	12	3
4	15	11	14
5	15	6	16
Sheet1

Cell Formulas
Range	Formula
A3:A4	A3	=Jennifer(Parm1,Parm2,-1)
A5	A5	=Jennifer(Parm1,Parm2,-2)

Named Ranges
Name	Refers To	Cells
Parm1	=Sheet1!$B:$B	A3:A5
Parm2	=Sheet1!$C:$C	A3:A5

or

Function Jennifer()
Jennifer = Application.Sum(Intersect(Range("Parm1"), Rows(ActiveCell.Offset(-1).Row)), Intersect(Range("Parm2"), Rows(ActiveCell.Offset(-1).Row)))
End Function

Book2
	A	B	C
1	Result	Parm1	Parm 2
2		18	1
3	19	12	3
4	15	11	14
5	25	6	16
Sheet1

Cell Formulas
Range	Formula
A3:A5	A3	=Jennifer()

Named Ranges
Name	Refers To	Cells
Parm1	=Sheet1!$B:$B	A3:A5
Parm2	=Sheet1!$C:$C	A3:A5

 

[JenniferMurphy]

Probably a much shorter way of doing it but as a quick thought maybe...

Function Jennifer(Parm1 As Range, Parm2 As Range, xrow As Long)
Jennifer = Application.Sum(Intersect(Parm1, Rows(ActiveCell.Offset(xrow).Row)), Intersect(Parm2, Rows(ActiveCell.Offset(xrow).Row)))
End Function

That's great, but I'd like to break it up. I tried this, but it gets a value error.

Function Jennifer(pParm1 As Range, pParm2 As Range)

Dim SrcRow As Long
SrcRow = ActiveCell.Row - 1

Dim Parm1 As Range
Parm1 = Intersect(Parm1, Rows(ActiveCell.SrcRow.Row))

Dim Parm2 As Range
Parm2 = Intersect(Parm2, Rows(ActiveCell.SrcRow.Row))

Jennifer = Parm1 + Parm2

End Function

 

[MARK858]

Function Jennifer(pParm1 As Range, pParm2 As Range)

Dim SrcRow As Long, Parm1 As Double, Parm2 As Double
SrcRow = ActiveCell.Row - 1


Parm1 = Intersect(pParm1, Rows(SrcRow))


Parm2 = Intersect(pParm2, Rows(SrcRow))


Jennifer = Parm1 + Parm2

End Function

Book2
	A	B	C
1	Result	Parm1	Parm 2
2		18	1
3	19	12	3
4	15	11	14
5	25	6	16
Sheet1

Cell Formulas
Range	Formula
A3:A5	A3	=Jennifer(pParm1,pParm2)

Named Ranges
Name	Refers To	Cells
pParm1	=Sheet1!$B:$B	A3:A5
pParm2	=Sheet1!$C:$C	A3:A5

 

[JenniferMurphy]

Function Jennifer(pParm1 As Range, pParm2 As Range)

Dim SrcRow As Long, Parm1 As Double, Parm2 As Double
SrcRow = ActiveCell.Row - 1

Parm1 = Intersect(pParm1, Rows(SrcRow))

Parm2 = Intersect(pParm2, Rows(SrcRow))

Jennifer = Parm1 + Parm2

End Function

That works.

But there are some strange behaviors.

If I change one of the values (D8), they all change to the same result:

If I recalculate the sheet (ACS+F9), they all change to zero:

And if I delete a column, such as H, they all turn to value errors:

???

 

[Fluff]

How about this change

SrcRow = Application.Caller.Row - 1

 

[veyselemre]

Function Jennifer(Parm1 As Range, Parm2 As Range)
    Dim rw As Long
    rw = Application.Caller.Row - 1
    Jennifer = Parm1.Cells(rw) + Parm2.Cells(rw)
End Function

 

[JenniferMurphy]

How about this change

SrcRow = Application.Caller.Row - 1

That works, thanks.

 

[JenniferMurphy]

Function Jennifer(Parm1 As Range, Parm2 As Range)
    Dim rw As Long
    rw = Application.Caller.Row - 1
    Jennifer = Parm1.Cells(rw) + Parm2.Cells(rw)
End Function

That works even better. Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.