Lecture Notes: Using Google Maps Directions API with VBA in Excel

Introduction

• Speaker: Ryan
• Topic: Integrating Google Maps Directions API with Visual Basic for Applications (VBA) to fetch travel time and distance into Microsoft Excel.
• Objective: Create a function in Excel for calculating travel time and distance between two points using Google Maps Directions API.

Prerequisites

• API Key: Needed to access Google Maps Directions API.
• VBA JSON Tool: Required to parse JSON data in VBA.
• Visual Basic for Applications (VBA): Basic knowledge required to code macros.

Setup and API Key

1. Obtain API Key:
   • Go to the Google Developers' "Get Started" page.
   • Create a new API key if you have an existing account.
   • Copy the API key for use in Excel functions.
2. Secure API Key:
   • Optional: Secure the API key (not covered in this demo as it will be deleted after).

Understanding API Response

• Test URL: Use URL provided by Google for initial tests.
• API Response: JSON format containing routes, legs (segments), distance (meters), and duration (seconds).
• Handling: Focus on first route and sum up legs if multiple.

VBA Setup

1. Download VBA JSON: Required for JSON parsing.
2. Visual Basic Editor:
   • Import JSONConverter.bas in VBA.
   • Create a new module named "Google Maps".

Creating Functions

Travel Time Function

1. Function Declaration:
   • Function TravelTime(origin As String, destination As String, apiKey As String) As Integer
2. URL Construction:
   • Concatenate origin, destination, and API key into the request URL.
3. Web Request Setup:
   • Add references: Microsoft Scripting Runtime and WinHTTP Services.
   • Create an HTTP request to fetch data.
4. Parse JSON Response:
   • Use VBA JSON to parse and extract travel time.
   • Sum duration values from legs.
5. Return Value:
   • Return calculated travel time in seconds.
6. Testing:
   • Test with addresses for U.S. Capitol and the White House.

Travel Distance Function

1. Function Creation:
   • Similar steps as Travel Time, but focusing on distance.
2. Adjustments:
   • Rename variables to meters.
   • Extract distance instead of duration.
3. Result:
   • Convert and display result in kilometers.

Troubleshooting

• Inaccuracy in Distance:
  • Discrepancies may occur due to routing differences (e.g., walking vs. driving routes).
• Common Errors:
  • Correct variable names and ensure correct array indexing.

Applications and Extensions

• Use Cases:
  • Useful for calculating travel metrics for lists of addresses.
  • Potentially integrate with web-imported JSON data for project automation.

Additional Resources

• Written Tutorial: Full source code available on Ryan's website.
• Further Learning: Tutorial on importing JSON data available.

Conclusion

• The tutorial demonstrated how to use the Google Maps Directions API with VBA for practical use in Excel.
• Encouragement to explore further applications and projects using these methods.