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" 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