Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
2003: Having trouble with conditional formatting with custom user function (Repost)
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
2003: Having trouble with conditional formatting with custom user function (Repost)
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
2003: Having trouble with conditional formatting with custom user function (Repost)
AltaEgo, thanks for responding. My responses are inserted "in line"
below: "AltaEgo" <Somewhere@NotHere wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
2003: Having trouble with conditional formatting with custom user function (Repost)
See in line
-- Steve "Clif McIrvin" wrote in message ... 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 |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
2003: Having trouble with conditional formatting with custom user function (Repost)
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 ... See in line -- Steve "Clif McIrvin" wrote in message ... 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 |
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
2003: Having trouble with conditional formatting with custom user function (Repost)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - Applying Format Based Custom Functions | Excel Discussion (Misc queries) | |||
Trouble Finding Duplicate Values with Conditional Formatting | Excel Discussion (Misc queries) | |||
Custom Format for User Defined Function | Excel Discussion (Misc queries) | |||
Trouble with hyperlinks (repost) | Excel Discussion (Misc queries) | |||
Conditional Formatting (Dates) Repost with additional data | Excel Discussion (Misc queries) |