El otro día, realizando un desarrollo en Excel, me encontré con un problema relacionado con el almacenamiento de valores en memoria en una macro. Si después de esta frase inicial sigues leyendo esto, probablemente sepas que una variable almacena un valor. Si quieres almacenar una lista de valores, utilizaremos un tipo de variable que se denomina vector, arreglo o array. Si necesitamos almacenar una matriz de datos utilizaremos un arreglo de 2 o más dimensiones. En VBA, como en la mayoría de lenguajes de programación, hay una estructura más: el diccionario.

¿En qué consiste un diccionario?

Un diccionario es una estructura que almacena pares de valores. Normalmente se les llama clave/valor, key/value o key/item. Podemos pensar en esta estructura como cuando utilizamos la función BUSCARV: introducimos una clave y la función nos devuelve su valor. La diferencia es que el diccionario no tendrá sus valores en celdas, sino en memoria.

Ejemplo de valores de un diccionario:

Si al diccionario le pedimos que nos dé el valor de “Sandías”, nos devolverá 3.

Caso útil. Diccionario vs arreglo

Me encontré con una lista diaria de personas que habían realizado horas extras. Cada persona pertenecía a un departamento. Lo que necesitábamos era sumar todas las horas extra que se habían realizado en cada departamento. Sin embargo, el número de departamentos no es fijo. Este mes sí, pero el mes que viene puede aparecer un departamento nuevo o desaparecer otro existente. ¿Cómo podemos atacar el problema?

Bien, esto se puede resolver con una simple suma condicional. Nuestro problema es que si cada mes varían los departamentos, cada mes tenemos que reformular las sumas. Si las variaciones no son habituales, entonces es peor, porque el día que cambien, el operador seguro que olvidará actualizar las fórmulas. Además, como siempre, cuanto menos intervención humana, menos posibilidades de generar errores. Y por último, si lo puede hacer Excel, ¿por qué lo vamos a hacer nosotros? Así que me puse manos a la obra.

En mi caso, la tabla tenía del orden de 400 registros, pero aquí pongo un ejemplo de cómo era.

Mi objetivo era conocer el total de horas extras por cada departamento. Pero esta tabla me llega cada mes y a priori desconozco cuántos departamentos hay, y también desconozco cuántas personas hay y quiénes son. Por supuesto, el valor de horas extras es completamente variable, por lo que en el código no puedo dar por supuesto nada, salvo que habrá personas, departamentos y horas extra.

Si yo utilizo un array para llenar los datos, puedo realizar un bucle que recorra toda la tabla, leer el departamento, hacer un bucle que recorra todo mi array, y si encuentra el departamento, le suma el tiempo. Si no lo encuentra, añade al final el par departamento / horas.

Pero como sabéis, el tamaño del array hay que conocerlo en el momento de la declaración con DIM, o, por lo menos, en el momento de hacer un REDIM.

Pero si utilizamos un diccionario es todo mucho más sencillo. Recorro la tabla, pregunto al diccionario si tiene la clave. Si la tiene, añado el tiempo al valor que ya tiene asociado. Si no, añado al diccionario el par clave/valor. Y ya está.

Bueno, ya está… no está

Vale, cierto. Un diccionario no se puede declarar así como así. Necesitamos seguir una serie de pasos. Y ahí es donde tenemos que tener claros unos conceptos previos. Empecemos explicando qué es el “early binding” y qué es el “late binding”.

Cuando estamos programando, lo hacemos en nuestro ordenador, todo controlado, podemos actualizar, instalar, probar lo que queramos… Pero luego llegamos a producción, y la hoja de cálculo debe funcionar en varios ordenadores diferentes, conectados en una red. La gente de sistemas no nos deja instalar, desinstalar, ni en general tocar los ordenadores. Los usuarios no saben de qué les estamos hablando, y probablemente tampoco tengan los permisos necesarios para modificar configuraciones de Excel. Bastante es que hayamos conseguido que la gente de sistemas habilite el uso de macros.

En el caso del diccionario, hay que habilitar una referencia que se llama “Microsoft Scripting Runtime”. Como sabes, en el editor de VBA pinchamos en Herramientas/Referencias y nos sale una ventanita en la que lo tenemos que marcar. Al hacer eso, definiremos el diccionario de la siguiente manera:

'Declarar
Dim dict As Scripting.Dictionary
'Crear
Set dict = New Scripting.Dictionary

Y a partir de aquí, empezamos a trabajar con nuestro objeto dict.
Este tratamiento se denomina “early binding”. De esta manera, tendremos todo el Intellisense disponible, todo funcionará perfectamente y seremos felices… Hasta que llevemos nuestro código al ordenador de al lado.

Modo supercompatibilidad

En el ordenador de al lado el código probablemente no funcionará. Lo más probable es que sea porque no está cargada la referencia “Microsoft Scripting Runtime”, por lo que nuestro código cascará en la sentencia Dim.

Existen maneras más o menos elaboradas de conseguir forzar la carga de una referencia desde nuestro código. Lo malo es que esto permitiría a alguien un poco hábil cargar cualquier código en la aplicación de Office que estemos utilizando, pues se trata de cargar un dll y llamar a sus rutinas. Esto significa que cualquier sistema con las opciones de seguridad por defecto, o que esté en una red en la que los de sistemas sepan lo que hacen, no nos permitirá hacer semejante cosa.

Pero existe otra manera de utilizar nuestro diccionario sin necesidad de cargar la referencia explícitamente. Y es crear otra cosa que Excel entiende siempre: un objeto. Así, genérico. Luego se le pone un pequeño apellido y ya está. Esto se denomina “late binding”, y quedaría algo así:

'Declarar
Dim dict As object
'Crear
Set dict = CreateObject("Scripting.Dictionary")

A partir de este momento, podremos utilizar nuestro diccionario sin limitaciones, salvo que mientras lo programamos, el Editor de VBA no sabe de qué tipo de objeto estamos hablando, por lo que el Intellisense no nos sugiere nada.

Ya tengo un diccionario… ¿Y ahora qué?

Me puedo crear un bucle que introduzca los datos en el diccionario. Como los valores de departamentos empiezan en la celda B2, podemos utilizar este código:

fila = 2

Do While Cells(fila, 2).Value <> ""
  If dict.Exists(Cells(fila, 2).Value) Then
    dict(Cells(fila, 2).Value) = dict(Cells(fila, 2).Value) + Cells(fila, 3).Value
  Else
    dict.Add Cells(fila, 2).Value, Cells(fila, 3).Value
  End If
 fila = fila + 1
Loop

Como se puede observar, primero compruebo si ya existe la clave (en nuestro caso el número de departamento). Si es así, sumo el tiempo al valor que ya hay. Si no, creo la nueva clave con su valor inicial.

La sintaxis es esta:

Añadir clave (si existe previamente dará error)
dict.Add clave, valor

Cambiar el valor de una clave (debe existir previamente) 
dict(clave) = valor

Recuperar el valor de una clave 
Valor = dict(clave)

 Comprobar si la clave existe 
dict.Exists(clave)

Una manera típica de trabajar con los diccionarios es introducir la clave y que me devuelva el valor. Pero existen un par de maneras de recorrer todos los elementos de un diccionario. Dependerá de si lo hemos declarado con early binding o con late binding. Para el caso de early binding podemos hacer:

Dim cont As Long
For cont = 0 To dict.Count - 1
  Debug.Print dict.Keys(cont), dict.Items(cont) * 1440
Next

Y tanto si hemos hecho un late binding como si hemos hecho un early binding, se puede recorrer como si fuera una colección de objetos:

Dim clave As Variant

For Each clave In dict.keys
Debug.Print clave, dict(clave) * 1440
Next

En el ejemplo, lo de multiplicar por 1440 es para pasar el tiempo en “formato Excel” a minutos.

Para saber más…

• Información sobre late y early binding