VBA EXCEL error 91 object variable or with block variable not set

Hi I have this weird problem where I get error 91 ("object variable or with block variable not set"). I do not always get the error when I run the script, only now and then (random). I have tried to do as much as I can without success.

I am scraping a site to get the most recent prices of some pc components.

In order to do that I made a script and call the script sequentially multiple times for different parts. The error is not related to a specific part. I get the error randomly on all the parts. Sometimes I can do all the calls without any errors.

The script (not the full script, only what you need):

Sub ImportUrlData(url As String)

    Dim ie As InternetExplorer
    Dim html As HTMLDocument
    Dim varListingIDElement As IHTMLElement
    Dim varShopName As String
    varShopName = ""

    Set ie = New InternetExplorer
    ie.Visible = False
    ie.navigate url

    Do While ie.readyState <> READYSTATE_COMPLETE
        DoEvents
    Loop

    Set html = ie.document

    Set varListingIDElement = html.getElementById("listing")
    varShopName = getXPathElement("/table/tbody/tr[1]/td[1]/p/a", varListingIDElement).innerText

I always get the error on this last line:

varShopName = getXPathElement("/table/tbody/tr[" & count & "]/td[1]/p/a", varListingIDElement).innerText

(The error is not occurring inside the 'getXPathElement' function, so no need to show that too. If you do want to take a look at it just ask and I'll post it here in an edit.)

When I debug from start to end with F8 I never get the error. Is it possible that the next call already starts before the previous one isn't finished yet?

Thanks in advance!

(Sorry if there are some dutch words I have overseen.)

Solution

There are some cases where you can encounter an issue like this (where F8 always works, but a normal run doesn't) if the object return, in whatever way, isn't fully connected. It is difficult to say for certain if this is the case, and I have only encountered this when I am setting an object to something that has it's own process for initializing, and when this process is outside of the control of the VBA routine.

For your problem, specifically, there are a couple of approaches you can take:

Set varListingIDElement = html.getElementById("listing")
If Not varListingIDEElement is Nothing Then
    varShopName = getXPathElement("/table/tbody/tr[1]/td[1]/p/a", varListingIDElement).innerText
End If

The above approach should prevent the error entirely, but it also means varShopName may not be set.

On the other hand:

Set varListingIDElement = html.getElementById("listing")

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

varShopName = getXPathElement("/table/tbody/tr[1]/td[1]/p/a", varListingIDElement).innerText

This will force the application to wait three seconds between the initial setting of the variable, and the final calling of the variable. You could use this approach if it is indeed an issue of the prior process not finishing before the new one begins. You could also combine this approach with the first to get the best of both.

Good luck!