question on user-defined formula calculation mode: can it be instantaneous?

[jerjer]

Hello all,
I'm a beginner in the realms of user-defined formulas.
My problem is that the one that I just wrote doesn't update when its
arguments are updated: it will only update when I edit the cell it is
on, and validate the cell.

My formula is called GetNthInRange(r as range, n as integer, size as
integer).

The formula is supposed to return the nth top non-blank cell in a
1-cell wide, size-cell long range.
for instance, if I have a column that looks like this:
A
1
2
3 1
4 2
5 3
6

well, GetNthInRange(A1,1,5) returns the first non-blank cell in the
A1:A5 range, that is A3 or 1. GetNthInRange(A1,2,5) returns the second
one, A4 or 2. etc.

It works and provides the results that I expect when I first run it.

The problem is that if, say, I enter in A6, =GetNthInRange(A1,2,5),
(which evaluates to 2), if I then update A4, the value in A6 will not
change. If I do edit A6 and press enter, then the value of A6 will be
the new value of A4.

The code of the function is as follows
GetNthInRange = r.Offset(size -
(Application.WorksheetFunction.CountA(Range(r, r.Offset(size - 1))) -
(n - 1)))

Is there anything I can do to make it update instantaneously?
many thanks
jerome

 

[Bernard Liengme]

You need to make the UDF volatile so that it is recalculated everytime ANY
cell on the worksheet is recalculated.

Easy to do:
Function phoobar(cat, dog)
Application.Volatile
.... rest of code

best wishes

 

[jerjer]

thanks a lot, Bernard! it worked fine! I'm learning a little everyday!!