この記事はAdvent Calendar 2017 Excel VBA 3日目の投稿です。

なんでVBA触ることになったのか

Excel VBAのアドベントカレンダーに記事を書くことになったから（書くことが内定していたから・・・）といいつつ、日々お世話になっているExcelちゃんともうちょっと仲良くなろうと思ったのがキッカケ。

で、何したの？

如何にもVBAというネタは、まぁ初心者なので難しいしなぁと思ってはいた。
Web開発者らしく、潔く、ちまたのWebサービス使って何かしようと。
結果、Excel VBA から Cognitive Service 使って翻訳してみました！

Cognitive Serviceって何？なお方はAzureさんの公式サイトへ ^^
Azure/Cognitive Service

仕上がりはこんな感じ ^^

1. シート上のボタン押す

翻訳ソースの言語、翻訳する言語、Cognitive ServiceのAPIキーを入れて、ボタンを押します。
マクロが実行されます。

2. 翻訳する他のExcelブックを選ぶ

ダイアログが立ち上がるので、翻訳したいExcelブックを選びます。

3. ブック内の文字列が翻訳されてシートに書き出される

翻訳結果がシートに書き出されます

まぁ、これだけです。
ほんとこれだけ。

でも出来上がったときは感動したし、割といろいろVBAのハマリどころ？にはまった気がします・・・

何をどうしてるのか？

ソースコードはGitHubに上げてあります。
GitHub/Excel VBA
(AzureポータルでCognitive ServiceのText Translation（無料プランあり）を使えるようにすれば動かせるはず)

正直、コーディングだけでお腹いっぱいなのですが、この2日間の戦いの振り返りを兼ねてコードを少し見てみます。

ファイルを開く！未知との遭遇

'実行元Bookを退避
Dim hostBook As Workbook
Set hostBook = Application.ActiveWorkbook
    
'対象ファイルを選択させて取得
Dim filePath As String
filePath = Application.GetOpenFilename("Excel File,*.xlsx", Title:="対象ファイルの選択")
    
'キャンセルされたら終わり
If filePath = "False" Then
    Exit Sub
End If

今回は実行用ブックから、翻訳する別のブックの文字列を読み込んで翻訳しました。（だってその方が便利そうだから）
ググれば何でも出てくるこの時代。コピペッコピペッ。

でもいろいろ知らないことだらけ。

変数宣言も初期化も分からん!!
Ifどうすんの？
Returnどうすんの？
Applicationってなに？
ひたすら未知でした

動的2次元配列にハマる

今回一番ハマって、時間を浪費した配列関連・・・

動的配列ってなに？でもこれ使えそう
まず初期化で0入れるの意味わからん！→ Cellsは1からじゃん？でよく分からず頭が混乱する
極めつけ、最終次元しか拡張できない・・・

この最終次元縛りのおかげで、行列を逆転させて2次元配列に入れて・・・

Dim v() As Variant
ReDim v(2, 0)

...

'配列に値をセット
'2次元配列は最終次元しか拡張できないので、列→1次元、行→2次元で書き込んで拡張する
v(0, UBound(v, 2)) = c.Worksheet.Name + "!" + c.Address
v(1, UBound(v, 2)) = ct

...

ReDim Preserve v(2, UBound(v, 2) + 1)

縦横モドース！冗談みたいですが、これはVBAあるあるネタなんですよね？きっと？

'2次元配列の縦横を入れ替えて書き込み！
Range(Cells(1, 1), Cells(UBound(v, 2) + 1, UBound(v, 1) + 1)) = WorksheetFunction.Transpose(v)

Cognitive Service 呼び出す

HttpでAzure Cognitive ServiceのAPIを呼び出します。
ここはホント何もハマらないで終了。
普通にできるということは本当に素晴らしい。
ネット上に数多のVBA知見が転がっているのが本当に素晴らしい。

'API呼び出し!!
Set httpClient = CreateObject("MSXML2.XMLHTTP")
url = "https://api.microsofttranslator.com/V2/Http.svc/Translate?" _
    + "to=" + toLang _
    + "&from=" + fromLang _
    + "&text=" + Application.WorksheetFunction.EncodeURL(sourceText)
                
With httpClient
    .Open "GET", url, False
    .SetRequestHeader "Ocp-Apim-Subscription-Key", apiKey
    .Send
    resultText = .ResponseText
End With
        
'翻訳結果からタグと改行を削除
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.ignoreCase = True
re.Pattern = "<[^>]+?>"
resultText = re.Replace(resultText, "")
resultText = Replace(resultText, vbLf, "")
        
translatedText = translatedText + resultText

VBAと戯れてみて（感想）

最初は少し抵抗ありましたが、皆大好きExcelだけで色々できる！というのは可能性を感じました。
（あくまで簡単なツールとしての利用では）

個人的には、Http通信が普通に使えることが分かったので、基本的なロジックは外（Web APIとか）に出して、あくまでExcelさんのご都合にそぐわないトコだけVBAでごにょごにょするのが好みそうです。

でもコイツにトラウマ植え付けられたので、しばらくは距離を置こうと思います(笑)

最後に、Cognitive Serviceの翻訳テスト中に何度も励ましてくれた松岡修造さんに最大級の賛辞を送りたいと思います!