"Anthony" wrote:
I even used a msgbox to display the decimal values
to view what the code was seeing and they were
exactly the same.

It might help to know the original of the values in A1 and B1.

And what does Excel return from the formula =(A1-B1=0)? That might not be
the same as what Excel returns from the formula =(A1=B1) (!).

My suspicion: A1 and/or B1 is derived from some arithmetic operations.


Cells formatted [h]:mm
A1 = 45:30
B1 = 45:30

[....]
if(a1= b1) then
do some stuff to sheets
else
do something else
end if

This always went to the else section of the statement.

A1 can be as much as 37*2^-52 less than 45:30 (1+TIME(21,30,0)) -- about
0.7098 nanosec -- and B1 can be as much as 7*2^-52 more than 45:30 -- about
0.1343 nanosec -- and Msgbox and Excel will display the same value to 15
significant digits, namely 1.89583333333333.

But in fact, those values are different internally. Not enough for Excel to
consider them unequal when compared as A1=B1, due to its dubious "close to
zero" heuristic [1]. But different enough for an exact comparison like VBA
A1=B1 and Excel A1-B1=0 to return the correct answer, namely FALSE.

These kinds of small numeric aberrations are not uncommon as a result of
Excel and VBA arithmetic.

The work-around is to "round" values to the degree of precision that you
care about. Normally, we would use Excel ROUND -- WorksheetFunction.Round
in VBA [2]. But for time values, it is usually easier to use, in your
case, --TEXT(...,"[h]:mm") in Excel [3].


Endnotes:

[1] For the Excel "close to zero" heuristics, see "When a Value Reaches
Zero" in http://support.microsoft.com/kb/78113 .

[2] WorksheetFunction.Round is not the same as VBA Round. The latter does
so-called banker's rounding.

[3] Off-hand, I don't remember how to do the equivalent
of --TEXT(...,"[h]:mm") in VBA. VBA Format does not recognize "[h]:mm",
and --WorksheetFunction.Text(...,"[h]:mm") does not work.


----- original message -----

"Anthony" wrote in message
...
Hi,

I am using excel 07 in compatibility mode, due to the compay i work for
only have excel 03.

I have come across what seems to be a bug in excel vba or how it uses time
in calculations;

I wondered if anyone has had similar issues;

Cells formatted [h]:mm

A1 = 45:30
B1 = 45:30

in vba i had ;

if(a1= b1) then

do some stuff to sheets

else

do something else

end if

This always went to the else section of the statement.

I even used a msgbox to display the decimal values to view what the code
was seeing and they were exactly the same.

To get this to work I had to do the calculation on the worksheet and then
just test the cell value.


Anyone any idea? is it a bug or just something I have done wrong?

regards


Anthony