Code Snipit to Test

All –

Could some exceedingly kind wizard please run this very simple code snipit, and then explain it (the results) to me? Repeatable on my home machine and work machine running XL2002. On my Mac XL2004, I don’t have this problem.

Sub Test()
   Const Max_P As Currency = 2000000
   Dim P       As Currency
   Dim K       As Long
 
   For P = 12 To Max_P Step 2
   Next P
 
   For K = 12 To Max_P Step 2
   Next K
 
   Debug.Print P; K
 
End Sub

For P, I get 1717988 (???????)
For K, I get 2000002

And, well, I don’t get what I get ;-)

…mrt

Posted in Uncategorized

23 thoughts on “Code Snipit to Test

  1. Hmm, bit weird that.

    I note that defining Max_P as 4000000 and starting at 2000012 gives the expected result, and that the data type of Max_P doesn’t seem to matter.

    Call Grace Hopper!

  2. dbb –

    Thanks. I was using currency to be able to hold 100000^2. It has to do with Euler 75, next Sunday’s problem. I was confident of my approach, but just wasn’t getting the right answer. I found a website that gave the last triangle with perimeter below 2000000, and I couldn’t get that answer out from inside the loop above. Tracking that down lead me to P not getting to 2000000. I’ve only been up against this weirdness since mid-last week. If I’d cracked it earlier, #75 would have been up this week.

    From there, I adjusted my types, and got the answer. Using currency only where I needed it.

    Can this be considered a bug ;-)

    Certainly was to me.

    …mrt

  3. The lowest value of max_p where the equality P=K fails is 214748. Strange things happen at, near, and between higher multiples of 214748.

    The prime factorization of 214748 is 2^2 x 37 x 1,451, which seems like a lead because it is so “primey”… maybe this has to do with conversion of decimal to binary? Odd though, because the Currency type should not lose accuracy on whole numbers.

  4. Strange. In XL2003 if Max_P is defined as 1932734 or 1932735 I get 2147484 and 1932736. 2932739 gives 2576982 and 2932740.

    Going back to Max_P = 2000000 but starting the loop at 1900000 gives 1900000 and 2000002.

  5. From VB6 Language Reference Book 1 p1153

    The Currency data type is useful for calculations involving money and for fixed-point calculations in which accuracy is particularly important.

  6. I don’t know about the Currency/Long thing (same results on Win 2002 btw) but I think you may be able to get around some of the big squaring stuff on Pythagorean triangles by looking at some of the properties described here: http://en.wikipedia.org/wiki/Pythagorean_triple – for example, that one of the two orthogonal sides is always divisible by 3. There are a whole bunch more. So the brute force solution need not be quite so, er, brutal!

    Have you solved #73, btw? I’m stuck on what it tells me is a wrong answer, but I can’t figure out for the life of me what I’ve missed.

  7. To achieve the desired result, you can possibly use te forgotten Decimal data subtype.

    Following code does the job: (XL2007, Win Vista)

    Public Sub Test()
       Const Max_P As Variant = 2000000
       Dim P       As Variant
       
       For P = 12 To CDec(Max_P) Step 2
       Next P
     
       Debug.Print P
     
    End Sub
  8. Interesting. Sounds like a bug to me indeed.

    Funny thing is, if you add a counter in the first loop, you get another odd result:

    Sub Test()
        Const Max_P As Currency = 2000000
        Dim P As Currency
        Dim K As Long
        Dim u As Long
        For P = 12 To Max_P Step 2
            u = u + 2
        Next P

        For K = 12 To Max_P Step 2
        Next K

        Debug.Print P; K; u

    End Sub

    Result:
    1717988 2000002 1717976

    Where did the 12 go?

  9. This gives the expected results ( 2000002 2000002 ):

    Sub Test2()
       Const Max_P As Currency = 2000000
       Dim P       As Currency
       Dim K       As Long
     
       P = 12
       Do
        P = P + 2
        If P > Max_P Then Exit Do
       Loop
     
       K = 12
        Do
        K = K + 2
       If K > Max_P Then Exit Do
       Loop
     
       Debug.Print P; K

    End Sub

  10. You *definitely* don’t want to be trying brute force on Euler #75, by the way.

    Look at Euclid’s theorem on the Wikipedia page and think about the problem from a slightly different angle. You shouldn’t need square a number much larger than 1000.

    I solved it in Ruby in about 42sec, which makes me think I might have missed a trick somewhere as it ran on a very fast machine. I haven’t looked at the discussion yet, so I don’t know.

  11. Hi Mike –

    #75 took me a bloody week and a half because of the above issue to solve. I was only tallying singletons thru 1717986 and didn’t have a clue until a website gave me the last triangle, and that loop above wouldn’t printout the number of times that length perimeter was found.

    Now, using longs, it comes in at about 1.5 seconds on my slower machine.

    Interestingly, I was using currency to store precomputed sides squared up to 100000^2 for a verification step I DIDN’T NEED TO MAKE. (If Squared(a) + Squared(b) = Squared(c) then…. ) It never filtered anything.

    If I hadn’t been doing that, I’d never have encountered this.

    Might be another case where VBA wins ;-) If I get the time tonight, I’ll put it up. As Desi says, there some ‘splaining to do.

    …mrt

  12. i was getting the same result (XL2007)… but now i get the correct answer… do not know what i changed. here is what i have…

    Sub Test2()
    Dim Max_P, P, u As Currency
    Dim K, v As Long

    Max_P = 2000000
    u = 0
    v = 0

    For P = 12 To Max_P Step 2
    u = u + 1
    Next P

    For K = 12 To Max_P Step 2
    v = v + 1
    Next K

    MsgBox P & “, ” & K

    End Sub

  13. jvc –

    Return Max_P to a Constant, it gives same bad result

    Interesting discovery though…

    …mrt

  14. jvc –

    When you put multiple variables in a single Dim, it only sets the type to the last variable. So P is set to a variant.

  15. Some screen shots showing more strange behaviour when using the currency data type as a counter here: http://newtonexcelbach.wordpress.com/2009/02/28/you-cant-count-on-money/

    This bug is a bit of a worry in my opnion, not so much because of the results of using currency as a counter, but more because similar unexpected behaviour might be hidden away in calculations involving this data type. I understand that there are quite a few people who use Excel for just this purpose.

    For big counters the solution is simple, just use a double, or as brv said, for really huge counters you could use a decimal.

    I might have a look at using the decimal data type on some of the Project Euler problems (although I suspect there are always more elegant solutions using other means).


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.