VBA JSON dans Excel — transformer une réponse d'API en cellules avec VBA-JSON (Dictionary, Collection, Null)

TL;DR — VBA ne sait pas lire le JSON tout seul, et les astuces de chaînes (Split, InStr, regex) cassent au premier objet imbriqué ou au premier guillemet échappé. Utilisez l'analyseur open source VBA-JSON (JsonConverter.bas). Il transforme le JSON en un arbre fait de trois choses : les objets deviennent des Dictionary, les tableaux des Collection, et tout le reste une simple valeur. Votre code parcourt cet arbre et, à chaque étape, doit se demander ce qu'il a entre les mains, car l'arbre a ses propres règles : les Collection commencent à 1, lire une clé absente l'ajoute, null devient Null, et les identifiants numériques longs reviennent sous forme de chaînes.

Dim root As Object
Set root = JsonConverter.ParseJson("{""name"":""Berlin"",""tags"":[""a"",""b""]}")
Debug.Print root("name")          ' Berlin
Debug.Print root("tags")(1)       ' a    (le premier element est 1, pas 0)

Voici le deuxième article d'une série consacrée à faire entrer des données du web dans Excel : la requête HTTP en VBA récupère le texte, cet article le transforme en structure, et le web scraping en VBA couvre les pages sans API. L'idée de la série : une requête web vous remet du texte, pas des données. Entre le serveur et vos cellules se trouvent trois traductions qui vous appartiennent : des octets au texte, du texte à la structure, de la structure à la grille. L'analyse du JSON est celle du milieu.

Ce que vous allez apprendre

• Le modèle mental : un arbre de Dictionary et de Collection
• Installer VBA-JSON
• Lire les valeurs : avec ou sans Set
• Les tableaux commencent à 1
• Clés absentes et null
• Nombres, identifiants et dates
• Aplatir des enregistrements dans une feuille
• Écrire du JSON sans le casser sur un Windows européen

Le modèle mental : un arbre de Dictionary et de Collection

Le JSON n'a que quelques formes, et l'analyseur fait correspondre chacune à quelque chose que VBA possède déjà :

Une réponse analysée est donc un arbre dont les branches sont des Dictionary et des Collection, et dont les feuilles sont des valeurs. Rien dans l'arbre ne garde la trace de ce que vous attendiez de l'API. Si l'API envoie un tableau là où vous attendiez un objet, ou null là où vous attendiez un nombre, l'arbre le dit, et votre code soit pose la question, soit plante. Tout le savoir-faire est là : parcourez l'arbre et vérifiez la nature du nœud avant de l'utiliser. Voir VBA VarType et TypeName pour les outils qui posent la question.

Installer VBA-JSON

1. Téléchargez JsonConverter.bas depuis le projet VBA-tools/VBA-JSON sur GitHub.
2. Dans l'éditeur VBA, Fichier > Importer un fichier et sélectionnez-le. Un module nommé JsonConverter apparaît.
3. Outils > Références, cochez Microsoft Scripting Runtime. L'analyseur crée des objets Dictionary et ne compile pas sans cette référence (voir les références VBA).

Vous trouverez peut-être d'anciens conseils qui évaluent le JSON avec ScriptControl et JScript. Ne les suivez pas : ce contrôle n'existe que dans Office 32 bits, si bien que la macro échoue sur toutes les installations 64 bits, qui sont désormais la norme.

Lire les valeurs : avec ou sans Set

Une branche est un objet et une feuille est une valeur, et VBA ne les affecte pas de la même façon : les objets exigent Set, les valeurs ne doivent pas l'avoir. Quand vous connaissez la forme, écrivez-la directement :

Dim root As Object, items As Collection
Set root = JsonConverter.ParseJson(body)
Set items = root("items")                 ' une branche : Set
Debug.Print items(1)("name")              ' une feuille

Quand vous ne connaissez pas la forme, par exemple pour un champ qui est tantôt une chaîne, tantôt un objet, posez la question avec IsObject avant d'affecter :

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

Vérifiez aussi le niveau supérieur. Certaines API renvoient un objet qui contient les enregistrements ({"items":[...]}), d'autres un tableau nu ([...]). TypeName(root) vous dit lequel : "Dictionary" ou "Collection".

Les tableaux commencent à 1

Partout ailleurs, un tableau JSON commence à zéro : en JavaScript, en Python, dans la documentation de l'API. VBA-JSON le stocke dans une Collection, et le premier élément d'une Collection est 1. Un code transposé de items[0] dans la documentation vers items(0) s'arrête sur « L'indice n'appartient pas à la sélection », et une boucle écrite For i = 0 To items.Count - 1 échoue sur le premier élément et saute le dernier. Préférez For Each, qui n'a pas d'indice à rater :

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

Clés absentes et null

Les enregistrements d'une vraie API ne sont pas uniformes. Les champs facultatifs sont omis, et les champs vides sont envoyés sous forme de null. Les deux se comportent autrement que vous ne le pensez peut-être.

Lire une clé absente l'ajoute. C'est ainsi que fonctionne Scripting.Dictionary : rec("discount") sur un enregistrement sans cette clé n'échoue pas ; il crée la clé avec une valeur Empty et renvoie Empty. Votre code continue avec un zéro ou une chaîne vide que l'API n'a jamais envoyés, et l'enregistrement possède maintenant une clé qu'il n'avait pas. Posez d'abord la question :

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

null devient Null. Un champ envoyé sous la forme "phone": null est présent, donc Exists vaut True, mais sa valeur est le Null de VBA. L'affecter à une variable String ou Double s'arrête sur « Utilisation incorrecte de Null » (erreur 94). Lisez les feuilles dans un Variant et testez-les avec IsNull ; voir VBA IsNull.

Une petite fonction d'aide sort les deux vérifications de chaque ligne :

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

Nombres, identifiants et dates

Les nombres deviennent des Double, sauf les longs. VBA ne conserve que 15 chiffres significatifs : VBA-JSON stocke donc les entiers très longs (16 chiffres ou plus) sous forme de chaînes pour garder chaque chiffre. Les numéros de commande et les identifiants de compte atteignent souvent cette longueur. Résultat : dans une même colonne, les identifiants courts arrivent comme des nombres et les longs comme du texte. Quand vous les écrivez dans une feuille, Excel affiche une longue valeur numérique sous la forme 1.23457E+15 et ne garde que 15 chiffres. Traitez les identifiants comme du texte dès le départ : mettez la colonne au format texte (NumberFormat = "@") avant d'écrire, et ne faites jamais de calcul dessus.

Les dates arrivent sous forme de chaînes. Le JSON n'a pas de type date ; les API envoient du texte ISO 8601 comme "2026-10-05T08:30:00Z". CDate n'accepte pas ce format. VBA-JSON inclut JsonConverter.ParseIso, qui le convertit en Date VBA dans votre fuseau horaire local. Ce décalage est en général ce que vous voulez pour l'affichage, mais il signifie que le même enregistrement affiche des heures différentes sur des machines situées dans des fuseaux différents. Si l'heure doit correspondre exactement à celle de l'API, conservez la chaîne d'origine dans une autre colonne.

Vos propres conversions suivent les paramètres régionaux. VBA-JSON lit 3.14 correctement sur n'importe quel Windows. Mais si vous prenez un nombre que l'API a envoyé sous forme de chaîne et que vous le convertissez vous-même, CDbl le lit comme votre Windows écrit les nombres, et non comme l'API les écrit. Sur un Windows allemand ou espagnol, où le point sert de séparateur de milliers, CDbl("3.14") donne même 314. Utilisez Val, qui attend toujours un point, pour le texte venu d'une API.

Aplatir des enregistrements dans une feuille

L'objectif habituel est un tableau : une ligne par enregistrement, une colonne par champ. Deux règles rendent cela fiable. D'abord, choisissez les colonnes d'après le contrat de l'API, pas d'après le premier enregistrement, car celui-ci peut manquer de champs facultatifs. Ensuite, remplissez un tableau 2D et écrivez-le en une fois, pas cellule par cellule (voir les tableaux 2D en VBA).

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)                         ' ligne d'en-tete
    Next c
    For Each rec In root("items")
        r = r + 1
        For c = 0 To UBound(fields)
            out(r, c) = JGet(rec, fields(c))          ' absent, null, imbrique -> Empty
        Next c
    Next rec

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

Champs absents, valeurs null et objets imbriqués deviennent tous des cellules vides plutôt que des erreurs ou des clés inventées. Pour les données imbriquées, comme un objet address, décidez délibérément : ajoutez des colonnes comme address.city avec une seconde lecture, ou écrivez les enregistrements imbriqués dans une seconde feuille avec l'identifiant du parent comme lien.

Écrire du JSON sans le casser sur un Windows européen

Pour envoyer du JSON, construisez un Dictionary et laissez la bibliothèque écrire le texte :

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}

N'assemblez pas le JSON à la main. Outre l'oubli d'échappement des guillemets et des sauts de ligne dans le texte, VBA transforme 12.5 en 12,5 sur votre Windows français (comme sur un Windows allemand ou espagnol) dès qu'il insère un nombre dans une chaîne, ce qui rend le JSON invalide. Un corps construit à la main fonctionne sur la machine du développeur et échoue sur celle de l'utilisateur. La bibliothèque écrit le point décimal et échappe le texte correctement, et elle écrit les dates VBA sous forme de chaînes ISO. Envoyez le résultat avec le POST présenté dans la requête HTTP en VBA.

Le parti pris : analyser avec un analyseur, puis vérifier chaque nœud

Il y a ici deux décisions. La première : ne pas analyser le JSON à la main. Split et InStr fonctionnent sur la réponse d'exemple et cassent à la première virgule à l'intérieur d'une chaîne, au premier objet imbriqué ou au premier guillemet échappé. Un véritable analyseur, c'est un seul module importé.

La seconde : traiter l'arbre analysé comme non fiable. Chaque branche peut manquer, chaque feuille peut valoir null, et chaque identifiant peut être du texte. Mettez les vérifications dans une seule fonction d'aide comme JGet, choisissez vos colonnes d'après le contrat, et la macro survit à l'enregistrement qui ne ressemble pas à l'exemple.

Et avant d'écrire quoi que ce soit, demandez-vous si vous avez vraiment besoin de VBA. Si la tâche consiste à « importer cette API dans un tableau à chaque actualisation », Données > À partir du web de Power Query lit le JSON, développe les enregistrements en colonnes et s'actualise sans une ligne de code. Utilisez VBA quand il y a de la logique autour de l'appel : un POST, la pagination des résultats ou une réécriture vers l'API.

Comment ExcelMaster aide

Le code JSON est écrit d'après une réponse d'exemple, puis rencontre la vraie : un champ facultatif absent, un null dans un prix, un identifiant trop long pour un nombre.

ExcelMaster lit la réponse réelle avant d'écrire le code d'analyse, associe délibérément chaque champ à une colonne, et traite les clés absentes, les null et les identifiants longs au lieu de les abandonner à une erreur d'exécution.

Questions fréquentes

Comment analyser du JSON en Excel VBA ?

Importez JsonConverter.bas depuis le projet VBA-JSON, ajoutez une référence à Microsoft Scripting Runtime, puis appelez Set root = JsonConverter.ParseJson(text). Les objets deviennent des Dictionary et les tableaux des Collection.

Pourquoi obtient-on « L'indice n'appartient pas à la sélection » en lisant un tableau JSON ?

VBA-JSON stocke les tableaux dans une Collection, dont le premier élément est 1, pas 0. Utilisez items(1) pour le premier élément, ou bouclez avec For Each.

Comment vérifier si une clé existe dans un JSON analysé ?

Utilisez node.Exists("key"). Ne lisez pas la clé pour la tester : lire une clé absente d'un Dictionary l'ajoute avec une valeur Empty.

Pourquoi obtient-on l'erreur 94 Utilisation incorrecte de Null avec du JSON ?

Le champ a été envoyé sous forme de null, que VBA-JSON transforme en Null. Lisez-le dans un Variant et testez IsNull avant de l'affecter à une variable typée.

Comment convertir un Dictionary VBA en JSON ?

Appelez JsonConverter.ConvertToJson(dict). La fonction échappe le texte, écrit correctement les points décimaux quels que soient les paramètres régionaux et convertit les dates en chaînes ISO.

Testé dans

Testé dans : Excel 365 (Windows 11), VBA 7.1, VBA-JSON 2.3.1 — vérifié le 05/10/2026.

Guides connexes : Requête HTTP en VBA · Web scraping en VBA · VBA Dictionary · VBA Collection · VBA IsNull · VBA VarType et TypeName · Tableaux 2D en VBA · Références VBA