How to Create a Geographic Tool to Get Latitude and Longitude Coordinates for Addresses with Excel VBA

Follow this article and build your own geographic tool with which you can get the latitude and longitude coordinates for an address.  Converters like this are commonly used by Real estate brokers.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s prepare the GUI

All you need is a single Excel sheet and you can name the sheet as per your need. In this example, I am using the default sheet name “Sheet1”. The next step is to add necessary headers on this sheet. The latitude, longitude converter gives accurate result for the address that we pass if the input includes suburb, state, postcode and country. As shown in the image, prepare headers. Let us add Latitude and Longitude as last two columns. We also need a button to allow the user to do the conversion. So let’s insert a Shape and fill it with color to make it appear as a button.

Let’s make it functional

The script provided here should be copied into a new module. Do not forget to save your workbook as macro enabled workbook file. The Sub “FindThis” should be attached to the button that we have just created.

Let’s test it

Add address along with other information into respective columns. Click the button to run the macro which would display lat and long coordinates for all address listed on the sheet. The macro will start at row 2 and will continue to run till it reaches an empty row.

How it works?

With the script we have created two functions. One for fetching Lat value and another for fetching Long value. Using a FOR loop, we are passing each address to these functions and displaying the result on screen.

Script

Function GETLAT(v_address As String, v_suburb As String, v_state As String, v_postcode As Long)
    
    Dim URl As String, lastRow As Long
    Dim xmlHttp As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
    
    URl = "https://maps.googleapis.com/maps/api/geocode/xml?address=" & Application.WorksheetFunction.Substitute(v_address, " ", "+") & Application.WorksheetFunction.Substitute(v_suburb, " ", "+") & Application.WorksheetFunction.Substitute(v_state, " ", "+") & Application.WorksheetFunction.Substitute(v_postcode, " ", "+") & ",Australia"
    
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
    xmlHttp.Open "GET", URl, False
    xmlHttp.setRequestHeader "Content-Type", "text/xml"
    xmlHttp.send
    
    Set html = CreateObject("htmlfile")
    html.body.innerhtml = xmlHttp.ResponseText
    v_string = html.body.innerhtml
    x = InStr(1, v_string, "<LAT>")
    If x <> 0 Then
        y = InStr(x + 5, v_string, "</LAT>")
        GETLAT = Mid(v_string, x + 5, y - (x + 5))
    End If
End Function

Function GETLNG(v_address As String, v_suburb As String, v_state As String, v_postcode As Long)
    
    Dim URl As String, lastRow As Long
    Dim xmlHttp As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
    
    URl = "https://maps.googleapis.com/maps/api/geocode/xml?address=" & Application.WorksheetFunction.Substitute(v_address, " ", "+") & Application.WorksheetFunction.Substitute(v_suburb, " ", "+") & Application.WorksheetFunction.Substitute(v_state, " ", "+") & Application.WorksheetFunction.Substitute(v_postcode, " ", "+") & ",Australia"
    
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
    xmlHttp.Open "GET", URl, False
    xmlHttp.setRequestHeader "Content-Type", "text/xml"
    xmlHttp.send
    
    Set html = CreateObject("htmlfile")
    html.body.innerhtml = xmlHttp.ResponseText
    v_string = html.body.innerhtml
    
    x = InStr(1, v_string, "<LNG>")
    If x <> 0 Then
        y = InStr(x + 5, v_string, "</LNG>")
        GETLNG = Mid(v_string, x + 5, y - (x + 5))
    End If
End Function

Sub FindThis()
    For r = 2 To 5
        Range("F" & r).Value = GETLAT(Range("A" & r).Value, Range("B" & r).Value, Range("C" & r).Value, Range("D" & r).Value)
        Range("G" & r).Value = GETLNG(Range("A" & r).Value, Range("B" & r).Value, Range("C" & r).Value, Range("D" & r).Value)
    Next r
End Sub

If you are not receiving proper results using the script, corrupted excel might be a probable reason. You can then use Excel file recovery tool such as DataNumen Excel Repair to fix Excel.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc problem and outlook recovery software products. For more information visit www.datanumen.com