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

 

[RickXL]

Hi Alan, thanks for the link.

(Typo alert, you have a "memories" when I think you mean "memorise or memorize".)

I think what you said there is consistent with what I think happens and on the basis that most here will understand some VBA I have tried to distill it into some VBA-style pseudo-code. Please don't try and run this! It is not real.

Sub RaiseException(ReDirect, Statement)

    Application.CustomErrorHandler = False
    
    Select Case ReDirect        ' I.E. the "pointer" from the most recent On Error command
        Case "GoTo 0"
            Call VBADefault     ' This always ends in a Resume command
        Case "Resume Next"
            Clear Exception
            GoTo NextStatement  ' I.E. Next statement after Statement
        Case "GoTo -1"
            Clear Exception     ' Just reset the exception
        Case Else
            GoTo ReDirect
            ' The Error Handler at label ReDirect must Clear the Exception
    End Select
    
    If ExceptionCleared Then Application.CustomErrorHandler = ReDirect

End Sub

All VBA starts with an On Error GoTo 0 line. Excel effectively inserts that line for you but it is invisible.
When an error event is raised then something like the above code is called to process it. It is always passed the value of the most recent On Error keywords (ReDirect) and the current statement number (Statement).

Example 1 - On Error GoTo 0
First it uses (made up command) Application.CustomErrorHandler = False to disable any custom handlers.
Then it looks at the most recent On Error. (Assume it is GoTo 0).
The default VBA error handling routine kicks in to catch the exception.
Re-enable custom handlers.

Example 2 - On Error Resume Next
Disable any custom handlers.
Clear the exception.
GoTo the statement following the one with the error.
Re-enable custom handlers.

Example 3 - On Error GoTo ErrorLabel
Disable any custom handlers.
GoTo ErrorLabel
(Remember no custom exceptions allowed until the exception is cleared. To clear the exception you need to either: Resume (with or without the Next keyword), Exit Sub, Exit Function, Exit Property statement. So your error handler will need to end with one of these statements. The only alternative is to run an On Error Goto -1 statement. That will reset the error but do nothing else.)
If the exception is cleared then re-enable custom handlers.

Regards,

 

[DocAElstein]

Hi Rick,
Thanks for the reply and typo correction
Appreciate it -
your reply gives another interesting angle to what is a difficult Subject Thing to grasp.

Re: Your
Sub RaiseException(ReDirect, Statement)
And summaries below it.
_ - They are similar to my summaries in the table at the end of my notes. I have rewritten my summaries more often than I can remember. I think I have them right, or am about 95% there. Your input may help me finally get it right, now that I come back to the Theme after a long break from it……

Some comments then:

_0 ) Your idea:
“...All VBA starts with an On Error GoTo 0 line. Excel effectively inserts that line for you but it is invisible….“ I find this very interesting. – In my rambling notes i emphasize that
On Error GoTo 0
Does not go anywhere, but i do say it does something that could be thought of in an abstract way of doing that , - sort of staring again as far as errors are occurred. Which is sort of the same as having a
On Error GoTo 0
At the start.
So we may be having some parallel thinking there: - ( BTW, that should worry you a lot, as I am a bit of a Nut )

_1) your Example 1 - On Error GoTo 0
Agree with all that. ( But Note: it only works to disable the Error handler if a) either no error has occurred, or b) an error did occur but b)(i)you cleared the exception with a On Error Go To -1 or b)(ii) you cleared the exception a Resume.
These two ( :- On Error Go To -1 ;- and Resume ) are the only two things which clear the exception without also stopping the code ( I mean stopping the code by exiting the procedure (Exit Sub, Exit Funktion, Exit Property ) ).

_2) your Example 2 - On Error Resume Next
I am waiting for a bit of confirmation from the Master on this one
( scroll down bottom of page On Error WTF? | Excel Matters )
But i think
On Error Resume Next
Does not Clear the exception. It prevents ( suppresses an error) . So it is never raised. But i may be wrong here and the working is the same in any case either way, - So maybe no one knows exactly what VBA does here!!
It does not Re-enable custom / default error handler. It goes on working time and time again ( assuming you do nothing else to change that situation ) . This is the fundamental difference between
On Error Go To ______ ErrorLabel Place
And
On Error Resume Next
_ which is the thing that most catches people out and is why, i think, Rory wrote his Blog after answering so many Threads with this problem !!!!

_3 ) your Example 3 - On Error GoTo___ ErrorLabel
Almost Agree with all that.
Not sure if you are or are not saying the following: If there is a final Resume at the Error handler Label place, then you say the exception is cleared– Yes, I agree with that.. But I think you said also re-enable custom handlers. – I think Not on that: After the
Resume
the
On Error GoTo ErrorLabel Place
Will work again. It was not disabled by the
Resume
_ . This is a tricky conceptual point, and maybe no one really knows what is going on. – An error handler is not disabled, but will still not work if an Exception has been raised: I tried to explain that in my Notes, something along the lines....” VBA is in an Exceptional state of being, thinking that an error is being handled so ignores all further attempts at error handlers, or ignores further error handler statements that the code passes.... “ Again just a conceptual thing – open to interpretation: Your “....Remember no custom exceptions allowed until the exception is cleared….“ . Yous could say they are allowed – VBA will not Error if Yous put them in and the code passes them – it just ignores them. It takes notice of them again after Yous clear the exceptional state of being…. „


BTW: , from Rory’s stuff
"......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......" : I am not sure if that is completely correctly - On Error Goto -1 does not deactivate an error handler – it leaves Error handler enabled ( First error handler in my second demo code in my notes demonstrates that one )
Your point “.......an On Error Goto -1 statement. That will reset the error but do nothing else......” here supports me on this one. This could be a terminology thing. Maybe deactivate an error handler and disabling an error handler are two different things. Then what Rory said is OK. With “reset an active error condition” and “deactivate an error handler” he means “clear the exception” . He may just of made up a few terms here as he went along......

_ .......................................................................................

Some other points regarding your
Sub RaiseException(ReDirect, Statement)

The Error handler at the “Spring point Label Place:” does not have to clear the exception, but it usually does either because
if the Spring point Label Place:” is towards the end of the code, then after it the code ends ( exit Procedure is done ) )
or
if the Spring point Label Place: is not at the end , then usually a Resume is used. But it does not have to be. This is the typical case when the code goes on and an OP starts a Thread asking why in a loop his On Error GoTo ErrorLabel Place
only worked once.......... like this Nut:
.Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.Clear [SOLVED]







_............................................................................................


Your contribution is helping me get that last 5% clear in my head.

It is a difficult subject. I look forward to Rory’s re- write in the New year. I expect that will be the final Dog’s Bollox on this one. ( or the Mutt’s Nuts )



Alan

 

[RickXL]

Hi,

I am not quite sure that we are using exactly the same definitions so we might not see exactly eye to eye.

Chip Pearson has a quite good write up: Error Handling In VBA

I can see that you are a "detail person". I am too, but I have to distill that detail into something simple I can remember. Also, it does not matter what VBA actually does to form that model. All you need to remember is what it appears to do. Then I think how would I write the program. When I can write the program I think I am getting close.


My model starts by assuming that if an error occurs then VBA will always raise an error event. That event will always cause an error handler to be executed. (That handler could be one the programmer has coded or it could just be the one written by Microsoft.) Either way, an error handler is called.

How do you know which handler will be run? This is set with the On Error command. The On Error command posts a note to say where the execution should be directed if an error occurs.

What happens if you don't code an On Error command? Don't worry, VBA has, effectively, set On Error GoTo 0 for you.

So if you just rely on VBA or if you have coded an On Error GoTo 0 command then a subsequent error will cause the default VBA error handler to run. That is the one that shows the error message in a dialog and gives you the option to End or Debug. If you resume execution then the error is reset.

If you run an On Error Resume Next similar things happen when the next error is raised. It calls the default handler, in this case it does nothing but the error is reset by the Resume command and execution continues with the next instruction. Another error now will cause the same thing to happen but with the new statement.

If an error occurs after execution has been directed to a label (On Error GoTo ErrLabel) then that is where the execution goes, but, a bit like disabling events in a Worksheet_Change event handler, all bets are now off. You can't redirect the errors again (but see Note2 below). If an error occurs now then the default VBA handler will be called as a catch-all.

How does VBA know when the error has been fixed? The code could be directed anywhere so it needs a solid way to know. That is where the Resume, Exit Sub, Exit Function, Exit Property, and On Error GoTo -1 statements come in. When one of those is executed then VBA thinks the error handling is complete. All bets are back on and you can redirect errors again but it will still be remembering the most recently set error destination.

Note1: If you use the Resume option then there are several ways it can proceed. Resume, by itself will return to the line that caused the error. Resume Next will return to the line following the error and Resume ErrLabel will go to that label and continue.

Note2: If you are in the middle of processing an error, you can run On Error GoTo AnotherLabel. VBA will remember the label but will not act on it until the previous error has been reset.

Note3: All On Error commands, whether an error is being processed or not, will reset the Err object as will Err.Clear.

OK, that has got my brain in gear now to re-look at your previous post ...

BTW, that should worry you a lot ...

Yes, I am already quite worried

_1) your Example 1 - On Error GoTo 0

I think we are agreeing on that.

_2) your Example 2 - On Error Resume Next

I don't like to have two rules where one will do. So I treat On Error Resume Next as a special variation of this where "Fix error here" would be blank:

    On Error GoTo myError
    '''
    '''
    '''
myError:
        ' Fix error here
        Resume Next

It saves you from having to create the label and set up the code block etc.

_3 ) your Example 3 - On Error GoTo___ ErrorLabel

I am not sure what you are getting at here. You can use that construct anywhere, I think, even in the middle of handling an error. However, if you do it there it will not redirect anything. You will need to wait until after the reset has happened. Then the next error will pick up the most recent redirection.


I think I will get a drink and allow my brain to cool down now.

Regards,

 

[DocAElstein]

Hi

.....
I think I will get a drink and allow my brain to cool down now.
...

Think i will go for a jog in the Snow, have a cold shower and read the Thread and Rory's and my stuff again. i think we're are all roughly in agreement


Alan

 

[DocAElstein]

Hi Rick,
Thanks for the continual discussions..

Thanks for the link. Looks good, but i am puzzled he talks about just 3 Error statements.... As you rightly say we sometimes all have different definitions, and no one seems to be the real authority on this one..
Here is another link i like, where i cleared up a few things..
Difference Between 'On Error GoTo 0' And 'On Error GoTo -1'

_ I confess when I re thought about it your explanation of
On Error Resume Next
As a “blank bit” form of the
Resume after a
On Error Go To_____ label place
Sounds a bit better. I may go over to that way of thinking .. Perhaps needs a bit of clarity there, maybe...... but on the other hand....
I think we are in a grey area where no one knows, and the experts have opinions and understanding so the best experts maybe make things up or keep silent.... So we can do what we choose there I guess...

I think still we are agreeing mostly. As you say, definitions or rather wording may be a bit different in our heads – doesn’t help me that my brain mostly thinks initially in German these days... and has to translate that first...

Alan


P.s. i do get the bit about the
Err
Object

It is a good way to see if an error occurred after an Error handler such as
On Error Resume Next
By checking if
Err.Description= “” or not etc...

Clearing the exception, ( If i now agree with you that) on On Error Resume Next
), does not clear the
Err.Description
Register..._
_.. or what I mean there is that after an error occured whilst the On Error Resume Next is enabled, The Register is not cleared, ( of course - or it could never e used in such a case )...

P.p. s.
I initially was also a bit sceptical about opinions about thet one should mostly avoiding using Error handlers. In the meantime i have found though that i can find an alternative almost always...or rather up until now allways... ( I am sure there are exceptions, - with my limited experience I have not met them yet..

 

[RickXL]

... my brain mostly thinks initially in German these days... and has to translate that first...

That reminded my of something my French teacher used to say. He liked to point out that French speakers did not think in English then have to translate it into French!

You are right that you hardly ever need to use On Error. The original question is one of those exceptions.

When someone posts a question here with code that includes On Error Resume Next, I usually comment it out before I start. It is surprising how often that reveals the coding error the questioner is trying to locate!

Regards,