GPS co-ordinates VBA Code

DHayes

Board Regular
Joined
Nov 12, 2014
Messages
244
Good day,
I managed to get the following VBA below a while back from somewhere and it was working and now all of sudden I am getting an error and can't seem to find a solution. Hope fully someone can assist.

The error I am getting is
Compile error:
User-define type not defined
The part highlighted in red seems to be the problem

Code:
Function MyGeocode(address As String) As String  Dim strAddress As String
  Dim strQuery As String
  Dim strLatitude As String
  Dim strLongitude As String
  strAddress = URLEncode(address)
  'Assemble the query string
  strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
  strQuery = strQuery & "address=" & strAddress
  strQuery = strQuery & "&sensor=false"
  'define XML and HTTP components
[COLOR=#ff0000]  Dim googleResult As New MSXML2.DOMDocument[/COLOR]
[COLOR=#ff0000]  Dim googleService As New MSXML2.XMLHTTP[/COLOR]
  Dim oNodes As MSXML2.IXMLDOMNodeList
  Dim oNode As MSXML2.IXMLDOMNode
  'create HTTP request to query URL - make sure to have
  'that last "False" there for synchronous operation
  googleService.Open "GET", strQuery, False
  googleService.send
  googleResult.LoadXML (googleService.responseText)
  Set oNodes = googleResult.getElementsByTagName("geometry")
  If oNodes.Length = 1 Then
    For Each oNode In oNodes
      strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
      strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
      MyGeocode = strLatitude & "," & strLongitude
    Next oNode
  Else
    MyGeocode = "Not Found (try again, you may have done too many too fast)"
  End If
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
No idea if this is the issue, but do you have the Microsoft XML reference added? You can check by going to visual basic (ALT+F11 from excel), Tools, References, and then ensuring that either Microsoft XML, v3.0 or v6.0 is checked.

According to this post, v6.0 is the recommended option by Microsoft.
 
Upvote 0
thank you for your assistance. enabled the XML v6.0 and is working again. Appreciated:)
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top