LIKE operator fails? (Excel 2003)

[ChrisGreaves]

I'm stumped and need help.

1.png

This bit of code works just fine in Toronto, Canada on a Win7HP/Office 2003 Excel application.
It fails when I email the workbook to the UK on a WinXP/Office2003 installation.

My contact and I single-stepped through the code; his code failed the test and jumped over the CALL statement, whereas mine highlighted the CALL statement.

Code: Select all

Function TESTLike()
    Dim strText As String
    strText = "6:15"
    If Left(strText, 4) Like "#:##" Then
        MsgBox "We have found at least one race"
    Else
        MsgBox "We FAILED TO FIND A race"
    End If
End Function

This would be en equivalent test-case, I think.
I think it is nothing to do with date/time formats on our systems, becaus although we humans know we are looking at race-times, in the VBA it's just a character string, as the simple test case above shows.
Googling for "Like operator fails" doesn't bring me any relevant clues, as far as I can see.

[HansV]

What if the time was 10:30 AM? Left(strText, 4) would be "10:3" which is not like "#:##".

[ChrisGreaves]

What if the time was 10:30 AM? Left(strText, 4) would be "10:3" which is not like "#:##".

Thanks for the response, Hans.

I agree with you, but these happen to be racing times from a public web site and as far as I have seen, they are always <digit><colon><digit><digit>.
(In the UK they never race horses before noon?)


Besides which, over the 'phone we single-stepped through the VBA.
I had my colleague hover the mouse over HIS strText variable identifier and he read out "7:05", so for the specific case that failed, he and I had the same string.
(Since we were single-stepping we were both at the same race-card, same location, same race, same horse etc.)

I confess that I don't often use LIKE, but on what started off as a quick-and-cheap application I thought that LIKE would suffice for strings that were consistently the same format.

[HansV]

Could there have been a space before the time string, so that it was in fact " 7:05" instead of "7:05" on his machine, while you saw "7:05"?

[ChrisGreaves]

Could there have been a space before the time string, so that it was in fact " 7:05" instead of "7:05" on his machine, while you saw "7:05"?

Thanks again, Hans, but not likely.

• I email a copy of the workbook to him.
• On SKYPE we load the same (copy) of the workbook on our machines.
• Under my spoken cueing we Step-Into and Step-Over and Run-to-breakpoints.
• The VBA code accesses the same public web page.
• The application downloads the web page into a "data" worksheet.
• Still on Skype we agree that our Excel worksheet, built from a downloaded web-page is "identical", certainly in terms of the number of rows, columns gained, the values in the first few and last few cells.
• Still under my cueing we Step until we reach that critical statement.
• We confirm by visual inspection (hover over the identifier) that we have the same data in the variable. We have gained the same data by loading the .Value of the same cell (row, col) from the downloaded web page in the "data" worksheet
• Colleague's test fails and skips over the Call, my test succeeds and prepares to Call.

I kid you not, this has me stumped.
Short of emailing to him the simple test code in my first post on this thread, I can't think of a more-parallel running that what we went through. (And once the dust has settled I plan to email the simple test to him and see if the LIKE operator fails when presented with hard-coded inline data)
(later) I have asked him to run the simple function in both Excel and Word at his end.

We are not processing a data-file-on-his-machine vs. a data-file-on-my-machine (contents might have been edited).
We are grabbing a public web page in real time, probably less than a second apart.

If there were an Olympic event "synchronized data processing", we'd get gold.
Except for the behavior of the LIKE statement.


I think LIKE has been around for 10 years or so, and unless he is running Office2003/prerelease/gamma or similar, it ought to work.
(Thinks: I'll email and ask him to Help,About in his copy of Excel)

[HansV]

I still think it has to do with location - the web page can retrieve the location of the PC from the IP address and use this to display the time in a localized format.

[ChrisGreaves]

I still think it has to do with location - the web page can retrieve the location of the PC from the IP address and use this to display the time in a localized format.

Ah!

NOW we're getting somewhere!
I too had thoughts about location - after all, what else is different? same workbook, same code, same web page, ...
But he had read out the 4-character string to me.

Maybe he HAD missed a leading-space.


(I'll drop him email number 13 of today's haul and let you know)

[rory]

I can run a UK test if it would assist.

[ChrisGreaves]

I can run a UK test if it would assist.

Thanks, Rory; very much appreciated.
I have attached a stripped-down test.
Since this is a confidential project I'll send the password in a PM within minutes.

[ChrisGreaves]

(Thinks: I'll email and ask him to Help,About in his copy of Excel)

11.8332.8333 SP3

So he's more up-to-date than am I!

[rory]

Yes they are coming through as hh:mm for me, not h:mm

[ChrisGreaves]

Yes they are coming through as hh:mm for me, not h:mm

Aaaaaaargh!
Make that TWO meals at The Montreal Deli.
(They deliver ....)

Thanks Rory.

[ChrisGreaves]

Yes they are coming through as hh:mm for me, not h:mm

Confirmed by my colleague.
Make that THREE meals at The Montreal Deli.

[ChrisGreaves]

This should fit the bill for now:-

Code: Select all

Public Function blnIsTime(strIn As String) As Boolean
    On Error GoTo Failed:
    Dim dt As Date
    dt = TimeValue(strIn)
    blnIsTime = True
    Exit Function
Failed:
'Sub TESTblnIsTime()
'    Debug.Assert True = blnIsTime("07:15")
'    Debug.Assert True = blnIsTime("7:15")
'    Debug.Assert False = blnIsTime("Chris")
'End Sub
End Function

I should text the original "strText" variable and if it fails the IsTime test, then something has gone horribly wrong, and I deal with it.

[ChrisGreaves]

This should fit the bill for now.

Amongst other things, my new test to see if a cell contains a valid date/time is getting tripped up in the 3:30 at Beverly on May 10th, where horse number three was "Mayan Flight".
http://www.racingpost.com/horses/result ... &popup=yes" onclick="window.open(this.href);return false;
My code grabs the leftmost 5 characters, sees "3 May" and declares that that IS a valid date/time (Aaaaaaaaaaaaaaaaargh!)

moz-screenshot-11.png

(Puts on thinking cap ... )

(later).
Sigh! I wrote a Regular Expression, which is what i should have done in the first place, only y'all were too kind to nag me about it ...

Code: Select all

Public Function blnIsTime(strIn As String) As Boolean
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Pattern = "^(([0-1]?[0-9])|([2][0-3])):([0-5]?[0-9])(:([0-5]?[0-9]))?$"
    blnIsTime = RegEx.test(strIn)
     Set RegEx = Nothing
'Sub TESTblnIsTime()
'    Debug.Assert blnIsTime("22:30")
'    Debug.Assert blnIsTime("02:30")
'    Debug.Assert blnIsTime("2:30")
'    Debug.Assert blnIsTime("22:30:00")
'End Sub
End Function