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

For the time being, you can add a line before and a line after: On Error Resume Next : .Range.... : On Error Goto 0. Note that this is not a proper solution, neither is your extraction of the data. Hopefully, someone more qualified will explain the issues. — VBasic2008
– VBasic2008, Commented Mar 12, 2021 at 18:28
It is necessary like that? it makes me a mistake On Error Resume Next .Range("C" & i).Value = Split(Split(Txt, "id=""distanciaRuta"">")(1), "</strong>")(0) .Range.... : On Error Goto 0 — Theo27
– Theo27, Commented Mar 12, 2021 at 18:41
A line before and a line after means 1. line On Error Resume Next, 2.line .Range...., and 3. line On Error Goto 0. — VBasic2008
– VBasic2008, Commented Mar 12, 2021 at 18:43
Oh sorry, I didn't quite understand! Thank you for your answer, it leaves a gap and goes on to the next one. What did you mean when you said that my solution is not appropriate? — Theo27
– Theo27, Commented Mar 12, 2021 at 18:48

HackSlash · Accepted Answer · 2021-03-12 18:56:55Z

3

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

answered Mar 12, 2021 at 18:56

HackSlash

6,0372 gold badges28 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Theo27 Over a year ago

Thank you very much for your explanations, this is exactly what I wanted.

Theo27 Over a year ago

But I have to add the line On Error Resume Next before your modifications if I want the program not to stop when it does not find a city?

HackSlash Over a year ago

No. I'm saying "never do that". If you think you need to resume next, think again. Ignoring bugs doesn't make them go away.

Theo27 Over a year ago

Yes you are absolutely right. Thank you for your answers

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related