Excel VBA调用Google Directions API计算距离报#Value错误咨询

TRAVELDISTANCE返回#VALUE错误和地址格式、URL编码无关，核心原因是VBA变量类型溢出：

• VBA中Integer为16位有符号整数，可存储的数值上限仅为32767
• 你写的TRAVELTIME函数用Integer类型存储时长秒数：32767秒约合9.1小时，日常出行场景极少超过这个时长阈值，因此大部分场景不会触发错误，运行正常
• 你写的TRAVELDISTANCE函数用Integer类型存储距离米数：32767米仅为32.7公里，当输入带城市名的完整精准地址后，API返回的实际路线距离只要超过32.7公里，给meters变量赋值时就会触发溢出错误，直接返回#VALUE；仅输入门牌号这类模糊地址时，API会匹配距离最近的同名地址，返回的距离很短不会触发溢出，因此看起来"运行正常"
  你之前尝试替换地址中的空格、逗号属于方向错误的排查，自然无法解决问题。

1. 先解决溢出问题 #

将两个函数中存储数值的变量类型从Integer修改为Long（32位长整数，数值上限为2147483647，可支持存储200余万公里的距离、68年的秒数，完全覆盖所有出行计算场景），对应修改的代码片段：

' TRAVELTIME函数中修改这行
Dim seconds As Long

' TRAVELDISTANCE函数中修改这行
Dim meters As Long

改完这一步，带城市名的地址计算就会恢复正常。

2. 补全正确的URL编码逻辑 #

不要手动写规则替换特殊字符，直接调用VBA内置的EncodeURL函数处理起止地址参数，避免地址中包含的&、重音字符、中文标点、空格等特殊字符导致API请求解析异常，URL拼接部分统一修改为：

strUrl = "https://maps.googleapis.com/maps/api/directions/json?origin=" & Application.EncodeURL(origin) & "&destination=" & Application.EncodeURL(destination) & "&key=" & apikey

3. 合并重复逻辑，节省API配额 #

当前两个函数逻辑几乎完全一致，计算同一对起止点的时间和距离会发起两次HTTP请求，既浪费API的免费配额，计算速度也慢，可以抽离公共的请求逻辑，一次请求同时拿到时长和距离数据，优化后的完整代码如下：

' 内部公共方法，发起Directions API请求并返回解析后的结果
Private Function GetDirectionsResp(origin As String, destination As String, apikey As String) As Dictionary
    Dim strUrl As String, httpReq As Object
    strUrl = "https://maps.googleapis.com/maps/api/directions/json?origin=" & Application.EncodeURL(origin) & "&destination=" & Application.EncodeURL(destination) & "&key=" & apikey
    
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    With httpReq
        .Open "GET", strUrl, False
        .Send
    End With
    
    Set GetDirectionsResp = JsonConverter.ParseJson(httpReq.ResponseText)
End Function

' 自定义函数：返回两点间出行时长，单位为秒
Function TRAVELTIME(origin, destination, apikey) As Long
    Dim parsed As Dictionary, leg As Dictionary, totalSec As Long
    Set parsed = GetDirectionsResp(origin, destination, apikey)
    
    For Each leg In parsed("routes")(1)("legs")
        totalSec = totalSec + leg("duration")("value")
    Next leg
    TRAVELTIME = totalSec
End Function

' 自定义函数：返回两点间出行距离，单位为米
Function TRAVELDISTANCE(origin, destination, apikey) As Long
    Dim parsed As Dictionary, leg As Dictionary, totalMeter As Long
    Set parsed = GetDirectionsResp(origin, destination, apikey)
    
    For Each leg In parsed("routes")(1)("legs")
        totalMeter = totalMeter + leg("distance")("value")
    Next leg
    TRAVELDISTANCE = totalMeter
End Function

4. 可选优化：增加错误兜底 #

可以在函数中增加API返回状态判断，当API返回密钥错误、无可用路线、配额超限等异常时，返回明确的提示文本，而不是直接抛出#VALUE错误，方便快速定位问题。

内容的提问来源于stack exchange，提问作者SanderVeeken