　disconnectedな環境でほそぼそとプログラムを書いているが、こんな環境だとたまにやって来るのが
古来から存在するExcelやAccessに機能付加をしてくれ
と言う案件。
今回は、データ交換といえばCSVという時代に先人が作ったブツへ、近代兵器JSONのインポート機能を実装しろというのだ。

　もはや、pythonなんかで作り直したら。。。とは思うのだが、口に出すと開発保守案件に昇格するため間違っても口には出せない。一旦セルなりテーブルなりに帳尻合わせて落としてしまえば、あとの処理は先人が残した~~スパゲッティな~~亡霊に任せてしまえるので、あくまでコストの観点からインポートのみを実装することにした。

情報収集

　"vba json"で検索してみると、CreateObject("ScriptControl")を使うという記事が見当たるが、64bit環境では動作しないらしい。残念。
　続いてVBA-JSONなる有志によるモジュールを使うという記事を見つけたが、disconnectedなため、写経をするしかなく、思ったよりコードが長いのを見て断念した。
　おおよそこの手の問題はネットの天才たちが解決済みであり、それを少しfixしてあたかも自分の手柄のように見せるのが私の仕事なのだが、今回はこれと言った解決法が見つからなかった。（検索力の問題かもしれないが）

俺達のieはまだまだこれからだver1

　そんな中、ふとInternetExplorerコントロールで読み込んだhtmlの内部に記述したJSを発火したというニッチな経験を思い出した。functionの発火ができるなら、返り値のデータを受け取れるのでは？という安直な考えが浮かんだのである。

parseJSON.html

<html>
  <head>
    <script>
      function parseJSON(json){
        return eval('('+json+')');
      }
    </script>
  </head>
</html>

既に邪道なのでJSON.parseではなくevalを使います。違いとして、evalだと要素名を"で括らなくても通ります。但し、SQLインジェクションみたいなことができるかもしれません。

このようなhtmlファイルを用意して、フォームに配置したInternetExplorerコントロールへ読ませる。これをフォームのVBAから

parseJSON.bas

Private Sub parseJSON()
  Dim obj As Object
  Set obj = Me.インターネットエクスプローラー0.object.document.parentWindow.parseJSON("{'a':0,'b':[1,2]}")
  Debug.Print obj.a
  '> 0
End Sub

"インターネットエクスプローラー0"はコントロール名なので、適時変更してください。

とすれば、長ったらしいが、出力0が得られる。
　このときobjはObject/JScriptTypeInfoという型になっており、プロパティの入れ子として返却されるようである。JScriptはJavaScriptと~~同じである~~互換があるため、変換してVBAでも扱えるようにしましたよ。ということだろうか。
　ともあれ、原理はわからないが、当初の目論見通りie上のJSでJSONをパースし、functionの返り値として得ることができたことになる。

ver1の問題点

配列が扱えない

　プロパティの入れ子になっている時点で嫌な予感はしていたが、返ってきたobj.bは配列ではなく、下図のような入れ子である。
obj
├a
└b
　├0
　└1
配列ではないので、当然obj.b(0)という参照は通らないし、VBAではプロパティ名の先頭に数字をつけられないことから、obj.b.0という参照は記述すらできない。同じく、JavaScriptで多用される_(アンダーバー)から始まるプロパティへの参照も記述できない。更に都合が悪いことに、JavaScript上では自動生成されるlength等のプロパティも削除されているので、返り値から要素数が判定できず、ForEachも対応していない。

コントロールを用意しないといけない

　InternetExplorerコントロールを使用しているため、このためのフォームを用意する必要がある。Accessならまだ良いがExcelだと流石に手間がかかりすぎる。

ver1の問題解決策

配列が扱えない

　.item(0)だったり、CStrだったり、色々試したところ、VBA側に持ってきてからではどうにもならないようなので、JavaScript側にパースしたデータを保持し、アクセッサ(と言っていいのかわからないが)を用意して、データにアクセスすることとした。

コントロールを用意しないといけない

　これはInternetExplorerオブジェクトで代替できるだろうと踏んでいたが、HTMLDocumentなる更に適したオブジェクトが存在した。InternetExplorerオブェクトはnewした時点ではDOMツリーを持っていないため、内部のdocumentにアクセスしようとしても拒否される。対して、HTMLDocumentはnewした時点で最低限の構造を保持しており、window以下の既定メソッドを実行できる。

俺達のieはまだまだこれからだver2

　前述した解決策を盛り込んだソースは以下の通りである。

JSONparser.cls

'データ保持用のHTMLDocument
Private doc As Object

'なぜか引数付きコンストラクタがないらしい
Public Sub Init(ByVal json As String)
  '参照設定するとクラスとして使いにくい
  Set doc = CreateObject("htmlfile")
  '既定メソッドexecScriptでfunctionを書き込む
  doc.parentWindow.execScript "" & _
  "//JSONをパースしたオブジェクトを格納" & _
  "var obj;" & _
  "//JSONをパースしてobjに格納する" & _
  "function parseJSON(json){" & _
  "  obj = eval('('+json+')');" & _
  "}" & _
  "//jsonのパスをもとにオブジェクトにアクセスする" & _
  "function accesser(path){" & _
  "  return eval('(obj'+path+')');" & _
  "}"
  doc.parentWindow.parseJSON json
End Sub

Public Function accesser(ByVal path As String)
  On Error GoTo errpoint
  accesser = doc.accesser(path)
  Exit Function
errpoint:
  accesser = Empty
End Function

テスト用のモック

mock.bas

Public Sub test()
  Dim ps As New JSONParser
  ps.Init "{'a':0,'b':[1,2]}"
  Debug.Print ps.accesser(".a")
  '> 0
  Debug.Print ps.accesser(".b.0")
  '> 1
  Dim i As Integer
  For i = 0 To ps.accesser(".b.length") - 1
    Debug.Print ps.accesser(".b[" & i & "]")
  Next
  '> 1
  '> 2
End Sub

　これでver1に挙げた問題点、特に配列を扱えない問題が解決できた。

今後の課題と解決案

連想配列の扱い

　ver2では個別のプロパティへのアクセスが確実に行えるようになり、なんとか配列に対してForをかけられるようになったが、依然として連想配列にはForEachが適用できない。

　これについて、簡単な方法では、JS側にkeyの一覧を返すようなkeysメソッドを用意して、

Dim ps As New JSONParser
ps.Init "{'a':0,'b':[1,2],'c':{'c1':3}}"
For Each k In ps.keys(".c")
  Debug.Print ps.accesser(".c." & k)
Next
'> 3

とすれば、ver2を拡張するような形で処理可能となる。(検証はしていない。)

accesserで配列と連想配列を指定できない

　accesserメソッドで取得できるのはあくまでプリミティブ型だけであり、連想配列や配列になっているプロパティを対象とするとEmptyが返る。これは、VBAで受け取る際にプリミティブ型以外にはSet句を使用しなければならないためである。

　これについては、JS側のaccesserにtypeof判定を設けて、これを保持したレスポンス用の連想配列

{"type":"dict","item":obj}

を返し、JSONParserクラス内で配列であればArrayに、連想配列であればCollectionに詰め替えてから値を返すことで、ForEachを使用して処理可能となる。(もっとも、この方法だと指定した階層より下に配列が入れ子になっていると正しく扱えない問題が残るのだが。)

おわりに

　今のところ用途的にver2で問題ないが、気が向いたら前述した課題を解決したver3を作成しようと思う。
　初めてQiitaで記事を書いたため、至らぬ点が多々あると思うが、ご容赦いただきたい。また、誤字や内容の誤りがあれば指摘いただけると幸いである。