Excel VBA调用Bing API获取坐标如何在偏移单元格输出结果

你需要通过Application.Caller获取自定义函数被调用的单元格位置，即可实现结果写入相邻单元格的需求，修正后的代码如下：

Option Explicit
' 配置Bing地图API参数
Const bingURL As String = "http://dev.virtualearth.net/REST/v1/Locations?query="
Const apiKey As String = "BingsMapKey" ' 请替换为你自己的Bing地图API密钥
Private Const ReadyOk As Long = 4

Public Function CoordinatesBing(reqAddress As String) As String
    Dim URL As String
    Dim reqHTTP As New MSXML2.XMLHTTP60
    Dim xmlAddress As String
    Dim Latitude As Variant
    Dim Longitude As Variant
    Dim Result As Range
    
    ' 对请求地址做URL编码
    xmlAddress = WorksheetFunction.EncodeURL(reqAddress)
    
    ' 构造API请求地址
    URL = bingURL & xmlAddress & "&o=xml" & "&key=" & apiKey
    reqHTTP.Open "GET", URL, False
    reqHTTP.send ("")
    
    ' 等待请求完成并判断状态
    Do While reqHTTP.readyState <> ReadyOk
        DoEvents
    Loop
    If reqHTTP.Status <> 200 Then
        CoordinatesBing = "API请求失败"
        Exit Function
    End If
    
    ' 解析返回的XML提取经纬度
    On Error Resume Next ' 测试版临时容错，后续可替换为正式异常捕获逻辑
    Latitude = WorksheetFunction.FilterXML(reqHTTP.responseText, "/Response/ResourceSets/ResourceSet/Resources/Location/Point/Latitude")
    Longitude = WorksheetFunction.FilterXML(reqHTTP.responseText, "/Response/ResourceSets/ResourceSet/Resources/Location/Point/Longitude")
    On Error GoTo 0
    
    If IsEmpty(Latitude) Or IsEmpty(Longitude) Then
        CoordinatesBing = "未查询到坐标"
        Exit Function
    End If
    
    ' 获取当前函数所在的单元格
    Set Result = Application.Caller
    ' 按示例需求输出：调用单元格写入纬度，右侧相邻单元格写入经度
    Result.Value = Latitude
    Result.Offset(0, 1).Value = Longitude
    ' 如果你需要原来的偏移逻辑（偏移2、3列），替换为以下两行即可
    ' Result.Offset(0, 2).Value = Latitude
    ' Result.Offset(0, 3).Value = Longitude
    
    CoordinatesBing = "查询成功"
End Function

说明 #

• 使用前请确保已经在VBA编辑器的「工具-引用」中勾选了Microsoft XML, v6.0，如果不想提前引用，可将Dim reqHTTP As New MSXML2.XMLHTTP60替换为Dim reqHTTP As Object: Set reqHTTP = CreateObject("MSXML2.XMLHTTP.6.0")使用晚绑定
• 代码已经适配你给出的示例场景：在B1单元格输入=CoordinatesBing(A1)，B1会返回纬度，C1会返回经度
• 当前版本补充了基础的请求状态判断和容错逻辑，后续可根据业务需求完善异常捕获、批量查询等能力

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