VBA JSON en Excel — convertir la respuesta de una API en celdas con VBA-JSON (Dictionary, Collection, Null)

TL;DR — VBA no sabe leer JSON por sí solo, y los trucos con cadenas (Split, InStr, expresiones regulares) se rompen con el primer objeto anidado o la primera comilla escapada. Usa el analizador de código abierto VBA-JSON (JsonConverter.bas). Convierte el JSON en un árbol de tres cosas: los objetos pasan a ser Dictionaries, los arrays pasan a ser Collections y todo lo demás pasa a ser un valor simple. Tu código recorre ese árbol y, en cada paso, tiene que preguntar qué tiene entre manos, porque el árbol tiene sus propias reglas: las Collections empiezan en 1, leer una clave que no existe la añade, null se convierte en Null y los ID numéricos largos vuelven como cadenas.

Dim root As Object
Set root = JsonConverter.ParseJson("{""name"":""Berlin"",""tags"":[""a"",""b""]}")
Debug.Print root("name")          ' Berlin
Debug.Print root("tags")(1)       ' a    (el primer elemento es 1, no 0)

Este es el segundo artículo de una serie sobre cómo traer datos de la web a Excel: VBA HTTP request obtiene el texto, este artículo lo convierte en una estructura y VBA web scraping se ocupa de las páginas que no tienen API. La idea de la serie es que una petición web te entrega texto, no datos. Entre el servidor y tus celdas hay tres traducciones que son responsabilidad tuya: de bytes a texto, de texto a estructura y de estructura a cuadrícula. Analizar el JSON es la del medio.

Lo que aprenderás

• El modelo mental: un árbol de Dictionaries y Collections
• Configurar VBA-JSON
• Leer valores: con Set o sin Set
• Los arrays empiezan en 1
• Claves que faltan y null
• Números, ID y fechas
• Volcar registros en una hoja
• Escribir JSON sin que se rompa en un Windows europeo

El modelo mental: un árbol de Dictionaries y Collections

JSON solo tiene unas pocas formas, y el analizador asigna cada una a algo que VBA ya tiene:

Así que una respuesta analizada es un árbol cuyas ramas son Dictionaries y Collections y cuyas hojas son valores. Nada en el árbol recuerda lo que esperabas que enviara la API. Si la API envía un array donde esperabas un objeto, o null donde esperabas un número, el árbol lo refleja, y tu código o pregunta o se estrella. Esa es toda la habilidad: recorre el árbol y comprueba el tipo de nodo antes de usarlo. Consulta VBA VarType y TypeName para las herramientas que hacen esa pregunta.

Configurar VBA-JSON

1. Descarga JsonConverter.bas del proyecto VBA-tools/VBA-JSON en GitHub.
2. En el editor de VBA, Archivo > Importar archivo y selecciónalo. Aparece un módulo llamado JsonConverter.
3. Herramientas > Referencias, marca Microsoft Scripting Runtime. El analizador crea objetos Dictionary y no compila sin ella (consulta referencias en VBA).

Puede que encuentres consejos antiguos que evalúan el JSON con ScriptControl y JScript. No los sigas: ese control solo existe en Office de 32 bits, así que la macro falla en todas las instalaciones de 64 bits, que hoy son las predeterminadas.

Leer valores: con Set o sin Set

Una rama es un objeto y una hoja es un valor, y VBA los asigna de forma distinta: los objetos necesitan Set y los valores no deben llevarlo. Cuando conoces la forma, escríbelo directamente:

Dim root As Object, items As Collection
Set root = JsonConverter.ParseJson(body)
Set items = root("items")                 ' una rama: Set
Debug.Print items(1)("name")              ' una hoja

Cuando no conoces la forma, por ejemplo un campo que unas veces es una cadena y otras un objeto, pregunta con IsObject antes de asignar:

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

Comprueba también el nivel superior. Algunas API devuelven un objeto con los registros dentro ({"items":[...]}), otras devuelven un array sin más ([...]). TypeName(root) te dice cuál: "Dictionary" o "Collection".

Los arrays empiezan en 1

Un array JSON empieza en cero en todas partes: en JavaScript, en Python, en la documentación de la API. VBA-JSON lo guarda en una Collection, y el primer elemento de una Collection es el 1. El código que traduce el items[0] de la documentación a items(0) se detiene con El subíndice está fuera del intervalo, y un bucle escrito como For i = 0 To items.Count - 1 falla en el primer elemento y además se salta el último. Mejor usa For Each, que no tiene índice con el que equivocarse:

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

Claves que faltan y null

Los registros de una API real no son uniformes. Los campos opcionales se omiten y los campos vacíos se envían como null. Ninguno de los dos casos se comporta como cabría esperar.

Leer una clave que falta la añade. Así funciona Scripting.Dictionary: rec("discount") sobre un registro sin esa clave no falla; crea la clave con un valor Empty y devuelve Empty. Tu código sigue adelante con un cero o una cadena vacía que la API nunca envió, y el registro tiene ahora una clave que antes no tenía. Pregunta primero:

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

null se convierte en Null. Un campo enviado como "phone": null está presente, así que Exists es True, pero su valor es el Null de VBA. Asignarlo a una variable String o Double se detiene con Uso no válido de Null (error 94). Lee las hojas en un Variant y compruébalas con IsNull; consulta VBA IsNull.

Una pequeña función auxiliar saca las dos comprobaciones de cada línea:

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

Números, ID y fechas

Los números se convierten en Double, salvo los largos. VBA solo conserva 15 cifras significativas, así que VBA-JSON guarda los enteros muy largos (16 cifras o más) como cadenas para no perder ninguna cifra. Los números de pedido y los ID de cuenta suelen ser así de largos. El resultado: en la misma columna, los ID cortos llegan como números y los largos como texto. Al escribirlos en una hoja, Excel muestra un valor numérico largo como 1.23457E+15 y conserva solo 15 cifras. Trata los ID como texto desde el principio: da formato de texto a la columna (NumberFormat = "@") antes de escribir, y no hagas nunca operaciones aritméticas con ellos.

Las fechas llegan como cadenas. JSON no tiene tipo fecha; las API envían texto ISO 8601 como "2026-10-05T08:30:00Z". CDate no acepta ese formato. VBA-JSON incluye JsonConverter.ParseIso, que lo convierte en un Date de VBA en tu zona horaria local. Ese desplazamiento suele ser lo que quieres para mostrarlo, pero significa que el mismo registro muestra horas distintas en equipos de zonas horarias distintas. Si la hora tiene que coincidir exactamente con la de la API, guarda la cadena original en otra columna.

Tus propias conversiones siguen la configuración regional. VBA-JSON lee 3.14 correctamente en cualquier Windows. Pero si tomas un número que la API envió como cadena y lo conviertes tú mismo, CDbl lo lee como tu Windows escribe los números, no como los escribe la API. En tu Windows en español (con la configuración de España; en México, por ejemplo, se usa el punto), igual que en uno alemán, el punto es el separador de miles, y CDbl("3.14") te devuelve 314. Para el texto que viene de una API, usa Val, que siempre espera un punto.

Volcar registros en una hoja

El objetivo habitual es una tabla: una fila por registro, una columna por campo. Dos reglas lo hacen fiable. Primera, elige las columnas según el contrato de la API, no según el primer registro, porque al primer registro le pueden faltar campos opcionales. Segunda, rellena un array 2D y escríbelo de una vez, no celda a celda (consulta arrays 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)                         ' fila de encabezado
    Next c
    For Each rec In root("items")
        r = r + 1
        For c = 0 To UBound(fields)
            out(r, c) = JGet(rec, fields(c))          ' ausente, null, anidado -> Empty
        Next c
    Next rec

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

Los campos que faltan, los valores null y los objetos anidados se convierten en celdas vacías en lugar de errores o claves inventadas. Para los datos anidados, como un objeto address, decide a propósito: añade columnas como address.city con una segunda búsqueda, o escribe los registros anidados en una segunda hoja con el ID del padre como enlace.

Escribir JSON sin que se rompa en un Windows europeo

Para enviar JSON, construye un Dictionary y deja que la biblioteca escriba el texto:

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}

No montes el JSON a mano. Además de olvidar escapar las comillas y los saltos de línea del texto, VBA convierte 12.5 en 12,5 en tu Windows en español, igual que en uno alemán o francés, cuando une un número a una cadena, y eso invalida el JSON. Un cuerpo montado a mano funciona en el equipo del desarrollador y falla en el del usuario. La biblioteca escribe el punto decimal y escapa el texto correctamente, y escribe las fechas de VBA como cadenas ISO. Envía el resultado con el POST que se muestra en VBA HTTP request.

La decisión de criterio: analiza con un analizador y luego comprueba cada nodo

Aquí hay dos decisiones. La primera es no analizar el JSON a mano. Split e InStr funcionan con la respuesta de ejemplo y se rompen con la primera coma dentro de una cadena, el primer objeto anidado o la primera comilla escapada. Un analizador de verdad es un solo módulo importado.

La segunda es tratar el árbol analizado como no fiable. Cualquier rama puede faltar, cualquier hoja puede ser null y cualquier ID puede ser texto. Pon las comprobaciones en una sola función auxiliar como JGet, elige tus columnas según el contrato, y la macro sobrevivirá al registro que no se parece al de ejemplo.

Y antes de escribir nada de esto, pregúntate si necesitas VBA. Si el trabajo es «traer esta API a una tabla en cada actualización», Datos > De la web de Power Query lee JSON, expande los registros en columnas y se actualiza sin una línea de código. Usa VBA cuando haya lógica alrededor de la llamada: un POST, recorrer los resultados página a página o escribir de vuelta.

Cómo ayuda ExcelMaster

El código JSON se escribe pensando en una respuesta de ejemplo y luego se encuentra con la real: un campo opcional que falta, un null en un precio, un ID demasiado largo para ser un número.

ExcelMaster lee la respuesta real antes de escribir el código que la analiza, asigna cada campo a una columna a propósito y trata las claves que faltan, los null y los ID largos en lugar de dejárselos a un error en tiempo de ejecución.

Preguntas frecuentes

¿Cómo leo JSON en Excel VBA?

Importa JsonConverter.bas del proyecto VBA-JSON, añade una referencia a Microsoft Scripting Runtime y llama a Set root = JsonConverter.ParseJson(text). Los objetos se convierten en Dictionaries y los arrays en Collections.

¿Por qué aparece El subíndice está fuera del intervalo al leer un array JSON?

VBA-JSON guarda los arrays en una Collection, cuyo primer elemento es el 1, no el 0. Usa items(1) para el primer elemento, o recorre la colección con For Each.

¿Cómo compruebo si existe una clave en el JSON analizado?

Usa node.Exists("key"). No leas la clave para comprobarla: leer una clave que falta en un Dictionary la añade con un valor Empty.

¿Por qué obtengo el error 94 Uso no válido de Null con JSON?

El campo se envió como null, y VBA-JSON lo convierte en Null. Léelo en un Variant y comprueba IsNull antes de asignarlo a una variable con tipo.

¿Cómo convierto un Dictionary de VBA en JSON?

Llama a JsonConverter.ConvertToJson(dict). Escapa el texto, escribe bien el punto decimal con cualquier configuración regional y convierte las fechas en cadenas ISO.

Probado en

Probado en: Excel 365 (Windows 11), VBA 7.1, VBA-JSON 2.3.1 — última verificación el 05/10/2026.

Guías relacionadas: VBA HTTP request · VBA web scraping · VBA Dictionary · VBA Collection · VBA IsNull · VBA VarType y TypeName · Arrays 2D en VBA · Referencias en VBA