Latitude Longitude Functions

treasure map

Working with distance, latitude and longitude in Excel

This page presents various methods for acquiring latitude and longitude in Excel/VBA, as well as driving distance.

Make sure your system meets the requirements for running the code found on this page. You will probably also need to download the MSXML utility functions.

xml logo

Get Latitude and Longitude using XML

We can use web services to look up latitude and longitude. WebserviceX.net offers a few free web APIs for consuming weather information. Fortunately, this service also returns the latitude and longitude for a given zip code. Paste the following into a standard module and run the TestGetLatLong function.

The GetLatitudebyZip and GetLongitudeByZip functions create temporary XML files and do a rudimentary parsing for the latitude and longitude found therein. The temp file is removed after processing. You could easily combine these two functions to return both the latitude and longitude. For example, as an array.

Const XML_FILE_EXTENSION As String = ".xml"

Sub TestGetLatLong()

  Debug.Print GetLatitudeByZip("11103")
  Debug.Print GetLongitudeByZip("11103")

End Sub

Function GetLatitudeByZip(ZipCode As String) As Double
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=44
' requires %windir%\system32\msxml2.dll

Dim xml As Object
Dim result As String
Dim nextFileNum As Long
Dim tempFile As String
Dim doc As Object
Dim objRoot As Object
Dim objChild As Object

  ' create XML document with weather info (which also
  ' contains lat/long)
  Set xml = GetMSXML

  xml.Open "GET", _
"http://www.webservicex.net/WeatherForecast.asmx/GetWeatherByZipCode?ZipCode=" & _
ZipCode, False
  xml.Send

  result = xml.responsetext

  ' write to temp xml file
  nextFileNum = FreeFile

  tempFile = Environ("temp") & Application.PathSeparator & ZipCode & XML_FILE_EXTENSION

  Open tempFile For Output As #nextFileNum
  Print #nextFileNum, result
  Close #nextFileNum

  ' create new XML doc
  Set doc = CreateObject("MSXML2.DOMDocument")
  doc.validateOnParse = False

  doc.Load tempFile

  Set objRoot = doc.documentElement

  ' loop through top-level children
  For Each objChild In objRoot.childNodes

    If objChild.nodeName = "Latitude" Then
      GetLatitudeByZip = objChild.Text
      Kill tempFile
      Exit Function
    End If
  Next objChild

End Function

Function GetLongitudeByZip(ZipCode As String) As Double
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=44
' requires %windir%\system32\msxml2.dll

Dim xml As Object
Dim result As String
Dim nextFileNum As Long
Dim tempFile As String
Dim doc As Object
Dim objRoot As Object
Dim objChild As Object

  ' create XML document with weather info (which also
  ' contains lat/long)
  Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

  xml.Open "GET", _
"http://www.webservicex.net/WeatherForecast.asmx/GetWeatherByZipCode?ZipCode=" & _
ZipCode, False
  xml.Send

  result = xml.responsetext

  ' write to temp xml file
  nextFileNum = FreeFile

  tempFile = Environ("temp") & Application.PathSeparator & ZipCode & XML_FILE_EXTENSION

  Open tempFile For Output As #nextFileNum
  Print #nextFileNum, result
  Close #nextFileNum

  ' create new XML doc
  Set doc = CreateObject("MSXML2.DOMDocument")
  doc.validateOnParse = False

  doc.Load tempFile

  Set objRoot = doc.documentElement

  ' loop through top-level children
  For Each objChild In objRoot.childNodes

    If objChild.nodeName = "Longitude" Then
      GetLongitudeByZip = objChild.Text
      Kill tempFile
      Exit Function
    End If
  Next objChild

End Function

Latitude and Longitude from Geonames

Geonames is a web service that returns geographical information from its database. It also returns latitude and longitude for select functions.

The following function may be used (within the United States) to return the latitude and longitude for a given zip code. It's approximate, but good enough for many purposes.

Don't forget to paste the helper functions into a standard module in the same project.

Function GetLatLong(postCode As String, Optional countryCode As String = "USA", _
                    Optional radius As Long = 10, _
                    Optional maxRows As Long = 1) As String()

Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim geoNames As Object ' MSXML2.IXMLDOMNode
Dim code As Object ' MSXML2.IXMLDOMNode
Dim i As Long

  tempFile = Environ("temp") & "\" & postCode & "latlong.xml"

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    xml.Open "GET", "http://ws.geonames.org/findNearbyPostalCodes?postalcode=" & _
                    postCode & "&country=" & countryCode & "&radius=" & radius & _
                    "&maxRows=" & maxRows & "&type=xml", False
    xml.send

    result = ConvertAccent(xml.responseText)

    ' create XML file from result
    Call CreateFile(tempFile, result)

  End If

  ' create XML doc
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get first level nodes
  Set geoNames = xmlDoc.childNodes(1)

  ' resize array
  ReDim tempString(1 To 2)

  ' the first and only node
  Set code = geoNames.childNodes(0)

  tempString(1) = code.childNodes(3).nodeTypedValue
  tempString(2) = code.childNodes(4).nodeTypedValue

  GetLatLong = tempString

End Function

The function returns latitude and longitude as a String array; the first element is latitude, the second is longitude.

Sub TestGetLatLongFromGeonames()

Dim results() As String
Dim latitude As String
Dim longitude As String

  results = GetLatLong("11103")

  latitude = results(1)
  longitude = results(2)

End Sub

For more Geonames API functions, visit my blog.

Latitude and longitude using Business.Gov API

We can also return latitude and longitude using the Business.Gov API. It returns latitude and longitude for a city/state combination (U.S. only). While this isn't as accurate as lat/lon for a street address, it may be good enough for your needs.

To use this function you'll need the URLEncode function, as well as the helper functions used elsewhere.

Function GetLatLong(stateAbbr As stateAbbr, city As String) As String()
' uses business.gov API to get latitude and
' longitude for a city/state combo

Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim tempString() As String
Dim result As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim sites As Object ' MSXML2.IXMLDOMNodeList
Dim site As Object ' MSXML2.IXMLDOMNode
Dim i As Long, j As Long
Dim state As String

  state = GetStateAbbr(stateAbbr)

  Const TEMP_FILENAME As String = "LatLong"
  Const XML_FILE_EXTENSION As String = ".xml"

  ' if XML file exists, don't requery website
  tempFile = environ("temp") & "\" & TEMP_FILENAME & state & city & XML_FILE_EXTENSION

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", _
    "http://api.business.gov/geodata/all_links_for_city_of/" & _
    URLEncode(city) & "/" & state & ".xml", False
      .send
    End With

    result = ConvertAccent(xml.responseText)

    ' save result as temp XML document
    tempFile = CreateFile(tempFile, result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)

  ' get first level child nodes
  Set sites = GetChildNodes(xmlDocRoot)

  ' resize array
  ReDim tempString(1 To 2)

  Set site = sites.item(0)

  tempString(1) = site.childNodes.item(10).nodeTypedValue
  tempString(2) = site.childNodes.item(11).nodeTypedValue

  GetLatLong = tempString

End Function

You'll also need the following Enum section, and a function to translate the constant into a String. Paste it at the top of a standard module in the same project. The GetStateAbbr function translates the constant into a string needed for the GetLatLong function.

Public Enum stateAbbr
  ALABAMA
  ALASKA
  AMERICAN_SAMOA
  ARIZONA
  ARKANSAS
  CALIFORNIA
  COLORADO
  CONNECTICUT
  DELAWARE
  DISTRICTOFCOLUMBIA
  MICRONESIA
  FLORIDA
  GEORGIA
  GUAM
  HAWAII
  IDAHO
  ILLINOIS
  INDIANA
  IOWA
  KANSAS
  KENTUCKY
  LOUISIANA
  MAINE
  MARSHALL_ISLANDS
  MARYLAND
  MASSACHUSETTS
  MICHIGAN
  MINNESOTA
  MISSISSIPPI
  MISSOURI
  MONTANA
  NEBRASKA
  NEVADA
  NEW_HAMPSHIRE
  NEW_JERSEY
  NEW_MEXICO
  NEW_YORK
  NORTH_CAROLINA
  NORTH_DAKOTA
  NORTHERN_MARIANA_ISLANDS
  OHIO
  OKLAHOMA
  OREGON
  PALAU
  PENNSYLVANIA
  PUERTO_RICO
  RHODE_ISLAND
  SOUTH_CAROLINA
  SOUTH_DAKOTA
  TENNESSEE
  TEXAS
  UTAH
  VERMONT
  VIRGIN_ISLANDS
  VIRGINIA
  WASHINGTON
  WEST_VIRGINIA
  WISCONSIN
  WYOMING
End Enum

Function GetStateAbbr(stateAbbr As stateAbbr) As String
  Select Case stateAbbr
    Case 0: GetStateAbbr = "AL"
    Case 1: GetStateAbbr = "AK"
    Case 2: GetStateAbbr = "AS"
    Case 3: GetStateAbbr = "AZ"
    Case 4: GetStateAbbr = "AR"
    Case 5: GetStateAbbr = "CA"
    Case 6: GetStateAbbr = "CO"
    Case 7: GetStateAbbr = "CT"
    Case 8: GetStateAbbr = "DE"
    Case 9: GetStateAbbr = "DC"
    Case 10: GetStateAbbr = "FM"
    Case 11: GetStateAbbr = "FL"
    Case 12: GetStateAbbr = "GA"
    Case 13: GetStateAbbr = "GU"
    Case 14: GetStateAbbr = "HI"
    Case 15: GetStateAbbr = "ID"
    Case 16: GetStateAbbr = "IL"
    Case 17: GetStateAbbr = "IN"
    Case 18: GetStateAbbr = "IA"
    Case 19: GetStateAbbr = "KS"
    Case 20: GetStateAbbr = "KY"
    Case 21: GetStateAbbr = "LA"
    Case 22: GetStateAbbr = "ME"
    Case 23: GetStateAbbr = "MH"
    Case 24: GetStateAbbr = "MD"
    Case 25: GetStateAbbr = "MA"
    Case 26: GetStateAbbr = "MI"
    Case 27: GetStateAbbr = "MN"
    Case 28: GetStateAbbr = "MS"
    Case 29: GetStateAbbr = "MO"
    Case 30: GetStateAbbr = "MT"
    Case 31: GetStateAbbr = "NE"
    Case 32: GetStateAbbr = "NV"
    Case 33: GetStateAbbr = "NH"
    Case 34: GetStateAbbr = "NJ"
    Case 35: GetStateAbbr = "NM"
    Case 36: GetStateAbbr = "NY"
    Case 37: GetStateAbbr = "NC"
    Case 38: GetStateAbbr = "ND"
    Case 39: GetStateAbbr = "MP"
    Case 40: GetStateAbbr = "OH"
    Case 41: GetStateAbbr = "OK"
    Case 42: GetStateAbbr = "OR"
    Case 43: GetStateAbbr = "PW"
    Case 44: GetStateAbbr = "PA"
    Case 45: GetStateAbbr = "PR"
    Case 46: GetStateAbbr = "RI"
    Case 47: GetStateAbbr = "SC"
    Case 48: GetStateAbbr = "SD"
    Case 49: GetStateAbbr = "TN"
    Case 50: GetStateAbbr = "TX"
    Case 51: GetStateAbbr = "UT"
    Case 52: GetStateAbbr = "VT"
    Case 53: GetStateAbbr = "VI"
    Case 54: GetStateAbbr = "VA"
    Case 55: GetStateAbbr = "WA"
    Case 56: GetStateAbbr = "WV"
    Case 57: GetStateAbbr = "WI"
    Case 58: GetStateAbbr = "WY"
  End Select

End Function

Sample usage

Sub TestGetLatLong()

Dim results() As String
Dim latitude As String
Dim longitude As String

results = GetLatLong(NEW_YORK, "Albany")
latitude = results(1)
longitude = results(2)

End Sub

Abbreviations.com Zip Codes API returns Latitude and Longitude

Here's yet another way to get latitude and longitude. Will it ever stop?

To use the Zip Codes API from Abbreviations.com, visit their API page. You'll need an API key to use the following function. I have an API key (they call it a "token ID"), but in the sample code below it has been removed.

This function takes a zip code and returns the latitude and longitude as an array. It's not as accurate as a street address, but might be close enough for most purposes. It is late bound but uses MSXML2.DLL which should be located in your %windir%\system32 folder.

Note: The second and third values of the returned String array contain the latitude and longitude for the given zip code.

Public Const zip_base_URL As String = "http://www.uszip.com/services/v1/zip.aspx"

Function GetLatLong(tokenID As String, zipCode As String, _
                            Optional forceRequery As Boolean = False) As String()

Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String
Dim tempString() As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim resultNode As Object ' MSXML2.IXMLDOMNode

Const XML_FILE_EXTENSION As String = ".xml"

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & zipCode & XML_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", zip_base_URL & "?tokenid=" & tokenID & "&zip=" & zipCode, False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set resultNode = GetNode(xmlDocRoot, 1)

  ' resize array
  ReDim tempString(1 To 1, 1 To resultNode.childNodes.Length)

  tempString(1, 1) = GetNode(resultNode, 1).nodeTypedValue
  ' latitude
  tempString(1, 2) = GetNode(resultNode, 2).nodeTypedValue
  ' longitude
  tempString(1, 3) = GetNode(resultNode, 3).nodeTypedValue

  GetLatLong = tempString

End Function

Sample Usage

This sample procedure will return the latitude and longitude for Astoria, NY (zip code 11103).

Sub tst()

Dim tokenID As String
Dim results() As String
Dim result As String
Dim i As Long, j As Long

  tokenID = "get your token ID from Abbreviations.com"
  results = GetLocationFromZip(tokenID, "11103")

  For i = LBound(results) To UBound(results)
    For j = LBound(results, 2) To UBound(results, 2)
      Debug.Print results(i, j)
    Next j
  Next i

End Sub

Don't forget to copy the helper functions.

See more Abbreviations.com API code samples.

Get Latitude and Longitude from UK Post Codes

UK Post Codes has a web API for returning latitude and longitude based on a given post code. Note that this works for post codes in the UK only.

Since we're using post code, the latitude and longitude returned by these functions is approximate, but should be good enough for most needs.

GetLatitude Function

This function takes a UK post code as a string and returns a Double representing the latitude of the post code. It's adapted from PHP code posted by the API owner.

Specifically, it returns the result as CSV instead of XML. The CSV is easier to parse.

Function GetLatitude(postCode As String, Optional forceRequery As Boolean = False) _
    As Double
Dim requestString As String
Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim results As String
Dim result() As String
Dim fileNum As Integer

Const CSV_FILE_EXTENSION As String = ".csv"
Const base_URL As String = "http://www.uk-postcodes.com/postcode/"

  ' temp file name
  tempFile = environ("temp") & "\" & postCode & CSV_FILE_EXTENSION

  ' if temp file exists, or force requery, then query site again
  If (Len(Dir(tempFile)) = 0 Or forceRequery) Then

    ' create request string
    requestString = base_URL & postCode & CSV_FILE_EXTENSION

    Set xml = GetMSXML

    With xml
      .Open "GET", requestString, False
      .send
    End With

    ' check for html
    ' if present, error
    If InStr(xml.responseText, "html") > 0 Then
      Exit Function
    Else
      ' cache API response
      Call CreateFile(tempFile, xml.responseText)

    End If
  End If

  fileNum = FreeFile
  Open tempFile For Input As #fileNum
    results = Input$(LOF(fileNum), #fileNum)
    result = Split(results, ",")
  Close #fileNum

  GetLatitude = result(1)

End Function

GetLongitude Function

This function takes a UK post code as a string and returns a Double representing the longitude of the post code.

Function GetLongitude(postCode As String, _
   Optional forceRequery As Boolean = False) As Double
' adapted from: http://gist.github.com/364477
Dim requestString As String
Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim results As String
Dim result() As String
Dim fileNum As Integer

Const CSV_FILE_EXTENSION As String = ".csv"
Const base_URL As String = "http://www.uk-postcodes.com/postcode/"

  ' temp file name
  tempFile = environ("temp") & "\" & postCode & CSV_FILE_EXTENSION

  ' if temp file exists, or force requery, then query site again
  If (Len(Dir(tempFile)) = 0 Or forceRequery) Then

    ' create request string
    requestString = base_URL & postCode & CSV_FILE_EXTENSION

    Set xml = GetMSXML

    With xml
      .Open "GET", requestString, False
      .send
    End With

    ' check for html
    ' if present, error
    If InStr(xml.responseText, "html") > 0 Then
      Exit Function
    Else
      ' cache API response
      Call CreateFile(tempFile, xml.responseText)

    End If
  End If

  fileNum = FreeFile
  Open tempFile For Input As #fileNum
    results = Input$(LOF(fileNum), #fileNum)
    result = Split(results, ",")
  Close #fileNum

  GetLongitude = result(2)

End Function

Sample Usage

Sub TestUKPostCodeInfo()

Dim result(1 To 2) As Double
Dim i As Long

  result(1) = GetLatitude("DH981BT")
  result(2) = GetLongitude("DH981BT")

  For i = LBound(result) To UBound(result)
    Debug.Print result(i)
  Next i

End Sub

Return Latitude Or Longitude with One Function

Using an Enum we can create a function that can selectively return either latitude or longitude.

First, paste this at the top of a standard module. This will allow our function to limit the choice to either latitude or longitude.

Public Enum LatLong
latitude
longitude
End Enum

Function GetLL(ll As LatLong) As Long
  Select Case ll
    Case 0
      GetLL = 1
    Case 1
      GetLL = 2
  End Select
End Function

Function GetLatLong(postCode As String, ll As LatLong, _
   Optional forceRequery As Boolean = False) As Double

Dim requestString As String
Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim results As String
Dim result() As String
Dim fileNum As Integer

Const CSV_FILE_EXTENSION As String = ".csv"
Const base_URL As String = "http://www.uk-postcodes.com/postcode/"

  ' temp file name
  tempFile = environ("temp") & "\" & postCode & CSV_FILE_EXTENSION

  ' if temp file exists, or force requery, then query site again
  If (Len(Dir(tempFile)) = 0 Or forceRequery) Then

    ' create request string
    requestString = base_URL & postCode & CSV_FILE_EXTENSION

    Set xml = GetMSXML

    With xml
      .Open "GET", requestString, False
      .send
    End With

    ' check for html
    ' if present, error
    If InStr(xml.responseText, "html") > 0 Then
      Exit Function
    Else
      ' cache API response
      Call CreateFile(tempFile, xml.responseText)

    End If
  End If

  fileNum = FreeFile
  Open tempFile For Input As #fileNum
  results = Input$(LOF(fileNum), #fileNum)
  result = Split(results, ",")
  Close #fileNum

  ' return either latitude or longitude based on parameter
  GetLatLong = result(GetLL(ll))

End Function

Sample Usage

Sub TestUKPostCodeInfo()

Dim result As Double
Dim i As Long

  result = GetLatLong("DH981BT", longitude)

  Debug.Print result
End Sub

Return both Latitude and Longitude At Same Time

Since it's so simple to return either the latitude or longitude, and the functions are nearly identical, let's return both as a Double array.

Function GetLatLong(postCode As String, Optional forceRequery As Boolean = False) _
    As Double()

Dim requestString As String
Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim tempLL(1 To 2) As Double
Dim results As String
Dim result() As String
Dim fileNum As Integer

Const CSV_FILE_EXTENSION As String = ".csv"
Const base_URL As String = "http://www.uk-postcodes.com/postcode/"

  ' temp file name
  tempFile = environ("temp") & "\" & postCode & CSV_FILE_EXTENSION

  ' if temp file exists, or force requery, then query site again
  If (Len(Dir(tempFile)) = 0 Or forceRequery) Then

    ' create request string
    requestString = base_URL & postCode & CSV_FILE_EXTENSION

    Set xml = GetMSXML

    With xml
      .Open "GET", requestString, False
      .send
    End With

    ' check for html
    ' if present, error
    If InStr(xml.responseText, "html") > 0 Then
      Exit Function
    Else
      ' cache API response
      Call CreateFile(tempFile, xml.responseText)

    End If
  End If

  fileNum = FreeFile
  Open tempFile For Input As #fileNum
  results = Input$(LOF(fileNum), #fileNum)
  result = Split(results, ",")
  Close #fileNum

  tempLL(1) = result(1)
  tempLL(2) = result(2)

  GetLatLong = tempLL

End Function

Sample Usage

Sub TestUKPostCodeInfo()

Dim result() As Double
Dim i As Long

  result = GetLatLong("DH981BT")
  For i = LBound(result) To UBound(result)
    Debug.Print result(i)
  Next i

End Sub

Google Maps API returns Latitude/Longitude

For creating maps with Google Maps, you'll need latitude and longitude for any given address. The Geocoding API provided by Google converts string addresses into latitude and longitude.

Be warned, however: using the Google Maps Geocoding API results without a corresponding map is prohibited by Google's ToS.

The following function returns latitude and longitude for any given street address, city and state (in the U.S. and I assume for anywhere in the world).

Function GetLatAndLong(address As String, city As String, _
    state As String) As Double()
' adapted from http://snipplr.com/view.php?codeview&id=40956

Dim domDoc As Object ' MSXML2.DOMDocument
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim geometry As Object ' MSXML2.IXMLDOMNode
Dim tempLL(1 To 2) As Double

  Set domDoc = GetDomDoc

  domDoc.async = False

  domDoc.Load "http://maps.google.com/maps/api/geocode/xml?address=" & _
              URLEncode(address) & ",+" & URLEncode(city) & ",+" & _
              URLEncode(state) & "&sensor=false"

  If LoadError(domDoc) Then
    MsgBox "error occurred, please try again."
    Exit Function
  End If

  Set results = domDoc.GetElementsByTagName("result")
  Set geometry = GetNode(results.item(0), 11)

  tempLL(1) = GetNode(GetNode(geometry, 1), 1).nodeTypedValue
  tempLL(2) = GetNode(GetNode(geometry, 1), 2).nodeTypedValue

  GetLatAndLong = tempLL

End Function

I couldn't get this function to work without causing an error. Once I click "Debug" and then F5 to continue, it produces the correct result. But if you add "On Error Resume Next" to the function and run it, the result is 0. Thanks to Mark who emailed with a fix for this — setting the async Property to False.

Geocoder Latitude and Longitude

Geocoder has a few more web services, these also return latitude and longitude when passed a valid street address.

The following function takes a street address and returns the latitude and longitude, as well as a formatted version of that address.

Function GetAddressInfo(streetAddress As String, _
    Optional forceRequery As Boolean = False) As String()
' http://geocoder.us/help/
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim results() As String
Dim tempFolder As String
Dim tempFile As String

Const CSV_FILE_EXTENSION As String = ".csv"
Const baseURL As String = "http://rpc.geocoder.us/service/csv?address="

  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & streetAddress & CSV_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", baseURL & Replace(streetAddress, " ", "+"), False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, result

  End If

  ' open file
  results = Split(GetText(tempFile), ",")

  GetAddressInfo = results

End Function

Helper Functions

The function above uses the following function, as well as the GetText function found at ExcelUser. The CreateFile function writes the XML result to a CSV file, while the GetText function opens the CSV file.

Normally I would just split xml.responseText (since it already contains the comma-delimited list of values found in the text file) but I want to cache the result since Geocoder is a bit stingy with how many API calls they allow in a given period of time.

Visit MSXML Object Library Routines to grab the CreateFile function.

Sample Usage

Sub TestGetAddressInfo()

Dim result() As String
Dim i As Long, j As Long

  result = GetAddressInfo("1600 Pennsylvania Ave, Washington DC")

  For i = LBound(result) To UBound(result)
    Debug.Print result(i)
  Next i

End Sub

Click the dropdown to view articles in the Latitude Longitude Functions section:

Working with distance, latitude and longitude in Excel

Get Latitude and Longitude using XML

Latitude and Longitude from Geonames

Latitude and longitude using Business.Gov API

Sample usage

Abbreviations.com Zip Codes API returns Latitude and Longitude

Sample Usage

Get Latitude and Longitude from UK Post Codes

GetLatitude Function

GetLongitude Function

Sample Usage

Return Latitude Or Longitude with One Function

Sample Usage

Return both Latitude and Longitude At Same Time

Sample Usage

Google Maps API returns Latitude/Longitude

Geocoder Latitude and Longitude

Helper Functions

Sample Usage