0

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?

YowE3K
  • 23,852
  • 7
  • 26
  • 40
moozeeque
  • 1
  • 1

2 Answers2

4

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

Community
  • 1
  • 1
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 1
    If you're looking to trap other specific errors as well, check http://onlinelibrary.wiley.com/doi/10.1002/9781118257616.app3/pdf as well (I always keep it bookmarked). – barvobot Sep 01 '17 at 19:44
1

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
curious
  • 1,504
  • 5
  • 18
  • 32
  • 2
    `XYZ / 0` doesn't return an error value, it raises an error. So, even if `Provoke` was a `Variant`, it would not work because there is nothing to be assigned to `Provoke`. If the error needs to be returned, the raised error needs to be trapped and then a `Variant/Error` could potentially be returned (after changing the function type to allow that). – YowE3K Sep 01 '17 at 19:56
  • good pt YowE3K. my initial focus was to highlight the fact that `XYZ / 0` is an infinite number. following your note, i have updated my answer accordingly. thanx – curious Sep 02 '17 at 01:36