On error GoTo doesn't work properly

JacobMortensen

Board Regular
Joined
Feb 25, 2015
Messages
85
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,773
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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
 

JacobMortensen

Board Regular
Joined
Feb 25, 2015
Messages
85
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,177
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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:

JacobMortensen

Board Regular
Joined
Feb 25, 2015
Messages
85
The On Error GoTo -1 fixed it. Thank you very much both Rory and Rick:)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,177
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,858
Messages
5,489,306
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top