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

[Lloyd Blankfein]

Please Login or Register  to view this content.

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!!!