VBA code called from cell, permanently triggered by Excel???

[Lloyd Blankfein]

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

[romperstomper]

Surely if the calculation mode is manual, the code shouldn't work at all?

[Lloyd Blankfein]

Nope, of course triggered the code by calling the formula from cell level. The code works as expected if calculation mode is manual.

[romperstomper]

If calculation is manual, the code should only run when you first enter the formula. If it's automatic, the code will be called whenever you change any of the inputs - either the filterval, or any cell within the input range. Switching from manual to automatic will also cause the code to run if the cell(s) using the function have been flagged as dirty.

[Lloyd Blankfein]

the code will be called whenever you change any of the inputs - either the filterval, or any cell within the input range

Thx. That's it! I underestimated the consumption of resources of this code.

Is there a way to suppress the recalculation for a specified cell/range?

[romperstomper]

If you don't want it as a live function, I suggest you use a subroutine to populate the required values as and when needed. Alternatively, you could use a flag cell that controls whether to call your function or not - i.e. something like:

=IF(A1=1,yourfunction(),"")

and then simply switch A1 to 1 when you want the calculation to happen.

[Lloyd Blankfein]

Good idea, thx!!!