UDF - using variable list as sensitivity list

[Ilushka]

Win 7
Excel 2007

Hi,

I am trying to achieve a UDF which re-calculates only when specific cell values change, the function must be non volatile.

I tried to use the variable list as a sensitivity list such that my UDF re-calculates only if specific cell value change.

I would like to 'look' at the cells based on the cell in which the UDF is contained, therefore I pass the cells ROW() and COLUMN() to use as its address.

Basically the UDF would return the sum of the cells around itself. Sum of the cell to the left, cell above and cell to the right.

eg. myUDF is in cell F31 and the cells which I wish to be in the sensitivity list are one left(E31), one up(F30), one right (G31)

=myUDF(ROW(),COLUMN(),E31,F30,G31)

Function myUDF(cell_Row As Integer, cell_Column As Integer, cell_1 As Range, cell_2 As Range, cell_3 As Range)
Dim x As Double
Dim temp As Range

Set temp = ThisWorkbook.ActiveSheet.Cells(cell_Row, cell_Column)
x = temp.Offset(-1, 0).Value + temp.Offset(0, -1).Value + temp.Offset(0, 1).Value

myUDF = x
End Function

This works perfectly except that I do not wish to pass the cell addresses due to issues in case where a row is inserted directly above the UDF cell, this would cause the UDF to re-calculate when the value of the cell 2 rows above changes, not the cell directly above.

One solution was to use IDIRECT() to offset from the UDF cell, although INDIRECT() being volatile defeats the point of the sensitivity list.

=myUDF(ROW(), COLUMN(),INDIRECT(ADDRESS(ROW(),COLUMN()-1)),INDIRECT(ADDRESS(ROW()-1,COLUMN())),INDIRECT(ADDRESS(ROW(),COLUMN()+1)))

I will be using this UDF many times in the spreadsheet and being volatile will cause long calculation delays.

I Have also tried using INDEX() as it is not volatile, but INDEX() requires the cell reference once again, does not suite as per the insert issue.

If I use:

=INDEX(ADDRESS(ROW(),COLUMN()-1,4),0,0)

this will return the cell address and does not update if the cell value has changed, as the address does not change.

This is due to the fact that ADDRESS() returns text and INDEX() requires a cell reference.
So i made another UDF which returns a cell reference:

Function myAddress(row As Integer, col As Integer)
Application.Volatile
Dim x As Range
Set x = ThisWorkbook.ActiveSheet.Cells(row, col)
myAddress = x
End Function

I then used this as the input to INDEX(),

INDEX(myAddress(ROW(), COLUMN()-1),0,0)

this resulted in INDEX() returning the value of the respective cell correctly although it does not update when the respective cells value changes, only when I click and ENTER in the cell containing the UDF.

I hope my description is not too confusing, I am out of ideas

<tbody>
</tbody>

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

Unless there's a reason you want to reference the cell holding the formula by Row() and Column(), you can remove those parameters by using Application.Caller.

A modified function could be written like this...

Function myUDF(cell_1 As Range, cell_2 As Range, cell_3 As Range)
   With Application.Caller
      myUDF = .Cells(1, 0).Value + .Cells(0, 1).Value + .Cells(1, 2).Value
   End With
End Function

One approach to have non-volatile references to adjacent cells that will persist if cells are inserted would be to use named ranges with relative references.

To set that up, select Cell B2, then define these three names
"CellToLeft" refers to =A2 (note there are no "$")
"CellToRight" refers to =C2
"CellAbove" refers to =B1

Once setup you could use this formula to call the UDF
=myUDF(CellToLeft,CellAbove,CellToRight)


EDIT: I just realized you could further simplify the UDF by using the passed references...

Function myUDF(cell_1 As Range, cell_2 As Range, cell_3 As Range)
      myUDF = cell_1.Value + cell_2.Value + cell_3.Value
End Function

 

[Ilushka]

Thank you Jerry, that worked perfectly