testing for "delivery_line_2" in SmartyStreet's API response

Question

I'm working with the SmartyStreets API and I'm trying to test the response to see if there is a value in the "delivery_line_2" field. I keep getting a variable undefined error in VBA for Excel when trying to run the code. Anyone have a suggestion for the best way to do this? I'm using the following code to try to access:

    Dim candidates, candidate, components, metadata, analysis As MSXML2.IXMLDOMNode
    Set candidates = xmlDoc.DocumentElement
    
    For Each candidate In candidates.ChildNodes
        Set components = candidate.SelectSingleNode("components")
        Set metadata = candidate.SelectSingleNode("metadata")
        Set analysis = candidate.SelectSingleNode("analysis")

        Cells(RowIndex, DPVColumn).Value = analysis.SelectSingleNode("dpv_match_code").nodeTypedValue
        Cells(RowIndex, DPVColumn + 1).Value = components.SelectSingleNode("zipcode").nodeTypedValue & "-" & components.SelectSingleNode("plus4_code").nodeTypedValue
        Cells(RowIndex, DPVColumn + 2).Value = candidate.SelectSingleNode("delivery_line_1").nodeTypedValue
        **If Len(candidate.SelectSingleNode("delivery_line_2").nodeTypedValue) > 1 Then
            Cells(RowIndex, DPVColumn + 3).Value = candidate.SelectSingleNode("delivery_line_2").nodeTypedValue
        End If**

Thanks.

score 1 · Answer 1 · edited Jul 10 '20 at 08:40

SmartyStreets warns you that not all data is returned every time. This is true for anything pretty much it seems, although some pieces of the return tend to be there, others not so much. delivery_line_2 is often not returned. When this is the case trying to get the value may raise an Error 91 "Object variable or with block variable not set" as it does not exist. Since it has not even been instantiated most simple test such as IsEmpty, IsError, IsMissing, etc. fail to check this. It adds a lot of code but the best recourse is to first Set a variable equal to the referenced part of the XML return like you do with metadata. Also do this for delivery_line_2. If the reference isn't in the return the variable will be set as "Nothing". For some reason this does not raise an error. Now you can test the variable:

Set DeliveryLine2 = candidate.SelectSingleNode("delivery_line_2")  'Note lack of nodeTypedValue
    
If Not DeliveryLine2 Is Nothing Then
    
  Cells(RowIndex, DPVColumn + 3).Value = candidate.SelectSingleNode("delivery_line_2").nodeTypedValue
    
End If

In addition to testing each piece of the return you need to recognize that pieces will not always be there, and properly handle that with your code. SmartyStreets tells you this, but then their reference to a multitude of help on sites such as this one is a little weak. I did my work in Access 2010, but it is still VBA.

testing for "delivery_line_2" in SmartyStreet's API response

1 Answers1