Edited at

宇宙一わかりやすい?VBA-JSONを使ったJSONパースのしかた


はじめに

VBAでJSONをパースする際にはCreateObject("ScriptControl")を使用するのが一般的でしょう。(サンプル例)

しかし、この手段は32bit限定であり、64bitでは使用できません。将来性を考えたら他の手法を用いるべきであり、JSONパースのライブラリーとして一般的なVBA-JSONを使ってみることにしました。


導入の背景

Excel2010のサポートもそろそろ来年にきれるし、必要なときに必要なだけバラバラに買っていたのをやめて、Office365にバージョンアップしようとしたら64bit版がインストールされてしまって、JSONを使えなりました。なお、"HtmlFile"なる手段を使うやり方もあるようですが、IEやedgeの将来性を考えたらこれもつかいたくありません。


今までVBA-JSONを見送った背景

なら、VBA-JSONしかないとなりましたが、

1.モジュールがやたら長くて敷居が高いイメージ

2.日本語の解説がわかりずらいなどということから

今までは私も解説がわかりやすくコードも読みやすいことからCreateObjectを使ってパースをしていましたが、必要に迫られて導入したところ案ずるより産むが易しで拍子抜けするくらいに簡単にできました。


VBA-JSONの導入

かつては、Googleのサイト内でソースコードがホスティングされていたようですが、現在は、GitHubに最新版のソースがあります。

Link -> https://github.com/VBA-tools/VBA-JSON

VBA-JSONをURLからDLし標準モジュール[JsonConverter.bas]を導入したいOfficeのファイルにインポートをしてください。今回のようにパースの場合にはParseJson関数を使用します。ConvertToJson関数はVBAでJSONを生成する関数です。わかりやすい解説がありますのでそちらをご覧ください。

Link -> https://doruby.jp/users/ookouchi/entries/201708-EXCEL-VBA-JSON-API-MSXML2-XMLHTTP

なお、日本語文字をVBA-JSONでコンバートする場合にはjson_Encode関数のSelect Caseの最後を以下のように書き換えるようにしましょう。

 Case 0 To 31, 127 

' Non characters -> convert to 4-digit hex
json_Char = "\u" & VBA.Right$("0000" & VBA.Hex$(json_AscCode), 4)
End Select


サンプルコード


基本

下記に配列と非配列を表示するためのサンプルコードを示してみました。

(この至極単純なコードに至るのに、あれこれ嵌まって半日近く掛かりました。ローカルウィンドウにKeyの文字しか書いて無くて最初何じゃこりゃと思いましたし。)


JSON_1.bas

Private Sub VBA_JSON_TEST()

Dim JSON As String
Dim Parse As Object
Dim Band_Member As Variant

'JSONのロード
JSON = "{""group"":""poppin_party"",""genre"":""rock"",""member"":[""kasumi"",""tae"",""arisa"",""saaya"",""rimi""]}"
Set Parse = JSONConverter.ParseJson(JSON)

'非配列を読み出す
Debug.Print Parse("group")
'配列を読み出す(例)
For Each Band_Member In Parse("member")
Debug.Print Band_Member
Next
End Sub


上のプログラムを実行した結果は以下の通りとなります。


結果

poppin_party

kasumi
tae
arisa
saaya
rimi

パースしたデータはDictionary型として帰ってきます。なお、内部の配列はCollection型です。


応用

応用として使えそうな数種類かサンプルコードを付記します。実際にVBAに移植しコメントとの結果を比べてください。なお、Parse("member").COUNTは1から始まるので他の配列に代入するときには注意が必要です。


JSON_2.bas

Private Sub VBA_JSON_TEST2()

Dim JSON As String
Dim Parse As Object
Dim Key As Variant

'JSONのロード
JSON = "{""group"":""poppin_party"",""genre"":""rock"",""member"":[""kasumi"",""tae"",""arisa"",""saaya"",""rimi""]}"
Set Parse = JSONConverter.ParseJson(JSON)

'.Countを付ければ配列の要素数が出せる
Debug.Print Parse("member").COUNT
'("member")(1)でkasumiがでる
Debug.Print Parse("member")(1)
'Each Forでキーの一覧を出せる
For Each Key In Parse
Debug.Print Key
Next
End Sub



結果

 5 

kasumi
group
genre
member


UTF-8で作成されたファイルを読むとき


JSON_3.bas

Private Function ReadUTF8Text(argPath As String) As String

Dim buf As String

With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.Type = 2 'adTypeText
.LineSeparator = -1 'adCrLf
.Open
.LoadFromFile argPath
buf = .ReadText(-1) 'adReadAll
.Close
End With

ReadUTF8Text = buf

End Function

Private Sub VBA_JSON_TEST3()
Dim JSON As String
Dim Parse As Object
Dim Key As Variant
Dim JSON_FILE As String

'JSONファイルの指定
JSON_FILE = "D\test.json"

'JSONのロード
Set Parse = JSONConverter.ParseJson(ReadUTF8Text(JSON_FILE))

'以下略



参考サイト

【VBA】JSONファイルの内容をEXCELファイルに読み込む

VBAでWeb API(REST API)を使うときの作法

仮想通貨自動取引ツール