Md. Araf Bin Jayed
Reverse geocoding means finding out the actual address or location from given latitude and longitude. This task can be quite hectic if you want to perform it manually. But with the help of Excel, you can perform reverse geocoding easily. In this article, I will show you, how to perform reverse geocoding in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
5 Easy Steps to Perform Reverse Geocoding in Excel
In this article, you will see five easy steps to perform reverse geocoding in Excel. To perform this task, I will need the help of VBA code to build a custom function for Excel. Then, by applying the custom function, I will find out the address from the latitude and longitude.
To illustrate my article further, I will use the following sample data set.
Step 1: Open Microsoft Visual Basic Application from Developer Tab
Firstly, to write a VBA code, you must open a module, as you cannot write it into a normal worksheet like other formulas. For that,
- First of all, go to the Developer tab of the ribbon
- Then from the Code group, select Visual Basic.
- Secondly, you will see the Visual Basic window.
- Afterward, from the Insert tab, choose Module.
- Consequently, a module will look like the following image after opening where you will be able to write the code.
Read More: How to Convert Latitude and Longitude to Address in Excel
Step 2: Select Necessary Reference from Tool Option
In the second step, you will need to activate a particular reference so that the custom function can perform accurately. To do that,
- Firstly, from the Tools tab of the VBA window, select References.
- Secondly, you will see a list of available references in the References – VBAProject dialog box.
- Then, from the list, select Microsoft XML, v3.0, and make sure the reference is marked.
- Lastly, press OK.
Read More: How to Create Latitude Longitude Converter in Excel (2 Examples)
Step 3: Use Code to Build Required Custom Function
Now for the third step, I will write the code that will create the custom function required for performing reverse geocoding. For that,
- First of all, copy the following code and paste it into the module from the first step.
- Here, I have named the custom functions as ReverseGecoder.
Option Explicit
Function ReverseGeocoder(lati As Double, longi As Double) As String
On Error GoTo 0
Dim xD As New MSXML2.DOMDocument
Dim URL As String, vbErr As String
xD.async = False
URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
"&lon=" + CStr(longi)
xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
"&lon=" + CStr(longi))
If xD.parseError.ErrorCode <> 0 Then
Application.Caller.Font.ColorIndex = vbErr
ReverseGeocoder = xD.parseError.reason
Else
xD.SetProperty "SelectionLanguage", "XPath"
Dim loca As MSXML2.IXMLDOMElement
Set loca = xD.SelectSingleNode(" / reversegeocode / result")
If loca Is Nothing Then
Application.Caller.Font.ColorIndex = vbErr
ReverseGeocoder = xD.XML
Else
Application.Caller.Font.ColorIndex = vbOK
ReverseGeocoder = loca.Text
End If
End If
Exit Function
0:
Debug.Print Err.Description
End Function
- Secondly, after pasting the code save it, and go back to the worksheet with the coordinates.
Read More: How to Convert Address to Lat Long in Excel (2 Easy Methods)
Step 4: Implement Custom Function
In the fourth step, I will implement the custom function from the previous procedure and see if it really shows the address for a particular coordinate. For doing that,
- Firstly, into your Excel sheet, type =REV in cell D5 and you will see the auto-suggestion of the custom function from the previous step.
- To select the function press Tab on the keyboard or double-click on the function using the mouse.
- Secondly, to implement the function type the following formula in cell D5.
=ReverseGeocoder(B5,C5)
- Finally, press Enter and see the desired location for the coordinates of cells B5 and C5.
Step 5: Show Final Result
This is the final step of this procedure. After completing all the previous steps, I will now show the final result or outcome. To do that,
- First of all, you will find the AutoFill feature in the following image if you take your cursor at the lower right corner of cell D5.
- After selecting it drag the feature to the lower to implement the formula in the lower cells as well.
- Finally, you will be able to see all the locations by using the custom functions which worked as a reverse geocoder.
Things to Remember
- If you don’t activate the reference before implementing the custom function, it won’t show the desired result.
- Your Excel file might act slower after implementing the function, so keep patience after using AutoFill.
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to perform reverse geocoding in Excel. Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.
