Analyze Google API for latitude & longitude of adress - Regex.pattern issue

Hi all!

I'm currently stuck with this Google API Query.
As you may know you can get information about longitude, latitude etc. via Google API. This is done via this web query: "https://maps.googleapis.com/maps/api/geocode/json?address=NewYorkUSA" (example for New York).

But in my excel sheet I have many other cities left, therefore I'd like to do this automatically. It actually works nearly perfect with a slight modification of this guys' code: http://analystcave.com/excel-calcula...een-addresses/
And yes, I have seen his solution to getting the longitudes and latitudes. But that is written as a SUB and not as a function. Unfortunately I need this as a function for my specific usage :/


My modified code:

'Latitude via Google Maps
Public Function GetLatitude(start As String)
    Dim firstVal As String
    firstVal = "https://maps.googleapis.com/maps/api/geocode/json?address="
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+")
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """location"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """lat"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetLatitude = CDbl(tmpVal)
    Exit Function
ErrorHandl:
    GetLatitude = 0
End Function

But if I then start this function as following, the value of the function is "40".
But the difference of one degree of latitude equals 110km, while I need an exact analysis of all digits.

City	Latitude
New York	=GetLatitude(A2)=40

Therefore I fooled a bit around with following passage:

Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """lat"".*?([0-9]+)": regex.Global = False

In my understanding the issue here is the search pattern which only gives whole numbers as output, therefore I changed it to:

Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """lat"".*?([0.-9]+)": regex.Global = False

I even checked it with a regex-analyzer and it worked. Unfortunately not with Excel 2007.


Has someone maybe an idea, how Excel could deliver the output with the whole decimal number?

Many thanks!!

Best Regards,
Lasse