The "VBA study" floating-point calculation is always error-

Iamlaosong

Numbers are expressed in two ways, one is an integer and one is a floating-point number. A floating-point number is a numeric representation of a number in a given subset of a rational number, which is used to approximate any real numbers in a computer. Specifically, the real number is obtained by multiplying an integer or fixed-point number (that is, the mantissa) by a radix (usually 2 in the computer), which is similar to the scientific notation of cardinality 10.

The way the floating-point numbers are stored in the computer determines the floating-point number is often an approximation, because we use the decimal in daily life, and the computer is binary, binary hard to express decimal decimals, here do not want to discuss how the computer is stored floating-point numbers, it will be a long piece of boring text , which is verified only experimentally, see the following procedure:

Sub Tt1 () for    i = 0 to + Step 0.1        Debug.Print I    NextEnd Sub

run above you will find that the front can be correctly displayed values, only one decimal place, added to 6 after theafter the decimal point, it increased to more than 10, the result is as follows:

。。。
5.3
5.4
5.5
5.6
5.7
5.8
5.9
6
6.1
6.19999999999999
6.29999999999999
6.39999999999999
6.49999999999999
6.59999999999999
6.69999999999999

。。。

This is the cumulative error of floating-point numbers, because the computer's binary storage cannot accurately store 0.1 of this number. To change the step size of the above program to 0.125, then run the above program, there is no error, this is because the binary can accurately store 0.125 of this number.

Such errors can lead to comparative errors, such as the above value, you think should be equal to 6.2, but the actual value is less than 6.2, so in some need to accurately calculate, accurate comparison of occasions, it is best not to use floating point. If the original data is a floating-point number, it can be calculated, compared, and then restored to a floating-point number. For example, the above procedure can be written as follows:

Sub Tt1 () for    i = 0 to 1        Debug.Print   I/10    NextEnd Sub

This avoids the cumulative error caused by floating-point calculation. In addition, floating-point numbers should try to avoid accurate comparisons, generally take greater than, less than such comparisons, so as not to produce a logic error.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

The "VBA study" floating-point calculation is always error-