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

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

"Clif McIrvin" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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
>
>
>
>

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

"AltaEgo" <Somewhere@NotHere> wrote in message
news:(E-Mail Removed)...
> 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:

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

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

>> When my new function / conditional formatting is active,

**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: "; _
>> Application.ScreenUpdating
20 >> Application.ScreenUpdating = True
30 >> Debug.Print "ProtectAll End ScreenUpdating: "; _
>> Application.ScreenUpdating
>>
40 >> End Sub
>>

Any other ideas for me to try?

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

Again,
Thanks for responding!

--
Clif

>
>
> --
> Steve
>
> "Clif McIrvin" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
>> 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
>>
>>
>>
>>



--
Clif

See in line
--
Steve

"Clif McIrvin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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:
>


>>> When my new function / conditional formatting is active,

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


--
Steve

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?!

--
Clif

"AltaEgo" <Somewhere@NotHere> wrote in message
news:OxMr%(E-Mail Removed)...
> See in line
> --
> Steve
>
> "Clif McIrvin" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> 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:
>>
>
>
>>>> When my new function / conditional formatting is active,
>
> 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
>
>
> --
> Steve



--
Clif

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.

--
Steve

> I'm half afraid to try a code cleaner
>> http://www.appspro.com/Utilities/CodeCleaner.htm