Receiving Run-time Error 91 when adding further lines to Excel table

Question

Dear Stack Overflow Community,

When running a simple Excel table, the code below is working without any issues. However, when extending the table significantly to several 1000 lines, I keep receiving a "Run-time Error 91" for the code, mentioning that an object variable has not been set for Cells(i, 7).Value = var1.innerText.

I have set a variable in the previous line however (Set var1 = html.getElementById("resultStats")). Do you see why this error keeps occurring although the variable has been set?

I have already checked the usual troubleshooting pages (https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/object-variable-not-set-error-91 etc), of which none could really help.

Many thanks for your help.

Option Explicit

Sub TermCount()
Dim url As String, lastRow As Long
Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
Dim start_time As Date
Dim end_time As Date
Dim var As String
Dim var1 As Object

lastRow = Range("A" & Rows.Count).End(xlUp).Row

Dim cookie As String
Dim result_cookie As String

start_time = Time
Debug.Print "start_time:" & start_time

For i = 1654 To lastRow

    url = "https://www.google.com/search?q=" & Cells(i, 1) & "&source=lnt&tbs=cdr%3A1%2Ccd_min%3A" & Cells(i, 5) & "%2Ccd_max%3A" & Cells(i, 6) & "&tbm=nws"

    Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    XMLHTTP.send

    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.responseText
    Set objResultDiv = html.getElementById("rso")
    Set var1 = html.getElementById("resultStats")
    Cells(i, 7).Value = var1.innerText

    DoEvents
Next

end_time = Time
Debug.Print "end_time:" & end_time

Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)

End Sub

Answer 1

The fact that you set the variable with this line:

Set var1 = html.getElementById("resultStats")

does not necessarily mean that var1 is actually not Nothing. In fact, this depends on your link.

In your loop, you create the link to your request as such:

url = "https://www.google.com/search?q=" & Cells(i, 1) & "&source=lnt&tbs=cdr%3A1%2Ccd_min%3A" & Cells(i, 5) & "%2Ccd_max%3A" & Cells(i, 6) & "&tbm=nws"

... and use it into an XMLHTTP request:

XMLHTTP.Open "GET", url, False
XMLHTTP.send

... which may or may not return the desired result. In the case where you get the error, most probably you have:

Set var1 = html.getElementById("resultStats")
>>> var1 = Nothing

... and so, when you try to run the next line:

Cells(i, 7).Value = var1.innerText

... you get a NullReferenceException because you try to take the .innerText of Nothing.

In order to prevent this type of issue, you should test your variable for nullity and only then doing what you're meant to do:

Set var1 = html.getElementById("resultStats")
If Not var1 Is Nothing Then
    Cells(i, 7).Value = var1.innerText
    '... and whatever else you want to do with var1
End If

Note: more generally, this is something you should always do in your code when there's the risk for nullity. For example:

• Set var1 = Range("A1"). Here there's no risk, the worksheet will always have a Range("A1") in it so there's no way var1 might be Nothing.
• Set var1 = Range("A1:A100").Find("something that doesn't exist"). In this case, there might be the risk that the Find does return Nothing, if the value you were searching for does not exist. Hence, you should always test var1 for nullity to avoid referencing something on a Nothing object.

When you work with XMLHTTP requests, you potentially never know if the object returned from the request is something or is Nothing. Hence, always test your code for nullity before doing whatever you want to do with these objects.