VBA:Google翻訳関数 -

エクセルVBAによる翻訳関数のマクロです。対象文字列を指定し、第二引数に言語を指定することで翻訳ができます。第二引数はオプショナルとしており、もし第二引数がない場合は自動で和英訳するようにしています。

記事の内容

・コード

・ポイント機能

コード

翻訳関数は下記サイトでのマクロを参考にさせていただきました。

【VBAマクロダウンロード】Excel関数でセルを自動翻訳｜GoogleTranslate関数をMicrosoft Officeで利用する方法 | 個人DX/RPA 業務の自動化の教科書｜ゆるけみ

➤コードはこちら

Option Explicit

Public Function Gootrans_DefJPandEn(rng As Range, Optional translateFrom As String, Optional translateTo As String) As String
'Dim translateFrom As String, translateTo As String
                        
    '変数設定
    Dim param As String, trans As String, url As String
    
    'HTTPリクエストの設定
    Dim objHTTP As Object
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
    'セルの値を取得
    param = rng.Value
    
    If translateFrom = "" Or translateTo = "" Then
    
    If IsJPTexts(param) Then
        translateFrom = "ja"
        translateTo = "en"
    Else
        translateFrom = "en"
        translateTo = "ja"
    End If
    
    End If
    
    
    
    'HTTPリクエストのURL設定
    url = "https://translate.google.pl/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & EncodeURL(param)
    
    'HTTPリクエスト
    objHTTP.Open "GET", url, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    
    'HTTPリクエストのレスポンステキストを取得
    Dim oHtml As New MSHTML.HTMLDocument
    Set oHtml = New MSHTML.HTMLDocument
    oHtml.body.innerHTML = objHTTP.responseText
    
    '翻訳結果を取得
    Dim transtext As String
    transtext = oHtml.getElementsByClassName("result-container")(0).innerText
    
    '翻訳結果があればClean関数を実行、なければエラー出力
    If transtext <> "" Then
        Gootrans_DefJPandEn = Clean(transtext)
    Else
        Gootrans_DefJPandEn = CVErr(xlErrValue)
    End If

End Function

'翻訳前のテキストのエンコード用に変換
Function ConvertToGet(str As String)

    str = Replace(str, " ", "+")
    str = Replace(str, vbNewLine, "+")
    str = Replace(str, "(", "%28")
    str = Replace(str, ")", "%29")
    
    ConvertToGet = str

End Function

'翻訳後のテキスト内の文言を変換
Function Clean(str As String)

    'str = Replace(str, """, """")
    str = Replace(str, "%2C", ",")
    str = Replace(str, "'", "'")
    
    Clean = str

End Function

'日本語をURLエンコード
Function EncodeURL(ByVal str As String) As String
    EncodeURL = Application.WorksheetFunction.EncodeURL(str)
End Function

'関数の説明
Sub RegisterGoogleTransleFormula()

    '関数名の設定
    Dim strFunc As String
    strFunc = "Gootrans_DefJpAndEn"
    
    '関数の説明
    Dim strDesc As String
    strDesc = "Gootrans_DefJpAndEn関数" & vbNewLine & vbNewLine & _
    "英語から日本語に翻訳したい場合、Gootrans_DefJpAndEn(A1,""en"",""ja"")" & vbNewLine & vbNewLine & _
    "日本語から英語に翻訳したい場合、Gootrans_DefJpAndEn(A1,""ja"",""en"")" & vbNewLine & vbNewLine & _
    "他の言語を使いたい場合は: https://cloud.google.com/translate/docs/languages/" & vbNewLine & "   "

    '関数内の引数の説明
    Dim strArgs(0 To 2) As String
    strArgs(0) = "翻訳したいセルを選択"
    strArgs(1) = "現在の言語を入力(ex. 英語はen,日本語はja)"
    strArgs(2) = "翻訳したい言語を入力(ex. 英語はen,日本語はja)他：中国語簡体:zh-CN, マレー語:ms,韓国語:ko"
    
    Application.MacroOptions Macro:=strFunc, Description:=strDesc, ArgumentDescriptions:=strArgs, Category:="Custom Category"

End Sub

Option Explicit

Public Function Gootrans_DefJPandEn(rng As Range, Optional translateFrom As String, Optional translateTo As String) As String
'Dim translateFrom As String, translateTo As String
                        
    '変数設定
    Dim param As String, trans As String, url As String
    
    'HTTPリクエストの設定
    Dim objHTTP As Object
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
    'セルの値を取得
    param = rng.Value
    
    If translateFrom = "" Or translateTo = "" Then
    
    If IsJPTexts(param) Then
        translateFrom = "ja"
        translateTo = "en"
    Else
        translateFrom = "en"
        translateTo = "ja"
    End If
    
    End If
    
    
    
    'HTTPリクエストのURL設定
    url = "https://translate.google.pl/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & EncodeURL(param)
    
    'HTTPリクエスト
    objHTTP.Open "GET", url, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    
    'HTTPリクエストのレスポンステキストを取得
    Dim oHtml As New MSHTML.HTMLDocument
    Set oHtml = New MSHTML.HTMLDocument
    oHtml.body.innerHTML = objHTTP.responseText
    
    '翻訳結果を取得
    Dim transtext As String
    transtext = oHtml.getElementsByClassName("result-container")(0).innerText
    
    '翻訳結果があればClean関数を実行、なければエラー出力
    If transtext <> "" Then
        Gootrans_DefJPandEn = Clean(transtext)
    Else
        Gootrans_DefJPandEn = CVErr(xlErrValue)
    End If

End Function

'翻訳前のテキストのエンコード用に変換
Function ConvertToGet(str As String)

    str = Replace(str, " ", "+")
    str = Replace(str, vbNewLine, "+")
    str = Replace(str, "(", "%28")
    str = Replace(str, ")", "%29")
    
    ConvertToGet = str

End Function

'翻訳後のテキスト内の文言を変換
Function Clean(str As String)

    'str = Replace(str, """, """")
    str = Replace(str, "%2C", ",")
    str = Replace(str, "'", "'")
    
    Clean = str

End Function

'日本語をURLエンコード
Function EncodeURL(ByVal str As String) As String
    EncodeURL = Application.WorksheetFunction.EncodeURL(str)
End Function

'関数の説明
Sub RegisterGoogleTransleFormula()

    '関数名の設定
    Dim strFunc As String
    strFunc = "Gootrans_DefJpAndEn"
    
    '関数の説明
    Dim strDesc As String
    strDesc = "Gootrans_DefJpAndEn関数" & vbNewLine & vbNewLine & _
    "英語から日本語に翻訳したい場合、Gootrans_DefJpAndEn(A1,""en"",""ja"")" & vbNewLine & vbNewLine & _
    "日本語から英語に翻訳したい場合、Gootrans_DefJpAndEn(A1,""ja"",""en"")" & vbNewLine & vbNewLine & _
    "他の言語を使いたい場合は: https://cloud.google.com/translate/docs/languages/" & vbNewLine & "   "

    '関数内の引数の説明
    Dim strArgs(0 To 2) As String
    strArgs(0) = "翻訳したいセルを選択"
    strArgs(1) = "現在の言語を入力(ex. 英語はen,日本語はja)"
    strArgs(2) = "翻訳したい言語を入力(ex. 英語はen,日本語はja)他：中国語簡体:zh-CN, マレー語:ms,韓国語:ko"
    
    Application.MacroOptions Macro:=strFunc, Description:=strDesc, ArgumentDescriptions:=strArgs, Category:="Custom Category"

End Sub

ポイント機能

・Optionalによる自動和英判定

・引数への指定言語

Optionalによる自動和英判定

関数では引数に翻訳元言語と、翻訳対象言語を設定します。これはoptionalで指定しなくても設定が可能です。設定しない場合は、IsJP関数により、対象文での日本語の割合に応じて和英判定がされます。

和文の定義は、「ひらがな、全角カタカナ、半角カタカナ、日本語の漢字、句読点（。、）を含めた文章が全体の内半数以上の場合」

としています。

全角/半角のひらがな、カタカナ、漢字が入っている場合は日本語と判定して英訳、それ以外は英訳するようになっています。句読点等の記号でも日本語の判定は可能ですが、全角コロン「：」などの記号は日本語になってしまい、例えば英語の文でも記号が入っていると和文と認識してしまうため、和文判定基準からは除外しています。

関数は2つあり、

①日本語の割合から和文を判断する関数と、

②日本語の量を数える関数です。①から②を呼び出し、①で割合を計算し和英判断をしています。

②では引数を受け取り、標準表現Regexを使用することで和文を数えます。

Regexの使用では参照設定で事前バインディングを行います。これをすることでピリオドを押すとIntelliSenseが機能し入力しやすくなります。

Regex（正規表現）とは、特定のパターンに一致する文字列を検索・置換・抽出するための表記法です。プログラミングやデータ処理で広く使用されます。数値や日本語など様々な文字は正規表現を使うことでより細かく、包括的に操作することが可能です。

例えば日本語であれば、平仮名などの其々の形態ごとに正規表現があり、簡単に操作が可能です。

ひらがな → [ぁ-ゖ]
カタカナ（全角） → [ァ-ヶ]
カタカナ（半角） → [ｦ-ﾟ]
漢字 → [々〇〻\u3400-\u9FFF\uF900-\uFAFF]

Regexを使った文字カウントでは基本的なスクリプトが決まっています。オブジェクト変数にRegexを割り当て、Patternに対象にしたい文字や記号を指定、IgnoreCaseで大文字小文字区別するかを指定し、Globalで対象の文字列すべてを検索するか、最初に見つけるまでとするかを設定します。今回は、ひらがな～句読点まで日本語に現れ、英語には表れない表現を指定し、IgnoreCace、GlobalをTrueにしたことで、大文字に関係なく、対象文章からすべての日本語を捜査対象にしています。