TL;DR — VBA は単独では JSON を読めません。文字列の小技（
Split、InStr、正規表現）は、最初の入れ子のオブジェクトやエスケープされた引用符で壊れます。オープンソースの VBA-JSON パーサー（JsonConverter.bas）を使ってください。これは JSON を三種類のものからなるツリーに変換します。オブジェクトは Dictionary に、配列は Collection に、それ以外はすべて単なる値になります。 コードはそのツリーをたどり、一歩ごとに自分が何を持っているのかを確かめなければなりません。ツリーには独自のルールがあるからです。Collection は 1 から始まり、存在しないキーを読むとそのキーが 追加され、nullは Null になり、長い数値の ID は 文字列 として返ってきます。
Dim root As Object
Set root = JsonConverter.ParseJson("{""name"":""Berlin"",""tags"":[""a"",""b""]}")
Debug.Print root("name") ' Berlin
Debug.Print root("tags")(1) ' a （先頭の要素は 0 ではなく 1）
この記事は、Web のデータを Excel に取り込む シリーズの第二回です。VBA の HTTP リクエスト がテキストを取得し、この記事がそれを構造に変え、VBA の Web スクレイピング が API のないページを扱います。シリーズを貫く考え方は、Web リクエストが渡してくれるのはデータではなくテキストだ ということです。サーバーとセルの間には、あなたが責任を持つ三つの変換があります。バイト列からテキストへ、テキストから構造へ、構造からグリッドへ。 JSON の解析は、その真ん中にあたります。
この記事で学べること
- 考え方の軸：Dictionary と Collection のツリー
- VBA-JSON の準備
- 値の読み取り：Set を付けるか付けないか
- 配列は 1 から始まる
- 存在しないキーと null
- 数値、ID、日付
- レコードをシートに展開する
- ヨーロッパの Windows でも壊れない JSON の書き出し
考え方の軸：Dictionary と Collection のツリー
JSON の形はわずかしかなく、パーサーはそれぞれを VBA にすでにあるものへ対応させます。
| JSON | VBA での姿 | 読み取り方 |
|---|---|---|
オブジェクト { "a": 1 } |
Dictionary |
node("a")、node.Exists("a")、node.Keys |
配列 [1, 2] |
Collection |
node(1)、node.Count、For Each |
文字列 "x" |
String |
そのまま代入 |
数値 3.5 |
Double（長い整数は String） |
そのまま代入 |
true / false |
Boolean |
そのまま代入 |
null |
Null |
IsNull |
つまり、解析したレスポンスは、枝が Dictionary と Collection で、葉が値のツリーです。ツリーの中には、あなたが API に何を期待していたかを覚えているものは何もありません。オブジェクトを期待した場所に API が配列を送ってきたり、数値を期待した場所に null を送ってきたりすれば、ツリーはそのとおりの姿になり、コードは確かめるか、クラッシュするかのどちらかです。必要な技術はこれに尽きます。ツリーをたどり、ノードを使う前にその種類を確かめること。 種類を確かめる道具については VBA の VarType と TypeName を参照してください。
VBA-JSON の準備
- GitHub の VBA-tools/VBA-JSON プロジェクトから
JsonConverter.basをダウンロードします。 - VBA エディターで ファイル > ファイルのインポート を選び、そのファイルを指定します。
JsonConverterという名前のモジュールが現れます。 - ツール > 参照設定 で Microsoft Scripting Runtime にチェックを入れます。パーサーは
Dictionaryオブジェクトを作成するので、この参照がないとコンパイルできません（VBA の参照設定 を参照）。
ScriptControl と JScript で JSON を評価する古いアドバイスを見かけるかもしれません。使わないでください。このコントロールは 32 ビット版の Office にしか存在しないので、今や既定となった 64 ビット版の環境では、どこでもマクロが失敗します。
値の読み取り：Set を付けるか付けないか
枝はオブジェクトで、葉は値です。VBA はこの二つを違う方法で代入します。オブジェクトには Set が必要で、値には付けてはいけません。形がわかっているなら、そのまま書きます。
Dim root As Object, items As Collection
Set root = JsonConverter.ParseJson(body)
Set items = root("items") ' 枝なので Set
Debug.Print items(1)("name") ' 葉
形がわからないとき、たとえばあるときは文字列、あるときはオブジェクトになるフィールドでは、代入する前に IsObject で確かめます。
If IsObject(rec("address")) Then
Set addr = rec("address")
Else
addressText = rec("address")
End If
最上位も確認してください。レコードを中に入れたオブジェクトを返す API（{"items":[...]}）もあれば、裸の配列（[...]）を返す API もあります。どちらなのかは TypeName(root) が教えてくれます。"Dictionary" か "Collection" です。
配列は 1 から始まる
JSON の配列は、ほかのどこでも 0 から始まります。JavaScript でも、Python でも、API のドキュメントでもそうです。VBA-JSON は配列を Collection に格納し、Collection の先頭の要素は 1 です。ドキュメントの items[0] を items(0) に書き換えたコードは「インデックスが有効範囲にありません。」（Subscript out of range）で止まり、For i = 0 To items.Count - 1 と書いたループは、先頭の要素で失敗するうえに最後の要素を飛ばします。間違えるインデックスがそもそもない For Each を使ってください。
Dim rec As Object
For Each rec In root("items")
Debug.Print rec("id"), rec("name")
Next rec
存在しないキーと null
実際の API のレコードはそろっていません。省略可能なフィールドは省かれ、空のフィールドは null として送られてきます。どちらも、予想とは違う振る舞いをします。
存在しないキーを読むと、そのキーが追加されます。 これは Scripting.Dictionary の仕様です。そのキーを持たないレコードで rec("discount") を読んでも失敗はしません。Empty の値でキーを作成し、Empty を返します。コードは API が送ってきてもいないゼロや空文字列のまま処理を続け、レコードには元々なかったキーが増えています。先に確かめてください。
If rec.Exists("discount") Then d = rec("discount") Else d = 0
null は Null になります。 "phone": null として送られたフィールドは存在するので、Exists は True ですが、その値は VBA の Null です。それを String や Double の変数に代入すると「Null の使い方が不正です。」（Invalid use of Null、エラー 94）で止まります。葉は Variant に読み込み、IsNull で判定してください。VBA の IsNull を参照してください。
小さな補助関数を用意すれば、両方の確認を毎行書かずに済みます。
Function JGet(ByVal node As Object, ByVal key As String, Optional ByVal fallback As Variant = Empty) As Variant
JGet = fallback
If Not node.Exists(key) Then Exit Function
If IsObject(node(key)) Then Exit Function
If IsNull(node(key)) Then Exit Function
JGet = node(key)
End Function
数値、ID、日付
数値は Double になります。ただし長いものは例外です。 VBA は有効桁数を 15 桁しか保持できないので、VBA-JSON はとても長い整数（16 桁以上）をすべての桁を保つために 文字列 として格納します。注文番号や口座 ID は、それくらい長いことがよくあります。その結果、同じ列の中で、短い ID は数値として、長い ID はテキストとして届きます。それをシートに書き込むと、Excel は長い数値を 1.23457E+15 と表示し、15 桁しか保持しません。ID は最初からテキストとして扱ってください。書き込む前に列をテキストの書式にし（NumberFormat = "@"）、ID で計算は決してしないでください。
日付は文字列として届きます。 JSON には日付型がないので、API は "2026-10-05T08:30:00Z" のような ISO 8601 形式のテキストを送ってきます。CDate はこの形式を受け付けません。VBA-JSON には JsonConverter.ParseIso があり、これを VBA の Date に ローカルのタイムゾーンで 変換します。表示のためならたいていこのずれが望ましいのですが、同じレコードでも、タイムゾーンの違うマシンでは違う時刻が表示されることになります。時刻を API と正確に一致させる必要があるなら、元の文字列を別の列に残しておいてください。
自分で行う変換は地域設定に従います。 VBA-JSON は、どの Windows でも 3.14 を正しく読み取ります。しかし、API が 文字列として 送ってきた数値を自分で変換すると、CDbl は API の書き方ではなく、その PC の Windows の数値の書き方で読みます。ピリオドが桁区切り記号であるドイツやスペインの Windows では、CDbl("3.14") は 314 を返します。日本の PC では問題なくても、ヨーロッパの同僚の PC で実行すると数値が化けるわけです。API から来たテキストには、常にピリオドを前提とする Val を使ってください。
レコードをシートに展開する
よくある目標は表です。1 レコードを 1 行に、1 フィールドを 1 列にします。これを確実にするルールが二つあります。一つ目は、列は最初のレコードからではなく、API の仕様から決めること。 最初のレコードには省略可能なフィールドが欠けているかもしれないからです。二つ目は、セルごとにではなく、2 次元配列を埋めてから一度に書き込むこと（VBA の 2 次元配列 を参照）。
Sub JsonToSheet(ByVal body As String, ByVal target As Range)
Dim root As Object, rec As Object
Dim fields As Variant, out() As Variant
Dim r As Long, c As Long
Set root = JsonConverter.ParseJson(body)
fields = Array("id", "name", "price", "updated")
ReDim out(0 To root("items").Count, 0 To UBound(fields))
For c = 0 To UBound(fields)
out(0, c) = fields(c) ' 見出し行
Next c
For Each rec In root("items")
r = r + 1
For c = 0 To UBound(fields)
out(r, c) = JGet(rec, fields(c)) ' 欠落・null・入れ子 -> Empty
Next c
Next rec
target.Resize(UBound(out, 1) + 1, UBound(fields) + 1).Value = out
End Sub
欠落したフィールドも、null の値も、入れ子のオブジェクトも、エラーや勝手に作られたキーではなく、空のセルになります。address オブジェクトのような入れ子のデータについては、意図して決めてください。2 回目の参照で address.city のような列を追加するか、親の ID をつなぎにして、入れ子のレコードを 2 枚目のシートに書き出すかです。
ヨーロッパの Windows でも壊れない JSON の書き出し
JSON を送るときは、Dictionary を組み立て、テキストはライブラリに書かせます。
Dim order As New Dictionary
order("sku") = "A-100"
order("qty") = 3
order("price") = 12.5
body = JsonConverter.ConvertToJson(order) ' {"sku":"A-100","qty":3,"price":12.5}
JSON を手作業で継ぎ合わせてはいけません。テキスト中の引用符や改行のエスケープ漏れに加えて、ドイツ、フランス、スペインの Windows では、VBA が数値を文字列に連結するときに 12.5 を 12,5 に変えてしまい、JSON が不正になります。手で組み立てた本文は、開発者のマシンでは動き、ユーザーのマシン（たとえばドイツやフランスの同僚の PC）では失敗します。ライブラリは小数点を正しく書き、テキストを正しくエスケープし、VBA の日付を ISO 形式の文字列として書き出します。結果は VBA の HTTP リクエスト で紹介している POST で送ってください。
判断の分かれ目：パーサーで解析し、すべてのノードを確かめる
ここには判断が二つあります。一つ目は、JSON を手作業で解析しないことです。Split と InStr はサンプルのレスポンスでは動き、文字列の中の最初のカンマ、最初の入れ子のオブジェクト、最初のエスケープされた引用符で壊れます。本物のパーサーは、モジュールを一つインポートするだけです。
二つ目は、解析したツリーを信用しないことです。どの枝も欠けている可能性があり、どの葉も null の可能性があり、どの ID もテキストの可能性があります。確認は JGet のような一つの補助関数にまとめ、列は仕様から決めてください。そうすれば、マクロはサンプルと合わないレコードにも耐えられます。
そして、何かを書く前に、そもそも VBA が必要かどうかを考えてください。仕事が「この API を更新のたびに表に取り込む」なら、Power Query の Web から は JSON を読み、レコードを列に展開し、コードを一行も書かずに更新できます。VBA を使うのは、呼び出しの周りにロジックがあるときです。POST、結果のページ送り、あるいは書き戻しなどです。
ExcelMaster の活用
JSON のコードはサンプルのレスポンスに合わせて書かれ、そのあとで本物に出会います。省略可能なフィールドが欠けている、価格に null が入っている、ID が数値にするには長すぎる。
ExcelMaster は解析コードを書く前に実際のレスポンスを読み、各フィールドを意図して列に対応させ、存在しないキー、null、長い ID を実行時エラーに任せずに処理します。
よくある質問
Excel VBA で JSON を解析するには？
VBA-JSON プロジェクトの JsonConverter.bas をインポートし、Microsoft Scripting Runtime への参照を追加してから、Set root = JsonConverter.ParseJson(text) を呼びます。オブジェクトは Dictionary に、配列は Collection になります。
JSON の配列を読むと「インデックスが有効範囲にありません。」になるのはなぜですか？
VBA-JSON は配列を Collection に格納し、その先頭の要素は 0 ではなく 1 だからです。先頭の要素には items(1) を使うか、For Each でループしてください。
解析した JSON にキーが存在するかを確かめるには？
node.Exists("key") を使います。確かめるためにキーを読んではいけません。Dictionary で存在しないキーを読むと、Empty の値でそのキーが追加されてしまいます。
JSON でエラー 94「Null の使い方が不正です。」が出るのはなぜですか？
そのフィールドが null として送られ、VBA-JSON がそれを Null に変換したからです。Variant に読み込み、型付きの変数に代入する前に IsNull で判定してください。
VBA の Dictionary を JSON に変換するには？
JsonConverter.ConvertToJson(dict) を呼びます。テキストをエスケープし、どの地域設定でも小数点を正しく書き、日付を ISO 形式の文字列に変換します。
検証環境
検証環境: Excel 365 (Windows 11)、VBA 7.1、VBA-JSON 2.3.1 — 最終確認 2026-10-05。
関連ガイド： VBA の HTTP リクエスト · VBA の Web スクレイピング · VBA の Dictionary · VBA の Collection · VBA の IsNull · VBA の VarType と TypeName · VBA の 2 次元配列 · VBA の参照設定