https://www.exceldemy.com/learn-excel/geocoding/ I created a new module and used Tools/References to check Microsoft XML, 6.0. Then I pasted this code into the new module:
Function Co_Ordinates(address As String) As String
Application.Caller.Font.ColorIndex = xlNone
Dim xDoc As New MSXML2.DOMDocument
xDoc.async = False
xDoc.Load ("
https://nominatim.openstreetmap.org/search?format=xml&q=" + WorksheetFunction.EncodeURL(address))
If xDoc.parseError.ErrorCode 0 Then
Application.Caller.Font.ColorIndex = vbErr
Co_Ordinates = xDoc.parseError.reason
Else
xDoc.SetProperty "SelectionLanguage", "XPath"
Dim loc As MSXML2.IXMLDOMElement
Set loc = xDoc.SelectSingleNode("/searchresults/place")
If loc Is Nothing Then
Application.Caller.Font.ColorIndex = vbErr
NominatimGeocode = xDoc.XML
Else
Application.Caller.Font.ColorIndex = vbOK
Co_Ordinates = loc.getAttribute("lat") & "," & loc.getAttribute("lon")
End If
End If
End Function
But when I select the top line in the function and click the run arrow, I get a pop-up list of macros that does not include Co_Ordinates. The image below shows what I see. Also, if I use that function in a cell (=Co_Ordinates(K12)), I get a Name? error. What am I doing wrong?