XML VBA Error Catching

This code gets the data from the website

http://api.yr.no/weatherapi/location...5;lon=8.555967

The information obtained from this website is then organized in a spreadsheet. I am trying to avoid crashing the program. I've noticed that if an incorrect latitude/longitude is input then the website returns an error. What would need to be added in these codes to have the code check if an error is produced and then ask the user to check their input BEFORE adding the input into the "Site List" worksheet.

Private Sub CommandButtonEditUpdate_Click()

    Dim Msg As String

    If ComboBoxEditSiteName.ListIndex < 0 Then
        MsgBox "You must select a site to edit"

    Else

        If TextBoxEditSiteName = "" Then
            Msg = Msg & "You must include a site name" & vbCrLf
        End If

        If TextBoxEditLat = "" Then
            Msg = Msg & "You must include a latitude" & vbCrLf
        ElseIf Not IsNumeric(TextBoxEditLat) Then
            Msg = Msg & "Latitude must be numeric" & vbCrLf
        End If

        If TextBoxEditLong = "" Then
            Msg = Msg & "You must include a longitude" & vbCrLf
        ElseIf Not IsNumeric(TextBoxEditLong) Then
            Msg = Msg & "Longitude must be numeric" & vbCrLf
        End If

        If Msg <> "" Then
            MsgBox Msg

        Else
            ' locate site record
            ' Write site name to cell
            ' Build URL from lat and long and write to cell

            ' clear form and leave form showing


            Dim selRow As Long
            Dim lat1 As String
            Dim lon1 As String
            Dim sitelink1 As String
            Dim sitename1 As String

            'Assign variables to construct URL
            selRow = ComboBoxEditSiteName.ListIndex + 1
            lat1 = Format(cpUserForm.TextBoxEditLat.Value, "0.0000000000000000")
            lon1 = Format(cpUserForm.TextBoxEditLong.Value, "0.0000000000000000")

            sitelink1 = "http://api.yr.no/weatherapi/locationforecastlts/1.2/?lat=" & lat1 & ";lon=" & lon1
            sitename1 = cpUserForm.TextBoxEditSiteName.Value

            'Write values to spreadsheet cells.
            Worksheets("Site List").Cells(selRow, 1) = sitelink1
            Worksheets("Site List").Cells(selRow, 2) = sitename1
            Worksheets("Site List").Cells(selRow, 3) = lat1
            Worksheets("Site List").Cells(selRow, 4) = lon1


            AddClear
            LoadComboBoxEditSiteName
            LoadListBoxDeletePickSiteList
            EditClear
        End If
    End If
End Sub

Private Sub CommandButtonAddUpdate_Click()


    Dim Msg As String

    If TextBoxAddSiteName = "" Then
        Msg = Msg & "You must include a site name" & vbCrLf
    End If

    If TextBoxAddLat = "" Then
        Msg = Msg & "You must include a latitude" & vbCrLf
    ElseIf Not IsNumeric(TextBoxAddLat) Then
        Msg = Msg & "Latitude must be numeric" & vbCrLf
    End If

    If TextBoxAddLong = "" Then
        Msg = Msg & "You must include a longitude" & vbCrLf
    ElseIf Not IsNumeric(TextBoxAddLong) Then
        Msg = Msg & "Longitude must be numeric" & vbCrLf
    End If

    If Msg <> "" Then
        MsgBox Msg
    Else
        ' Create a new site record
        ' Write site name to cell
        ' Build URL from lat and long and write to cell

        ' clear form and leave form showing

        Dim emptyRow As Long
        Dim lat As String
        Dim lon As String
        Dim siteLink As String
        Dim siteName As String

        'Assign variables to construct URL
        emptyRow = Sheets("Site List").Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
        lat = Format(cpUserForm.TextBoxAddLat.Value, "0.0000000000000000")
        lon = Format(cpUserForm.TextBoxAddLong.Value, "0.0000000000000000")
        siteLink = "http://api.yr.no/weatherapi/locationforecastlts/1.2/?lat=" & lat & ";lon=" & lon
        siteName = cpUserForm.TextBoxAddSiteName.Value

        'Add values to site list worksheet
        Worksheets("Site List").Cells(emptyRow, 1) = siteLink
        Worksheets("Site List").Cells(emptyRow, 2) = siteName
        Worksheets("Site List").Cells(emptyRow, 3) = lat
        Worksheets("Site List").Cells(emptyRow, 4) = lon

        'Refresh lists
        AddClear
        LoadComboBoxEditSiteName
        LoadListBoxDeletePickSiteList

    End If

End Sub

If you want to catch the errors as they happen, i think you will need to collect them differently. It looks like your user is filling in a form, and then all of the fields values collected are validated with a series of IF statements.



Honestly, this would appear to be a case where you could tie those values to specific cells in a worksheet - and just use the worksheet in place of a form. The advantage there is, worksheet cells can be validated to immediately tell someone what is wrong when they enter something.

Using worksheet data validation would eliminate the need for VBA error checking - cells can be validated to only accept numbers. Does that make sense?

You appear to already have code to check for valid input before trying to get the data from the website, is it not working as expected?

Well that is just for making sure that the input data is in the correct format. But if the person inputs a longitude/latitude that does not exist then you would get an error from the API.

So I would guess that the best way for the user to be informed that there is an error is exactly when the person is trying to add/edit a site. That way they will know what is wrong and correct it. I would figure that the way to do that is that in the edit and add tabs the code can send a request to the API and if the API returns an error tell the user that whatever they are trying to add or edit is incorrect, to try again.

What's actually returned by the site when that happens?

When you run the code with invalid latitude and longitude is that what's returned in the responseText of the request?

If it is I would suggest that you check the responseText before you load it into the XLM/DOM document Resp.

Something like this perhaps.

    For Each Site In SiteList

        req.Open "GET", Site.Value, False
        req.send

        ctrr = ctrr + 1
        siteNames = Worksheets("Site List").Cells(ctrr, 2).Value

        If Instr(req.ResponseText, "An error occurred!") Then
            ' code to do something if there's been a problem
        Else
            Resp.LoadXML req.responseText
            ' continue with code to get data
       End If
 
    Next Site

When you send out an XML request what's returned is what you would see if you entered the URL manually.

So when you put in a invalid latitude/longitude the code should return the error message in the response text of the XML request.



To pinpoint the sites just refer to the current site you are on in the loop throught the site list

For Each Site In SiteList
  ' Site is the current site in SiteList

Forgot to ask, did you try the code I suggested?