Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: On error GoTo doesn't work properly

  1. #1
    Board Regular
    Join Date
    Feb 2015
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default On error GoTo doesn't work properly

    Dear All.

    In the code below I'm having problems with the second "On error GoTo" statement(UserCancelOnOutputSelection) - it doesn't work and that results in a runtime error: Division by zero.

    Can someone help me?

    Code:
    Option Explicit
    
    
    Sub SplitToMultipleColumns()
    
    
    Dim TitleId As String: TitleId = "Split Columns"
    Dim inputArray() As Variant
    Dim inputRange As Range
    Dim outputRange As Range
    Dim NumberOfoutputRows As Integer
    Dim NumberOfOutputColumns As Integer
    Dim NumberOfCells As Integer
    Dim i As Integer
    Dim ArrayInputvalue As Variant
    Dim iRow As Integer
    Dim iCol As Integer
    
    
    
    
    GoBackToSelectingRange:
            On Error GoTo UserCancelOnRangeSelection
            Set inputRange = Application.Selection
            Set inputRange = Application.InputBox("Select the range you want to split:", TitleId, inputRange.Address, Type:=8)
            On Error GoTo 0
        
    GoBackToSelectingOutputRange:
            On Error GoTo UserCancelOnOutputSelection
            NumberOfoutputRows = Application.InputBox("Enter number of rows that you want to split into:", TitleId)
            On Error GoTo 0
        
        NumberOfOutputColumns = inputRange.Cells.Count / NumberOfoutputRows
        
        
        On Error GoTo UserCancelOnOutputRange
        Set outputRange = Application.InputBox("Out put to (single cell):", TitleId, Type:=8)
        On Error GoTo 0
        
        
        ReDim inputArray(1 To NumberOfoutputRows, 1 To NumberOfOutputColumns)
        
        
        NumberOfCells = inputRange.Cells.Count - 1
        
        
        For i = 0 To NumberOfCells
            ArrayInputvalue = inputRange.Cells(i + 1)
                iRow = i Mod NumberOfoutputRows
                iCol = Int(i / NumberOfoutputRows)
                inputArray(iRow + 1, iCol + 1) = ArrayInputvalue
        Next i
        
        outputRange.Resize(UBound(inputArray, 1), UBound(inputArray, 2)).Value = inputArray
        
        Exit Sub
        
    '************************
    'Error handling section:
    '************************
    UserCancelOnRangeSelection: Exit Sub
    UserCancelOnOutputSelection: Resume GoBackToSelectingRange
    UserCancelOnOutputRange: Resume GoBackToSelectingOutputRange
    '*************************************************************
    End Sub

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,324
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On error GoTo doesn't work properly

    InputBox will return FALSE if the user cancels.

    Set inputRange = FALSE will generate an error. But NumberOfoutputRows = FALSE won't generate an error.

    So one way forward would be to test explicitly for the user cancelling, rather than using On Error GoTo.

  3. #3
    MrExcel MVP RickXL's Avatar
    Join Date
    Sep 2013
    Location
    UK North Midlands
    Posts
    4,313
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: On error GoTo doesn't work properly

    Hi,

    I think what you want to do is this:
    Code:
    Sub SplitToMultipleColumns()
    
        Dim TitleId                 As String
        Dim inputArray()            As Variant
        Dim inputRange              As Range
        Dim outputRange             As Range
        Dim NumberOfoutputRows      As Integer
        Dim NumberOfOutputColumns   As Integer
        Dim NumberOfCells           As Integer
        Dim i                       As Integer
        Dim ArrayInputvalue         As Variant
        Dim iRow                    As Integer
        Dim iCol                    As Integer
    
        TitleId = "Split Columns"
    
    GoBackToSelectingRange:
            On Error GoTo UserCancelOnRangeSelection
            Set inputRange = Application.Selection
            Set inputRange = Application.InputBox("Select the range you want to split:", TitleId, inputRange.Address, Type:=8)
            On Error GoTo 0
        
    GoBackToSelectingOutputRange:
            On Error GoTo UserCancelOnOutputSelection
            NumberOfoutputRows = Application.InputBox("Enter number of rows that you want to split into:", TitleId)
            On Error GoTo 0
        
        NumberOfOutputColumns = inputRange.Cells.Count / NumberOfoutputRows
        
        
        On Error GoTo UserCancelOnOutputRange
        Set outputRange = Application.InputBox("Out put to (single cell):", TitleId, Type:=8)
        On Error GoTo 0
        
        
        ReDim inputArray(1 To NumberOfoutputRows, 1 To NumberOfOutputColumns)
        
        
        NumberOfCells = inputRange.Cells.Count - 1
        
        
        For i = 0 To NumberOfCells
            ArrayInputvalue = inputRange.Cells(i + 1)
                iRow = i Mod NumberOfoutputRows
                iCol = Int(i / NumberOfoutputRows)
                inputArray(iRow + 1, iCol + 1) = ArrayInputvalue
        Next i
        
        outputRange.Resize(UBound(inputArray, 1), UBound(inputArray, 2)).Value = inputArray
        
        Exit Sub
        
    '************************
    'Error handling section:
    '************************
    UserCancelOnRangeSelection: Exit Sub
    'UserCancelOnOutputSelection: Resume GoBackToSelectingRange
    'UserCancelOnOutputRange: Resume GoBackToSelectingOutputRange
    UserCancelOnOutputSelection: Resume GoBackToSelectingOutputRange
    UserCancelOnOutputRange: Resume GoBackToSelectingRange
    '*************************************************************
    End Sub
    However, the length of the labels and the jumping backwards and forwards nature of the code hid it quite well.

    My preferred solution would look more like the following. The flow of the code is maintained and there are no labels to get your head round:
    Code:
    Sub SplitToMultipleColumns()
    
        Dim TitleId                 As String
        Dim inputArray()            As Variant
        Dim inputRange              As Range
        Dim outputRange             As Range
        Dim NumberOfoutputRows      As Integer
        Dim NumberOfOutputColumns   As Integer
        Dim NumberOfCells           As Integer
        Dim i                       As Integer
        Dim ArrayInputvalue         As Variant
        Dim iRow                    As Integer
        Dim iCol                    As Integer
    
        TitleId = "Split Columns"
    
        On Error Resume Next
        Set inputRange = Application.InputBox("Select the range you want to split:", TitleId, Selection.Address, Type:=8)
        On Error GoTo 0
        If inputRange Is Nothing Then Exit Sub
    
        Do While (NumberOfoutputRows <= 0)
            NumberOfoutputRows = Application.InputBox("Enter number of rows that you want to split into:", TitleId)
        Loop
    
        NumberOfOutputColumns = inputRange.Cells.Count / NumberOfoutputRows
        
        Do While (outputRange Is Nothing)
            On Error Resume Next
            Set outputRange = Application.InputBox("Out put to (single cell):", TitleId, Type:=8)
            On Error GoTo 0
        Loop
        
        ReDim inputArray(1 To NumberOfoutputRows, 1 To NumberOfOutputColumns)
        
        NumberOfCells = inputRange.Cells.Count - 1
        
        For i = 0 To NumberOfCells
            ArrayInputvalue = inputRange.Cells(i + 1)
            iRow = i Mod NumberOfoutputRows
            iCol = Int(i / NumberOfoutputRows)
            inputArray(iRow + 1, iCol + 1) = ArrayInputvalue
        Next i
        
        outputRange.Resize(UBound(inputArray, 1), UBound(inputArray, 2)).Value = inputArray
    
    End Sub
    RickXL

    Excel 2013 and Windows 10

  4. #4
    Board Regular
    Join Date
    Feb 2015
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On error GoTo doesn't work properly

    Thank you for your answers. I just don't understand why "UserCancelOnOutputSelection" wont go to it's error handler and won't Resume "GoBackToSelectingRange:"

    I don't think it should be necessary to test "NumberOfoutputRows" - since the error goto should have taken it to previous code?

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: On error GoTo doesn't work properly

    On Error GoTo 0 doesn't clear the current exception, it only disables the active error handler. Any error that occurs while there is an active exception will not be handled.

    Have a read of this: On Error WTF? | Excel Matters

  6. #6
    MrExcel MVP RickXL's Avatar
    Join Date
    Sep 2013
    Location
    UK North Midlands
    Posts
    4,313
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: On error GoTo doesn't work properly

    Quote Originally Posted by RoryA View Post
    On Error GoTo 0 doesn't clear the current exception, it only disables the active error handler. Any error that occurs while there is an active exception will not be handled.

    Have a read of this: On Error WTF? | Excel Matters
    Thanks again Rory - that's twice in a day!

    The link you pointed us to took some understanding but I think I am there now. My simple summary follows:

    If you use:
    Code:
    On Error GoTo 0
    or
    Code:
    On Error Resume Next
    Then things work as I would expect. The problem starts when you direct the code to a label. Even then everything works as expected unless you actually get an error. As soon as the code jumps to a label then that error handler is "locked-in" to the system and you can't change it again without a "reset" of some sort. Also, any subsequent errors will not be handled by that error handler either unless there is a "reset".

    The bit I did not know until today was that none of the following commands qualify as a reset:
    Code:
    On Error GoTo 0
    On Error Resume Next
    On Error GoTo AnotherErrorHandler
    The "official" reset command is:
    Code:
    On Error GoTo -1
    although the article in the link you provided deprecates its use. It is not clear to me why its use is not approved of. To quote from the article, one of the following is required to perform a "reset".
    The only ways to reset an active error condition and deactivate an error handler are via a Resume, Exit Sub, Exit Function, or Exit Property statement, or via an On Error Goto -1 statement.
    (Note: "Resume", above, means executing the Resume command directly, not putting it into an "On Error Resume" command because without an error that will not execute the Resume command.)

    Again, thanks for the info. Another gap in my Excel knowledge has been filled and my folder of Excel Examples now has another entry.

    Regards,
    Last edited by RickXL; Nov 23rd, 2015 at 12:48 PM.
    RickXL

    Excel 2013 and Windows 10

  7. #7
    Board Regular
    Join Date
    Feb 2015
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On error GoTo doesn't work properly

    The On Error GoTo -1 fixed it. Thank you very much both Rory and Rick

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: On error GoTo doesn't work properly

    Quote Originally Posted by RickXL View Post
    The link you pointed us to took some understanding
    Yeah, I know. I plan to rewrite it when I have time (probably in the new year).

    The "official" reset command is:
    Code:
    On Error GoTo -1
    although the article in the link you provided deprecates its use. It is not clear to me why its use is not approved of.
    Firstly, using deprecated items is always a little risky - although I think that one could argue VBA itself is somewhat deprecated in MS' eyes.

    More importantly, I dislike it because it tends to lead to badly written code. Just as there are occasions when Goto can be used wisely, I don't think it should be the first option you think of generally. If you need to be handling multiple different errors in different places, in my opinion you should probably be refactoring your code into separate, single-purpose routines, rather than simply adding On Error Goto -1 everywhere.

    To be clear though - that article represents my opinions and understanding, not an official document.

  9. #9
    MrExcel MVP RickXL's Avatar
    Join Date
    Sep 2013
    Location
    UK North Midlands
    Posts
    4,313
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: On error GoTo doesn't work properly

    Thanks for the clarification, Rory.

    I think I get it now.

    The assumption is that if you need to process an error you need to be fairly single-minded about it. The code goes to the fixing routine, the error handler, and it remains there until the problem is fixed. At that point you can resume normal operation and return to the point in the code where the error happened. That would usually be either the line that produced the error or the next one. So either Resume or Resume Next would normally be used. Hence, no real need for On Error GoTo -1. QED.
    RickXL

    Excel 2013 and Windows 10

  10. #10
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On error GoTo doesn't work properly

    Rick,

    Quote Originally Posted by RickXL View Post
    .....
    The link you pointed us to took some understanding but I think I am there now. My simple summary follows:
    ......,

    Just another angle....

    ... my interpretation after I learnt from Rory...

    https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo

    Alan

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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