UDF - using variable list as sensitivity list

Ilushka

New Member
Joined
Feb 6, 2014
Messages
2
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)


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

Code:
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.
Code:
=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:
Code:
=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:
Code:
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(),
Code:
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>
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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...

Code:
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...
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top