And Alex would like just one way to enter ("submit") a formula. Of course, ctrl+shift+Enter is unnecessary. For example, press ctrl+shift+Enter instead of just Enter Lip-service to why we want to avoid "array formulas" that is, formulas that must be array-entered.īut Alex asks: why not always press ctrl+shift+Enter, even if the formula does not have to be array-enter. The article explains when we must array-enter a formula (press ctrl+shift+Enter). I hope this MS article would suffice explanation needed This is what Alex is asking about: why not use ctrl+shift+Enter for all formulas, even if they are not "array formulas"? That demonstrates that the formula in A1 is recalculated whenever any cell in the workbook is edited, the definition of a "volatile formula". Notice that now we do see a MsgBox pop up. In contrast, replace myudf(TRUE) with RAND()<0.5, and press ctrl+shift+Enter.Īfter the initial MsgBox, enter 5 into any cell. So the formula in A1 is not recalculated. Note that do not see any MsgBoxes popping up. Then array-enter the following formula (press ctrl+shift+Enter instead of just Enter) into A1:Īfter pressing ctrl+shift+Enter, we see two MsgBoxes. they recalculate for the slightest change made anywhere in the workbook.Ībsolutely wrong! To demonstrate, enter the following into a normal VBA module: Array formulas are volatile formulas i.e.
0 kommentar(er)
