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

Source: Internet
Author: User


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:



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-

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.