Function code triggering

[tiredofit]

Further to this thread of mine:

https://www.mrexcel.com/forum/excel-questions/1020204-auto-update-udf-value.html#post4895641

I have the following in Module1:

Option Explicit

Function MyFn(firstval As Double, val As Double) As Double
    
    If firstval = 1 Then
    
        MyFn = val + 10
    
    Else
    
        MyFn = val + 20
    
    End If
    
End Function

In Sheet2 cell B2, I type:

=myfn(Sheet1!A1, 10)

In Sheet1 cell A1, I have a value of 1.

Sheet2 cell B2 has the correct value of 30.

What I find strange is if I change the value in Sheet1 Cell A1, the code is triggered off.

For example if I change the value from 1 to 2, the code starts BUT if I simply press F2 in Sheet1 cell A1, the cell isn't triggered.

So my question is:

1. Why is the code triggered at all when I change the value in Sheet1 cell A1.

2. Why doesn't it change when I simply press F2?

Thanks

 

[shg]

The change event doesn't entail any overhead if there is no subscriber.

I am not sure what that means.

If there is no code in the event procedure, then the event doesn't incur any overhead.

My point, though, is this. Let's say you have a lengthy Change event procedure that takes a full minute to execute when cell A1 changes. What is the point in firing that code if you enter edit mode for cell A1 and then just hit the Enter key without changing the value? I mean, your code will tie everything up for a full minute and then return the worksheet to the same state it was in before you entered Edit mode... I don't see the point.

If I had a procedure that took a minute to run, I would check to make sure I needed to run it.

I expect you believed as I did that calculation was triggered on enter, even if there were no change. What actually happens moderates that. I'm way beyond judging Excel, just observing behavior I thought well considered and interesting.

 

[LockeGarmin]

1. Why is the code triggered at all when I change the value in Sheet1 cell A1.

2. Why doesn't it change when I simply press F2?

In the background Excel keeps track of a "Dependency Tree" of all of your functions in your open workbooks. When you have calculations turned on to "Automatic" Excel will search for functions that have had input values changed. If an input gets changed, the Excel will fire the function again to be recalculated and the result posted to the cell. When you Press F2 and then Enter, the Dependency Tree will check to see if the input was changed for that function, but because you didn't change the input value Excel skips that function and so the UDF code will not fire.

In the case of Volatile functions the Dependency Tree considers those needing to be recalculated not matter what and Excel fires those functions every time (i.e. those will "Application.Volatile" used in the code).

 

[LockeGarmin]

Pretty smart? I don't know... if nothing actually changed in the cell, why is the Change event firing? I mean, won't the workbook end up in the same state afterwards as it was in before given the cell value did not change? What is the ultimate purpose is firing the Change event given that?

It would create too may questions on what constitutes enough of a change. For instance if cell A1 has "=2", but then you change it to "=1+1", is that enough of a change to fire off the change event? What about if A1 has the value "A", and you change it to "a"? Is that enough of a change (In excel formulas ="A"="a" evaulates to TRUE)? How about if you have "=TODAY()" and you edit the cell, and press enter while the value is still "=TODAY()" is that enough of a change? In each case we've changed something different (formula, localization, value) but kept something the same. Ultimately it would be easier to let the change event stay more generic and fire whenever a new value is submitted to the cell and let the programmer decide what to do with it.

As a side note the Change event does not react to calculations changing, such as with =TODAY(). If you have a cell with a volatile function and recalculate the sheet (not by pressing F2-Enter, but by "Calculate" in the formula tab) a change event will not fire. It really just has to do when you submit a new value (any value, formula, or deletion) into a cell that it will fire.

 

[Rick Rothstein]

It would create too may questions on what constitutes enough of a change. For instance if cell A1 has "=2", but then you change it to "=1+1", is that enough of a change to fire off the change event? What about if A1 has the value "A", and you change it to "a"? Is that enough of a change (In excel formulas ="A"="a" evaulates to TRUE)? How about if you have "=TODAY()" and you edit the cell, and press enter while the value is still "=TODAY()" is that enough of a change?

When I wrote my comment, I was thinking only of entering Edit mode and then immediately hitting the Enter key and my question was why would that kick off the Change event. Each of what you wrote above physically changed what was in the cell, so I would expect those to kick off the Change event as something changed.

How about if you have "=TODAY()" and you edit the cell, and press enter while the value is still "=TODAY()" is that enough of a change?

Now I have to say... this one gave me pause. Entering edit mode at one minute to midnight, waiting without doing anything and then hitting the Enter key one minute after midnight would not physically change the anything in the cell, but a change in content of the cell would have taken place. The NOW function would be a better example as you don't have to wait for midnight for the change to occur. I had not thought of this when I made my original comment. I guess because of "automatic" functions like TODAY, and a desire to present a consistent functionality as to when the Change event fires or doesn't fire, may have been behind Microsoft's decision to simply fire the Change event whenever edit mode was exited.