Well Cheers again, I think we've beaten this "dead horse" enough :-),
although I would still appreciate comments on the proposed
"EnableCalculate"
property.

Yes the old horse has had enough !
One last trivial thing, my very first reply to you was not intended as
"terse" but succinct with the assumption (wrong) that most round here would
know of volatile functions.

You could start a new topic for your cell EnableCalculate property but I
don't think you'll get very far. A workaround for your special needs -

Sub CelCalc(cel As Range, bCalc As Boolean, Optional bFuncOnly As Boolean)
Dim pos As Long
Dim sFmla As String
Const cN1 As String = "+N(""="
Const cN2 As String = """)"

If Not cel.HasFormula Then Exit Sub
sFmla = cel.Formula

pos = InStr(2, sFmla, cN1)

If bCalc And pos Then

sFmla = Mid$(sFmla, pos + 4, Len(sFmla) - pos - 5)
cel.Formula = sFmla

ElseIf Not bCalc And pos = 0 Then
' don't process if includes the N function

If bFuncOnly Then
If InStr(2, sFmla, "(") = 0 Then
'no possibility of volatile function in formula
Exit Sub
End If
End If

'temporary #NAME? error if string
sFmla = "=" & cel.Value2 & cN1 & Mid$(sFmla, 2, 255) & cN2
cel.Formula = sFmla

End If

End Sub


You argued your points well and I accept some. Anyway, argument is the
essence of discussion *

Regards,
Peter T

* Oscar Wilde, misquoted


You could start a new topic for
"mickey" wrote in message
...
Hi, Also In line *.

"Peter T" wrote:

Hello again, comments in line

Peter,

Instability is defined as something erratic, however for code it is
generally accepted to add, producing unpredicted and unexplained
behaviors. Unpredicted and unexplained behaviors are indications of
coding errors, which could also have as yet undiscovered deleterious
effects. So far no one has
explained why it would be necessary for OFFSET to force a SAVE: hence
it fits the generally accepted definition for unstable code.

Both Lori and I tried to describe why functions some functions need to
be
volatile. Perhaps in some cases 'need to be' is too strong but they just
are, no doubt due to legacy. In complex spreadsheets they may indeed
give
rise to unexpected problems. However their behaviour IS explainable and
well
documented (incl the force a save) and therefore predicatble, eg by
Charles
Williams in the link referred to by Jon Peltier.

As I mentioned earlier, personally I don't understand why they recalc on
autosize which is a slightly different matter.

* I would agree that "legacy" is a central reason for the "unexpected
problems", as they are very rarely revisited and correctly resolved. I
also
agree that the resulting KNOWN problems have been well documented. My
issue
is still with unknown problems, which could manifest themselves in the
future. This is not an idle speculation: I have witnessed coding problems
that were not properly run to ground, responsible for bring down aircraft,
when just the right combination of improbable events coincides. For the
record I fully understand the need for "volatile" functions, but I still
view
it as incomplete coding when a function gives rise to unnecessary
consequences. Right now it appears that volatile functions like OFFSET
simply perform a recalculation on loading, and never confirm whether that
has
resulted in an actual change. Perhaps I'm just being obtuse here, but I
don't like code that (I'll make a concession here) if not unstable, gives
the
impression of being unstable, as I've defined instability.

Regarding cell calculation: cells have many properties, not the least
of
which is a "dirty" property which Excel uses to mark cells for
re-calculation. I was hoping that there would be a property, which
would
cause Excel to ignore the dirty-flag. If it existed it could be
implemented
by Cell.EnableCalculate = False. Internally Excel would AND the two
properties to determine whether it should recalculate a given cell.

OK I see what you are getting at. But I can't imagine how in practice
that
could be implemented, eg multiple dependancies, named formulas etc.

* With the property I'm suggesting individual cell calculation would come
under control of UDF VB code. In proposing this particular property I'm
assuming that each cell maintains a register (i.e. memory block) that
represents the value it displays. The property I'm suggesting would
simply
force the cell to retain it's last value (freeze if you will), even though
the dependencies may have changed (this of course would affect cells that
are
dependent on the frozen cell, but this would be the programmers
responcibility). Then when the VB code determined it wanted an update, it
would unfreeze the cell (i.e. Cell.EnableCalculate = True) and the entire
sheet would resolve itself to the re-calculated value. I do have a
specific,
albeit unorthodox application, which would simplify things greatly if this
property existed.


Regarding the "Black Hole" statement, note that I was referring to the
"Forum" in general, not this specific "thread". You may have
misunderstood my comment due to a type-o, where my comment
read "...were to mention" should have read "were I to mention".
I was speaking about a POSSIBLE response if I
were ever to post the code behind some of my workbooks, which could be
interpreted as deviating from Excel general design principles.
Further I
only made this comment because of various responses I've seen to posts
on
this forum regarding the use of UDF's, which have in many cases ranged
from general reluctance to out-right antagonism.

Fair enough

Regarding your statement: "If you got the impression of being jumped
on
earlier on it may have been triggered by what appeared to be an
unqualified yet dogmatic view that your UDF was a superior alternative
to Excel's problematic worksheet function." Firstly, in no way did
I ever feel as though I was ever "jumped on":

OK I got the wrong impression

* By the with the "Black Hole" comment I was actually trying to inject a
little humor - Im sorry it fell flat.


quite the opposite I have gone out of my way
to thank those who provided suggestions.

Always appreciated, makes a big difference from those who don't bother
to
follow up at all !

I'm glad to see that you qualified
your statement with the word "appeared" as I do take issue with your
use of
the term "unqualified"; for in your own statement you use the term
"problematic" when referring to the subject worksheet function.
I never used
the term "superior" in referring to my UDF, however, if superior could
be
used to differentiate a function, which does not exhibit "problematic"
behavior from one that does, then my UDF could be said to be
"superior" to
the subject Excel worksheet function.

Yes I know you didn't use the term "superior" vis your UDF, perhaps I
chose
the wrong word but a shorthand way of describing the overall impression
you
appeared to give (see below).

As far as the term "dogmatic" goes, it
was certainly not my intent to leave the impression that I was
dogmatic in
any reference to my UDF, and in fact I would appreciate it if you
would point out the specific statements of mine, which you
interpreted as "dogmatic". :-)

A couple of examples -

"No properly designed built-in function need be, or should be
"volatile".
Volatile code is a result of sloppy programming."

Seemed not only dogmatic but also unqualified due to your later response
in
which you said -
"...I mis-interpreted your statement about the OFFSET function being
Volatile. For "Volatile" I was reading unstable. I now understand that
"Volatile" in MS terms means ..."

* Well again, my initial statement was a misunderstanding of your use of
the
word "volatile", which as it happens, is a synonym for "unstable". Your
original terse statement "Offset is a volatile function" could be
interpreted
as a statement resigning oneself to the fact that OFFSET had problems: at
least this was the way I erroneously interpreted your original comment.
Given that I explained my error and that should have negated any
categorization of the comment being "dogmatic".


"The UDF itself is comprised of one VB
instruction: I don't think you'll find anything faster."

Less dogmatic but in overall context gave the impression you were
proposing
your UDF as a superior alternative to the volatile worksheet function.
JP had earlier suggested Index, which as it turned out was directly
equivalent
to your UDF yet would be very significantly faster.

* This was simply a response to the previous post by Peltier:
"You said in the first place you switched from lookups to offsets for
performance reasons. Offset gave problems with its volatility, so you
coded
a UDF. How does this UDF compare with the lookups? I would expect it to be
slow."

Particularly his coment "I would expect it to be slow."

Again not "dogmatic" simply a response to his unqualified speculation that
my UDF would be slow.

Well Cheers again, I think we've beaten this "dead horse" enough :-),
although I would still appreciate comments on the proposed
"EnableCalculate"
property.


<snip