How to handle errors when some values are none?

[shahin]

I've created a macro using XMLHTTP request to parse some specific fields from a table out of a webpage. The data I'm interested in are within the first two columns and they are : 1. Name 2. Link to the name 3. Company and 4. Link to the company. The first script I'm gonna paste can fetch the required data in the right way. However, my intention is to do the same using error handler. As I've zero experience to manipulate data using error handler, I wish to learn the same in the effective way.

The problem I'm facing with that table is that it has got first two "tr" without any "tds'". So when I run my script as it is, it encounters value error which is "run time error 91" in this case. How can I tackle this using error handler?

First try (successful):

Sub FetchTabularData()
    Dim HTML As New HTMLDocument
    Dim elem As Object, post As Object, R&

    With New XMLHTTP60
        .Open "GET", "https://admintool.noah-connect.com/widget/attendees", False
        .send
        HTML.body.innerHTML = .responseText

        For Each elem In HTML.getElementsByTagName("table")(0).getElementsByTagName("tr")
            Set post = elem.getElementsByTagName("td")
          
            If Not post(0) Is Nothing Then
                R = R + 1: Cells(R, 1) = post(0).getElementsByTagName("a")(0).innerText
                Cells(R, 2) = post(0).getElementsByTagName("a")(0).getAttribute("href")
            End If
        Next elem
    End With
End Sub

The way I would like to move on (It is also working but I'm quite unsure whether I did it in the right way).

Sub FetchTabularData()
    Dim HTML As New HTMLDocument
    Dim elem As Object, post As Object, R&

    With New XMLHTTP60
        .Open "GET", "https://admintool.noah-connect.com/widget/attendees", False
        .send
        HTML.body.innerHTML = .responseText

        For Each elem In HTML.getElementsByTagName("table")(0).getElementsByTagName("tr")
            Set post = elem.getElementsByTagName("td")
          
            On Error GoTo handler
            R = R + 1: Cells(R, 1) = post(0).getElementsByTagName("a")(0).innerText
            Cells(R, 2) = post(0).getElementsByTagName("a")(0).getAttribute("href")
            On Error GoTo 0
handler:
        On Error Resume Next
        Resume Next
        Next elem
    End With
End Sub

I pasted the first script to let you know that I'm not interested to go in that way as it may be a possible solution I may get.

 

[Marc L]

Using an error handler when it is not necessary can't be a good idea !
As yet shown in several of your previous threads,
just check the collection length property
or like for any collection via an easy For Each loop …
​

 

[shahin]

@Marc L, right you are. You meant something like below I suppose.

It is also working:

Sub FetchTabularData()
    Dim HTML As New HTMLDocument, post As Object
    Dim elem As Object, tabdata As Object, I&, R&

    With New XMLHTTP60
        .Open "GET", "https://admintool.noah-connect.com/widget/attendees", False
        .send
        HTML.body.innerHTML = .responseText
       
        Set tabdata = HTML.getElementsByTagName("table")(0).getElementsByTagName("tr")
       
        For I = 2 To tabdata.Length - 1
            Set post = tabdata(I).getElementsByTagName("td")
           
            R = R + 1: Cells(R, 1) = post(0).getElementsByTagName("a")(0).innerText
            Cells(R, 2) = post(0).getElementsByTagName("a")(0).getAttribute("href")
        Next I
    End With
End Sub

However, I do not wish to go that way either. I only want to do the same using error handler. Thanks.

 

[NARAYANK991]

Hi ,

This is not directly related to your question , but you might find these links helpful :

https://excelmacromastery.com/vba-error-handling/

http://what-when-how.com/excel-vba/error-handling-techniques-in-excel-vba/

https://www.dropbox.com/s/98ipp2lcvr2u8vo/Error_Handling.pdf?dl=0

http://www.fmsinc.com/microsoftaccess/modules/code/ErrorHandling/VBA/ErrorHandlerVBA_class.htm

Narayan