Masters of VBA - Reverse Geocoding - how to make it work?

[hehanhan]

Hello masters of VBA,

I found this code in https://usefulgyaan.wordpress.com/2015/07/05/reverse-geocoding/

Function GEOAddress(dblLatitude As Double, dblLongitude) As String
         
    Dim strJSON         As String
    Dim strAddress      As String
    Dim lngTemp         As Long
    Dim objXml          As Object
    Dim strUrl          As String
        
    strUrl = "http://maps.googleapis.com/maps/api/geocode/json?latlng=" & dblLatitude & "," & dblLongitude & "&sensor=false"
    Set objXml = CreateObject("Microsoft.XMLHTTP")
    With objXml
        .Open "GET", strUrl, False
        .send
        strJSON = .responseText
    End With
    Set objXml = Nothing
        
    lngTemp = InStr(1, strJSON, "formatted_address")
    strAddress = Mid(strJSON, lngTemp + 22, InStr(lngTemp, strJSON, """,") - (lngTemp + 22))
    GEOAddress = strAddress
    
End Function

I suppose to let you reverse coordinates into close by addresses. I had done something to put address into coordinates before. I understand to click XML6.0 reference and such. But the code in the site just don't work for me no matter what I do. I looked at few posts here, it did not help any way.

Can someone please point it out for me? What I really wish to do is that have latitude and longitude expressed as "43.840376, -79.037738" in Cell A1 and A2, then return me address in A3

Is there any way? is it not possible? I am sorry if I am asking stupid questions here, as I don't have whole a lot of VBA knowledge. Most of them I have learned from this SITE. Thank you so much for reading!

 

[mrmmickle1]

Use this UDF I created from scraps of code across the internet.

I ran across a need for this last month....It will only work for the first 1000 results as that is what Google allows through their API.

Excel 2010
	A	B
1	Address	Coordinates
2	1225 Permanente Creek Trail, Mountain View, CA 94043, USA	37.4238253802915,-122.085598880291
3	Strada Radu Voda 8, Bucure?ti, Romania	44.42514,26.10540
4	20 Queens Quay E, Toronto, ON M5E, Canada	43.6419646,-79.3746487
5	141 W 41st St, New York, NY 10036, USA	40.7547747,-73.985898
6	8340 N Community House Rd, Charlotte, NC 28277, USA	35.0555481,-80.8383825
7	5500-5540 N River Rd, Rosemont, IL 60018, USA	41.9788038,-87.8624106
8	6801 Franklin Ave, New Orleans, LA 70122, USA	30.030512,-90.04931
9	Japan, ?559-0034 Osaka-fu, Osaka-shi, Suminoe-ku, Nankokita, 1 Chome-4 ????????????	34.6381228,135.4253848
10	299-1 Bangi-dong, Songpa-gu, Seoul, South Korea	37.5198209,127.1227901
11	Soi Mit Nawi, Tambon Bang Rak Phatthana, Amphoe Bang Bua Thong, Chang Wat Nonthaburi 11140, Thailand	13.8973058,100.3910926
12	Tsar'-Pushka, Moskva, Russia, 119072	55.7512419,37.6184217
13	ArenA Boulevard 590, 1101 DS Amsterdam-Zuidoost, Netherlands	52.3123574,4.9441509
Sheet1

Cell Formulas
Range	Formula
A2	=ReverseGeoCode(B2)

I just tested it...it is still functioning:

[COLOR=#0000ff]Function[/COLOR] ReverseGeoCode(myInput [COLOR=#0000ff]As String[/COLOR]) [COLOR=#0000ff]As String

[/COLOR][COLOR=#008000]'You will need to reference Microsoft XML, v6.0 object library[/COLOR]


    [COLOR=#0000ff]Dim[/COLOR] XMLDoc       [COLOR=#0000ff] As New[/COLOR] DOMDocument
[COLOR=#0000ff]    Dim [/COLOR]XMLNode       [COLOR=#0000ff]As [/COLOR]IXMLDOMNode
[COLOR=#0000ff]    Dim[/COLOR] i           [COLOR=#0000ff]  As Long[/COLOR]
 [COLOR=#0000ff]   Dim [/COLOR]lat        [COLOR=#0000ff]   As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] lng           [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]myAddress     [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    
[/COLOR]
    lat = Split(myInput, ",")(0)
    lng = Split(myInput, ",")(1)
    
    XMLDoc.Load "http://maps.googleapis.com/maps/api/geocode/xml?latlng=" & lat & "," & lng & " &sensor=false"


[COLOR=#0000ff]    Do Until [/COLOR]XMLDoc.readyState = 4
        DoEvents
[COLOR=#0000ff]    Loop[/COLOR]


   [COLOR=#0000ff] If [/COLOR]Len(XMLDoc.Text) = 0 Then
        Call MsgBox("No data!")
[COLOR=#0000ff]        Exit Function[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]


 [COLOR=#0000ff]   Set[/COLOR] XMLNode = XMLDoc.SelectSingleNode("/GeocodeResponse/result/formatted_address")


[COLOR=#0000ff]    For[/COLOR] i = 0 [COLOR=#0000ff]To[/COLOR] XMLNode.ChildNodes.Length - 1
        myAddress = XMLNode.ChildNodes(i).Text
[COLOR=#0000ff]    Next[/COLOR] i


    ReverseGeoCode = myAddress


[COLOR=#0000ff]End Function[/COLOR]

 

[hehanhan]

Thank you very much Sir for helping me out!! This works!!

 

[mrmmickle1]

Glad you were able to find a use for this. I've been saving it. I knew it would come in handy