Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm programming a small loop in which I test what is in a cell. The in the "else" clause of the case, I am checking for a graphical element in the cell (for e.g. cell "B2" named "myShape_$B$2"). Here's a piece from my code:

    Case Else
    On Error GoTo NoHalf
    Select Case ActiveSheet.Shapes("myShape_" & C.Address).Fill.ForeColor.RGB
    Case RGB(164, 255, 164) 'Green
    'something
    Case RGB(201, 201, 201) 'Grey
    'something else
    End Select
    '
    NoHalf:
    On Error GoTo 0
    End Select


    This works fine... for the first few cycles of the outer loop. Then suddenly I get an error in the "Select Case" line... how is that possible if I'm trapping that???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What happens if you place

    NoHalf:
    On Error GoTo 0

    after the line

    End Select

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    What happens if you place

    NoHalf:
    On Error GoTo 0

    after the line

    End Select
    (Doesn't seem logical) I did it and it does not help (more exact: it does not seem to make any difference)

    Strange problem isn't it?

    Here's the error I get:

    Run-time error '-2147024809 (80070057)':

    The item with the specified name wasn't found.


  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by ErikJan View Post
    Code:
    NoHalf:
        On Error GoTo 0
        End Select
    If you add a debug.print statement into your NoHalf, is it actually running? Does it ever run twice?

    If I recall correctly, to restore error handling from an error handler, you usually need to use the Resume statement. For example (air code/not tested):

    Code:
            	Case Else 
                	On Error GoTo NoHalf
                	Select Case ActiveSheet.Shapes("myShape_" & C.Address).Fill.ForeColor.RGB
                	Case RGB(164, 255, 164)    	'Green
                    	'something
                	Case RGB(201, 201, 201)    	'Grey
                    	'something else
    CloseTheStructure:
                	End Select
                	'
    NoHalf:
                	On Error GoTo 0
            	Resume CloseTheStructure

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Select Tools | Options... in the Visual Basic Editor.
    Activate the General tab.
    Make sure that "Break on unhandled errors" is selected in the "Error Trapping" section.

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Select Tools | Options... in the Visual Basic Editor.
    Activate the General tab.
    Make sure that "Break on unhandled errors" is selected in the "Error Trapping" section.
    It was (and is)

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jscher2000 View Post
    If you add a debug.print statement into your NoHalf, is it actually running? Does it ever run twice?

    If I recall correctly, to restore error handling from an error handler, you usually need to use the Resume statement. For example (air code/not tested):

    Code:
            	Case Else 
                	On Error GoTo NoHalf
                	Select Case ActiveSheet.Shapes("myShape_" & C.Address).Fill.ForeColor.RGB
                	Case RGB(164, 255, 164)    	'Green
                    	'something
                	Case RGB(201, 201, 201)    	'Grey
                    	'something else
    CloseTheStructure:
                	End Select
                	'
    NoHalf:
                	On Error GoTo 0
            	Resume CloseTheStructure
    Thanks, although this is (also?) strange as the code after "End Select" will run into the Resume statement, you did make me try the resume statement and I think that's working... Still some (other?) quirks... I'll test a bit and will come back with what I finally have.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ErikJan View Post
    Thanks, although this is (also?) strange as the code after "End Select" will run into the Resume statement, you did make me try the resume statement and I think that's working... Still some (other?) quirks... I'll test a bit and will come back with what I finally have.
    Yep... here it is (a bit spaghetti by I see not simpler way amd it does work)

    Code:
                        Case Else
                        	On Error GoTo NoHalf
                        	Select Case Worksheets("Something").Shapes("myShape_" & C.Address(False, False)).Fill.ForeColor.RGB
                        	Case RGB(164, 255, 164)	'Green
                            	'Something
                        	Case RGB(201, 201, 201)	'Grey
                            	'Something else
                        	End Select
                        	GoTo Continue
                        	'
    NoHalf:
                        	On Error GoTo 0
                        	Resume Continue
    Continue:
                    	End Select

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by ErikJan View Post
    Yep... here it is (a bit spaghetti by I see not simpler way amd it does work)
    Aha, I think you're saying that during normal execution the event handler runs. To prevent that, I usually place all the event handlers together before the End Sub statement, and place an Exit Sub statement just before the first event handler.

  10. #10
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by jscher2000 View Post
    Aha, I think you're saying that during normal execution the event handler runs. To prevent that, I usually place all the event handlers together before the End Sub statement, and place an Exit Sub statement just before the first event handler.
    This is standard practice, and is part of many formal VBA coding standards, e. g.,

    Code:
    Sub MySub()
    
       On Error goto MySub_Err
    	' Do something.
    
      MySub_End:
    
    	Exit Sub
    
    MySub_Err:
    
    	' Report the error.
    	Resume MySub_End
    
    End Sub
    Another option is to limit your error handlers to the top level routines, and let the error "bubble up." The advantage of this approach is that all exceptions are caught and handled. The disadvantage is that you may not be able to pin down the exact source and cause of the error, because the stack trace is unwound before the exception is finally caught.

    You should also bear in mind that, once a routine transfers control to its error handler, the handler is immediately disabled. When intermittent errors are possible, such as in file I/O routines, it is common to trap specific errors, re-enable the error handler, and resume to a label inside the routine that just threw the exception, e. g.,

    Code:
    Sub Intermittent (ByVal pintMaxRetries As Integer)
    
    	On Error GoTo Intermittent_Err
    	' Do ordinary stuff.
    	Dim intRetryCount as Integer : intRetryCount = 0
    
    Intermittent_Try:
    
    	' Do something that might have intermittent errors.
    
    Intermittent_End:
    
    	Exit Sub
    
    Intermittent_Err:
    
    	Select Case Err. Number
        	Case RecoverableErrorNumber
            	If intRetryCount  < pintMaxRetries Then
                	intRetryCount = intRetryCount + 1
                	On Error GoTo Intermittent_Err	'  Re-enable handler.
                	Resume Intermittent_Try       	'  Try again.
            	Else
                	' Report the error, and that it failed pintMaxRetries times.
                	Resume Intermittent_End:      	' Bail out.
            	End If
    
        	Case AnotherRecoverableErrorNumber
            	If intRetryCount  < pintMaxRetries Then
                	intRetryCount = intRetryCount + 1
                	On Error GoTo Intermittent_Err	'  Re-enable handler.
                	Resume Intermittent_Try       	'  Try again.
            	Else
                	' Report the error, and that it failed pintMaxRetries times.
                	Resume Intermittent_End:      	' Bail out.
            	End If
    
        	Case Else
            	' Report other errors.
            	Resume Intermittent_End:          	' Bail out.
    	End Select
    
    End Sub
    Since this sub takes the retry count as argument pintMaxRetries , you can change the retry count at run time.

    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    To prevent spaghetti code, you could also try this:

    Code:
        Dim lRGB As Long
        'Set initial value for rgb to -1, if an error occurs,
        'this is the value that remains
        lRGB = -1
        On Error Resume Next
        lRGB = ActiveSheet.Shapes("myShape_" & C.Address).Fill.ForeColor.RGB
        On Error GoTo 0
        
        Select Case lRGB
        Case RGB(164, 255, 164)                 'Green
            'something
        Case RGB(201, 201, 201)                 'Grey
            'something else
        Case -1
            'Code errored, do something here?
        End Select
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by pieterse View Post
    To prevent spaghetti code, you could also try this:

    Code:
        Dim lRGB As Long
        'Set initial value for rgb to -1, if an error occurs,
        'this is the value that remains
        lRGB = -1
        On Error Resume Next
        lRGB = ActiveSheet.Shapes("myShape_" & C.Address).Fill.ForeColor.RGB
        On Error GoTo 0
        
        Select Case lRGB
        Case RGB(164, 255, 164)             	'Green
            'something
        Case RGB(201, 201, 201)             	'Grey
            'something else
        Case -1
            'Code errored, do something here?
        End Select
    I didn't think about that one... nice! Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •