Word VBA, Single and double data types

Hi

I have a theoretical question on this subject but I'm naff at maths so don't
laugh.

From a book, I can see that the lower and upper limits of a single data type
is

-3.402823E38 to - 1.401298E-45 for negative values
1.401298E-45 to 3.402823E38 for positive values

I don't even know how to do calculations with exponents on my computer to
check for myself but what I'd like to know is

a How do - exponents work

eg 10 to the power of 2 = 100
10 to the power of -2 = ?

b Do the limits of a single or double data type leave a 'gap' in between?

ie A short data type (-32,768 to 32767) can clearly handle any figure in
between but this isn't so clear for me with regards to floating point data
types where there seems to be a gap.

Why not -0.000000 and 0.000000 or sth similar?

Thanks and remember what Einstein4S Maths teacher said about him when he was
16 (ok I ain't 16 anymore but...)


Dave Neve

Hi Dave,

A negative exponent means "divide 1 by the base (10) to the positive
exponent", for example,

10 to the power of -2 = 1/(10 to the power of 2) = 1/100 = 0.01

You can also think of exponents of 10 as "moving the decimal point",
to the right if positive or to the left if negative. For example,

3.14E2 = 314
3.14E-2 = 0.0314

You're correct, there is a gap between zero and the smallest positive
number that can be represented as a single or double data type.
There's an equal gap between zero and the smallest negative number
that can be represented by a single or a double. This gap is purely a
result of the limited number of bits used to store the number in the
computer's memory, and not true for real numbers. The gap is less for
doubles than for singles, because doubles can store more digits.

In digital storage as in real numbers, there is only one zero and it
is neither positive nor negative.

By the way, I give you credit for asking. Lots of people would just
shrug and say "that's the way it is".

n Sun, 1 Aug 2004 09:59:48 +0200, "Dave Neve"
< XXXX@XXXXX.COM > wrote:


This is called scientific notation. A google search might lead to a
good tutorial.

The exponent simply tells you how many places to move the decimal
point. A positive exponent means move to the right, a negative to the
left. For example, 1.234E2 is 123.4 while 1.234E-1 is .1234 etc.

When dealing with large exponents, you can assume there are 0s to the
left or right of the number. Thus, 1.23E5 is 123000 and 1.23E03 is
.00123 etc.


A negative exponent means 1 over 10 to the equivalent positive power
(absolute value). 10 to the power -2 is 1 over 10 to the power 2 is 1
over 100 is .01. Using the notation above for consistency, these
could be written 1E2 (or 1.0E2) and 1E-2.


This is called floating point arithmetic and another google search
might help.

In normal arithmetic, we are taught that the real numbers are
continuous and dense. In computer arithmetic, we suffer under
limitations of precision and range.

Consider your original comment above. 1.401298E-45 is the smallest
possible positive (greater than 0) value the type can represent. In
normal arithmetic, you could divide this value by 2 to get
7.00649E-46. On your computer, you cannot. Since the result of the
division is smaller than the smallest representable value, you would
probably get 0.

In fact, since a finite number of bits are used to represent the
numbers, the vast majority of numbers are not representable exactly.
(How many digits does it take to write the decimal representation of
1/3 by hand?) Since computers represent numbers in binary, even
simple numbers have this problem. For example, 10.0 can be
represented exactly but .1 cannot. As a consequence of this built-in
inaccuracy, the expression 10.0 - 100 * .1 may not compare equal to 0.

So the answer to your question is yes. The types on a computer can
only represent a finite sample of all the possible values within the
range. The computer uses these representable values to approximate
all the other values within the range.

In addition to limitations on range, precision can cause non-intuitive
results also. For example, 10 - 10.0000000000000000000999 can very
easily compare equal to 0 when we all now the result should be
negative. This is because there are not enough bits in the type for
the computer to distinguish between the two values. They are both
approximated by the same representable value.


A short can only handle the integers in this range. In arithmetic,
dividing 3 by 2 and then multiplying the result by 2 will produce 3
again. On your computer it will produce 2 (or 4). This is because
the intermediate result of 1.5 cannot be expressed as a short and most
systems truncate (but some round).


0 is representable exactly in your type. However, 0 is neither
positive or negative so was not included in the two ranges you quoted.
Some systems do distinguish between -0 and +0 but that is a different
discussion.



<<Remove the del for email>>

Hi

Thanks for the maths lesson.

But what about this gap then. How can you use singles and doubles data types
especially if the user is going to assign variables like in a calculator
program.

The result of the calculation could fall into the gap.

Would the program round off, cite an unhandled exception or just crash?

Regards

Dave Neve

XXXX@XXXXX.COM ...

don't
type
between?
data
was

For an in-depth discussion, see
http://research.microsoft.com/ ~hollasch/cgindex/coding/ieeefloat.html

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

i Dave,

First let me say what *should* happen: When the macro calculates a
number too small to store (but not zero), the VBA core should cause an
error (this condition is formally called "underflow", the opposite of
the "overflow" error when a number is too big). Your macro should be
written to trap that error and display a message to the user, so they
know not to trust the result.

What *does* happen in VBA is that sometimes it incorrectly returns the
smallest storable number, and sometimes it rounds down to zero, but it
never causes an underflow error. For example, the calculation

Dim X As Single
X = 1.5E-45 * 0.9
MsgBox X

displays the result 1.401298E-45 (the smallest Single value) although
the correct answer is 1.35E-45. If you change the multiplier from 0.9
to 0.4, it displays the result 0.

Things are even stranger in the case of Double variables. If you try
to make a literal assignment of any number smaller than 1e-309 to a
Double, the VBA editor will replace the number you entered with the
nearest number that can be represented exactly in binary notation
(which is what the computer uses internally). For example, try to type
this in:

Dim X As Double
X = 1E-310

As soon as you move the cursor off the second line, VBA will replace
it with this:

X = 9.99999999999997E-311

Doing a calculation that results in an underflow will again give bogus
results without causing an error:

Dim X As Double
X = 1E-309 * 0.000000000000004 ' 4E-15
MsgBox X

should result in 4E-324 but does return 4.94065645841247E-324, the
smallest Double value.

I've never bothered to write a calculator in VBA -- there are plenty
of excellent calculators if you need one, and the problem doesn't
interest me as a programming exercise -- so I haven't looked at ways
to work around these bugs.

Regards,
Jay Freedman http://aspnet2.com/mvp.ashx?JayFreedman
Microsoft Word MVP FAQ: http://www.mvps.org/word

"Dave Neve" < XXXX@XXXXX.COM > wrote:

On Sun, 1 Aug 2004 19:35:02 +0200, "Dave Neve"



The program will assign the representable value which comes closest to
approximating the value specified by the user.


Some round, some truncate. Most only issue an exception for overflow.
I don't know of any that will crash but that doesn't mean they aren't
out there.


<<Remove the del for email>>