优化Excel正则清洗与谷歌翻译批量处理代码以提升速度

核心优化思路 #

• 摒弃逐单元格循环逻辑，改用批量API请求大幅减少网络交互次数（这是耗时的主要原因）
• 对长文本做可控拆分，在API字符限制内最大化单请求处理量
• 一次性写入翻译结果，避免频繁的单元格读写操作

优化后VBA实现代码 #

Option Explicit

' 批量翻译主程序
Sub BatchTranslateVerbatim()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim targetRng As Range
    Dim textBatch As Collection
    Dim currentBatch As String
    Dim maxBatchChars As Integer
    Dim resultsArr() As String
    Dim batchIndex As Integer, rowIndex As Integer
    Dim cellContent As String
    
    ' 初始化参数
    Set ws = ThisWorkbook.Sheets("你的工作表名") ' 替换为实际工作表名
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ' Verbatim列假设为B列
    Set targetRng = ws.Range("B2:B" & lastRow) ' 跳过表头，从第2行开始
    Set textBatch = New Collection
    maxBatchChars = 4500 ' 预留500字符缓冲，适配谷歌API单请求5000字符上限
    ReDim resultsArr(1 To lastRow - 1)
    rowIndex = 1
    
    ' 第一步：按字符限制批量收集待翻译文本
    For Each cell In targetRng
        cellContent = cell.Value
        If cellContent <> "" Then
            ' 检查当前批次是否超限，超限则提交批次并重置
            If Len(currentBatch) + Len(cellContent) + 3 > maxBatchChars Then
                textBatch.Add Left(currentBatch, Len(currentBatch) - 3) ' 移除末尾分隔符
                currentBatch = cellContent & "|||" ' 用自定义分隔符区分不同条目
            Else
                currentBatch = currentBatch & cellContent & "|||"
            End If
        End If
    Next cell
    ' 加入最后一批未提交的文本
    If currentBatch <> "" Then textBatch.Add Left(currentBatch, Len(currentBatch) - 3)
    
    ' 第二步：批量调用翻译API并解析结果
    For batchIndex = 1 To textBatch.Count
        Dim xmlHttp As Object
        Dim apiUrl As String
        Dim jsonResp As Object
        Dim translations As Variant
        Dim splitTexts As Variant
        
        Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
        apiUrl = "https://translation.googleapis.com/language/translate/v2?key=你的API密钥" & _
                  "&q=" & URLEncode(textBatch(batchIndex)) & "&source=auto&target=zh-CN" ' 调整目标语言
        
        ' 发送API请求
        xmlHttp.Open "POST", apiUrl, False
        xmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        xmlHttp.Send
        
        ' 解析JSON返回（需提前导入VBA-JSON库）
        Set jsonResp = JsonConverter.ParseJson(xmlHttp.responseText)
        translations = jsonResp("data")("translations")
        
        ' 拆分翻译结果并映射到数组
        splitTexts = Split(textBatch(batchIndex), "|||")
        For i = LBound(splitTexts) To UBound(splitTexts)
            resultsArr(rowIndex) = translations(i + 1)("translatedText")
            rowIndex = rowIndex + 1
        Next i
    Next batchIndex
    
    ' 第三步：一次性写入所有翻译结果
    ws.Range("C2:C" & lastRow).Value = Application.Transpose(resultsArr) ' 结果写入C列，可自行调整
End Sub

' URL编码辅助函数，处理特殊字符
Function URLEncode(ByVal inputText As String) As String
    Dim byteArr() As Byte
    Dim charCode As Integer
    Dim i As Long
    
    byteArr = StrConv(inputText, vbUnicode)
    For i = 0 To UBound(byteArr) Step 2
        charCode = byteArr(i)
        Select Case charCode
            Case 48 To 57, 65 To 90, 97 To 122, 45, 46, 95, 126
                URLEncode = URLEncode & Chr(charCode)
            Case 32
                URLEncode = URLEncode & "+"
            Case Else
                URLEncode = URLEncode & "%" & Right("0" & Hex(charCode), 2)
        End Select
    Next i
End Function

关键注意事项 #

• 需提前导入VBA-JSON库到你的VBA工程（用于解析API返回的JSON数据）
• 替换代码中的你的工作表名和你的API密钥为实际信息
• 若单句文本超过5000字符，需在收集批次前添加句子拆分逻辑（可按。！？等标点分割）
• 谷歌翻译API有调用配额限制，需确保你的账号配额足够处理16000+行数据

额外优化方向 #

• 使用Power Query实现无VBA批量翻译：Power Query原生支持批量HTTP请求，可直接连接翻译API，全程可视化操作，无需编写循环代码
• 启用异步API请求：调整VBA代码为异步模式，同时发送多个请求，进一步压缩等待时间
• 添加翻译缓存：对已翻译的文本做本地缓存，避免重复调用API

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