On error GoTo doesn't work properly

[JacobMortensen]

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?

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

 

[StephenCrump]

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.

 

[RickXL]

Hi,

I think what you want to do is this:

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:

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

 

[JacobMortensen]

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?

 

[RoryA]

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

 

[RickXL]

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:

On Error GoTo 0

or

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:

On Error GoTo 0
On Error Resume Next
On Error GoTo AnotherErrorHandler

The "official" reset command is:

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,

 

[JacobMortensen]

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

 

[RoryA]

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:

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.

 

[RickXL]

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.

 

[DocAElstein]

Rick,

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