I've written some code in vba to scrape names and phone numbers from a webpage that has spread across some pages I don't wish to know of. The main interesting thing with this scraper is that It only needs to know the first page number then it traverse across all the pages and fetch the information I've mentioned above. I tried to make it error-free. Here is what I did:

Sub Yellowpage_Parser()

Const mlink = "https://www.yellowpages.com/search?search_terms=pizza&geo_location_terms=San%20Francisco%2C%20CA&page="
Dim http As New XMLHTTP60, html As New HTMLDocument
Dim post As HTMLHtmlElement

    y = y + 1
    With http
        .Open "GET", mlink & y, False
        html.body.innerHTML = .responseText
    End With

        For Each post In html.getElementsByClassName("info")
            With post.getElementsByClassName("n")(0).getElementsByTagName("span")
                If .Length Then x = x + 1: Cells(x, 1) = .item(0).innerText
            End With

            With post.getElementsByClassName("phones phone primary")
                If .Length Then Cells(x, 2) = .item(0).innerText
            End With
        Next post

Loop While InStr(http.responseText, "next ajax-page")

    Set html = Nothing
    MsgBox "Collected totals are " & ActiveSheet.UsedRange.Rows.Count

End Sub

I tried to make it error-free.

Except this is the Web, and whether a server located somewhere on the globe will respond with something you expect, is completely out of your control.

This instruction:

.Open "GET", mlink & y, False

Succeeds under normal circumstances, but one day the site will be down for maintenance, or whatever - and you'll get a run-time error, be it here or at the .Send call.

Code that doesn't handle errors is code that is written for the "happy path" - it's code that works well, until it doesn't. And then, when one thing goes wrong, everything burst up in flames, in a quite inelegant way. There are ways to graciously handle run-time errors.

Use them.

On Error GoTo CleanFail

Looks like you like scraping stuff. That's great, but at one point you need to solve the more generic problem, and move the URL from a local Const to a parameter.

Consider implementing the website-specifics as interface implementations.

Option Explicit

Public Function Parse(ByVal url As String) As VBA.Collection
End Function

And when you start implementing interfaces in VBA, you'll find that VBA will refuse to compile when you have an implemented public interface member with an underscore in its name - so you might as well drop that habit now.

The indentation is wrong. Get the latest Rubberduck and use its Smart Indenter. Rubberduck will also warn you about other things, e.g. multiple declarations in a single instruction, single-letter identifiers, and As New, which literally makes an indestructible object - which is usually a very bad idea. Try adding this instruction:

Set http = Nothing
Debug.Print http Is Nothing

If you expected that to print True, you've fallen prey to the As New "feature". Best stay away from that when you want to control your objects' life time.

  • \$\begingroup\$ Thanks sir Mat's Mug, for your invaluable suggestion and review. I always try to implement a proper indentation but because of lack of experience and guideline I can't make it. I don't have any idea as to how I use Rubberduck. I'll give it a try, though. Finally, I thought so far that using error handler is not a good practice cause I always wanted to hurdle with the errors so that I can shun those by writing flawless code but it seems I was wrong. Thanks again sir. \$\endgroup\$ – SIM Jul 22 '17 at 21:57
  • 1
    \$\begingroup\$ Ah, easy: Rubberduck > Indentation > Indent module; done! And you can configure your indentation preferences in the settings dialog; with Rubberduck your indentation will always be consistent! Version 2.1.2001 (pre-release) is fresh out of the oven! \$\endgroup\$ – Mathieu Guindon Jul 22 '17 at 22:02
  • \$\begingroup\$ I've installed Rubberduck. However, pressing ctrl+M, I got my code indented. It seems like magic to me. Just a curiosity - Does this add-in make the program slower or anything? \$\endgroup\$ – SIM Jul 23 '17 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.