accuracy of numbers question #1

[BluesMatt]

I am trying to understand the errors that seem to occur when working with numbers in VB. For example, run the little program below and look at the contents of the Immediate window. Once the program gets to 4.015, it adds a very small number to yield 4.01500000000001. Where does this number come from?

Option Explicit

Dim temp1 As Double

Private Sub Form_Load()
For temp1 = 4 To 4.199 Step 0.001
Debug.Print temp1
Next temp1
End Sub

Thanks in advance.......BluesMatt

[MartinLiss]

There have been a number of discussions about this known "problem". Use the forum search and you may be able to find them, but basically it's due to the way Double data is stored.

[jim mcnamara]

It's because you are doing arithmetic operations using decimal numbers on datatypes that are binary.

Floating point numbers can't represent all decimal numbers correctly. This happens on all computers. Single, Double, Currency datatypes are floating point.

Some databses internally use BCD format numbers, which are slower to work with, but do not have this problem.

[MartinLiss]

Actually Curreny isn't a floating-point type.

Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807.

[Shaggy Hiker]

Another point that should be considered is that decimal doesn't represent all decimal numbers accurately either. Consider the number 2/3. If you wanted to use this in decimal form in a calculation, you would have to decide how many decimal places to use, and what digit to end with. No matter what you choose, the number you use will not be precisely 2/3, but the error may be acceptably small. This is true in computers as well. At some point, the accuracy of single and double precision floating points was decided upon. These numbers are not "right", nor can they ever be, they are only "close enough for most things".

If you need greater accuracy, it can be obtained, but only with a fair amount of work. Just as there are libraries with functions that can give you integers larger than 4 bytes, there are undoubtedly libraries that give you functions with higher precision numbers. They do this by dividing the problem into smaller chunks that fit within the limitations of the hardware, then combining the results of the chunked equations to produce a result for the original problem.

[BluesMatt]

OK. From the replies, what I think I have learned here is that IF I want to do arithemtic operations on a number that has digits to the right of the decimal point, that number should be stored as a Currency datatype (because Currency datatypes are stored as integers and are an EXACT conversion from the decimal number entered). (This assumes no more than 4 digits to the right of the decimal point.) Using any other datatype for a decimal number has the potential for having a rounding error when converting from decimal to binary floating point format.

Have I understood this correctly?

[Shaggy Hiker]

Mostly, yes. If you want to use floating point values, you need to keep in mind that there will be some rounding errors.

[BluesMatt]

In working this accuracy problem today, I have also noticed that the Immediate window seems to be limited to 199 lines. Is it possible to expand this limit? What if I wanted to have 1,000 lines in the Immediate window? Can I do this? Is there a setting somewhere that allows me to expand the amount of data that the window will hold before overwriting previously written data?

As a separate but similiar question (sort of), the size of forms also seems to be limited. If I wanted to write my own Excel program (Excel seems to have a nearly endless long and wide form) how would I do this? Would I have to use some kind of paging scheme and dynamically redefine the form to show different sections of the spreadsheet at any one time?

Thanks to all! I have learned a lot today! .........BluesMatt

[Shaggy Hiker]

You might try those questions as separate posts. You'll probably get a different set of eyeballs looking at them, and the answers may help other people.

[MartinLiss]

In working this accuracy problem today, I have also noticed that the Immediate window seems to be limited to 199 lines. Is it possible to expand this limit?

To my knowledge, no.

As a separate but similiar question (sort of), the size of forms also seems to be limited. If I wanted to write my own Excel program (Excel seems to have a nearly endless long and wide form) how would I do this? Would I have to use some kind of paging scheme and dynamically redefine the form to show different sections of the spreadsheet at any one time?

See the attached form.