0

As I was debugging my VBA code, I came across this weird phenomenon:
This loop 

Dim x,y as Double  
x = 0.7  
y = 0.1  
For x = x - y To x + y Step y
Next x

runs only twice!

I tried many variations of this code to nail down the problem, and here is what I came up with:

  • Replacing the loop boundaries with simple numbers (0.6 to 0.8) - helped.
  • Replacing variables with numbers (all the combinations) - didn't help.
  • Replacing the for-loop with do while/until loops - helped.
  • Replacing the values of x and y (y=0.01, 0.3, 0.4, 0.5, 0.7, 0.8, 0.9 - helped. 0.2, 0.6 -didn't help. x=1, 2 ,3 helped. x=4, 5, 6, 7, 8, 9 - didn't help.
  • Converting the Double to Decimal with CDec() - helped.
  • Using the Currency data type instead of Double - helped.

So what we have here is a floating-point rounding-error that happens on mysterious conditions.

What I'm trying to find out is what are those conditions, so we can avoid them.
Who will unveil this mystery?

(Pardon my English, it's not my mother tongue).

falcon
  • 1
  • 1
  • I would not use`x` for two different purposes in one line as you have. Also `x` is not a double. You need to declare each variable separately. – Doug Glancy Nov 16 '15 at 05:24
  • Basically it always happens. Only time it doesn't is when the number can be written as the sum of powers of 2 and doesn't have too many digits. Exact same problem as a human trying to get the perfect answer to 10 / 3, only perfect if you count with 3 fingers. A human likes 0.1, can be written cleanly as a sum of powers of 10. A computer doesn't, with 2 fingers it has an infinite number of digits. Use the Decimal type if you don't like surprises like this, it calculates with base 10. Very slow on a computer but more predictable. – Hans Passant Nov 16 '15 at 08:57

1 Answers1

0

GD Falcon,

Generally in solving a For...Next loop it would not be advisable to use 'double' or 'decimal' or 'currency' variables as they provide a level of uncertainty in their accuracy, it's this level of inaccuracy that is wrecking havoc on your code as the actual stop parameter (when x-y, plus (n x y) = x+y) is, in terms of absolutes, an insolvable equation unless you limit the number of decimals it uses.

It is generally considered better practice to use integers (or long) variables in a For...Next loop as their outcome is more certain.

See also below post:

How to make For loop work with non integers

If you want it to run succesfully and iterate 3 times (as I expect you want)

Try like below:

Dim x, y As Double
x = 0.7
y = 0.1
For x = Round(x - y, 1) To Round(x + y, 1) Step Round(y, 1)
   Debug.Print x
Next x

Again, it is better not to use Doubles in this particular way to begin with but if you must you would have to limit the number of decimals they calculate with or set a more vague end point (i.e. x > y, rather than x = y)

The coding you use implies that you wish to test some value x against a tolerance level of y. Assuming this is correct it would imply testing 3 times where;

test_1: x = x - y
test_2: x = x 
test_3: x = x + y

The below code would do the same but it would have a better defined scope.

Dim i As Integer
Dim x, y, w As Double

x = 0.7
y = 0.1

For i = -1 To 1 Step 1
   w = x + (i * y)
   Debug.Print w
Next i

Good luck !

Community
  • 1
  • 1
mtholen
  • 1,631
  • 2
  • 15
  • 27