Different results from same if statement in VBA/Excel

[GomezAddamz]

I'm having trouble with an Excel VBA formula and have isolated the cause. I have a worksheet with the times 17:00, 13:10, 4:10, and 8:00 in columns A, B, C, and D respectively. In column E, I have the excel formula "=IF(A1-B1+C1=D1,"True","False")", which returns "True". In column F, I have "=Test(A1,B1,C1,D1)", which returns "FALSE". Does anyone know why these two functions would give different results? Any and all help is greatly appreciated, thanks!

Function Test(A As Double, B As Double, C As Double, D As Double) As Boolean
    If (A - B + C = D) Then
        Test = True
    Else
        Test = False
    End If
End Function

 

[SkipVought]

hi,

TRUE is not equal to "True"

True/False are BOOLEAN constants.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

I'd hazard a guess that it's a data type problem.

My few minutes of searching didn't clearly identify what data type Excel uses for dates, but if it's not DOUBLE then a difference in the umpteen-billionth decimal point might be the problem.

 

[SkipVought]

actually, in VBA, the rounding of the float arithmetic yields an actual inequality.

Try this

    If Format(A - B + C, "hh:nn:ss") = Format(D, "hh:nn:ss") Then

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GomezAddamz]

If Format(A - B + C, "hh:nn:ss") = Format(D, "hh:nn:ss") Then

That got it! Thanks!

AFAIK, Excel stores dates and times as doubles, with everything to the right of the decimal representing the hour, minute, seconds, and everything to the left representing the day, month, and year. I still think it's odd that VBA would be doing different arithmetic than Excel, but thank you for this solution!

 

[SkipVought]

Date/Time values in Excel are represented in days since 12/31/1899.

The decimal part simply represents a fraction of a day.

This number can be displayed, formatting the cell as GENERAL.

faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]