Excel VBA开发谷歌翻译工具代码报错无法定位问题求助

问题代码存在的错误点 #

• 语法结构不完整：Google_Translate 子过程中的For循环无对应Next语句、If判断无对应End If闭合，直接运行会报语法错误
• 变量定义错误：Dim i, colcnt As Interior 类型定义错误，统计列数应使用Long整数类型，而非Interior（单元格格式对象类型）；后续变量名笔误将colcnt写为co1cnt（字母l错写为数字1）
• 常量拼写错误：自动计算常量x1calculationAutomatic首字符应为字母l而非数字1，正确写法是xlCalculationAutomatic
• 未定义依赖项：代码中用到的LanguageCode枚举、inputAuto、returnEnglish、langcode均未提前定义，运行时会报变量未定义错误
• 变量名笔误：定义了未使用的angfrom变量，实际使用的langfrom未在该位置定义
• 实现逻辑失效：IE浏览器已停止官方支持，谷歌翻译当前前端页面结构已变更，原代码中抓取的tlid-translation translation类名不存在，无法获取翻译结果
• 未处理URL编码：直接拼接文本到请求链接中，遇到中文、特殊字符时请求会直接失败

修正后代码 #

Option Explicit
' 提前定义语言枚举
Public Enum LanguageCode
    inputAuto = 0
    returnEnglish = 1
    zhCN = 2
    ' 可按需扩展其他语言
End Enum
Dim Output As String
Dim langcode As String

Sub Google_Translate()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    ' 初始化语言编码对应关系
    langcode = "auto,en,zh-CN"
    
    Dim i As Long, colcnt As Long
    colcnt = ActiveSheet.UsedRange.Columns.Count
    For i = 1 To colcnt
        If Cells(1, i).Value <> "" Then
            ' 示例：将第一行内容翻译后写入第二行对应位置
            Cells(2, i).Value = autotranslate(Cells(1, i).Value, inputAuto, zhCN)
        End If
    Next i
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Public Function autotranslate(ByVal Text As String, Optional LanguageFrom As LanguageCode, Optional Languageto As LanguageCode) As String
    Dim langfrom As String, langto As String, http As Object, url As String, myarray
    ' 初始化默认值
    If IsMissing(LanguageFrom) Then LanguageFrom = inputAuto
    If IsMissing(Languageto) Then Languageto = returnEnglish
    If langcode = "" Then langcode = "auto,en,zh-CN"
    
    myarray = Split(langcode, ",")
    langfrom = myarray(LanguageFrom)
    langto = myarray(Languageto)
    
    ' URL编码处理
    Text = WorksheetFunction.EncodeURL(Text)
    url = "https://translate.google.cn/translate_a/single?client=gtx&sl=" & langfrom & "&tl=" & langto & "&dt=t&q=" & Text
    
    ' 改用XMLHTTP请求替代已失效的IE方案
    Set http = CreateObject("MSXML2.XMLHTTP.6.0")
    With http
        .Open "GET", url, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded;charset=UTF-8"
        .send
        Do Until .readyState = 4
            DoEvents
        Loop
    End With
    
    ' 解析返回结果
    Dim result As String
    result = http.responseText
    result = Split(Split(result, """", 2)(1), """", 2)(0)
    autotranslate = result
    Output = autotranslate
    
    Set http = Nothing
End Function

使用注意事项 #

• 使用前需确保Excel宏设置已开启，允许运行VBA代码
• 可根据需求自行扩展LanguageCode枚举和langcode对应的语言编码列表
• 单次翻译文本长度不要超过5000字符，避免请求失败

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