Option Explicit


Function countValChangeIn1DimRange(r As Range, Optional filterVal As Variant) As Long

Dim i As Long, k As Long

k = 0

If IsMissing(filterVal) Then

    For i = 2 To r.Count
        If r.Item(i).Value <> r.Item(i - 1).Value Then
            k = k + 1
        End If
    Next i

Else

    For i = 2 To r.Count
        If r.Item(i).Value <> r.Item(i - 1).Value And r.Item(i).Value = filterVal Then
            k = k + 1
        End If
    Next i

End If



countValChangeIn1DimRange = k

End Function
Hi,

I wrote this simple code. Its meaning is

1) to count every value change in a 1dim-range
2) or, to count every change in a 1dim-range, on a specific value (filterVal)

The code works as expected if calculation mode is manual. However, by switching to calculation mode automatic, Excel calls permanently the function.
By setting a breakpoint anythere in the code, the debugger immediately stops there.

I have no clue why this happens. Any ideas?

Lloyd