PS....

I wrote:
[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.

Well, WorksheetFunction.Round(cell * 24 * 60, 0) / 24 / 60 does work in this
case. And of course, 24*60 and 24/60 can be replaced by 1440.

But I would not trust it to always return exactly (internally) what Excel
does for time values formatted as "[h]:mm" because WorksheetFunction.Round
expression above involves floating point arithmetic.


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

"JoeU2004" wrote in message
...
"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