Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value compare problem, As Single variables Feb09
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. -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value compare problem, As Single variables Feb09
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" wrote: 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. -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value compare problem, As Single variables Feb09
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 -- Neal Z "Hong Quach" wrote: 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" wrote: 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. -- Neal Z |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value compare problem, As Single variables Feb09
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 "Neal Zimm" wrote: 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 -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare variables in two excel workbooks | Excel Programming | |||
How to store variables of a single cell in a column? | Excel Programming | |||
From single cell variables to a single column serie | New Users to Excel | |||
Compare two variables across two worksheets | Excel Worksheet Functions | |||
Compare two lists with two variables | Excel Worksheet Functions |