12

Why is 0/0 throwing Overflow error in VBA, while in .Net languages it is simply a Division by 0 error?

E.g., in C# it is a System.DivideByZeroException

static void Main()
{
    int k = 0;
    int p = 0;
    Console.WriteLine(k/p);
}

Div/0 error exists in VBA. But 0/0 gives an overflow exception, while anything else divided by 0 gives a Div/0 exception:

Public Sub TestMe()

    'Integer
    PrintAndCheck (11)      '- Division by zero error

    'Double
    PrintAndCheck (0.9)     '- Division by zero error

    'Long
    PrintAndCheck (50000)   '- Division by zero error

    'String
    PrintAndCheck ("1.1")   '- Division by zero error

    '----------------------------------------------------
    '----------------BUT---------------------------------
    '----------------------------------------------------

    'Integer
    PrintAndCheck (0)       '- Overflow?

End Sub

Public Sub PrintAndCheck(lngDivisor As Variant)

    On Error Resume Next

    Debug.Print lngDivisor / 0
    Debug.Print Err.Description & " from type -> " & VarType(lngDivisor)

    On Error GoTo 0

End Sub

That's what you get in the immediate window:

Division by zero from type -> 2
Division by zero from type -> 5
Division by zero from type -> 3
Division by zero from type -> 8
Overflow from type -> 2

Edit: To make the whole story more interesting:

Public Sub TestMe()
    On Error Resume Next
    Debug.Print Evaluate("0/0")     'Division by 0 error (CVErr(xlErrDiv0)=2007)
    Debug.Print 0 \ 0               'Division by 0 error
    Debug.Print Err.Description
    On Error GoTo 0
End Sub
Community
  • 1
  • 1
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Have you *explicitly* tried 0 / 0? – meowgoesthedog Aug 03 '17 at 13:23
  • @meowgoesthedog - in C# it does not compile, in VBA it gives `Overflow` – Vityata Aug 03 '17 at 13:24
  • 12
    If every language behaved identically in all circumstances, there wouldn't be different languages. Since `0/0` is very poorly defined, there no single correct response to it. – Damien_The_Unbeliever Aug 03 '17 at 13:32
  • 1
    It's an interesting question, which I think would be better if you left C# / .Net exceptions out of it, and re-worded it to compare VBA behavior when dividing by zero with a non-zero numerator VS. a zero numerator. – Rufus L Aug 03 '17 at 13:56
  • classic VB had A LOT of quirks and inconsistent behavior, which is one of the reasons many base behaviors were changed when they implemented the .NET platform. – Jeremy Aug 03 '17 at 13:59
  • 4
    It is worth pointing out that 0 / 0 is a special case. The normal argument against calculations of the form x / 0 where x <> 0 is that there is no number y such that y * 0 = x, and that x / 0 is therefore undefined. This is not the case for 0, as 0 * 0 is indeed 0. So if VBA generates a different error for 0 / 0 as opposed to 1 / 0, I take my hat off to it! – Jonathan Willcock Aug 03 '17 at 14:18
  • Calculators sometimes give different errors for 0/0 and 1/0. My HP-39 calculator gives "Infinite Result" for 1/0 but "Undefined Result" for 0/0. Calling the 0/0 error an "overflow" error is perhaps a bit odd, but serviceable. – John Coleman Aug 03 '17 at 14:30
  • If you try same code in VB6 where you have control over optimization flags, checking "Remove floating point error checks" turns the errors into `-1,#IND` for `0/0`, `1,#INF` for `1/0`, and `-1,#INF` for `-1/0` (note the difference between `INF` and `IND`). It would then appear https://stackoverflow.com/a/347940/11683 explains why it is so (non-zero divided by zero is an infinity, zero divided by zero is a NaN). Apparently VB decides to expose the `NaN` as an overflow, which apparently is expected, as "[0/0 is not a divide by zero error](https://stackoverflow.com/a/7212363/11683)". – GSerg Aug 03 '17 at 21:50
  • @JonathanWillcock - see the edit, probably you would keep your hat on. – Vityata Aug 04 '17 at 08:15
  • I still like Overflow as the Exception for 0/0. a/b = c is equivalent to a = bc As I wrote in my comment, a/b is undefined for a <> 0 and b=0 since there is no solution for c in a = bc. Note it is not infinity but undefined. As the dividor tends to 0 then the answer tends to infinity, is however true. The problem with 0/0 is not that in this case there is no solution for c, when re-written, but that every number is a solution. Again the set of all numbers is not the same as infinity but given that it includes massive numbers I think overflow is a good exception. – Jonathan Willcock Aug 04 '17 at 08:41
  • The last edit is actually not surprising. `Evaluate` does not raise an error, it returns a Variant value with an error flag, and there is no "overflow" flag available. The ``\`` is the integer division operator, so there is no floating point number that could contain an `#IND` to begin with. – GSerg Aug 04 '17 at 20:00
  • Anyone willing to add an answer, explaining all the cases? :) – Vityata Aug 07 '17 at 21:47
  • @GSerg - this thing with the flags is interesting. Can you make an answer with screenshots, I am not sure I have seen anythinglike this in `VBA`. Still, if it is `VB6`, it can be interesting again :) – Vityata Aug 07 '17 at 21:59
  • @JonathanWillcock - I understood the reason for being an Overthrow Exception fan of `0/0`, but the point is that VBA is somehow not consistent there - e.g. `Debug.Print Evaluate("0/0")` gives `xlErrDiv0` – Vityata Aug 07 '17 at 22:01
  • @Vityata It's just an educated guess. I don't know for a fact it's true. I can't turn it into an answer. – GSerg Aug 07 '17 at 22:49

2 Answers2

1

https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/floating-point-division-operator

Besides the obvious differences in implementation of languages and the way VBA handles division, MS Doc link above expands on the reasons for overflow exception , that if the operand data types are integer then it will throw Overflow exception (Last statement below)

enter image description here

Alternatively there is \ division operator that checks the range for you and throws Division by zero exception

Ravi Yenugu
  • 3,895
  • 5
  • 40
  • 58
  • 4
    That's an interesting reading indeed, but it is for `VB.Net`. In `VBA`, a `Single`, `Double` or even a `String` value of **0/0** would throw an Overthrow error. – Vityata Aug 03 '17 at 14:25
  • Wouldn't division using string become overloaded and converted to number? – Ravi Yenugu Aug 03 '17 at 14:32
  • 1
    @While it is true that this is for `vb.net`, the rationale for raising an overflow error as opposed to a division by zero error is doubtless the same. Still, it would be good to find a link to the *VBA* or at least *VB6* documentation for this point. – John Coleman Aug 03 '17 at 14:32
  • @RYenugu - yes. The result would be a number. Or an error :) – Vityata Aug 03 '17 at 14:40
  • I guess in my opinion the error type is dependent on the operand types in question, that's all I'm trying to say :) – Ravi Yenugu Aug 03 '17 at 14:46
  • 1
    @RYenugu - I understand, but in `VBA` zero is an `Integer`. See the updated version of the question. – Vityata Aug 03 '17 at 14:56
1

Will try to summarize the answers from the comments:

  1. In VBA 0/0 throws an Overflow exception, because 0/0 is a specific case of division by 0. Thus, it is a good idea to throw a different exception than the standard Division by zero error.

  2. In VBA Evaluate("0/0") returns a Division by zero error, because Evaluate does not raise an error, it returns a Variant value with an error flag, and there is no "overflow" flag available.

  3. In VBA integer division 0\0 returns a Division by zero error, because the result should be an integer value and #IND is a floating point value. As far as #IND cannot be returned, it gives the next best thing - Division by zero error.

More reading concerning 0/0 in other languages:

Vityata
  • 42,633
  • 8
  • 55
  • 100