Excel VBA + Google Map 計算兩地點距離

使用 Excel VBA + Google Map API 來自動來計算兩個地點的距離、油錢、時間與最佳路徑

介紹

使用 Excel VBA 並利用 Google Map API 取得兩地點之間移動的最佳路徑資訊，並用網址連結來產生一個可以點擊該網址來顯示所規劃出來的最佳路徑（時間最短）。

以下是詳細的步驟與範例程式碼，說明如何在 Excel 中以 VBA 搭配 Google Maps 的服務（例如「Distance Matrix API」或「Directions API」）來取得兩個地址間的距離（公里數）。請注意，需要具備以下先決條件：

1. Google Maps API 金鑰：必須註冊 Google Cloud Platform，啟用 Maps API（如 Directions API 或 Distance Matrix API），並取得 API 金鑰。
2. 可用於 VBA 的網路存取：確保電腦能從 VBA 端透過網路存取 Google API。

前置準備

1. 取得 Google API 金鑰：

• 前往 Google Cloud Platform 開啟專案。
• 啟用「Directions API」或「Distance Matrix API」。
• 建立 API Key，並將限制套用於特定 API。假設取得的 API Key 為 YOUR_API_KEY。（請務必用自己的 Key 替換）

2. 地點放置位置：

• A2 儲存 起點地址（例如：高雄85大樓 地址）
• B2 儲存 終點地址（例如：台北101 地址）

使用 Directions API （方法）

Directions API 回傳的 JSON 範例

呼叫 URL 範例（請自行替換地址與金鑰）：

https://maps.googleapis.com/maps/api/directions/json?origin=【A地址】&destination=【B地址】&key=YOUR_API_KEY

此 API 會回傳一個 JSON 結構，其中會有 legs -> distance -> value （公尺為單位），以及 text（如 "351 km"）等資訊。可以解析 JSON 以取得數值。

解析 JSON 的方法

在 VBA 中解析 JSON 有幾種方式：

• 引用第三方的 JSON 解析庫（如使用「JsonConverter.bas」）
• 自己以字串搜尋方式取得關鍵資訊（可行但不建議）

以下範例將示範使用 JsonConverter（JsonConverter Github專案）解析回傳結果。請先下載 JsonConverter.bas 並匯入至 VBA 專案。

將 JSON 解析工具 匯入 Excel

1. 在 Excel 中按下 Alt+F11 開啟 VBA 編輯器。

2. 在 VBA 編輯器中，選擇 File -> Import File...，將先前下載的 JsonConverter.bas 匯入。

3. 在 VBA 編輯器中，於「工具 (Tools)」->「參考 (References)」中，勾選 Microsoft Scripting Runtime。(因為 JsonConverter 需要使用 Scripting.Dictionary)

Google Map API 的注意事項

• API 用量限制：
  請注意 Google Maps Platform 的 API 使用可能需收費，有每日免費額度與限制，詳情請參閱 Google 官方說明。
• 地址格式：
  為確保地址順利解析**，請放入盡可能精準與標準化的地址，或直接使用經緯度座標（如：25.0339639,121.5644722）。
• Directions API / Distance Matrix API 的差異：
  本程式使用 Directions API。Distance Matrix API 也可類似使用，其結構稍有不同，但用法近似。請參考官方文件調整解析方式。Distance Matrix API 的 URL 類似：
  https://maps.googleapis.com/maps/api/distancematrix/json?origins=【A地址】&destinations=【B地址】&key=YOUR_API_KEY
• 若使用 Distance Matrix API，取得距離值的解析路徑略有不同 (在 rows → elements → distance)，請根據返回的 JSON 格式來解析。

功能設計說明

1. 輸入 起（origin）、迄（destination）、油耗、出發日期時間、並利用下拉選單來挑選交通工具
2. 交通工具對應：

• 徒步：walking
• 腳踏車：bicycling
• 機車：driving (+ avoid=highways)
  Google 官方：並未在台灣公開「Motorcycle」模式，不過在某些地區 (如部分亞洲國家/地區) 有局部測試。如果在該地區使用，也許可直接指定 mode=motorcycle；但在多數情況下並不可行。
  
  近似做法：「機車」實際上跟汽車一樣是使用 mode=driving，但額外指定 avoid=highways，模擬機車無法進入高速公路或快速道路的狀況。若起點/終點有包含高速公路，則可產生明顯不同的路線。
  
  並非完美：若 Google 本身沒有遵守「avoid=highways」或該路線區域高速公路是唯一通道，可能仍出現相同路線或無法找到可行路線。
• 汽車：driving
• 公車與火車：transit（火車增加 transitMode = "rail"，但網頁連結無法顯示細分模式）
• 飛機與郵輪：不支援

3. 結果顯示：

• 平常日/假日、尖峰時間/離峰時間、距離、油錢、時間
• 最佳路徑連結

程式碼 (共分為兩個函數：主函數 和 最佳路徑函數)：

1. 主函數：GetDistanceTimeAndCost

Option Explicit

Function GetDistanceTimeAndCost(origin As String, _
																		destination As String, _
																		unit As String, _
																		fuelType As String, _
																		fuelEfficiency As Double, _
																		travelDatetime As Date, _
																		vehicleType As String) As Variant			
	Dim apiKey As String
	apiKey = "YOUR_API_KEY"
	
	'------------------------------------
	' (1) 依據使用者的交通工具，轉成 Directions API mode + 顯示用文字 + 可能的額外參數
	'------------------------------------
	Dim mode As String ' 給 Google Directions API 用
	Dim modeDisplay As String ' 在最終結果顯示用
	Dim transitMode As String ' 大眾運輸子模式 (bus/rail)
	transitMode = ""

	' 透過一個變數來裝可能用於避免高速公路/收費道路等參數
	Dim avoidParam As String
	avoidParam = "" ' 預設不避免任何道路

	Select Case LCase(vehicleType)
	Case "徒步", "foot", "walk"
		mode = "walking"
		modeDisplay = "走路"

	Case "腳踏車", "bicycle", "bike"
		mode = "bicycling"
		modeDisplay = "腳踏車"

	Case "汽車", "car"
		' 直接用 driving
		mode = "driving"
		modeDisplay = "開車"
		
	Case "機車", "motorcycle"
		' 同樣用 driving，但盡量避開高速公路，以模擬機車
		mode = "driving"
		modeDisplay = "機車"
		avoidParam = "highways"
		
	Case "公車", "bus"
		mode = "transit"
		transitMode = "bus"
		modeDisplay = "大眾運輸"

	Case "火車", "train"
		mode = "transit"
		transitMode = "rail"
		modeDisplay = "大眾運輸"

	Case "飛機", "plane", "郵輪", "cruise", "輪船"
		' 飛機、郵輪不支援
		GetDistanceTimeAndCost = "時間無法取得(「" & vehicleType & "」模式不支援)"
		Exit Function
		
	Case Else
		' 不認得的交通工具，預設 driving
		mode = "driving"
		modeDisplay = "開車"
	End Select

	'------------------------------------
	' (2) 判斷平日/假日 + 尖峰/離峰
	'------------------------------------
	Dim wkDay As VbDayOfWeek
	wkDay = Weekday(travelDatetime, vbUseSystemDayOfWeek)

	Dim dayType As String
	If wkDay = vbSaturday Or wkDay = vbSunday Then
		dayType = "假日"
	Else
		dayType = "平常日"
	End If

	Dim hourVal As Integer
	hourVal = Hour(travelDatetime)

	Dim timeType As String
	If (hourVal >= 7 And hourVal < 9) Or (hourVal >= 17 And hourVal < 19) Then
		timeType = "尖峰時間"
	Else
		timeType = "離峰時間"
	End If

	' departure_time (Unix time)
	Dim departureTimeUnix As Long
	departureTimeUnix = CLng((travelDatetime - #1/1/1970#) * 86400)

	'------------------------------------
	' (3) 油價、油錢相關
	'------------------------------------
	Dim fuelPrice As Double
	Select Case LCase(fuelType)
		Case "92"
			fuelPrice = 28
		Case "95"
			fuelPrice = 30
		Case "98"
			fuelPrice = 32
		Case Else
			fuelPrice = 30 ' 預設95
	End Select

	'------------------------------------
	' (4) 組合 API URL
	'------------------------------------
	Dim originForApi As String, destinationForApi As String
	originForApi = Replace(origin, " ", "+")
	destinationForApi = Replace(destination, " ", "+")

	' 若兩地相同
	If LCase(originForApi) = LCase(destinationForApi) Then
		GetDistanceTimeAndCost = "[交通工具：" & modeDisplay & "]" & vbCrLf & _
																"平日假日判定：" & dayType & "，" & timeType & vbCrLf & _
																"距離: 0 " & unit & ", 油錢: 0元, 時間: 0小時0分鐘0秒"
		Exit Function
	End If

	Dim url As String
	url = "https://maps.googleapis.com/maps/api/directions/json?" & _
					"origin=" & originForApi & _
					"&destination=" & destinationForApi & _
					"&mode=" & mode & _
					"&key=" & apiKey & _
					"&region=tw&language=zh-TW"

	' 若機車 (mode=driving) 需要避開高速公路
	If avoidParam <> "" Then
		url = url & "&avoid=" & avoidParam
	End If

	' 若為未來時間，且 mode = driving 或 transit 時，帶入 departure_time
	If (mode = "driving" Or mode = "transit") And (travelDatetime > Now) Then
		url = url & "&departure_time=" & departureTimeUnix
		If mode = "driving" Then
			url = url & "&traffic_model=best_guess"
		End If
	End If

	' 如果是大眾運輸且有子模式 (bus/rail)
	If mode = "transit" And transitMode <> "" Then
		url = url & "&transit_mode=" & transitMode
	End If

	'------------------------------------
	' (5) 呼叫 Directions API, 解析結果
	'------------------------------------
	On Error GoTo ErrHandler
	Dim http As Object
	Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
	http.Open "GET", url, False
	http.Send

	If http.Status = 200 Then
		Dim jsonResponse As Object
		Set jsonResponse = JsonConverter.ParseJson(http.ResponseText)

		Dim apiStatus As String
		apiStatus = jsonResponse("status")

		If apiStatus = "OK" Then
			If jsonResponse("routes").Count > 0 Then
				Dim firstRoute As Object, firstLeg As Object
				Set firstRoute = jsonResponse("routes")(1)

				If firstRoute("legs").Count > 0 Then
					Set firstLeg = firstRoute("legs")(1)
					
					' 距離 (公尺)
					Dim distanceValue As Double
					distanceValue = firstLeg("distance")("value") / 1000#
					
					' 時間 (秒)
					Dim durationSecs As Long
					' 若帶了 departure_time, driving 或 transit 可能有 duration_in_traffic
					If (mode = "driving" Or mode = "transit") And firstLeg.Exists("duration_in_traffic") Then
						durationSecs = firstLeg("duration_in_traffic")("value")
					Else
						durationSecs = firstLeg("duration")("value")
					End If
					
					' 轉為 X小時X分鐘X秒
					Dim h As Long, m As Long, s As Long
					h = durationSecs \ 3600
					m = (durationSecs Mod 3600) \ 60
					s = durationSecs Mod 60
					
					' 單位 (km 或 mi)
					Dim distanceDisplay As Double
					Select Case LCase(unit)
						Case "km"
							distanceDisplay = Round(distanceValue, 2)
						Case "mi"
							distanceDisplay = Round(distanceValue * 0.621371, 2)
						Case Else
							distanceDisplay = Round(distanceValue, 2)
							unit = "km"
					End Select
					
					' (5.1) 計算油錢
					Dim litersNeeded As Double, totalCost As Double
					If distanceValue > 0 And fuelEfficiency > 0 Then
						litersNeeded = distanceValue / fuelEfficiency
						totalCost = Round(litersNeeded * fuelPrice, 0)
					Else
						totalCost = 0
					End If
					
					' (5.2) 若是「徒步(走路)」或「腳踏車」，將油錢視為0
					If (modeDisplay = "走路" Or modeDisplay = "腳踏車") Then
						totalCost = 0
					End If
					
					' 回傳
					GetDistanceTimeAndCost = "[交通工具：" & modeDisplay & "]" & vbCrLf & _
																			"平日假日判定：" & dayType & "，" & timeType & vbCrLf & _
																			"距離: " & distanceDisplay & " " & unit & _
																			", 油錢: " & totalCost & "元, " & _
																			"時間: " & h & "小時" & m & "分鐘" & s & "秒"
					Exit Function
				End If
			End If
			
			' 沒有 routes/legs
			GetDistanceTimeAndCost = "距離/時間無法取得(無可用路線)"
			Exit Function
			
		ElseIf apiStatus = "ZERO_RESULTS" Then
			GetDistanceTimeAndCost = "距離/時間無法取得(無法找到可行路線)"
			Exit Function
			
		Else
			Dim errMsg As String
			If jsonResponse.Exists("error_message") Then
				errMsg = jsonResponse("error_message")
			Else
				errMsg = "API狀態: " & apiStatus
			End If
			GetDistanceTimeAndCost = "距離/時間無法取得(" & errMsg & ")"
			Exit Function
		End If
	End If

	' 如果 HTTP 狀態非 200
	GetDistanceTimeAndCost = "距離/時間無法取得(HTTP回應非200)"
	Exit Function
	
ErrHandler:
	GetDistanceTimeAndCost = "距離/時間無法取得(發生錯誤)"
End Function

2. 最佳路徑函數：GetBestRouteLink

Function GetBestRouteLink(origin As String, destination As String, vehicleType As String) As String
	Dim mode As String
	Dim avoidParam As String
	avoidParam = ""
	
	Select Case LCase(vehicleType)
		Case "徒步", "foot", "walk"
			mode = "walking"
		Case "腳踏車", "bicycle", "bike"
			mode = "bicycling"
		Case "汽車", "car"
			mode = "driving"
		Case "機車", "motorcycle"
			mode = "driving"
			avoidParam = "highways"
		Case "公車", "bus", "火車", "train"
			mode = "transit"
		Case "飛機", "plane", "郵輪", "cruise", "輪船"
			GetBestRouteLink = "此交通工具不支援取得路線"
			Exit Function
		Case Else
			mode = "driving"
	End Select

	' 組合 Google Maps 網址
	Dim originForLink As String, destinationForLink As String
	originForLink = Replace(origin, " ", "+")
	destinationForLink = Replace(destination, " ", "+")

	If LCase(originForLink) = LCase(destinationForLink) Then
		GetBestRouteLink = "無(起終點相同)"
	Else
		Dim linkUrl As String
		linkUrl = "https://www.google.com/maps/dir/?" & _
								"api=1&region=tw&origin=" & originForLink & _
								"&destination=" & destinationForLink & _
								"&travelmode=" & mode
								
		If avoidParam <> "" Then
			' Google Maps 網址中允許帶 "&avoid=highways" 或 "&avoid=tolls"
			linkUrl = linkUrl & "&avoid=" & avoidParam
		End If
		
		GetBestRouteLink = linkUrl
	End If
End Function

使用方式

使用方式，假設：
A2：起點地址（例如：「高雄市苓雅區自強三路5號」）
B2：終點地址（例如：「台北市信義區信義路五段7號」）
C2：油耗（燃油效率 km/L，即車輛每公升可行駛的公里數，例如：12）
D2：出發日期時間（例如：2024/12/31 08:00:00）
時間若已過去，因 Google 不提供對過去時段的交通壅塞預測，只會得到一般的
duration；
若想要 duration_in_traffic，請輸入【未來時間】。
E2：交通工具（例如：「汽車」）

在 F2 輸入：

=GetDistanceTimeAndCost(A2, B2, "km", "95", C2, D2, E2)

其中，
fuelType：油品種類 ("92"、"95"、"98")。
kilometer/mile：輸入 "km" ，可取得公里數；輸入 "mi" ，可取得英里數。

執行後的結果中將顯示：
平常日(週一 ~ 週五) 或 假日(週六、週日)、尖峰或離峰、距離、油錢、時間。

F2 公式

在 G2 輸入：

=IF(LEFT(GetBestRouteLink(A2, B2, E2), 5)="https", HYPERLINK(GetBestRouteLink(A2, B2, E2), "點此查看最佳路徑"), GetDistanceTimeAndCost(A2, B2, "km", "95", C2, D2, E2))

自動產生一條可以點擊的 Google Maps 路徑規劃連結（顯示該交通模式下的最佳路徑，即時間最短路徑），點擊此連結，將在瀏覽器中打開 Google Maps 並顯示建議的最佳路線。

G2 公式

嚴重警語：

API KEY 請妥善保存 ! 勿外流 ~