Some bizarre maths in Excel VBA

Associate
Joined
26 Feb 2004
Posts
970
Location
China (Qinhuangdao)
Actually, this originated in some old VB6 code that someone found an error in, and asked me to have a look at, but I've reproduced it in VBA for simplicity.

I thought the problem was due to defining some variables as 'Single' data type, but now I'm not so sure.

Code:
Sub BasicError()

Dim XCofG As Single
Dim Weight As Single
Dim Moment As Single

    XCofG = 331250
    Weight = (135.93 + 135.11 + 134.39) / 3
    Moment = XCofG * Weight
    Debug.Print "XCofG: " & Format(XCofG, "0.0000000")
    Debug.Print "Weight: " & Format(Weight, "0.0000000")
    Debug.Print "331250 x 135.1433: " & Format(331250 * 135.1433, "0.0000000")
    Debug.Print "Moment: " & Format(Moment, "0.0000000")

End Sub

Edit: I should have added the results of that code:

XCofG: 331250.0000000
Weight: 135.1433000
331250 x 135.1433: 44766218.1250000
Moment: 44766230.0000000


Because the variables are defined as Single, Weight becomes 135.1433, XCofG is 331250 and moment should therefore be 135.1433 x 331250 = 44766218.125, but because it is also Single, and Single numbers can only have 7 significant figures, it should be rounded to 44766220, but it's rounded to 44766230, and I can't understand why?

It's almost like when it calculates Moment, it is not considering the Weight as a Single number, but using the full number to make the calculation, and then reduces Moment to 7 significant figures.
 
Last edited:
Back
Top Bottom