Continuing my last post, on how to get the distance between any addresses using VBA, I wanted to add a capability to my previous Excel file that would allow me to get any address coordinates. How to quickly get the lat. and long. of an address? Again why not use Google API…

Address coordinates in Excel

Sub GetLocation(address As String, ByRef lat As String, ByRef lng As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    firstVal = "http://maps.googleapis.com/maps/api/geocode/json?address="
    lastVal = "&sensor=false"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(address, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """lat""") = 0 Then GoTo ErrorHandl
    tmpVal = Right(objHTTP.responseText, Len(objHTTP.responseText) - InStr(objHTTP.responseText, """lat"" : ") - 7)
    lat = Split(tmpVal, ",")(0)
    tmpVal = Right(objHTTP.responseText, Len(objHTTP.responseText) - InStr(objHTTP.responseText, """lng"" : ") - 7)
    lng = Replace(Split(tmpVal, "}")(0), " ", "")
    Exit Sub
ErrorHandl:
    lat = lng = 0
End Sub

This function can easily be used in any VBA algorithm like this:

Dim lat1 as String, lng1 as String
call GetLocation("Al. Jerozolimskie 2, Warsaw", lat:=lat1, lng:=lng1)

In the example above the address coordinates (latitude and longitude) will be returned to lat1 and lng1 variables.

Limits and common issues

Google limits the amount of free queries you can execute. Considering the limitations and common issues please read this section of my post.