Error Handler Is Only Exiting Sub

[Hmerman]

Good day all,
Hope you are well.

I have written a Error Handler based on analystcave.com' info on proper techniques. The Error Handler is for multiple errors, Error 11 (division by zero) and other errors. Two different nmessages are displayed in the event of errors.

The problem is even if I divide by zero on purpose (to check if the Error Handler works), there is no error message: the code seems to Exit Sub and no message or anything.

My code is formulas to calculate calibration of tractors and pumps for given values in textboxes on a Userform 1.

Here is my code with the error handler:
<code>
Private Sub CommandButton4_Click()
'----------------------------------------------------------------------------------------------------------
'CALCULATES THE CALIBRATION OF TRACTORS AND PUMPS FOR IRRIGATION.
'----------------------------------------------------------------------------------------------------------
Dim errMsg As String

On Error GoTo ErrorHandler

'in Textbox 8

If Arec8 = "" And Arec5.Value <> "" Then​

Arec8.Value = Arec5 / 60
​

End If

'in Textbox 6

If Arec6 = "" And Arec1 = "" And Arec7.Value <> "" And Arec8.Value <> "" Then​

Arec6 = Arec8 * Arec7
​

Else

If Arec6 = "" And Arec4.Value <> "" And Arec1.Value <> "" Then

Arec6 = (Arec4 * 100 * Arec1) / 10000
​

End If
​

End If

'in Textbox 1

If Arec1 = "" And Arec6.Value <> "" And Arec4.Value <> "" Then​

Arec1 = (Arec6 * 10000) / (Arec4 * 100) 'If the value in textbox Arec4 is zero I get a error 11 which should be Handled by the error handler below
​

End If

'in Textbox 7

If Arec7 = "" And Arec6.Value <> "" And Arec8.Value <> "" Then​

Arec7 = Arec6 / Arec8 'If the value in textbox Arec4 is zero I get a error 11 which should be Handled by the error handler below
​

End If

Arec1 = Format(Arec1.Value, "0")​

Arec3 = Format(Arec3.Value, "0")​

Arec4 = Format(Arec4.Value, "0.00")​

Arec5 = Format(Arec5.Value, "0.00")​

Arec6 = Format(Arec6.Value, "0.00")​

Arec7 = Format(Arec7.Value, "0.00")​

Arec8 = Format(Arec8.Value, "0.00")
​

Exit Sub
ErrorHandler:

Select Case Err.Number
​

Case 11:
​

GoTo DivideByZeroError
​

Case Default:
​

GoTo OtherError
​

End Select
​

DivideByZeroError:

Debug.Print "Cannot divide by zero!"​

Err.Clear
​

Exit Sub

OtherError:

errMsg = "Error number: " & Str(Err.Number) & vbNewLine & _​

"Source: " & Err.Source & vbNewLine & _​

"Description: " & Err.Description​

Debug.Print errMsg​

Err.Clear
​

Exit Sub
​

</code>

<code> End Sub
</code>
The text in red is where a zero would give a error 11.

Can someone please help to make the Error Handler work?

Regards
Herman
​

 

[Joe4]

Have you tried stepping though your code line-by-line using the F8 key?
You have a lot of IF statements in there, so it is possible that your tests may not even be hitting those lines of code. Using the F8 key to go through a test line-by-line and seeing exactly what is happening each step of the way should shed some light on exactly what is happening.

 

[Hmerman]

For the sake of clarity the calculations on the Userform work fine when I am not inducing an error.

When I press f8 the userform loads and I cannot step into the private sub CommandButton4. Therefore I tried something else:

Not sure if this is the correct way but I copied the code into a module, loaded the Userform, filled the relevant textboxes with zeroes to cause the error and went through it line for line as you suggested.

What happens is that the code goes until the line with the first If (textbox 8), then jumps to the Select Case err.Number, then goes through each Case, Exit Select, DivideByZeroError: Debug.Print, Err.Clear and stops at Exit Sub.

I am not sure why it behaves in this manner. I have always used the resume next error handler, but as I am not the end user and this macro will be used as an add-in I decided to do it properly.

Do you have another suggestions or advice?

 

[Fluff]

What are you expecting to happen?
From your description, the error handler is working correctly.

 

[Hmerman]

At the line:
<code>
<code>Debug.Print "Cannot divide by zero!"</code>
</code>
it should print the "Cannot divide by zero!" message which informs the user that he or she is using a zero as a denominator. There is no message.

I also tried declaring the message as a string (below the code), but still no message.
<code><code>
Dim erMg as String

DivideByZeroError:
erMg = "Cannot divide by zero!"

Debug.Print erMg
​

</code>
</code>
Sorry I am still learning. How do I get the message to show?

Regards
Herman

 

[Kyle123]

it is printing to the immediate window, the user doesn't see that, it's a development tool. if you want the user to see it, you need to use a message box

 

[Hmerman]

Ctrl + G The MsgBox solved it.

Thank you very much for everybody's input. I learned a lot in the last 24 hrs about Error Handlers and Debugging.

Have a gr8 2018!