Found a glitch in the OFFSET function.

[Peter T]

Not sure why you regard Offset as unstable!

By the way would by any chance know of a Range property which could
effectively shield a cell from automatic re-calculation (kind of the opposite
of the "Dirty" method)?

Nothing I know of other than setting calc to manual. Can't imagine how
anything like what you describe would work.

FWIW, I have noticed sometimes wb's with volatile functions don't re-calc on
file open even with calc set to automatic. The tell-tale is "Calculate" in
the status bar.

As for creating a "Black Hole" nobody's accused you of that. UDF's are a
valuable resource and I'm quite sure yours would do no harm. 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

Regards,
Peter T

 

[mickey]

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.

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.

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.

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": quite the opposite I have gone out of my way
to thank those who provided suggestions. 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. 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â€.

Cheers.

 

[Peter T]

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.

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.

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

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 ..."

"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.

Regards,
Peter T

pmbthornton gmal com

<snip>

 

[Peter T]

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 predictable, 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.

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 dependencies, named formulas etc.

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

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 ..."

"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.

Regards,
Peter T

pmbthornton gmal com

<snip>

 

[Peter T]

Sorry about the double post, not sure why, I didn't send twice.

Peter T

 

[mickey]

Hi, Also In line *.

Hello again, comments in line


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.

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.

Fair enough


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.

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


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).


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.

 

[Peter T]

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

Hi, Also In line *.



* 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.

* 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.


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


* 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".


* 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>

 

[mickey]

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

Thanks for the coding suggestion, I check it out.

By the way one item I forgot to mention in my first post, when I was
experimenting with OFFSET. In addition to it generating a "Calculate Event"
when double clicking on a row or column, it would sometimes generate a
"Calculate Event" when cell selection was changed, but not always. I was did
not have the time to figure out why it sporadically generated the event when
selecting a different cell.


It's been a pleasue - I enjoy a good discussion.

Cheers