UDF Not Updating When Data Changes

[44ron]

Hi, I’m using Excel 2007 and I have a UDF which calculates the total of the largest x number of values in a range.
I.e. in the range 1 2 3 4 5 6 7 8 9 10 where x is 2 the result would be 19 (9 + 10).

The function works but does not update (or more accurately throws a #VALUE error) when either the range value or value of x is changed - if I force a recalculation the values update correctly.

My code is as follows:

valrange is the range of values from which I wish to extract the largest values
discs is the number of relevant values (x in my example)

Public Function Discards(valrange As Range, discs As Integer) As Double

Application.Volatile
Discards = 0

Do While discs > 0
Discards = Discards + Application.Large(Range(valrange.Address), discs)
discs = discs - 1
Loop

End Function

I'd really appreciate any suggestions as to how I can make this recalculate automatically!
Thanks

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to amend the thread title accordingly and add CODE tags.

Thanks.

All done, sorry about that!

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to amend the thread title accordingly and add CODE tags.

Thanks.