Value compare problem, As Single variables Feb09

[Neal Zimm]

The If statement code snippet below is failing, the else
branch is taken for two seemingly = values.
All values are dim'd As Single
uEPPAy is an array of user type records.


Prior to this: Weeks 1 and 2 hours are split in the worksheet
by formula into regular and Overtime OTi hours. Cells are number
format with 2 decimals, (via time clock hundreths of an hour).

'Check total, control all hours, sum Wk 1,2, sum Reg+OT, must be = .

uEPPAy(Ix).nPPTotHrs = uEPPAy(Ix).nWk1TotHrs + uEPPAy(Ix).nWk2TotHrs

nPPchkTotHrs = uEPPAy(Ix).nPPRegHrs + uEPPAy(Ix).nPPOTiHrs

uEPPAy(Ix).nPPTotHrs, dim'd single

nPPchkTotHrs, in the macro's vba.


debug.Print format(uEPPAy(Ix).nPPTotHrs,"0.000000000")
56.480000000
debug.Print format(nPPchkTotHrs,"0.000000000")
56.480000000

If uEPPAy(Ix).nPPTotHrs = nPPchkTotHrs Then
'code for controls are =
else
'this code is executing on above values ????
end if

Why is this happening ?

Is a Single value in a record 'different' from a VBA variable?

Do I have to "fool around" with Single type values
when comparing them? Something like:

if abs(value - value) < 0.001 then
else
end if


Thanks,
Neal Z.

 

[Hong Quach]

Hi Neal,

Your own suggestion should solve your problem. Comparing the value of 2
decimal numbers in your case is best to use the absolute different to be less
than the tolerance (1/100 of an hour).

As to why this happen, you can saerch on the web for something like
"comparing floating point number." The basic idea is that
56.48000000000000000000 does not equal to 56.48000000000000000001. The two
numbers have a higher chance of equal to each other when they come from the
same calculation such as (1.0 - 56.48000000000000000000) = (1.0 -
56.48000000000000000000). Otherwise, they are not the same. So try compare
the different with the smallest acceptable value for your data.

Hong Quach

 

[Neal Zimm]

Thanks Hong,
I kinda came to my own conclusion after I posted the note.

Here's the interesting part,

While experimenting, I changed the if statement to:

if value <> value then ..... ELSE .....

and it WORKED JUST FINE.

GO FIGURE.


THANKS AGAIN

 

[Hong Quach]

Hi Neal,

Switching the if else statement by taking negation is still present you with
the same problem. Remember, not equal will only evaluate to false if the two
items is equal. So, the else clause will likely not get execute after taking
the negation in the if expression.

Hong Quach