VBA code only works when in debug mode

Question

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

Probably because you are using application.wait instead of actually waiting for the webpage to load. It might take longer than 3 seconds. You need to replace it with a loop that check to ensure the webpage is fully loaded. — K.Dᴀᴠɪs
– K.Dᴀᴠɪs, Commented Jan 11, 2018 at 18:35
@ScottHoltzman Good point, didn't even notice that sub. OP - please edit your post with the Cargar sub as pointed out by Scott. — K.Dᴀᴠɪs
– K.Dᴀᴠɪs, Commented Jan 11, 2018 at 18:41
You have it backwards. You have Do Until it doesn't equal Completely Loaded. You want your Cargar sub to say Do While It's Not completely Loaded , hence the sub I provided as an answer. — K.Dᴀᴠɪs
– K.Dᴀᴠɪs, Commented Jan 11, 2018 at 18:56

K.Dᴀᴠɪs · Accepted Answer · 2018-01-11 18:38:06Z

1

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

answered Jan 11, 2018 at 18:38

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicholas Kemp Over a year ago

I do have that sub, it is explained on the notes that Cargar does what you say. Application.Wait is there to ensure the page is actually fully loaded. Waiting for longer also doesn't work, so I don't think loading times are the problem. Thank you for answering anyways!

Nicholas Kemp · Accepted Answer · 2018-01-12 16:48:24Z

1

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.

answered Jan 12, 2018 at 16:48

Nicholas Kemp

3373 silver badges18 bronze badges

Collectives™ on Stack Overflow

VBA code only works when in debug mode

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related