VBA谷歌翻译自定义函数处理含特殊字符输入时失效的问题

• 未做URL编码直接拼接查询参数：HTTP规范要求URL查询参数中的非ASCII字符（中文、阿拉伯语等）、特殊标点、空格必须经过百分号编码才可正常传输。原代码直接将输入内容拼接到q=参数后，这类字符会被客户端截断、转义为乱码，谷歌翻译服务端收到错误的查询内容后，要么无法返回正确结果，要么直接返回参数错误页面。
• 响应解码逻辑不兼容UTF-8：原代码使用的MSXML2.ServerXMLHTTP组件会默认按照系统本地ANSI编码（如中文Windows默认GBK编码）解析响应文本，即便请求携带了UTF-8标识，只要服务端返回的响应头未显式声明charset，组件就会把UTF-8编码的网页内容解码为乱码，后续解析DOM节点时根本找不到类名为result-container的翻译结果容器。
• 旧版请求标识触发反爬拦截：原代码设置的User-Agent为IE6时代的客户端标识，谷歌翻译服务端会直接拦截这类过期客户端的请求，返回人机验证页面或空响应，根本不会返回正常的翻译结果DOM结构。

• 对所有动态拼接的URL参数执行严格的百分号编码，确保非ASCII字符、特殊符号可被服务端正确识别。
• 替换编码处理更可靠的WinHttp.WinHttpRequest.5.1组件，手动按照UTF-8编码解析响应的二进制内容，避免组件默认解码导致的乱码。
• 更新请求头的User-Agent为当前主流桌面浏览器标识，降低被反爬拦截的概率。
• 增加基础异常捕获逻辑，未找到翻译结果容器时返回明确提示，避免函数静默返回空值。

' 辅助函数：对字符串做URL编码，兼容非ASCII字符
Private Function EncodeURL(ByVal str As String) As String
    Dim objStream As Object
    Dim bytes() As Byte
    Dim i As Long
    Dim result As String
    
    ' 把字符串转成UTF-8字节流
    Set objStream = CreateObject("ADODB.Stream")
    objStream.Charset = "UTF-8"
    objStream.Open
    objStream.WriteText str
    objStream.Position = 0
    objStream.Type = 1 ' 二进制模式
    bytes = objStream.Read(-1) ' 读全部字节
    objStream.Close
    Set objStream = Nothing
    
    ' 逐字节转成百分号编码格式
    result = ""
    For i = 0 To UBound(bytes) - 1
        ' 保留URL安全字符：字母、数字、-_.~
        If (bytes(i) >= 65 And bytes(i) <= 90) Or _
           (bytes(i) >= 97 And bytes(i) <= 122) Or _
           (bytes(i) >= 48 And bytes(i) <= 57) Or _
           bytes(i) = 45 Or bytes(i) = 95 Or bytes(i) = 46 Or bytes(i) = 126 Then
            result = result & Chr(bytes(i))
        Else
            result = result & "%" & Right("0" & Hex(bytes(i)), 2)
        End If
    Next i
    EncodeURL = result
End Function

' 辅助函数：把HTTP响应的二进制字节流转为UTF-8字符串
Private Function BytesToUTF8String(ByRef bytes() As Byte) As String
    Dim objStream As Object
    Set objStream = CreateObject("ADODB.Stream")
    objStream.Open
    objStream.Type = 1 ' 二进制模式
    objStream.Write bytes
    objStream.Position = 0
    objStream.Type = 2 ' 文本模式
    objStream.Charset = "UTF-8"
    BytesToUTF8String = objStream.ReadText(-1)
    objStream.Close
    Set objStream = Nothing
End Function

' 主翻译函数
Public Function Translate(strInput As String, strFromSourceLanguage As String, strToTargetLanguage As String) As String
    Dim strURL As String
    Dim objHTTP As Object
    Dim objHTML As Object
    Dim objDivs As Object, objDiv As Object
    Dim strTranslated As String
    Dim rawResponse As String
    
    On Error GoTo errHandler
    
    ' 构造URL时对所有动态参数做编码
    strURL = "https://translate.google.com/m?hl=" & EncodeURL(strFromSourceLanguage) & _
        "&sl=" & EncodeURL(strFromSourceLanguage) & _
        "&tl=" & EncodeURL(strToTargetLanguage) & _
        "&ie=UTF-8&prev=_m&q=" & EncodeURL(strInput)

    ' 替换为WinHTTP组件，兼容性更好
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    objHTTP.Open "GET", strURL, False
    ' 更新为当前主流Chrome浏览器的UA，避免被反爬拦截
    objHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36"
    objHTTP.setRequestHeader "Accept-Charset", "UTF-8"
    objHTTP.send ""
    
    ' 手动用UTF-8解码响应二进制内容，避免组件默认编码导致的乱码
    rawResponse = BytesToUTF8String(objHTTP.ResponseBody)

    ' 创建HTML解析对象
    Set objHTML = CreateObject("htmlfile")
    With objHTML
        .Open
        .Write rawResponse
        .Close
    End With
    
    Set objDivs = objHTML.getElementsByTagName("div")
    strTranslated = ""
    For Each objDiv In objDivs
        If objDiv.className = "result-container" Then
            strTranslated = objDiv.innerText
            Exit For
        End If
    Next objDiv
    
    If strTranslated = "" Then
        Translate = "翻译失败：未获取到有效结果"
    Else
        Translate = strTranslated
    End If
    
cleanup:
    Set objHTML = Nothing
    Set objHTTP = Nothing
    Exit Function
    
errHandler:
    Translate = "翻译请求出错：" & Err.Description
    Resume cleanup
End Function

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