excel - How to continue processing a VBA macro in case of an error?

Question

I am new to VBA and I have a problem.

I have an excel file with a macro in VBA that allows me to calculate the distance between two cities in kms :

The problem is that if the destination city does not exist the script stops and puts this error that says that the index does not belong to the selection.

When I select "Debug" it highlights this line in yellow:

 .Range("C" & i).Value = Split(Split(Txt, "id=""distanciaRuta"">")(1), "</strong>")(0)

How to do that even if the city is not found to continue the execution of the script by leaving a blank in the box?

Option Explicit

Public Const DIST = "http://www.distance2villes.com/recherche?source="


Sub Distance()
Dim lg As Integer, i As Integer
Dim Url As String, Txt As String

    With Sheets("Feuil1")
        lg = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 2 To lg
            Url = DIST & .Range("A" & i).Value & "&destination=" & .Range("B" & i).Value
            With CreateObject("WINHTTP.WinHTTPRequest.5.1")
                .Open "GET", Url, False
                .send
                Txt = .responseText
            End With
            .Range("C" & i).Value = Split(Split(Txt, "id=""distanciaRuta"">")(1), "</strong>")(0)
        Next i
    End With
End Sub

标签： excelvba

Answer 1

It is better to avoid throwing exceptions when you can. In your case, a null check is what you want

Option Explicit

Public Const DIST = "http://www.distance2villes.com/recherche?source="


Sub Distance()
Dim lg As Integer, i As Integer
Dim Url As String, Txt As String

    With Sheets("Feuil1")
        lg = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 2 To lg
            Url = DIST & .Range("A" & i).Value & "&destination=" & .Range("B" & i).Value
            With CreateObject("WINHTTP.WinHTTPRequest.5.1")
                .Open "GET", Url, False
                .send
                Txt = .responseText
            End With
            
            ' Only set the value if we got a response
            If Txt <> vbNullString Then .Range("C" & i).Value = Split(Split(Txt, "id=""distanciaRuta"">")(1), "</strong>")(0)

            ' Clear our variable before next
            Txt = vbNullString
        Next i
    End With
End Sub

To continue past an error you can use On Error Resume Next but I would only use this as a last resort on a single line where you know there may be an error you can't avoid.

If there are occasional errors it's best to use an error handler. You can write a simple one with an On Error Goto errorHandler pattern or if you are feeling adventurous you can look to well developed solutions for inspiration: https://www.everythingaccess.com/vbwatchdog.asp