Why do 1/0 and Sqr (-1) throw error in MSOffice VBA, but not in VB.NET?

user_19149687 0
Viewed 63

In MSOffice VBA, 1 / 0 throws error 11, and Sqr (-1) throws error 5. But in VB.NET, 1 / 0 does not throw an error, whose result is "∞". Math.Sqrt (-1) does not throw error either, whose result is "NaN". Why does it happen?

vba vb.net
1 Answers

Because the spec says so.

Both .NET's Double and VBA's Double are IEEE floating point values supporting the special values

  • positive infinity,
  • negative infinity, and
  • NaN ("not a number").

However, in VBA, 1 / 0 is defined in the VBA spec to

  • not only set the result to NaN, but
  • also to raise an error:

If this results in dividing a nonzero value by 0, runtime error 11 (Division by zero) is raised.

[...]

In either of these cases, if this expression was within the right-hand side of a Let assignment and both operands have a declared type of Double, the resulting IEEE 754 Double special value (such as positive/negative infinity or NaN) is assigned before raising the runtime error.

Thus, you can initialize a VBA Double to positive infinity as follows:

Dim d As Double

On Error Resume Next
d = 1 / 0
On Error GoTo 0

Debug.Print d   ' yields " inf"
user_876980
Related