Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop using double
I have the following sub.
Sub test() Dim i As Double Dim j As Double j = 0.2 For i = -3 To 3 Step j Debug.Assert i < 3 Debug.Print i Next End Sub If I run this code the last step 3 will not run. If I modify I to 2.5 it does. What's going on? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop using double
On 11/8/2010 2:40 AM, heev wrote:
Sub test() Dim i As Double Dim j As Double j = 0.2 For i = -3 To 3 Step j Debug.Assert i< 3 Debug.Print i Next End Sub I ran into that in one program that used a for loop with a double value as the step size. I think it has to do with some quirks in the way Excel handles doubles. When I ran the code instead of zero I got this value, which is small but still greater than zero: 3.88578058618805E-16 I never did figure it out how to fix it so I added a very small amount to the final loop value: For n = -3 To 3.00001 Step j You could try multiplying everything by ten then dividing your loop counter by 10 to get the value you need: Sub test4() Dim n As Double, j As Double j = 2 For n = -30 To 30 Step j Debug.Assert n/10 < 3 Debug.Print n / 10 Next End Sub Or you could try cleaning up the loop counter, maybe something like this: Sub test5() Dim n As Double, j As Double j = 0.2 For n = -3 To 3 Step j n = CDbl((CLng(n * 10)) / 10) Debug.Assert n < 3 Debug.Print n Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop using double
Here's an excellent article about decimal numbers and binary numbers and why
they don't always match: http://www.cpearson.com/Excel/rounding.htm HTH. Best wishes Harald "heev" wrote in message ... I have the following sub. Sub test() Dim i As Double Dim j As Double j = 0.2 For i = -3 To 3 Step j Debug.Assert i < 3 Debug.Print i Next End Sub If I run this code the last step 3 will not run. If I modify I to 2.5 it does. What's going on? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop using double
On Nov 8, 2:40*am, heev wrote:
j = 0.2 For i = -3 To 3 Step j Debug.Assert i < 3 * * Debug.Print i Next End Sub If I run this code the last step 3 will not run. If I modify I to 2.5 it does. What's going on? This is a common side-effect of computer binary arithmetic. Most numbers with decimal fractions cannot be represented exactly. Consequently, results of even simple arithmetic might not be "exact", i.e. match the internal representation of the equivalent constant. For example, the following will output False(!) [1]: Debug.Print 10.1 - 10 = 0.1 If the step is ostensibly non-integer, it is best to use integers for loop controls, then compute the floating-point value within the loop. For example, instead of i = -3.0 to 3.0 step 0.2, do: For x = -30 To 30 Step 2: i = x / 10 PS: For broader participation, you might want to post future inquiries using the MS Answers Forums at http://social.answers.microsoft.com/...ry/officeexcel. It's not that I like that forum. It's just that MS has ceased to support the Usenet newsgroups. Hence, participation here is limited to the sites that share a common newsgroup mirror, which is no longer centralized at MS. ----- Endnotes [1] 10.1 is exactly 10.0999999999999,996447286321199499070644378662109 375 10.1-10 is exactly 0.0999999999999996,4472863211994990706443786621093 75 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop using double
I just ran this code in Excel 2003 and it ran exactly as expected.
But typically I've only seen integers allowed in loops. Excel seems to be the exception to this rule. This code could be written as: Sub test() Dim i As integer Dim j As double j = 0.2 For i = -15 To 15 Debug.Assert i*j < 3 Debug.Print i*j Next But I am not familiar with the debug command. What do Debug.Assert and Debug.Print do? On Nov 8, 3:40*am, heev wrote: I have the following sub. Sub test() Dim i As Double Dim j As Double j = 0.2 For i = -3 To 3 Step j Debug.Assert i < 3 * * Debug.Print i Next End Sub If I run this code the last step 3 will not run. If I modify I to 2.5 it does. What's going on? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double Double Quotes | Excel Programming | |||
Help with double loop | Excel Programming | |||
double axis, double problem (i hope only to me) | Charts and Charting in Excel | |||
Help with "double" loop! | Excel Programming | |||
double loop madness - help with macro | Excel Programming |