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?

 

[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]

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

 

[Dave Peterson]

Of course not.

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

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

 

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

 

[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

 

[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]

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

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

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?

 

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

 

[Jim Thomlinson]

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

 

[Dave Peterson]

You have to be an MVP.

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

 

[Jim Thomlinson]

Perhaps with a few more years of dilligent service that powers that be will
let me into their club.

 

[Dave Peterson]

I proved that anything is possible when I got in <bg>.

 

[Jim Thomlinson]

As has so often been remarked (Groucho Marks if memory serves)... I would not
want to join any club that would have me as a member... In all seriousness I
have seen the caliber of your posts and level of dedication and you earned
it. When you post I always read the response. There is an interesting
discusion going on right now about the MVP on Daily Dose of Excel. I have
never seen so much contorversy posted about Excel...

 

[Dave Peterson]

A discussion about that blog entry has shown up in the private newsgroups, too.

As has so often been remarked (Groucho Marks if memory serves)... I would not
want to join any club that would have me as a member... In all seriousness I
have seen the caliber of your posts and level of dedication and you earned
it. When you post I always read the response. There is an interesting
discusion going on right now about the MVP on Daily Dose of Excel. I have
never seen so much contorversy posted about Excel...