Working with times - Bug?

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

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
 
hi
i don't think this is a bug but rather a syntax thing.
as you have writted it, VB can't read the values in A1 and B1. try this...
If Range("A1") >= Range("B1") Then
or
If [A1] >= [B1] Then

regards
FSt1
 
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
Click to expand...

I cannot reproduce your results.

It would be helpful if you posted the actual code you are using, as well as
information as to the actual contents of a1 and b1.

As written, your vba code is not referring to any cells. So either you have
initialized them previously, or they are merely undeclared variables.

If they are, in fact, undeclared empty variables, I'm surprised at your
results. I would have expected the code to always "do some stuff" as the
comparison should evaluate to True.

A handy option in writing good code is to require variable declarations. See
Tools/Options/Editor/Code settings. This precedes VBA code with the Option
Explicit statement.
--ron
 
Anthony said:
I even used a msgbox to display the decimal values
to view what the code was seeing and they were
exactly the same.
Click to expand...

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.
Click to expand...

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 said:
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
Click to expand...
 
PS....
[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.
Click to expand...

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 said:
Anthony said:
I even used a msgbox to display the decimal values
to view what the code was seeing and they were
exactly the same.
Click to expand...

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.
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
The a1 and b1 was just to show you that I was using the values of the cells
and not my actual code, it was an easy to read version of what I have.

Thank you for your reply.

Regards

Anthony

FSt1 said:
hi
i don't think this is a bug but rather a syntax thing.
as you have writted it, VB can't read the values in A1 and B1. try this...
If Range("A1") >= Range("B1") Then
or
If [A1] >= [B1] Then

regards
FSt1

Anthony said:
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
Click to expand...
Click to expand...
 
The a1 and b1 was just to show you that I was using the values of the cells
and not my actual code, it was an easy to read version of what I have.

The cells contain a time formatted as [h]:mm. These times are derived from
other information in the worksheet.

They are then checked to see if they are the same and then perform the
required action.

values in K and L are calculations which return a time value.

If (Worksheets("settings").Range("K21").Value >=
Worksheets("settings").Range("J21").Value) Then

Application.EnableEvents = False

Worksheets(Me.tbState.Text & "
workplan").Cells(CInt(Me.tbSelectedLine.Text), "A").Interior.Color = vbRed
'show delay
Worksheets(Me.tbState.Text & "
workplan").Cells(CInt(Me.tbSelectedLine.Text), "A").Value = _
Worksheets("duties - " &
Me.tbState.Text).Cells(CInt(Me.tbSelectedLine.Text), "A").Value & _
" (" &
time_to_text((Worksheets("settings").Range("L21").Value - _
Worksheets("settings").Range("K21").Value),
2) & ")"


Application.EnableEvents = True

Else

Application.EnableEvents = False

Worksheets(Me.tbState.Text & "
workplan").Cells(CInt(Me.tbSelectedLine.Text), "A").Interior.Color = vbWhite
'reset value
Worksheets(Me.tbState.Text & "
workplan").Cells(CInt(Me.tbSelectedLine.Text), "A").Value = _
Worksheets("duties - " &
Me.tbState.Text).Cells(CInt(Me.tbSelectedLine.Text), "A").Value

Application.EnableEvents = True

End If

'calculate new totals
Calculate_office_totals (Me.tbState.Text)

This is a small section of the code.

Thank you for your reply.

Regards

Anthony
Ron Rosenfeld said:
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
Click to expand...

I cannot reproduce your results.

It would be helpful if you posted the actual code you are using, as well
as
information as to the actual contents of a1 and b1.

As written, your vba code is not referring to any cells. So either you
have
initialized them previously, or they are merely undeclared variables.

If they are, in fact, undeclared empty variables, I'm surprised at your
results. I would have expected the code to always "do some stuff" as the
comparison should evaluate to True.

A handy option in writing good code is to require variable declarations.
See
Tools/Options/Editor/Code settings. This precedes VBA code with the
Option
Explicit statement.
--ron
Click to expand...
 
The a1 and b1 was just to show you that I was using the values of the cells
and not my actual code, it was an easy to read version of what I have.

The cells contain a time formatted as [h]:mm. These times are derived from
other information in the worksheet.

They are then checked to see if they are the same and then perform the
required action.

values in K and L are calculations which return a time value.

If (Worksheets("settings").Range("K21").Value >=
Worksheets("settings").Range("J21").Value) Then

Application.EnableEvents = False

Worksheets(Me.tbState.Text & "
workplan").Cells(CInt(Me.tbSelectedLine.Text), "A").Interior.Color = vbRed
'show delay
Worksheets(Me.tbState.Text & "
workplan").Cells(CInt(Me.tbSelectedLine.Text), "A").Value = _
Worksheets("duties - " &
Me.tbState.Text).Cells(CInt(Me.tbSelectedLine.Text), "A").Value & _
" (" &
time_to_text((Worksheets("settings").Range("L21").Value - _
Worksheets("settings").Range("K21").Value),
2) & ")"


Application.EnableEvents = True

Else

Application.EnableEvents = False

Worksheets(Me.tbState.Text & "
workplan").Cells(CInt(Me.tbSelectedLine.Text), "A").Interior.Color = vbWhite
'reset value
Worksheets(Me.tbState.Text & "
workplan").Cells(CInt(Me.tbSelectedLine.Text), "A").Value = _
Worksheets("duties - " &
Me.tbState.Text).Cells(CInt(Me.tbSelectedLine.Text), "A").Value

Application.EnableEvents = True

End If

'calculate new totals
Calculate_office_totals (Me.tbState.Text)

This is a small section of the code.

I did a work around for this by doing the sum on a worksheet and just
testing that value, which seemed to work correctly. I will give the rounding
a go and see if that helps


Regards



Anthony

JoeU2004 said:
Anthony said:
I even used a msgbox to display the decimal values
to view what the code was seeing and they were
exactly the same.
Click to expand...

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.
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
The a1 and b1 was just to show you that I was using the values of the cells
and not my actual code, it was an easy to read version of what I have.
Click to expand...

OK. Pardon me for asking, but when I can't reproduce the results from what
people post, I find the original code more helpful.

I think (for me) the following is relevant with regard to your problem:
values in K and L are calculations which return a time value.
Click to expand...

Is the "L" a typo and should that be a "J"??
If (Worksheets("settings").Range("K21").Value >=
Worksheets("settings").Range("J21").Value) Then
Click to expand...

(By the way, the outside set of parentheses are not necessary in this
expression).
...



End If
Click to expand...



Here is what I think is going on.

If you manually enter identical time values in K21 and J21, I would be very
surprised if the relevant section of your macro did not evaluate to True.

That being the case, the calculations are not resulting in identical results,
so your comparison evaluates to False.

This is not an uncommon problem when dealing with binary representation of
numbers in Excel (and other spreadsheets, most of which use the IEEE standard)
that cannot be represented exactly.

The visualized value appears identical (to the 15 digit precision available in
Excel, but the actual stored values differ.

If this is the issue, it is well known. There are several workarounds that
usually involve rounding the answers to the desired level of precision. But in
your case, since the fields are formatted the same, I'd wonder if you could get
acceptable results merely by comparing the .Text property of the two cells,
instead of the .Value property. That should work so long as you only need to
test for equality at the level of whole minutes.
--ron
 
I'd wonder if you could get
acceptable results merely by comparing the .Text property of the two cells,
instead of the .Value property. That should work so long as you only need to
test for equality at the level of whole minutes.
Click to expand...

One further note:

If you go with this method, for robustness, in your code, you probably should
explicitly set the .NumberFormat property of the cells being compared to
"[h]:mm".

This might better be done to the relevant group of cells at one step, than
doing it at each step of the loop.
--ron
 
Thank you for your reply. I will be giving the ideas a try once I have
completed some other work on the file.

I think the L was a typo should just be K and J.

Thank you again for your time.

Regards

Anthony

Ron Rosenfeld said:
I'd wonder if you could get
acceptable results merely by comparing the .Text property of the two
cells,
instead of the .Value property. That should work so long as you only need
to
test for equality at the level of whole minutes.
Click to expand...

One further note:

If you go with this method, for robustness, in your code, you probably
should
explicitly set the .NumberFormat property of the cells being compared to
"[h]:mm".

This might better be done to the relevant group of cells at one step, than
doing it at each step of the loop.
--ron
Click to expand...
 
Thank you for your reply. I will be giving the ideas a try once I have
completed some other work on the file.

I think the L was a typo should just be K and J.

Thank you again for your time.

Regards

Anthony
Click to expand...

You're welcome. Glad to help. Thanks in advance for any feedback.
--ron
 
Back
Top