2003: Having trouble with conditional formatting with custom user function (Repost)

[Clif McIrvin]

First posted a week ago to excel.programming. Thought I'd try again,
adding excel and excel.worksheet.functions hoping for a reply this time.


Office 2003 SP3 on XP Professional

Looking for advice as to where to start looking for my problem.

I have written a function to perform validity checking on selected
portions of my worksheet data. The new function works great, except that
as soon as I had tested it and added it to the conditional format
conditions on the relevant columns some of my other custom toolbar
macros began to misbehave.

I wrote a custom function so that I could test multiple (in excess of 6)
conditions and put the complexity of the formulas in VBA instead of long
and cumbersome conditional formatting formulas.

If I disable the conditional formatting -- either by closing the single
workbook that uses it, or by renaming the custom function -- all the
original macros return to normal behavior and function.

I set the conditional formating formula to:

=NOT(checkCyl(RCnn))

where nn is the column number under test. [It just occurred to me I
could use simply RC ... I'll test that.]

While investigating, I discovered this behavior - one of the custom
toolbar functions that misbehaves includes a call to this function (the
debug.print statements added for testing -- about 30 lines of code):

Sub ProtectAll(Optional xAll As String = "All", _
Optional xProtect As String = "Yes")
' xAll: process All or Active sheet(s)
' xProtect: Yes to Protect, No to Unprotect

Dim xFrom As Integer, xTo As Integer

Debug.Print "ProtectAll Begin ScreenUpdating: ";
Application.ScreenUpdating
Application.ScreenUpdating = False
Debug.Print "ProtectAll Begin ScreenUpdating: ";
Application.ScreenUpdating

If xAll = "All" Then
xFrom% = 1
xTo% = Sheets.Count
Else
xFrom% = ActiveSheet.Index
xTo% = xFrom%
End If

For ii% = xFrom% To xTo%
If xProtect = "No" Then
Sheets(ii%).Unprotect
Else
Sheets(ii%).Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, _
AllowFormattingCells:=True
Sheets(ii%).EnableSelection = xlUnlockedCells
End If
Next ii%

Debug.Print "ProtectAll End ScreenUpdating: ";
Application.ScreenUpdating
Application.ScreenUpdating = True
Debug.Print "ProtectAll End ScreenUpdating: ";
Application.ScreenUpdating

End Sub

When my new function / conditional formatting is active, only the first
three debug.print statements are executed. In fact, as near as I can
determine, VBA returns to Excel as though it completed normally, even
though it has 'bailed' somewhere 'in the middle' of the process.

I'm hoping that some of you "in this room" will have pointers for me on
what to look for and / or how to proceed with testing.

(My new function is approximately 150 lines of code ... I'm hesitant to
just throw it out here without invitation.)

 

[AltaEgo]

Remove from the code below the the 'Application.ScreenUpdating' lines that
do not set screen updating to false or true.

Step through your code by setting a break (using F9) on the line where you
want the code to pause. Press F8 to step through your code to monitor that
it is working as you expect.

Your conditional format function should return TRUE or FALSE in a cell or
the worksheet. If it does not, it will fail when used for conditional
formatting.


--
Steve

First posted a week ago to excel.programming. Thought I'd try again,
adding excel and excel.worksheet.functions hoping for a reply this time.


Office 2003 SP3 on XP Professional

Looking for advice as to where to start looking for my problem.

I have written a function to perform validity checking on selected
portions of my worksheet data. The new function works great, except that
as soon as I had tested it and added it to the conditional format
conditions on the relevant columns some of my other custom toolbar
macros began to misbehave.

I wrote a custom function so that I could test multiple (in excess of 6)
conditions and put the complexity of the formulas in VBA instead of long
and cumbersome conditional formatting formulas.

If I disable the conditional formatting -- either by closing the single
workbook that uses it, or by renaming the custom function -- all the
original macros return to normal behavior and function.

I set the conditional formating formula to:

=NOT(checkCyl(RCnn))

where nn is the column number under test. [It just occurred to me I
could use simply RC ... I'll test that.]

While investigating, I discovered this behavior - one of the custom
toolbar functions that misbehaves includes a call to this function (the
debug.print statements added for testing -- about 30 lines of code):

Sub ProtectAll(Optional xAll As String = "All", _
Optional xProtect As String = "Yes")
' xAll: process All or Active sheet(s)
' xProtect: Yes to Protect, No to Unprotect

Dim xFrom As Integer, xTo As Integer

Debug.Print "ProtectAll Begin ScreenUpdating: ";
Application.ScreenUpdating
Application.ScreenUpdating = False
Debug.Print "ProtectAll Begin ScreenUpdating: ";
Application.ScreenUpdating

If xAll = "All" Then
xFrom% = 1
xTo% = Sheets.Count
Else
xFrom% = ActiveSheet.Index
xTo% = xFrom%
End If

For ii% = xFrom% To xTo%
If xProtect = "No" Then
Sheets(ii%).Unprotect
Else
Sheets(ii%).Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, _
AllowFormattingCells:=True
Sheets(ii%).EnableSelection = xlUnlockedCells
End If
Next ii%

Debug.Print "ProtectAll End ScreenUpdating: ";
Application.ScreenUpdating
Application.ScreenUpdating = True
Debug.Print "ProtectAll End ScreenUpdating: ";
Application.ScreenUpdating

End Sub

When my new function / conditional formatting is active, only the first
three debug.print statements are executed. In fact, as near as I can
determine, VBA returns to Excel as though it completed normally, even
though it has 'bailed' somewhere 'in the middle' of the process.

I'm hoping that some of you "in this room" will have pointers for me on
what to look for and / or how to proceed with testing.

(My new function is approximately 150 lines of code ... I'm hesitant to
just throw it out here without invitation.)

 

[Clif McIrvin]

AltaEgo, thanks for responding. My responses are inserted "in line"
below:

Remove from the code below the the 'Application.ScreenUpdating' lines
that do not set screen updating to false or true.

Looking down below your reply I see that line wrap broke apart my
debug.print lines; when F8 stepping through this routine the debug.print
recorded True or False as expected *when my conditional formatting
function* was removed from the project. From my OP:

Step through your code by setting a break (using F9) on the line where
you want the code to pause. Press F8 to step through your code to
monitor that it is working as you expect.

No problems discovered in that exercise --- which I repeated more than
once with different test data --- *before* sending my OP.

Your conditional format function should return TRUE or FALSE in a cell
or the worksheet. If it does not, it will fail when used for
conditional formatting.

Works as expected. When setting a cell formula to "
=myfunction(cell.pointer) " it displays True or False as expected ....
also the conditional formatting works as expected.

The trouble seems to be that *other* functions (which have been working
flawlessly for months) suddenly fail without generating any error
message when I set up the conditional formatting. Again, from my OP:

**only the first three debug.print statements are executed**.

I discovered this by single stepping through some of my other, now
failing, code. While stepping through the code below, F8 would stop at
10 and 20, but when pressing F8 on line 20 the debugger would go
inactive and return to the "no code executing" state.


10 >> Debug.Print "ProtectAll End ScreenUpdating: "; _20 >> Application.ScreenUpdating = True
30 >> Debug.Print "ProtectAll End ScreenUpdating: "; _
Any other ideas for me to try?

Again,
Thanks for responding!

--
Clif

--
Steve

First posted a week ago to excel.programming. Thought I'd try again,
adding excel and excel.worksheet.functions hoping for a reply this
time.


Office 2003 SP3 on XP Professional

Looking for advice as to where to start looking for my problem.

I have written a function to perform validity checking on selected
portions of my worksheet data. The new function works great, except
that
as soon as I had tested it and added it to the conditional format
conditions on the relevant columns some of my other custom toolbar
macros began to misbehave.

I wrote a custom function so that I could test multiple (in excess of
6)
conditions and put the complexity of the formulas in VBA instead of
long
and cumbersome conditional formatting formulas.

If I disable the conditional formatting -- either by closing the
single
workbook that uses it, or by renaming the custom function -- all the
original macros return to normal behavior and function.

I set the conditional formating formula to:

=NOT(checkCyl(RCnn))

where nn is the column number under test. [It just occurred to me I
could use simply RC ... I'll test that.]

While investigating, I discovered this behavior - one of the custom
toolbar functions that misbehaves includes a call to this function
(the
debug.print statements added for testing -- about 30 lines of code):

Sub ProtectAll(Optional xAll As String = "All", _
Optional xProtect As String = "Yes")
' xAll: process All or Active sheet(s)
' xProtect: Yes to Protect, No to Unprotect

Dim xFrom As Integer, xTo As Integer

Debug.Print "ProtectAll Begin ScreenUpdating: ";
Application.ScreenUpdating
Application.ScreenUpdating = False
Debug.Print "ProtectAll Begin ScreenUpdating: ";
Application.ScreenUpdating

If xAll = "All" Then
xFrom% = 1
xTo% = Sheets.Count
Else
xFrom% = ActiveSheet.Index
xTo% = xFrom%
End If

For ii% = xFrom% To xTo%
If xProtect = "No" Then
Sheets(ii%).Unprotect
Else
Sheets(ii%).Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, _
AllowFormattingCells:=True
Sheets(ii%).EnableSelection = xlUnlockedCells
End If
Next ii%

Debug.Print "ProtectAll End ScreenUpdating: ";
Application.ScreenUpdating
Application.ScreenUpdating = True
Debug.Print "ProtectAll End ScreenUpdating: ";
Application.ScreenUpdating

End Sub

When my new function / conditional formatting is active, only the
first
three debug.print statements are executed. In fact, as near as I can
determine, VBA returns to Excel as though it completed normally, even
though it has 'bailed' somewhere 'in the middle' of the process.

I'm hoping that some of you "in this room" will have pointers for me
on
what to look for and / or how to proceed with testing.

(My new function is approximately 150 lines of code ... I'm hesitant
to
just throw it out here without invitation.)

 

[AltaEgo]

See in line
--
Steve

Looking down below your reply I see that line wrap broke apart my
debug.print lines;

Didn't have my spectacles on at the time!

The trouble seems to be that *other* functions (which have been working
flawlessly for months) suddenly fail without generating any error message
when I set up the conditional formatting. Again, from my OP:

Excel has additional calculation load.

**only the first three debug.print statements are executed**.

I discovered this by single stepping through some of my other, now
failing, code. While stepping through the code below, F8 would stop at 10
and 20, but when pressing F8 on line 20 the debugger would go inactive and
return to the "no code executing" state.

Sorry, cannot help.

Any other ideas for me to try?

I doubt this will help but you might try VBA code cleaner:

http://www.appspro.com/Utilities/CodeCleaner.htm

 

[Clif McIrvin]

Thanks again for taking the time to reply. I've come up with a couple
more ideas to try ... one of which is an alternative approach to
conditional formatting -- that additional calculation load I suspect is
quite significant in this case.

I'm half afraid to try a code cleaner .... I started this project
knowing nearly nothing about Excel or VBA ... much of that early code is
still in use <grin>.

Thanks again!

Didn't have my spectacles on at the time!

What!? That happens to you, too?!

 

[AltaEgo]

To remove your half fear, try it on a copy. First thing I expect you will
see is a sizeable file size reduction when you compare your clean copy to
the original.