VBA JSON in Excel — eine API-Antwort mit VBA-JSON in Zellen parsen (Dictionary, Collection, Null)

TL;DR — VBA kann JSON nicht von sich aus lesen, und String-Tricks (Split, InStr, reguläre Ausdrücke) scheitern am ersten verschachtelten Objekt oder maskierten Anführungszeichen. Verwenden Sie den Open-Source- Parser VBA-JSON (JsonConverter.bas). Er verwandelt JSON in einen Baum aus drei Dingen: Objekte werden zu Dictionaries, Arrays zu Collections, und alles andere wird zu einem einfachen Wert. Ihr Code durchläuft diesen Baum und muss bei jedem Schritt fragen, was er gerade in der Hand hält, denn der Baum hat eigene Regeln: Collections beginnen bei 1, das Lesen eines fehlenden Schlüssels fügt ihn hinzu, null wird zu Null, und lange numerische IDs kommen als Strings zurück.

Dim root As Object
Set root = JsonConverter.ParseJson("{""name"":""Berlin"",""tags"":[""a"",""b""]}")
Debug.Print root("name")          ' Berlin
Debug.Print root("tags")(1)       ' a    (erstes Element ist 1, nicht 0)

Dies ist der zweite Artikel einer Reihe darüber, Daten aus dem Web nach Excel zu holen: VBA HTTP Request holt den Text, dieser Artikel macht daraus eine Struktur, und VBA Web Scraping behandelt Seiten ohne API. Die Idee der Reihe: Eine Web-Anfrage liefert Ihnen Text, keine Daten. Zwischen dem Server und Ihren Zellen liegen drei Übersetzungen, für die Sie verantwortlich sind: Bytes zu Text, Text zu Struktur, Struktur zu Raster. Das JSON-Parsen ist die mittlere.

Was Sie lernen

• Das mentale Modell: ein Baum aus Dictionaries und Collections
• VBA-JSON einrichten
• Werte lesen: mit Set oder ohne Set
• Arrays beginnen bei 1
• Fehlende Schlüssel und null
• Zahlen, IDs und Datumsangaben
• Datensätze flach in ein Blatt schreiben
• JSON schreiben, ohne dass es auf einem deutschen Windows kaputtgeht

Das mentale Modell: ein Baum aus Dictionaries und Collections

JSON kennt nur wenige Formen, und der Parser bildet jede auf etwas ab, das VBA bereits hat:

Eine geparste Antwort ist also ein Baum, dessen Äste Dictionaries und Collections sind und dessen Blätter Werte. Nichts in diesem Baum weiß, was Sie von der API erwartet haben. Schickt die API ein Array, wo Sie ein Objekt erwartet haben, oder null, wo Sie eine Zahl erwartet haben, dann steht genau das im Baum, und Ihr Code fragt entweder nach oder stürzt ab. Darin besteht die ganze Kunst: den Baum durchlaufen und die Art des Knotens prüfen, bevor Sie ihn verwenden. Die Werkzeuge zum Nachfragen finden Sie unter VBA VarType und TypeName.

VBA-JSON einrichten

1. Laden Sie JsonConverter.bas aus dem Projekt VBA-tools/VBA-JSON auf GitHub herunter.
2. Wählen Sie im VBA-Editor Datei > Datei importieren und wählen Sie die Datei aus. Ein Modul namens JsonConverter erscheint.
3. Unter Extras > Verweise setzen Sie das Häkchen bei Microsoft Scripting Runtime. Der Parser erzeugt Dictionary-Objekte und lässt sich ohne diesen Verweis nicht kompilieren (siehe VBA-Verweise).

Vielleicht stoßen Sie auf ältere Ratschläge, die JSON mit ScriptControl und JScript auswerten. Verwenden Sie das nicht: Dieses Steuerelement gibt es nur in 32-Bit-Office, also scheitert das Makro auf jeder 64-Bit- Installation, und die ist heute der Standard.

Werte lesen: mit Set oder ohne Set

Ein Ast ist ein Objekt und ein Blatt ist ein Wert, und VBA weist beide unterschiedlich zu: Objekte brauchen Set, Werte dürfen es nicht haben. Wenn Sie die Form kennen, schreiben Sie sie direkt:

Dim root As Object, items As Collection
Set root = JsonConverter.ParseJson(body)
Set items = root("items")                 ' ein Ast: Set
Debug.Print items(1)("name")              ' ein Blatt

Wenn Sie die Form nicht kennen, etwa bei einem Feld, das mal ein String und mal ein Objekt ist, fragen Sie vor der Zuweisung mit IsObject nach:

If IsObject(rec("address")) Then
    Set addr = rec("address")
Else
    addressText = rec("address")
End If

Prüfen Sie auch die oberste Ebene. Manche APIs liefern ein Objekt mit den Datensätzen darin ({"items":[...]}), andere ein nacktes Array ([...]). TypeName(root) sagt Ihnen, welches: "Dictionary" oder "Collection".

Arrays beginnen bei 1

Ein JSON-Array ist überall sonst nullbasiert: in JavaScript, in Python, in der API-Dokumentation. VBA-JSON legt es in einer Collection ab, und das erste Element einer Collection ist 1. Code, der aus dem items[0] der Dokumentation ein items(0) macht, bricht mit Index außerhalb des gültigen Bereichs ab, und eine Schleife der Form For i = 0 To items.Count - 1 scheitert am ersten Element und überspringt obendrein das letzte. Nehmen Sie lieber For Each, da gibt es keinen Index, den man falsch setzen kann:

Dim rec As Object
For Each rec In root("items")
    Debug.Print rec("id"), rec("name")
Next rec

Fehlende Schlüssel und null

Echte API-Datensätze sind nicht einheitlich. Optionale Felder werden weggelassen, leere Felder werden als null gesendet. Beides verhält sich anders, als Sie vielleicht erwarten.

Das Lesen eines fehlenden Schlüssels fügt ihn hinzu. So funktioniert Scripting.Dictionary: rec("discount") auf einem Datensatz ohne diesen Schlüssel schlägt nicht fehl; es legt den Schlüssel mit dem Wert Empty an und gibt Empty zurück. Ihr Code rechnet mit einer Null oder einem leeren String weiter, den die API nie gesendet hat, und der Datensatz hat jetzt einen Schlüssel, den er vorher nicht hatte. Fragen Sie zuerst:

If rec.Exists("discount") Then d = rec("discount") Else d = 0

null wird zu Null. Ein Feld, das als "phone": null gesendet wird, ist vorhanden, also ist Exists True, aber sein Wert ist das Null von VBA. Die Zuweisung an eine String- oder Double-Variable bricht mit Unzulässige Verwendung von Null (Fehler 94) ab. Lesen Sie Blätter in einen Variant und prüfen Sie mit IsNull; siehe VBA IsNull.

Eine kleine Hilfsfunktion hält beide Prüfungen aus jeder einzelnen Zeile heraus:

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

Zahlen, IDs und Datumsangaben

Zahlen werden zu Double, außer lange. VBA behält nur 15 signifikante Stellen, deshalb speichert VBA-JSON sehr lange Ganzzahlen (16 Stellen oder mehr) als Strings, damit keine Ziffer verloren geht. Bestellnummern und Konto-IDs sind oft so lang. Das Ergebnis: In derselben Spalte kommen kurze IDs als Zahlen und lange als Text an. Wenn Sie sie in ein Blatt schreiben, zeigt Excel einen langen Zahlenwert als 1.23457E+15 an und behält nur 15 Stellen. Behandeln Sie IDs von Anfang an als Text: Formatieren Sie die Spalte vor dem Schreiben als Text (NumberFormat = "@") und rechnen Sie nie mit ihnen.

Datumsangaben kommen als Strings an. JSON kennt keinen Datumstyp; APIs senden ISO-8601-Text wie "2026-10-05T08:30:00Z". CDate akzeptiert dieses Format nicht. VBA-JSON enthält JsonConverter.ParseIso, das ihn in ein VBA-Date umwandelt, in Ihrer lokalen Zeitzone. Diese Verschiebung ist für die Anzeige meist gewollt, bedeutet aber, dass derselbe Datensatz auf Rechnern in verschiedenen Zeitzonen verschiedene Uhrzeiten zeigt. Wenn die Uhrzeit exakt mit der API übereinstimmen muss, behalten Sie den Original-String in einer weiteren Spalte.

Ihre eigenen Umwandlungen folgen den Regionaleinstellungen. VBA-JSON liest 3.14 auf jedem Windows korrekt. Wenn Sie aber eine Zahl, die die API als String gesendet hat, selbst umwandeln, liest CDbl sie so, wie Ihr Windows Zahlen schreibt, nicht so, wie die API sie schreibt. Auf Ihrem deutschen Windows ist der Punkt das Tausendertrennzeichen, und CDbl("3.14") gibt Ihnen 314. Verwenden Sie für Text, der von einer API kommt, Val, das immer einen Punkt erwartet.

Datensätze flach in ein Blatt schreiben

Das übliche Ziel ist eine Tabelle: eine Zeile pro Datensatz, eine Spalte pro Feld. Zwei Regeln machen das zuverlässig. Erstens: Wählen Sie die Spalten nach dem Vertrag der API, nicht nach dem ersten Datensatz, denn gerade dem ersten Datensatz können optionale Felder fehlen. Zweitens: Füllen Sie ein 2D-Array und schreiben Sie es in einem Rutsch, nicht Zelle für Zelle (siehe VBA 2D-Arrays).

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)                         ' Kopfzeile
    Next c
    For Each rec In root("items")
        r = r + 1
        For c = 0 To UBound(fields)
            out(r, c) = JGet(rec, fields(c))          ' fehlend, null, verschachtelt -> Empty
        Next c
    Next rec

    target.Resize(UBound(out, 1) + 1, UBound(fields) + 1).Value = out
End Sub

Fehlende Felder, null-Werte und verschachtelte Objekte werden alle zu leeren Zellen statt zu Fehlern oder erfundenen Schlüsseln. Bei verschachtelten Daten wie einem address-Objekt entscheiden Sie bewusst: Fügen Sie mit einem zweiten Zugriff Spalten wie address.city hinzu, oder schreiben Sie die verschachtelten Datensätze in ein zweites Blatt, mit der ID des übergeordneten Datensatzes als Verknüpfung.

JSON schreiben, ohne dass es auf einem deutschen Windows kaputtgeht

Um JSON zu senden, bauen Sie ein Dictionary und lassen die Bibliothek den Text schreiben:

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}

Kleben Sie JSON nicht von Hand zusammen. Abgesehen von fehlenden Maskierungen für Anführungszeichen und Zeilenumbrüche in Texten macht VBA auf Ihrem deutschen Windows (ebenso auf einem französischen oder spanischen) aus 12.5 ein 12,5, sobald es eine Zahl in einen String einfügt, und damit ist das JSON ungültig. Ein von Hand gebauter Body funktioniert auf dem Rechner des Entwicklers und scheitert auf dem des Benutzers. Die Bibliothek schreibt den Dezimalpunkt und maskiert Text korrekt, und sie schreibt VBA-Datumswerte als ISO-Strings. Senden Sie das Ergebnis mit dem POST aus VBA HTTP Request.

Die Abwägung: mit einem Parser parsen, dann jeden Knoten prüfen

Hier gibt es zwei Entscheidungen. Die erste: JSON nicht von Hand parsen. Split und InStr funktionieren mit der Beispielantwort und scheitern am ersten Komma innerhalb eines Strings, am ersten verschachtelten Objekt oder am ersten maskierten Anführungszeichen. Ein echter Parser ist ein einziges importiertes Modul.

Die zweite: den geparsten Baum als nicht vertrauenswürdig behandeln. Jeder Ast kann fehlen, jedes Blatt kann null sein, und jede ID kann Text sein. Bündeln Sie die Prüfungen in einer Hilfsfunktion wie JGet, wählen Sie Ihre Spalten nach dem Vertrag, und das Makro übersteht auch den Datensatz, der nicht zum Beispiel passt.

Und bevor Sie irgendetwas davon schreiben, fragen Sie sich, ob Sie VBA überhaupt brauchen. Lautet die Aufgabe „diese API bei jeder Aktualisierung in eine Tabelle holen“, dann liest Power Query mit Daten > Aus dem Web JSON, klappt Datensätze in Spalten auf und aktualisiert ohne eine Zeile Code. Nehmen Sie VBA, wenn um den Aufruf herum Logik nötig ist: ein POST, das Blättern durch Ergebnisseiten oder das Zurückschreiben.

Wie ExcelMaster hilft

JSON-Code wird gegen eine Beispielantwort geschrieben und trifft dann auf die echte: ein fehlendes optionales Feld, ein null im Preis, eine ID, die für eine Zahl zu lang ist.

ExcelMaster liest die tatsächliche Antwort, bevor es den Parsing-Code schreibt, ordnet jedes Feld bewusst einer Spalte zu und behandelt fehlende Schlüssel, Nulls und lange IDs, statt sie einem Laufzeitfehler zu überlassen.

Häufig gestellte Fragen

Wie parse ich JSON in Excel VBA?

Importieren Sie JsonConverter.bas aus dem Projekt VBA-JSON, fügen Sie einen Verweis auf Microsoft Scripting Runtime hinzu und rufen Sie dann Set root = JsonConverter.ParseJson(text) auf. Objekte werden zu Dictionaries und Arrays zu Collections.

Warum erhalte ich Index außerhalb des gültigen Bereichs beim Lesen eines JSON-Arrays?

VBA-JSON legt Arrays in einer Collection ab, deren erstes Element 1 ist, nicht 0. Verwenden Sie items(1) für das erste Element oder durchlaufen Sie das Array mit For Each.

Wie prüfe ich, ob ein Schlüssel im geparsten JSON existiert?

Verwenden Sie node.Exists("key"). Lesen Sie den Schlüssel nicht, um ihn zu testen: Das Lesen eines fehlenden Schlüssels aus einem Dictionary fügt ihn mit dem Wert Empty hinzu.

Warum erhalte ich bei JSON Fehler 94 Unzulässige Verwendung von Null?

Das Feld wurde als null gesendet, und VBA-JSON macht daraus Null. Lesen Sie es in einen Variant und prüfen Sie IsNull, bevor Sie es einer typisierten Variablen zuweisen.

Wie wandle ich ein VBA-Dictionary in JSON um?

Rufen Sie JsonConverter.ConvertToJson(dict) auf. Es maskiert Text, schreibt Dezimalpunkte unabhängig von den Regionaleinstellungen korrekt und wandelt Datumswerte in ISO-Strings um.

Getestet in

Getestet in: Excel 365 (Windows 11), VBA 7.1, VBA-JSON 2.3.1 — zuletzt geprüft am 05.10.2026.

Verwandte Anleitungen: VBA HTTP Request · VBA Web Scraping · VBA Dictionary · VBA Collection · VBA IsNull · VBA VarType und TypeName · VBA 2D-Array · VBA-Verweise