GeoIP API returns latitude and longitude based on IP

GeoIP is another service that returns latitude and longitude, based on IP address. If you know the IP address you want to lookup, you can pass it to the service and it will return the latitude and longitude of that IP address.

You can also call the service without an IP address and it will return the latitude and longitude of the current computer.

A service like this would be useful for VBA programs that use the current computer's IP address to determine location and customize content for that user. For example, you could

show the end user their IP address
use another lookup service (using lat and lon) to specify the current user's location and weather or traffic conditions
pre-populate and validate fill-in forms that require location information
show customized driving directions

All without having to write custom code for each user!

Sample XML Response

Calling the API produces the following XML response:

<?xml version="1.0" encoding="UTF-8" ?>
  <items>
    <ip>
      nn.nnn.nn.nn
    </ip>
    <timestamp>
      1234657898765
    </timestamp>
    <location>
      <coords>
        <latitude>
          42.6105
        </latitude>
        <longitude>
          -71.2306
        </longitude>
      </coords>
      <address>
        <city>
          Tewksbury
        </city>
        <country>
          United States
        </country>
        <country_code>
          US
        </country_code>
      </address>
      <gmtOffset>
        -5
      </gmtOffset>
      <dstOffset>
        -4
      </dstOffset>
    </location>
  </items>

Function to return latitude and longitude

Following is the function to return latitude and longitude based on IP. It accepts one (optional) parameter: the IP address you want to check. If no IP address is specified, the IP address of the computer running the code is used.

Function GetLatLonbyIP(Optional ipaddress As String) As String()
' returns IP, latitude and longitude for a given IP address
' returns latitude and longitude for current IP address if not specified

  Dim url As String
  Dim xml As Object  ' MSXML2.XMLHTTP60
  Dim xmlDoc As Object  ' MSXML2.DOMDocument60
  Dim items As Object  ' MSXML2.IXMLDOMNode
  Dim ip As Object  ' MSXML2.IXMLDOMNode
  Dim location As Object  ' MSXML2.IXMLDOMNode
  Dim coords As Object  ' MSXML2.IXMLDOMNode
  Dim lat As Object  ' MSXML2.IXMLDOMNode
  Dim lon As Object  ' MSXML2.IXMLDOMNode
  Dim latlon(1 To 3) As String

  Const BASE_URL As String = "http://geoip.prototypeapp.com/api/locate?format=xml"

  Set xml = GetMSXML
  Set xmlDoc = GetDomDoc
  If xml Is Nothing Then Exit Function
  If xmlDoc Is Nothing Then Exit Function

  ' build URL depending on whether IP was specified
  If Len(ipaddress) = 0 Then
    url = BASE_URL
  Else  ' add IP address
    url = BASE_URL & "&ip=" & ipaddress
  End If

  With xml
    .Open "GET", url
    .send
  End With

  ' load file into document object
  xmlDoc.loadXML xml.responseText

  ' walk the node hierarchy
  Set items = GetNode(xmlDoc, 2)
  ' first subnode contains IP address
  Set ip = GetNode(items, 1)
  ' location node contains lat and lon
  Set location = GetNode(items, 3)
  Set coords = GetNode(location, 1)
  Set lat = GetNode(coords, 1)
  Set lon = GetNode(coords, 2)

  latlon(1) = ip.nodeTypedValue
  latlon(2) = lat.nodeTypedValue
  latlon(3) = lon.nodeTypedValue

  GetLatLonbyIP = latlon

End Function

The GetMSXML, GetNode and GetDomDoc functions may be found at MSXML Object Library Routines.

Note that there is no validation of the IP address and no caching of the web response. I did not bother to test for errors so I have no idea what would happen if you passed a malformed IP address to the API. The code simply assumes that you are using a valid IP. There is no caching of the response, so if you pass the same IP over and over the function dutifully calls the API repeatedly.

I did not bother parsing any of the other return values such as timezone, but I could see how that could be useful. If you distribute workbooks globally, you could display the current local time for any given user and also account for Daylight Savings Time (DST), or use local time for logging purposes.

Sample Usage

Sub GetIP()

  Dim results() As String

  results = GetLatLonbyIP

  Debug.Print results(1)
  Debug.Print results(2)
  Debug.Print results(3)

End Sub