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.
Sub GetIP() Dim results() As String results = GetLatLonbyIP Debug.Print results(1) Debug.Print results(2) Debug.Print results(3) End Sub