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]

??

F2 (entering a cell for editing) doesn't trigger a calculation; hitting Enter does, even if the same value is re-entered.

 

[tiredofit]

Sorry, I meant pressing F2, then Enter (so the value is entered but not changed) doesn't trigger the code.

Even if the value was 1 and I typed in 1, the code is still not triggered.

 

[RoryA]

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

Because that's the point of the function.

 

[tiredofit]

Because that's the point of the function.

and pressing F2 + Enter (or simply typing the SAME value) does not constitute a change, hence the code's NOT triggered?

 

[RoryA]

Both of those should trigger it too.

 

[shg]

Both of those should trigger it too.

That's what I believed until two minutes ago. Reentering the same value in a single cell triggers the Change event, but not the Calculate event.

That's pretty smart.

 

[Rick Rothstein]

That's what I believed until two minutes ago. Reentering the same value in a single cell triggers the Change event, but not the Calculate event.

That's pretty smart.

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?

 

[shg]

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

 

[Rick Rothstein]

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

I am not sure what that means. 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.