Skip to content

Excel VBA 批量查 IP 归属地:不装插件不写 Python,表格里点一下跑完(完整宏代码) ​

运营群里甩过来一个表格,A 列六千多行用户 IP,需求写得很客气:"麻烦加上省份、城市、运营商三列,明天早上要用。"

按常规思路,这件事该丢给 Python:读表、循环调接口、写回。但现实往往卡在两个地方——装环境要 IT 审批,而对方电脑上连 Python 都没有;用现成的 Excel 插件又要按年收费。剩下的路只有一条:让表格自己把这件事干完。

一、为什么值得在 Excel 里解决 ​

不是所有数据活都该搬到代码里去。这类需求有几个特点,决定了 VBA 反而更合适:

  • 数据源和目标本来就在表格里,中间不需要落库、不需要调度;
  • 交付对象是业务同事,给他一个 xlsm 文件,双击、点一下宏按钮就出结果,零学习成本;
  • 量不大:几千行、几万行,VBA 的循环完全吃得下;
  • 可复用:宏是跟着文件走的,同事转发给别人,功能也跟着过去。

代价是 VBA 有两个"坑"必须先说清楚:一是发 HTTP 请求要用对组件,二是接口有 60 次/分钟的限速,代码里不做等待,跑到第 61 行就开始大面积报错。

二、思路:三件事必须先设计好 ​

第一,用 WinHttp 而不是老掉牙的 XMLHTTP。 VBA 里能用的 HTTP 组件不止一个,Microsoft.XMLHTTP 在老系统上默认走 TLS 1.0,访问 HTTPS 接口会直接挂;WinHttp.WinHttpRequest.5.1 走系统 TLS 栈,Win7 以上基本都能用,而且不需要在「引用」里勾任何东西——CreateObject 晚期绑定就行。

第二,JSON 不引第三方库,直接文本截取。 网上流传的 VBA-JSON 要额外导入一个 .bas 模块,同事转发文件时很容易丢。IP9 返回的字段是扁平的,prov、city、isp、country 都是字符串或者不带嵌套的值,用 InStr 定位 "prov": 再截到下一个引号/逗号就够了。

第三,缓存 + 限速是刚需,不是优化。 免费版限速 60 次/分钟,六千行意味着至少一百分钟。所以:同一 IP 只查一次(表格里重复 IP 很常见),请求间隔固定 1.1 秒,并且支持断点续跑——中途关了表格、接口抽风,下次再点一下从断的地方接着来,不用从头烧额度。

三、VBA 完整代码 ​

按 Alt + F11 打开 VBA 编辑器,插入一个模块,把下面整段贴进去。表结构约定:A 列是 IP,B/C/D 列自动填省份/城市/运营商,E 列记状态。

vb
Option Explicit

' 64 位 Office 必须加 PtrSafe,否则编译直接报错
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long

Private Const API_URL As String = "https://ip9.com.cn/get"
Private Const GAP_MS  As Long = 1100        ' 免费版 60 次/分钟,留点余量

Private cache As Object                     ' IP -> "省份|城市|运营商"
Private lastTick As Long

Public Sub BatchLookupIp()
    Dim ws As Worksheet, lastRow As Long, i As Long
    Dim ip As String, res As String, parts() As String

    Set ws = ActiveSheet
    ws.Range("B1").Value = "省份"
    ws.Range("C1").Value = "城市"
    ws.Range("D1").Value = "运营商"
    ws.Range("E1").Value = "状态"

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "A 列没有数据", vbExclamation
        Exit Sub
    End If

    Set cache = CreateObject("Scripting.Dictionary")
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual     ' 每写一格不重算,速度差好几倍
    On Error GoTo cleanup

    For i = 2 To lastRow
        ip = Trim$(CStr(ws.Cells(i, "A").Value))
        If Len(ip) > 0 Then
            ' 断点续跑:已经有结果且状态是 OK 的行直接跳过
            If Not (ws.Cells(i, "E").Value = "OK") Then
                res = QueryGeo(ip)
                If res = "" Then
                    ws.Cells(i, "E").Value = "失败"
                ElseIf Left$(res, 4) = "ERR:" Then
                    ws.Cells(i, "E").Value = Mid$(res, 5)      ' 例如 非法IP / 限制429 / HTTP 502
                Else
                    parts = Split(res, "|")
                    ws.Cells(i, "B").Value = parts(0)
                    ws.Cells(i, "C").Value = parts(1)
                    ws.Cells(i, "D").Value = parts(2)
                    ws.Cells(i, "E").Value = "OK"
                End If
            End If
        End If

        If i Mod 20 = 0 Then
            Application.StatusBar = "进度 " & i & "/" & (lastRow - 1) & _
                                    "  缓存命中 " & cache.Count & " 个 IP"
            DoEvents                                        ' 让界面能响应,不然像卡死
        End If
    Next i

cleanup:
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "跑完了。状态列不是 OK 的行,修好原因后再点一次宏即可续跑。", vbInformation
End Sub

' 返回 "省份|城市|运营商";失败返回 "" 或 "ERR:原因"
Private Function QueryGeo(ByVal ip As String) As String
    Dim t As Long, http As Object, body As String
    Dim prov As String, city As String, isp As String

    If cache.Exists(ip) Then
        QueryGeo = cache(ip)
        Exit Function
    End If

    ' 节流:用 GetTickCount 算毫秒差,避免 Timer 跨零点变负数的老问题
    t = GetTickCount()
    If t - lastTick < GAP_MS Then
        Sleep GAP_MS - (t - lastTick)
    End If

    On Error GoTo failed
    Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
    http.Open "GET", API_URL & "?ip=" & ip, False      ' 不传 ip 参数则查请求方自身 IP
    http.setRequestHeader "Accept", "application/json"
    http.Send
    lastTick = GetTickCount()

    If http.Status <> 200 Then
        QueryGeo = "ERR:HTTP " & http.Status
        Exit Function
    End If

    body = http.ResponseText                           ' 接口返回 UTF-8,带 charset 头,正常不会乱码
    If InStr(body, """ret"":429") > 0 Then
        QueryGeo = "ERR:限制429"
        Exit Function
    End If
    If InStr(body, """ret"":400") > 0 Then
        QueryGeo = "ERR:非法IP"
        Exit Function
    End If

    prov = JsonValue(body, "prov")
    city = JsonValue(body, "city")
    isp = JsonValue(body, "isp")
    QueryGeo = prov & "|" & city & "|" & isp
    cache(ip) = QueryGeo
    Exit Function

failed:
    lastTick = GetTickCount()
    QueryGeo = "ERR:超时或网络异常"
End Function

' 极简 JSON 取值:定位 "key": 之后,取字符串或裸值
Private Function JsonValue(ByVal json As String, ByVal key As String) As String
    Dim needle As String, p As Long, q As Long, ch As String
    needle = """" & key & """:"
    p = InStr(json, needle)
    If p = 0 Then
        JsonValue = "未知"
        Exit Function
    End If
    p = p + Len(needle)
    Do While Mid$(json, p, 1) = " "                     ' 跳过空格
        p = p + 1
    Loop

    If Mid$(json, p, 1) = """" Then                     ' 字符串值
        p = p + 1
        q = InStr(p, json, """")
        If q = 0 Then
            JsonValue = "未知"
        ElseIf q = p Then
            JsonValue = "未知"                          ' 空串,比如免费版的 area
        Else
            JsonValue = Mid$(json, p, q - p)
        End If
    Else                                                ' 数字/布尔值,读到逗号或右括号为止
        q = p
        do While q <= Len(json)
            ch = Mid$(json, q, 1)
            If ch = "," Or ch = "}" Then Exit Do
            q = q + 1
        Loop
        JsonValue = Trim$(Mid$(json, p, q - p))
    End If
End Function

代码跑起来的效果:B、C、D 三列依次填上「广东 / 深圳 / 中国电信」这种结果,E 列标 OK;遇到填错的 IP 会标「非法IP」,接口抽风标「HTTP 502」,撞上限速标「限制429」。

四、落地时的几个坑 ​

别把宏挂到 Worksheet_Change 上。 有同事图省事,想让"粘贴完 IP 自动查归属地"。问题是宏会往同一张表里写 B、C、D 列,一写又触发 Change 事件,直接套娃卡死。老老实实放一个按钮(插入→形状,右键指定宏),让人点了再跑。

六千行不要一口气跑完。 免费版 60 次/分钟,六千个不重复 IP 就是一百分钟。实际做法是下班前点一次,第二天看结果;中途要暂停直接按 Esc(会触发 cleanup 段恢复界面设置),重新点宏会从第一行"状态不是 OK"的地方续跑。批量大的时候按行数分批,比如每批两千行,跑完把已完成的几列复制成值,再处理下一段,这样文件也不会越跑越卡。

重复 IP 是缓存的最大价值。 一张真实业务表里,同一个 IP 出现几十次很常见——尤其是企业出口、校园网、运营商大区出口。缓存字典按 IP 去重之后,六千行往往只有一两千个独立 IP,时间直接砍掉三分之二。

状态列要保留,别手贱删掉。 它是断点续跑的锚点,也是排查依据。另外,结果建议另存一份只保留省级别再发出去:包含完整 IP 的表属于个人信息,转发范围要控制,这和站内《日志里的 IP 属于个人信息,但报表还要按地域统计?》讲的是同一件事。

区县那列为什么总是空。 GET https://ip9.com.cn/get?ip=<IP> 返回的字段里,area(区县)在免费版固定为空字符串,这是能力边界不是 bug;要省市区三级,需要 VIP 版的字段(同时还有 ip_type、ip_asn)。如果表格里出现 IPv6 地址不用担心,接口 IPv4/IPv6 都支持;但格式写错(比如端口没去掉、多了空格)会返回 ret=400,脚本里会标成「非法IP」,先在源数据里清洗一轮再跑。

偶尔乱码怎么办。 接口响应的 Content-Type 带 charset=utf-8,ResponseText 通常直接可用。如果你的环境上确实出现中文乱码(多见于把响应存成文件再读的老做法),换成 ResponseBody + ADODB.Stream 指定 Charset = "utf-8" 再读,就能解决。

总结 ​

在 Excel 里查 IP 归属地,技术上没有难点,麻烦的是两件小事:用对 HTTP 组件、把限速和缓存做进去。上面这段宏总共不到一百行,粘贴到一个模块里就能用,转给业务同事也不用装任何东西。

接口就是 https://ip9.com.cn/get?ip=<IP>——免费版不需要注册、不限总量,只限 60 次/分钟/IP,返回国家、省市、邮编、区号、运营商、经纬度、大区等字段;需要区县和 IP 类型(家宽/企业/机房)时再上 VIP 版。字段与额度都以官网 https://www.ip9.com.cn 的说明为准。