VBA keeps returning in the same sub, keep getting #Value

[Legacy99]

Hi guys,

I'm currently working on an excel sheet which i want to fill with Latitude, longitude, drivedistance and driveduration.
I work with 1 function which calls 2 functions.

The problem that i'm currently trying to solve is to stop my code from returning to the sub, over and over again, which results in getting the incorrect values in the incorrect cells.

My code:

Public Sub Fillin(Van As String, naar As String)


    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?address=" & [naar] & ",%20Nederland&key=AIzaSyDXfQXTUlRRlA44GhkHsdcv3hbqyrVnT_o", False
        .send
        strEndpoint = CStr(.responseXML.SelectSingleNode("//place_id").Text)
        strLatitude = CStr(.responseXML.SelectSingleNode("//geometry/location/lat").Text)
        strLongitude = CStr(.responseXML.SelectSingleNode("//geometry/location/lng").Text)
        
        .Open "GET", "https://maps.googleapis.com/maps/api/distancematrix/xml?units=metric&origins=place_id:" & [Van] & "&destinations=place_id:" & [strEndpoint] & "&key=AIzaSyAwQwhfw4R5DKGbNflNhVGj4u_wsr6Rmvg", False
        .send
        lngAfstandmeting = CLng(.responseXML.SelectSingleNode("//element/distance/value").Text) / 1000
        lngDuration = CLng(.responseXML.SelectSingleNode("//element/duration/value").Text) / 60
    End With
End Sub
Public Sub Cellen()
        lngA = ActiveCell.Row - 1
        strCell = "BX" & lngA
        
        ActiveCell.Offset(-1, 0).Value = lngDuration
        
        ActiveCell.Offset(-1, -12).Select
        ActiveCell.Value = lngAfstandmeting
        ActiveSheet.Range(strCell).Select
        ActiveCell.Value = strLatitude
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = strLongitude
        If ActiveCell.Value = strLongitude Then
            Exit Sub
        End If
    End Sub
Public Function Duration(Van As String, naar As String) As Long
Line1:
    If lngAantal = 0 Then
        Call Fillin(Van, naar)
        lngAantal = 1 + lngAantal
    ElseIf lngAantal < 3 Then
        lngAantal = 1 + lngAantal
        Cellen
    End If
    If lngAantal < 3 Then
        GoTo Line1
    Else
        lngAantal = 0
    End If
End Function

He keeps returning to the 'Cellen' Sub

 

[Stiuart_W]

Firstly

Change your function name. Excel already has a function called "DURATION" (At least in my newish version it does)

I step through and it crashes out at .send on the Fillin Sub so have a look at that

 

[Legacy99]

Thats because you don't have a [naar] and [van] variable, you should use '3535' as naar and as [Van] 'ChIJVXealLU_xkcRja_At0z9AGY'

In my code, it doesn't crash, it just keeps looping in the sub 'Cellen'