What are volatile functions and why would I want to activate them in the code below?
Function NumUniqueValues(Rng As Range) As Long
Dim myCell As Range, UniqueVals As New Collection
'force the function to recalculate when the range changes
Application.Volatile
On Error Resume Next
'the following places each value from the range into a collection.
'Because a collection can contain only unique values,
'there will be no duplicates. The error statements force the program to
'continue when the error messages appear for duplicate items in the collection
For Each myCell In Rng
UniqueVals.Add myCell.Value, CStr(myCell.Value)
Next myCell
' Reset error checking to normal
On Error GoTo 0
'returns the number of items in the collection
NumUniqueValues = UniqueVals.Count
End Function
Bookmarks