Geocode script doesn’t work – The A.CRE Geocoding Add-in for Excel, nor the text-based module (VBA code) works.

The A.CRE Geocoding Add-in, as well as the manual VBA code for auto-populating latitude and longitude, are used successfully and without issue by thousands of people every day. Nevertheless, due to fact that the user must create a connection between the add-in/code, and Google’s Geocoding API, users who are unfamiliar with the API often run into technical issues. We have put together a list of common solutions to help:

Troubleshooting the A.CRE Geocoding Add-in:

1. Receiving a ‘REQUEST_DENIED’ error in Excel when attempting to use the add-in? This is due to an issue with your Google API key. It’s similar to a ‘incorrect password’ error when attempting to login to a website. Effectively, Google doesn’t recognize the API key or the key was not setup correctly. Return to the ‘Using the A.CRE Geocoding Add-in’ section above, paying special attention to the Google instructional videos in items 1 through 5.
2. Are cells returning blank when using any of the A.CRE Geocoding Add-in functions? This occurs when the add-in has been disabled. To enable the add-in, go to the ‘GeoCode’ ribbon and click the ‘Addin Status’ button. Then click ‘Enable’.
3. API Key not working? This is almost always because the key was not setup properly, and billing details provided to Google. Find instructions for getting a Google Geocoding API Key here.

Troubleshooting the GOOGLE GEOCODING CUSTOM FUNCTION VIA VBA

1. Failure to Enable XML v3.0 library. In order to use this function you must enable the XML, v3.0 library from VBA editor. To do this, ‘Go to Tools -> References -> check the Microsoft XML, v3.0.
2. You haven’t inserted a proper API Key into the code. As of 2018, the code requires you to insert your own Google Maps Geocoding API key. And as of 2019, it’s necessary to attach billing information to your Google account and link that to your Google project. To get a free API key from Google, use this quick start guide provided by Google.
3. Getting a #VALUE, #NAME, or ‘Server denied the request’ errors. There are a handful of reasons for these errors but generally these errors are because either the function wasn’t setup correctly or the Google API key wasn’t setup correctly. My best advice if you’re struggling with one of these error is to:
   1. Follow the instructions to the T. I know that’s easier said than done, but if you’re not saving the file as a Macro-Enabled file, not turning on Microsoft XML 3.0, entering the API key in the wrong place, inadvertently deleting something from the VBA code, or not entering the address correctly, etc, than you will get an error.
   2. Make sure you’re running the most recent version of Excel. This code may work with older version of Excel, but I have not tested it on versions earlier than 2016.
   3. Make sure you have billing details on file with Google in order for your API key to work. While Google gives you a certain number of API requests for free, Google requires users have a credit card on file to avoid abuse of the API.
   4. Also here’s a quick tip. If you use the GETCOORDINATES() function, the VBA code has a few built-in custom error messages that will help you troubleshoot what you’re doing wrong. For instance, if you haven’t setup your API key correctly, the cell will output: “Invalid API Key”.