JSON を VBA で解析する

1. JSON 解析
2. VBA で JSON を解析する最初の方法
3. VBA で JSON を解析する 2 番目の方法
4. VBA で JSON を解析する 3 番目の方法
5. まとめ

JSON (JavaScript Object Notation) は、JavaScript のオブジェクト構文に基づいて構造化されたデータを表す標準的なテキストベースの形式です。 Webベースのアプリケーションでデータを保存および転送するために使用されます。たとえば、Webページに表示する必要があるデータをサーバーからクライアントに送信します。

JSON の構文は次のとおりです。

{ "name": "Alina", "age": 19, "gender": "female", }

JSON のデータは、コンマで区切られたキーと値のペアで格納されます。 各キーはスピーチマーク ("") で書かれ、キーに対応する値がコロンの後に続きます。

JSON 解析

JSON 解析について説明する前に、解析とは何かについて説明する必要があります。 あるデータ型から別のデータ型に値を変換することを解析と呼びます。 たとえば、文字列値を int データ型に変換するには、ユーザーが文字列値を解析する必要があります。

JSON 解析は、テキストベースの JSON を、後でプログラムで使用できる JavaScript オブジェクトに変換します。 解析機能は、提供されたデータが有効な JSON であるかどうかも保証します。

VBA を使用すると、外部ライブラリを使用せずに JSON を解析できます。 以下では、ユーザーが JSON を簡単に解析できるようにする 3つのソリューションについて説明します。

VBA で JSON を解析する最初の方法

VBA を使用して JSON を解析する最初の方法は次のとおりです。

Function DecodingOfJSON (jsonString As Variant)
    Set obj = CreateObject("ScriptControl"): obj.Language = "JScript"
    Set jsonDecode = obj.Eval("(" + jsonString + ")")
End Function

Sub main()
    Set arr = DecodingOfJSON(jsonString )
End Sub

関数 DecodingOfJSON() はパラメーターとして jsonString を取ります。 関数内で、オブジェクト obj が作成され、jsonString が評価されます。

jsonString の評価結果は、JSON から評価されたすべての JavaScript オブジェクトを格納する配列 arr に返されます。 オブジェクトの作成には、ScriptControl または MSScriptControl.ScriptControl のいずれかを使用できます。

VBA で JSON を解析する 2 番目の方法

2 番目のメソッドは、XMLHTTP オブジェクトを作成します。 クライアント コンピューターは、XMLHTTP オブジェクトを使用して、任意の HTTP 要求を送信し、要求からの応答を受信します。

Microsoft XML ドキュメント オブジェクト モデル (DOM) は、応答を解析し、ユーザーの要件に従って表示します。

JSON を解析するコードを以下に示します。

Sub parseJSON()

    Dim Book As Object
    Dim sc As Object

    Set sc = CreateObject("MSScriptControl.ScriptControl")
    sc.Language = "JScript"

    With CreateObject("MSXML2.XMLHTTP")

        .Open "GET", "http://www.omdbapi.com/?t=frozen&y=&plot=short&r=json", False
        .send
        Set Movie = sc.Eval("(" + .responsetext + ")")
        .abort

        With Sheets(7)
            .Cells(1, 1).Value = Book.Title
            .Cells(1, 2).Value = Book.Year
            .Cells(1, 3).Value = Book.Rated
            .Cells(1, 4).Value = Book.Released
            .Cells(1, 5).Value = Book.Writer

        End With
    End With
End Sub

このコードは、JSON を解析する ScriptControl オブジェクトを作成します。 .Open パラメーターで指定された URL から JSON を取得します。

スクリプト言語は JScript に設定され、JSON を解析します。 sc.Eval は JSON を解析し、結果の JavaScript オブジェクトを Book オブジェクトに割り当てます。

With ステートメントにより、ユーザーは Book 変数に格納された JavaScript オブジェクトの値をシート 7 の異なるセルに割り当てることができます。

注: ソリューションは、JSON パーサーではなく JScript パーサーです。 JSON に悪意のあるコードをインストールする可能性があります。 ScriptControl.UseSafeSubset = True コマンドを使用して、JScript パーサーが JSON に悪意のあるコードを追加するのを回避できます。

VBA で JSON を解析する 3 番目の方法

3 番目のメソッドは Variant を宣言し、responsetext を各項目を区切る引用符、カンマ、引用符の形式で分割します。 値は、必要な見積もりを探すことで抽出できます。

たとえば、最後の JavaScript オブジェクトが必要な場合は、InStrRev 関数を使用して最後の引用符を見つけます。 InStrRev 関数は、探していたオブジェクトを返します。

Dim Items As Variant
Dim RequiredStr As Variant

Items = Split(.responsetext, """,""")
RequiredStr = Mid(Items(8), InStrRev(Items(8), """") + 1)

まとめ

VBA を使用すると、ユーザーは外部ライブラリを使用せずに JSON を解析できます。

Microsoft アプリケーションでマクロを作成して、異なる JSON で同じコード行を繰り返し実行できます。 自分に合った適切なソリューションを見つけて、必要なときにいつでも使用できるようにマクロに保存するだけです。