Function code triggering

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,256
Further to this thread of mine:


I have the following in Module1:

Rich (BB code):
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:

Rich (BB code):
=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
 
Last edited:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
??

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

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,256
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.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,256

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Both of those should trigger it too.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
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?
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
The change event doesn't entail any overhead if there is no subscriber.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,233
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top