Excel VBA macros and error messages

Question

I'm very inexperienced in Excel and I'm running into a problem programming a macro. It simply stops without any error dialog, no matter whether I set a breakpoint and step over or not.

Am I missing something? Do I have to turn on error messages explicitly?

Something like this, in a module:

Function Provoke(XYZ As Integer) As Integer
   Provoke = XYZ / 0
End Function

The Function is used in a formula inside of a cell, like this

=Provoke(4711)

How can I make it complain about the division by zero?

Answer 1

If an error occurs in a Function when called from a worksheet as a UDF, you won't see any error messages- you just get a #VALUE error returned to the cell(s)

If you call that same function from a Sub in VBA, you will see the error.

If you want to be able to return more-specific error messages you'll need to to a little more coding.

E.g.

Function Provoke(XYZ As Integer)

   On Error GoTo haveError
   Provoke = XYZ / 0
   Exit Function

haveError:
    'what type of error did we get?
    Select Case Err.Number
        Case 11: Provoke = CVErr(xlErrDiv0)
        Case Else: Provoke = "Error!"
    End Select

End Function

A good reference for this: http://www.cpearson.com/excel/ReturningErrors.aspx

Answer 2

Yes you need to make provision to tell VBA what to do if error occurs.

The error is occuring because of this line in your code:

Provoke = XYZ / 0

No number is divisible by zero and it generates an error. Basic math: any number ÷ 0 = ∞.

To verify firsthand, you may try something like this to trap the error number as well as source and description:

Function Provoke(XYZ As Integer)
    On Error Resume Next
    Provoke = XYZ / 0
    If Err.Number <> 0 Then Provoke = "Error# " & Err.Number & " - generated by " & Err.Source & " - " & Err.Description
    On Error GoTo 0
End Function

Now, if you try

=Provoke(4711)

You'll get the result like:

Error# 11 - generated by VBAProject - Division by zero