Using BING MAPS get miles

[CHFD13]

Ok as I read in several of the older threads GOOGLE now charges for the look up of API. I had a Excel VBA using Google Maps API. that I paid for. Well now that it is no longer recognized. Am having to start over.
Ok now that I've had my rantings...
I am the National Fire Incident Reporting Systems(NFIRS) Officer for the Center Hill Fire Protection District, (CHFPD) pronounced as SHHFud).
I do an Annual Fire Board Report, detailing all of the calls that CHFPD responded to the previous year. (Jan 1-Dec31 2022)
Also I do a MILEAGE for the Volunteer Fire Fighters as well. Because they receive a per-call stipend at the end of the year. The FF's get a 10-99 IRS ( Inflamed Rectum Syndrome ) statement as well.
All of that to get to the brass tacks. What I am currently doing is exhaustive to do each manual lookup the miles ONE WAY from the CHFD Station to each incident, Address/ Location,
I have an active BING MAPS API that I'm trying to get to work with my address listing. All of the START POINTS will be at the CHFD Station.
[A Column is the Date, [ B Column is the NFIRS # [ C Column is the Address/ Location [ D Column is Type of Call [ E Column is the Miles to call ( G- Z are the FF's )
01/01/22 2022001 4100 Hwy 36 W [FIre] (have a pick list from) this will be a VBA GET MILES (have a Y or N box)
address will be linked to another sheet not shown

So as you can see I can get almost all of this done pretty simple But the MAPPING is driving me crazy
Thanks CHFD13

 

[johnnyL]

The following is the custom function I created for you:

Function DistanceTo(DestinationAddress As String)
'
    Dim WebServer                       As Object
    Dim API_Key                         As String
    Dim StartAddress                    As String
    Dim StreetAddress                   As String, City                         As String, StateAddress     As String
    Dim DestinationAddressCoordinates   As String, StartAddressCoordinates      As String
    Dim WebURL                          As String
'
    API_Key = "AiWSvDiDSOyokLxBDRsHMBKDbWdlTmWx6UClmFDQHDLUD3TC31cuGxut6DMzgDo9"                                                    ' <--- Set this to your actual Bing API Key
'
'    StartAddress = "4100 AR 36, Searcy, AR"
    StartAddress = "4100 AR 36 Searcy AR"
'
    StreetAddress = Left$(StartAddress, InStrRev(StartAddress, "Searcy") - 2)
    City = "Searcy"
    StateAddress = "AR"
'
    Set WebServer = CreateObject("MSXML2.ServerXMLHTTP")
'
'-----------------------------------------------------------------------------------------------------------------------------------
'
' Get Latitude & Longitude of the StartAddress
'
' Without zipcode
' http://dev.virtualearth.net/REST/v1/Locations/US/" & State & "/" & City & "/" & Street & "?output=xml&key=" & API_Key
    WebURL = "http://dev.virtualearth.net/REST/v1/Locations/US/" & StateAddress & "/" & City & _
            "/" & StreetAddress & "?output=xml&key=" & API_Key
'
    WebServer.Open "GET", WebURL, False
    WebServer.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    WebServer.Send ("")
'
    StartAddressCoordinates = WorksheetFunction.FilterXML(WebServer.ResponseText, "//Point/Latitude") & _
            ", " & WorksheetFunction.FilterXML(WebServer.ResponseText, "//Point/Longitude")                                         ' Get the Latitude, Longitude of the address
'
'-----------------------------------------------------------------------------------------------------------------------------------
'
' Get Latitude & Longitude of the DestinationAddress
    StreetAddress = Left$(DestinationAddress, InStrRev(DestinationAddress, "Searcy") - 2)
'
    WebURL = "http://dev.virtualearth.net/REST/v1/Locations/US/" & StateAddress & "/" & City & _
            "/" & StreetAddress & "?output=xml&key=" & API_Key
'
    WebServer.Open "GET", WebURL, False
    WebServer.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    WebServer.Send ("")
'
    DestinationAddressCoordinates = WorksheetFunction.FilterXML(WebServer.ResponseText, "//Point/Latitude") & _
            ", " & WorksheetFunction.FilterXML(WebServer.ResponseText, "//Point/Longitude")                                  ' Get the Latitude, Longitude of the address
'
'-----------------------------------------------------------------------------------------------------------------------------------
'
' Get the driving distance, in miles, between the two address coordinates
    WebURL = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" & StartAddressCoordinates & _
            "&destinations=" & DestinationAddressCoordinates & "&travelMode=driving&o=xml&key=" & API_Key & "&distanceUnit=mi"      ' Set the URL to go to
'
    WebServer.Open "GET", WebURL, False
    WebServer.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    WebServer.Send ("")
'
    DistanceTo = Round(WorksheetFunction.FilterXML(WebServer.ResponseText, "//TravelDistance"), 1) & " miles"                  ' Get the distance between the two addresses
End Function

After you add that code to a module, you will be able to use a formula in a cell to show the distance from the station to the address.

The formula to put into a cell after adding that custom function is something like:
= DistanceTo(C3)

That will calculate the driving distance from the station to the address.