CODEWITHSUNDEEP
vbaexcelinternet-explorer
Nicholas Kemp
Nicholas Kemp

Reputation: 337

VBA code only works when in debug mode

So I have this function that returns a value from a web page. The issue with this is that it works perfectly when I run it single step, but when I run it normally it returns another value and objIE.Quit is skipped. This is the code:

Private Function Mexico(partida As String) As String

partida = Left(partida, 8)

Set objIE = New InternetExplorer
objIE.Visible = True
objIE.navigate "http://www.siicexcaaarem.org.mx/Bases/TIGIE2007.nsf/4caa80bd19d9258006256b050078593c/$searchForm?SearchView"

Cargar

objIE.document.getElementsByName("Query")(0).Value = partida

For Each boton In objIE.document.getElementsByTagName("input")
    If boton.Value = "Search" Then
        boton.Click
        Exit For
    End If
Next

Cargar
Application.Wait Now + TimeValue("00:00:03")

Dim temp As String
Dim i As Integer
For Each t In objIE.document.getElementsByTagName("tr")
    If t.className = "domino-viewentry" Then
        temp = t.Children(8).innerText
    End If
Next

If InStr(temp, "*") > 0 Then
    temp = Left(temp, Len(temp) - 1)
End If

If InStr(temp, "%") = 0 Then
    temp = temp & "%"
End If

Mexico = temp

objIE.Quit

End Function

And I am testing this with this sub:

Sub Mex()
MsgBox Mexico("33030001")
End Sub

When I run it single step, it returns "15%" with the parameter passed in the sub, while it returns just "%" when I run it normally with any given parameter. Any idea why is this happening? Any help will be appreciated.

Note: objIE is defined as a public variable, but this has not brought me any inconveniences so far, as I have other functions working properly for different websites. Also, Cargar is the usual "wait until page has loaded" instruction.

Private Sub Cargar()

Do Until objIE.Busy = False And objIE.readyState = 4
    DoEvents
Loop

End Sub

Upvotes: 1

Views: 1599

Answers (2)

Nicholas Kemp
Nicholas Kemp

Reputation: 337

So after messing with the code for hours and realizing that, sadly, the solution wasn't so simple as fixing the Cargar Load IE sub (as the page doesn't "load" as it's JavaScript driven), I found out that this was the solution:

For Each t In objIE.document.getElementsByTagName("tr")
    DoEvents 'Holy fix
    If t.className = "domino-viewentry" Then
        temp = t.Children(8).innerText
    End If
Next

I am quite unsure as why this fixed it, and came up with this while checking with msgboxes which parts of the code were not running correctly, and the msgbox inside the loop fixed it too. I'd appreciate your comments as to why this works.

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You should ensure that your webpage is completely loaded before trying to grab objects off the webpage. Your line containing Application.Wait does not do this for you.

Add this sub into your module:

Sub ieBusy(ByVal ieObj As InternetExplorer)
    Do While ieObj.Busy Or ieObj.readyState < 4
        DoEvents
    Loop
End Sub

then replace your line continaing the Application.Wait with: ieBusy objIE

Upvotes: 1

Related Questions