Evaluation of Conditional Formats

[Jim Thomlinson]

Before I explain the situation the question is
"What causes a conditional format to be evaluated?"

A few threads down from here I answered a question about having a
conditional format based on the absence (or presence) of a comment in the
cell. I realized shortly after posting my initial response that it would not
work. (Adding a column populated with a UDF that returned true or false based
on a cell having a comment.) The UDF would not calculate because the addition
of a comment would not initiate a calculation to update the UDF (even with
application.volatile added). I didn't think it would work but I added the UDF
directly to the conditional format and I will be darned... but it actually
works (much to my surprise). The addition of a comment causes the formula in
the conditional format to be evaluated. Here is the code...

Public Function HasComment(ByVal Cell As Range) As Boolean
If Cell.Comment Is Nothing Then
HasComment = False
Else
HasComment = True
End If
End Function

In Cell A1 add to the conditional format the formula =HasComment(A1)

Now if you add or remove a comment the formatting changes. No events fire
(selection change, calculate, change). So out of pure curiosity why does this
work? What is causing the formula in the conditional format to be
re-evaluated?
--
Jim Thomlinson

[Dave Peterson]

That's an excellent question.

I'm gonna take a post your question in a private discussion group--just so it's
not missed by the really smart excel developers (RSED's <vbg>).

It's beyond me!

Jim Thomlinson wrote:
>
> Before I explain the situation the question is
> "What causes a conditional format to be evaluated?"
>
> A few threads down from here I answered a question about having a
> conditional format based on the absence (or presence) of a comment in the
> cell. I realized shortly after posting my initial response that it would not
> work. (Adding a column populated with a UDF that returned true or false based
> on a cell having a comment.) The UDF would not calculate because the addition
> of a comment would not initiate a calculation to update the UDF (even with
> application.volatile added). I didn't think it would work but I added the UDF
> directly to the conditional format and I will be darned... but it actually
> works (much to my surprise). The addition of a comment causes the formula in
> the conditional format to be evaluated. Here is the code...
>
> Public Function HasComment(ByVal Cell As Range) As Boolean
> If Cell.Comment Is Nothing Then
> HasComment = False
> Else
> HasComment = True
> End If
> End Function
>
> In Cell A1 add to the conditional format the formula =HasComment(A1)
>
> Now if you add or remove a comment the formatting changes. No events fire
> (selection change, calculate, change). So out of pure curiosity why does this
> work? What is causing the formula in the conditional format to be
> re-evaluated?
> --
> Jim Thomlinson

--

Dave Peterson

[Jim Thomlinson]

Really Smart Excel Developers? Is that an oxymoron... <vbg>
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

> That's an excellent question.
>
> I'm gonna take a post your question in a private discussion group--just so it's
> not missed by the really smart excel developers (RSED's <vbg>).
>
> It's beyond me!
>
> Jim Thomlinson wrote:
> >
> > Before I explain the situation the question is
> > "What causes a conditional format to be evaluated?"
> >
> > A few threads down from here I answered a question about having a
> > conditional format based on the absence (or presence) of a comment in the
> > cell. I realized shortly after posting my initial response that it would not
> > work. (Adding a column populated with a UDF that returned true or false based
> > on a cell having a comment.) The UDF would not calculate because the addition
> > of a comment would not initiate a calculation to update the UDF (even with
> > application.volatile added). I didn't think it would work but I added the UDF
> > directly to the conditional format and I will be darned... but it actually
> > works (much to my surprise). The addition of a comment causes the formula in
> > the conditional format to be evaluated. Here is the code...
> >
> > Public Function HasComment(ByVal Cell As Range) As Boolean
> > If Cell.Comment Is Nothing Then
> > HasComment = False
> > Else
> > HasComment = True
> > End If
> > End Function
> >
> > In Cell A1 add to the conditional format the formula =HasComment(A1)
> >
> > Now if you add or remove a comment the formatting changes. No events fire
> > (selection change, calculate, change). So out of pure curiosity why does this
> > work? What is causing the formula in the conditional format to be
> > re-evaluated?
> > --
> > Jim Thomlinson
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Of course not.

But it is redundant!
If you're an excel developer, then you have to be really smart <vvbg>.



Jim Thomlinson wrote:
>
> Really Smart Excel Developers? Is that an oxymoron... <vbg>
> --
> HTH...
>
> Jim Thomlinson
>
> "Dave Peterson" wrote:
>
> > That's an excellent question.
> >
> > I'm gonna take a post your question in a private discussion group--just so it's
> > not missed by the really smart excel developers (RSED's <vbg>).
> >
> > It's beyond me!
> >
> > Jim Thomlinson wrote:
> > >
> > > Before I explain the situation the question is
> > > "What causes a conditional format to be evaluated?"
> > >
> > > A few threads down from here I answered a question about having a
> > > conditional format based on the absence (or presence) of a comment in the
> > > cell. I realized shortly after posting my initial response that it would not
> > > work. (Adding a column populated with a UDF that returned true or false based
> > > on a cell having a comment.) The UDF would not calculate because the addition
> > > of a comment would not initiate a calculation to update the UDF (even with
> > > application.volatile added). I didn't think it would work but I added the UDF
> > > directly to the conditional format and I will be darned... but it actually
> > > works (much to my surprise). The addition of a comment causes the formula in
> > > the conditional format to be evaluated. Here is the code...
> > >
> > > Public Function HasComment(ByVal Cell As Range) As Boolean
> > > If Cell.Comment Is Nothing Then
> > > HasComment = False
> > > Else
> > > HasComment = True
> > > End If
> > > End Function
> > >
> > > In Cell A1 add to the conditional format the formula =HasComment(A1)
> > >
> > > Now if you add or remove a comment the formatting changes. No events fire
> > > (selection change, calculate, change). So out of pure curiosity why does this
> > > work? What is causing the formula in the conditional format to be
> > > re-evaluated?
> > > --
> > > Jim Thomlinson
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Jim Thomlinson]

Maybe if I am good in my next life I will be able to come back as an Excel
Developer. I always wanted to be really smart... :-)
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

> Of course not.
>
> But it is redundant!
> If you're an excel developer, then you have to be really smart <vvbg>.
>
>
>
> Jim Thomlinson wrote:
> >
> > Really Smart Excel Developers? Is that an oxymoron... <vbg>
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> > "Dave Peterson" wrote:
> >
> > > That's an excellent question.
> > >
> > > I'm gonna take a post your question in a private discussion group--just so it's
> > > not missed by the really smart excel developers (RSED's <vbg>).
> > >
> > > It's beyond me!
> > >
> > > Jim Thomlinson wrote:
> > > >
> > > > Before I explain the situation the question is
> > > > "What causes a conditional format to be evaluated?"
> > > >
> > > > A few threads down from here I answered a question about having a
> > > > conditional format based on the absence (or presence) of a comment in the
> > > > cell. I realized shortly after posting my initial response that it would not
> > > > work. (Adding a column populated with a UDF that returned true or false based
> > > > on a cell having a comment.) The UDF would not calculate because the addition
> > > > of a comment would not initiate a calculation to update the UDF (even with
> > > > application.volatile added). I didn't think it would work but I added the UDF
> > > > directly to the conditional format and I will be darned... but it actually
> > > > works (much to my surprise). The addition of a comment causes the formula in
> > > > the conditional format to be evaluated. Here is the code...
> > > >
> > > > Public Function HasComment(ByVal Cell As Range) As Boolean
> > > > If Cell.Comment Is Nothing Then
> > > > HasComment = False
> > > > Else
> > > > HasComment = True
> > > > End If
> > > > End Function
> > > >
> > > > In Cell A1 add to the conditional format the formula =HasComment(A1)
> > > >
> > > > Now if you add or remove a comment the formatting changes. No events fire
> > > > (selection change, calculate, change). So out of pure curiosity why does this
> > > > work? What is causing the formula in the conditional format to be
> > > > re-evaluated?
> > > > --
> > > > Jim Thomlinson
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Peter T]

Interesting !!

No answer except it's not restricted to comments

Public Function udf1(cel As Range) As Boolean
Static n As Long

udf1 = cel <> 0
n = n + 1
Debug.Print "udf1", n

End Function

View the immediate window together with Excel, seems to run a lot. Simply
selecting the cell triggers the debug. Yet no calculation (turn calc to
manual) and can't put a "break" in.

Potentially dangerous, I had to ctrl-alt-del Excel on typing this into the
function (with udf1 already applied in a CF formula)

cel.offset(2,2) = n

and even worse -

udf1 = cel.interior.colorindex > 0

When I colour formatted the cel, windows crashed (eventually) after getting
that familiar Kernal message. Even so, applying a format shouldn't trigger
anything.

I'll be watching to see what Dave reports back from the smart guys !

Regards,
Peter T


"Jim Thomlinson" <JThomlinson@belron-Re-Move-This-canada.ca> wrote in
message news:6B8A8792-79D2-4389-B568-BA9B65CD61CF@microsoft.com...
> Before I explain the situation the question is
> "What causes a conditional format to be evaluated?"
>
> A few threads down from here I answered a question about having a
> conditional format based on the absence (or presence) of a comment in the
> cell. I realized shortly after posting my initial response that it would
not
> work. (Adding a column populated with a UDF that returned true or false
based
> on a cell having a comment.) The UDF would not calculate because the
addition
> of a comment would not initiate a calculation to update the UDF (even with
> application.volatile added). I didn't think it would work but I added the
UDF
> directly to the conditional format and I will be darned... but it actually
> works (much to my surprise). The addition of a comment causes the formula
in
> the conditional format to be evaluated. Here is the code...
>
> Public Function HasComment(ByVal Cell As Range) As Boolean
> If Cell.Comment Is Nothing Then
> HasComment = False
> Else
> HasComment = True
> End If
> End Function
>
> In Cell A1 add to the conditional format the formula =HasComment(A1)
>
> Now if you add or remove a comment the formatting changes. No events fire
> (selection change, calculate, change). So out of pure curiosity why does
this
> work? What is causing the formula in the conditional format to be
> re-evaluated?
> --
> Jim Thomlinson

[Dave Peterson]

This was posted by Tushar Mehta (one of the smart people!).

========

Interesting. One way or another it's a bug.

It's consistent and correct only if the cell referenced in the C.F. is the
same cell that contains the C.F.

So, using the C.F. to format A1 and using the formula =xxx(A1) works
consistently and correctly each time.

But, for other cells it either consistently does not work or works
erratically.

It's almost as if the C.F. has an incomplete recalc chain to traverse.

When does it never work?

When the cell containing the C.F. is before the cell referenced. So,
suppose A1 contains a c.f. with =xxx(D1). Then, inserting or deleting a
comment in D1 has no effect on A1.

When does it work erratically? If the cell containing the c.f. is after the
referenced cell and in the same row.

So, if F1 contains a c.f. with =xxx(D1) then inserting a comment in D1
causes F1 to reformat *most* of the times. Deleting the comment in D1
causes F1 to reformat *only occasionally*. I cannot figure out when or why
it does(n't) work.

But, the cell has to be in the same row! If D2 (or A2) contains a c.f. of
=xxx(D1) (or A1), the reformat never happens.

It's like the c.f. module is carrying out a half-hearted lazy pass through
the recalculation chain.

Here's my guess of what's happening. Maybe someone from the PG will
clarify.

XL recalculates cells based on the recalculation chain it has created.
Then, once all the recalcs are done it triggers a reassessment of the c.f.s.
This makes sense since the c.f.s do need the final values in the various
cells. It is also analogous to how XL updates charts.

The c.f. engine piggybacks off the recalculation chain to figure out which
cells have changed. It only checks those cells. This also makes sense.

[As an aside, I wonder if this piggybacking is the reason why there are
ongoing, persistent, and unexplainable complaints by people that their
charts don't update correctly...]

But what if the c.f. engine is triggered when the recalculation chain is not
properly estabished? Then, the c.f. engine goes through a incomplete chain
and checks only a few cells. Of course, it always reevaluates the cell that
contains the changed c.f.

That would explain the behavior I observed.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Jim Thomlinson wrote:
>
> Before I explain the situation the question is
> "What causes a conditional format to be evaluated?"
>
> A few threads down from here I answered a question about having a
> conditional format based on the absence (or presence) of a comment in the
> cell. I realized shortly after posting my initial response that it would not
> work. (Adding a column populated with a UDF that returned true or false based
> on a cell having a comment.) The UDF would not calculate because the addition
> of a comment would not initiate a calculation to update the UDF (even with
> application.volatile added). I didn't think it would work but I added the UDF
> directly to the conditional format and I will be darned... but it actually
> works (much to my surprise). The addition of a comment causes the formula in
> the conditional format to be evaluated. Here is the code...
>
> Public Function HasComment(ByVal Cell As Range) As Boolean
> If Cell.Comment Is Nothing Then
> HasComment = False
> Else
> HasComment = True
> End If
> End Function
>
> In Cell A1 add to the conditional format the formula =HasComment(A1)
>
> Now if you add or remove a comment the formatting changes. No events fire
> (selection change, calculate, change). So out of pure curiosity why does this
> work? What is causing the formula in the conditional format to be
> re-evaluated?
> --
> Jim Thomlinson

--

Dave Peterson

[Jim Thomlinson]

Thanks Tushar/Dave. I had been playing with it a bit further and found out
some of what you did. Deffinietly an odd behaviour. It's recalc chains is
definitely seperate for the normal recalc and certainly odd. Adding a comment
DOES cause a recalc of the CF but it does not propogate to the dependants...
sometimes... this kind of thing is a real buyer be ware... I am not sure that
it is worth investigating at length but it certainly is interesting and
something take note of...

Where I still have a question for Tushar would be he mentions that

"XL recalculates cells based on the recalculation chain it has created.
Then, once all the recalcs are done it triggers a reassessment of the c.f.s.
This makes sense since the c.f.s do need the final values in the various
cells. It is also analogous to how XL updates charts."

but it does not appear as if a recalc has occured. The event handler
certainly does not throw an event. My guess was that it had to do somehow
with how Excel set "dirty" flags. A change to the comments creates a dirty
flag (at least for the purpose of the CF) but that flag is not set
consistently set for the dependants. When the CF recalc engine runs it just
does not see changes to the dependants and... certain CF updates are missed.
One thing I find interesting about this is that a change to the formatting
seems to trigger a recalc of the CF calculation engine. That is the part that
surprised me as I never expected it to run. I had expected it to requre a
sheet recalculation...

--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

> This was posted by Tushar Mehta (one of the smart people!).
>
> ========
>
> Interesting. One way or another it's a bug.
>
> It's consistent and correct only if the cell referenced in the C.F. is the
> same cell that contains the C.F.
>
> So, using the C.F. to format A1 and using the formula =xxx(A1) works
> consistently and correctly each time.
>
> But, for other cells it either consistently does not work or works
> erratically.
>
> It's almost as if the C.F. has an incomplete recalc chain to traverse.
>
> When does it never work?
>
> When the cell containing the C.F. is before the cell referenced. So,
> suppose A1 contains a c.f. with =xxx(D1). Then, inserting or deleting a
> comment in D1 has no effect on A1.
>
> When does it work erratically? If the cell containing the c.f. is after the
> referenced cell and in the same row.
>
> So, if F1 contains a c.f. with =xxx(D1) then inserting a comment in D1
> causes F1 to reformat *most* of the times. Deleting the comment in D1
> causes F1 to reformat *only occasionally*. I cannot figure out when or why
> it does(n't) work.
>
> But, the cell has to be in the same row! If D2 (or A2) contains a c.f. of
> =xxx(D1) (or A1), the reformat never happens.
>
> It's like the c.f. module is carrying out a half-hearted lazy pass through
> the recalculation chain.
>
> Here's my guess of what's happening. Maybe someone from the PG will
> clarify.
>
> XL recalculates cells based on the recalculation chain it has created.
> Then, once all the recalcs are done it triggers a reassessment of the c.f.s.
> This makes sense since the c.f.s do need the final values in the various
> cells. It is also analogous to how XL updates charts.
>
> The c.f. engine piggybacks off the recalculation chain to figure out which
> cells have changed. It only checks those cells. This also makes sense.
>
> [As an aside, I wonder if this piggybacking is the reason why there are
> ongoing, persistent, and unexplainable complaints by people that their
> charts don't update correctly...]
>
> But what if the c.f. engine is triggered when the recalculation chain is not
> properly estabished? Then, the c.f. engine goes through a incomplete chain
> and checks only a few cells. Of course, it always reevaluates the cell that
> contains the changed c.f.
>
> That would explain the behavior I observed.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> Jim Thomlinson wrote:
> >
> > Before I explain the situation the question is
> > "What causes a conditional format to be evaluated?"
> >
> > A few threads down from here I answered a question about having a
> > conditional format based on the absence (or presence) of a comment in the
> > cell. I realized shortly after posting my initial response that it would not
> > work. (Adding a column populated with a UDF that returned true or false based
> > on a cell having a comment.) The UDF would not calculate because the addition
> > of a comment would not initiate a calculation to update the UDF (even with
> > application.volatile added). I didn't think it would work but I added the UDF
> > directly to the conditional format and I will be darned... but it actually
> > works (much to my surprise). The addition of a comment causes the formula in
> > the conditional format to be evaluated. Here is the code...
> >
> > Public Function HasComment(ByVal Cell As Range) As Boolean
> > If Cell.Comment Is Nothing Then
> > HasComment = False
> > Else
> > HasComment = True
> > End If
> > End Function
> >
> > In Cell A1 add to the conditional format the formula =HasComment(A1)
> >
> > Now if you add or remove a comment the formatting changes. No events fire
> > (selection change, calculate, change). So out of pure curiosity why does this
> > work? What is causing the formula in the conditional format to be
> > re-evaluated?
> > --
> > Jim Thomlinson
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Stephen Bullen chimed in with his thoughts. I included them at the bottom of
this message.

Just to add to the weirdness...

I put a beep in the code:

Option Explicit
Public Function HasComment(ByVal Cell As Range) As Boolean
Beep
'MsgBox Cell.Address
If Cell.Comment Is Nothing Then
HasComment = False
Else
HasComment = True
End If
End Function

Sometimes, I could just mouse over the cell and I could hear the beep.
Sometimes not. I heard the beep when I had a simple formula in the cell
(=hascomment(a1)--in D6). Sometimes, I heard the beep on a cell that used that
as a conditional formatting formula.

Just swapping from one application back to excel cause the beep to sound.

That sure gives credence to the screen updating theory.

And when I uncommented that msgbox line. I was getting the active cell to
flash, just changing the selection--well, until xl2003 crashed!

===
Stephen, I gonna post this message to the public newsgroup, too.

Stephen Bullen wrote:
>
> Hi Tushar,
>
> > But, for other cells it either consistently does not work or works
> > erratically.
>
> From what I can tell, it's also based on when Excel thinks it needs to
> redraw the screen, and only those cells in the visible window have
> their cf's evaluated. So, for example, if your editing of a formula
> means that the edited text overlaps some cells, those cells will need
> to be redrawn and hence their cf's are updated.
>
> It's easy enough to test the behaviour - just create a UDF that logs
> its address to a text file, then see what's evaluated when.
>
> Regards
>
> Stephen Bullen

--

Dave Peterson
<<snipped>>

[Jim Thomlinson]

Where is this private message board? Any possibility of allowing an aspiring
smart person to listen in?
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

> Stephen Bullen chimed in with his thoughts. I included them at the bottom of
> this message.
>
> Just to add to the weirdness...
>
> I put a beep in the code:
>
> Option Explicit
> Public Function HasComment(ByVal Cell As Range) As Boolean
> Beep
> 'MsgBox Cell.Address
> If Cell.Comment Is Nothing Then
> HasComment = False
> Else
> HasComment = True
> End If
> End Function
>
> Sometimes, I could just mouse over the cell and I could hear the beep.
> Sometimes not. I heard the beep when I had a simple formula in the cell
> (=hascomment(a1)--in D6). Sometimes, I heard the beep on a cell that used that
> as a conditional formatting formula.
>
> Just swapping from one application back to excel cause the beep to sound.
>
> That sure gives credence to the screen updating theory.
>
> And when I uncommented that msgbox line. I was getting the active cell to
> flash, just changing the selection--well, until xl2003 crashed!
>
> ===
> Stephen, I gonna post this message to the public newsgroup, too.
>
> Stephen Bullen wrote:
> >
> > Hi Tushar,
> >
> > > But, for other cells it either consistently does not work or works
> > > erratically.
> >
> > From what I can tell, it's also based on when Excel thinks it needs to
> > redraw the screen, and only those cells in the visible window have
> > their cf's evaluated. So, for example, if your editing of a formula
> > means that the edited text overlaps some cells, those cells will need
> > to be redrawn and hence their cf's are updated.
> >
> > It's easy enough to test the behaviour - just create a UDF that logs
> > its address to a text file, then see what's evaluated when.
> >
> > Regards
> >
> > Stephen Bullen
>
> --
>
> Dave Peterson
> <<snipped>>
>

[Dave Peterson]

You have to be an MVP.

Jim Thomlinson wrote:
>
> Where is this private message board? Any possibility of allowing an aspiring
> smart person to listen in?
> --
> HTH...
>
> Jim Thomlinson
>
> "Dave Peterson" wrote:
>
> > Stephen Bullen chimed in with his thoughts. I included them at the bottom of
> > this message.
> >
> > Just to add to the weirdness...
> >
> > I put a beep in the code:
> >
> > Option Explicit
> > Public Function HasComment(ByVal Cell As Range) As Boolean
> > Beep
> > 'MsgBox Cell.Address
> > If Cell.Comment Is Nothing Then
> > HasComment = False
> > Else
> > HasComment = True
> > End If
> > End Function
> >
> > Sometimes, I could just mouse over the cell and I could hear the beep.
> > Sometimes not. I heard the beep when I had a simple formula in the cell
> > (=hascomment(a1)--in D6). Sometimes, I heard the beep on a cell that used that
> > as a conditional formatting formula.
> >
> > Just swapping from one application back to excel cause the beep to sound.
> >
> > That sure gives credence to the screen updating theory.
> >
> > And when I uncommented that msgbox line. I was getting the active cell to
> > flash, just changing the selection--well, until xl2003 crashed!
> >
> > ===
> > Stephen, I gonna post this message to the public newsgroup, too.
> >
> > Stephen Bullen wrote:
> > >
> > > Hi Tushar,
> > >
> > > > But, for other cells it either consistently does not work or works
> > > > erratically.
> > >
> > > From what I can tell, it's also based on when Excel thinks it needs to
> > > redraw the screen, and only those cells in the visible window have
> > > their cf's evaluated. So, for example, if your editing of a formula
> > > means that the edited text overlaps some cells, those cells will need
> > > to be redrawn and hence their cf's are updated.
> > >
> > > It's easy enough to test the behaviour - just create a UDF that logs
> > > its address to a text file, then see what's evaluated when.
> > >
> > > Regards
> > >
> > > Stephen Bullen
> >
> > --
> >
> > Dave Peterson
> > <<snipped>>
> >

--

Dave Peterson