Usando un diccionario de VBA
Un diccionario VBA funciona de manera similar a un objeto de colección, pero tiene más propiedades y métodos, y ofrece más flexibilidad.
El diccionario almacena los datos en la memoria y se puede manipular fácilmente. No se requiere cálculo automático, respaldo en segundo plano ni actualización de pantalla, por lo que su código se ejecutará considerablemente más rápido.
El objeto de diccionario funciona de manera similar a un diccionario normal que usaría si desea averiguar el significado de una palabra. Cada entrada en el objeto de diccionario tiene un valor de "clave" y un valor de "elemento". Utiliza la "clave" del valor de la clave para buscar el valor del elemento en el objeto de diccionario, de forma similar a como lo haría con un diccionario convencional.
Debido a la forma en que funciona el objeto de diccionario, todos los valores clave deben ser únicos, de la misma manera que en un diccionario convencional. Imagínese si abriera su diccionario convencional para buscar el significado de una palabra y encontrara la palabra en la lista más de una vez con dos definiciones completamente diferentes. ¡Estarías muy confundido!
Los valores clave suelen ser texto o números, o ambos. A los usuarios a menudo les resulta más fácil recordar los nombres de las teclas como texto en lugar de solo como números.
En comparación con un objeto de colección, el objeto de colección es de solo lectura. Solo tiene dos métodos (Agregar y Eliminar) y dos propiedades (Recuento e Elemento). Una vez que se ha agregado un artículo a un objeto de colección, solo se puede eliminar, pero no editar, lo cual es un procedimiento engorroso si es necesario cambiar el valor de un artículo.
Un objeto de diccionario cambiará de tamaño automáticamente para adaptarse a la cantidad de elementos que contiene. No es necesario definir su tamaño, como una matriz convencional.
El objeto de diccionario es unidimensional y el tipo de datos es "Variante", por lo que se puede ingresar cualquier tipo de datos, p. Ej. numérico, texto, fecha
El diccionario VBA no es nativo de Excel y se debe acceder a él mediante un enlace temprano o tardío al definir el objeto del diccionario.
| 123 | Sub EarlyBindingExample ()Dim MyDictionary como nuevo script.End Sub |
| 1234 | Sub LateBindingExample ()Atenuar MyDictionary como objetoEstablecer MyDictionary = CreateObject ("Scripting.Dictionary")End Sub |
Si utiliza el enlace anticipado, debe agregar una referencia a la biblioteca "Microsoft Scripting Runtime".
Para ello, seleccione "Herramientas | Referencias 'en la barra de menú de la ventana del Editor de Visual Basic (VBE) y aparecerá una ventana emergente con una lista de bibliotecas disponibles.
Desplácese hacia abajo hasta "Microsoft Scripting Runtime" y marque la casilla junto a él. Haga clic en Aceptar y esta biblioteca ahora es parte de su proyecto de VBA y se puede hacer referencia a ella mediante el enlace temprano. Todos los ejemplos de código de este artículo utilizarán enlaces anticipados.
Su código se ejecutará considerablemente más rápido con el enlace temprano, porque todo está compilado por adelantado. Con el enlace tardío, el objeto debe compilarse a medida que se ejecuta el código.
La biblioteca Scripting Runtime tiene "Intellisense". A medida que escribe su código, verá que aparecen listas de métodos y propiedades disponibles, lo que ayuda a evitar que se cometan errores de ortografía, lo que provocará errores en su programa.
Además, si presiona F2 dentro del VBE y selecciona la biblioteca "Scripting", verá todos los métodos y propiedades disponibles, y los parámetros requeridos para cada uno.
Distribución de su aplicación de Excel que contiene un diccionario
Como ya se señaló, la biblioteca Scripting Runtime no es parte de Excel VBA, por lo que si distribuye su aplicación a otros usuarios, ellos deben tener acceso a la biblioteca Scripting Runtime en su computadora. Si no es así, se producirá un error.
Es una buena idea incluir algún código VBA para verificar que esta biblioteca esté presente cuando se cargue su aplicación de Excel. Puede utilizar el comando "Dir" para hacer esto en el evento "Libro abierto"
La ubicación del archivo es C: \ Windows \ SysWOW64 \ scrrun.dll
Alcance de un objeto de diccionario
El objeto Diccionario solo está disponible mientras el libro de Excel está abierto. No se guarda cuando se guarda el libro.
Si su diccionario va a estar disponible para todas las rutinas dentro de su módulo, debe declararlo (Dim) en la sección Declarar en la parte superior del módulo
Lo define como un objeto global si desea que su diccionario se use en todo su código.
| 1 | MyDictionary global como nuevo diccionario |
Completar y leer de su diccionario
Para empezar, debe crear un diccionario, completarlo con algunos datos y luego recorrerlo para probar que los datos existen.
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary como nuevo script.MyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Para n = 0 a MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Siguiente nEnd Sub |
Este código crea un nuevo objeto de diccionario llamado "MyDictionary" y luego lo completa con tres elementos. El método Add tiene dos parámetros: Clave y Elemento, y ambos son obligatorios
Los tipos de datos para la clave y el elemento son variantes, por lo que aceptarán cualquier tipo de datos: numérico, texto, fecha, etc.
El primer elemento del diccionario se podría agregar como:
| 1 | MyDictionary.Add 10, "MyItem1" |
Los valores se han invertido entre la clave y el elemento, pero esto aún funcionaría, aunque la clave de búsqueda ahora se convertiría en 10.
Sin embargo, es importante comprender que el valor clave es el valor de búsqueda en el diccionario. Funciona de forma muy similar a la función BUSCARV en Excel. Debido a que todas las claves deben tener valores únicos, puede especificar un valor de clave y devolver instantáneamente el valor del elemento para esa clave.
Tenga en cuenta que el índice del diccionario comienza en 0, por lo que debe restar 1 del recuento del diccionario utilizado en el bucle For … Next
También puede usar un bucle For … Each para leer los valores en el diccionario:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary como nuevo script.Dictionary, I como varianteMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Para cada yo en MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Siguiente yoEnd Sub |
Este código iterará a través de cada artículo y mostrará la clave del artículo y el valor del artículo.
Uso del número de índice de artículo
Puede utilizar el número de índice de una clave o elemento para leer el valor
| 123456789101112 | Sub IndexNumbers ()Dim MyDictionary como nuevo script.MyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Keys (2)MsgBox MyDictionary.Items (1)End Sub |
Este código devolverá la clave "item3" ya que el índice comienza en 0 y el valor del elemento 20
Puede hacer referencia a valores de elementos o claves individuales dentro de las colecciones de Elementos o Claves mediante los números de índice.
Filtrar el diccionario
No existe un método directo para hacer esto, pero es bastante simple escribir código para hacerlo:
| 1234567891011 | Sub FilterDiccionario ()Dim MyDictionary como nuevo script.MyDictionary.Add "AAItem1", 10MyDictionary.Add "BBItem2", 20MyDictionary.Add "BBItem3", 30Para cada filtro I In (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Siguiente yoEnd Sub |
El valor del filtro solo funciona desde el principio del valor clave. No puede utilizar comodines en el filtro. Este código devolverá los dos valores de elementos con nombres de clave que comiencen con "BB"
Esto le dará un subconjunto del diccionario basado en su valor de filtro, que luego podría transferir a otro diccionario o una hoja de trabajo. Con una planificación cuidadosa de los nombres de las claves, asegurándose de que haya un prefijo significativo para cada uno, podrá dividir fácilmente el diccionario en varios componentes.
Cambiar el valor de un elemento de una clave
El objeto de diccionario tiene una gran ventaja sobre una colección en que el valor del elemento se puede cambiar, p.
| 1 | MyDictionary ("MyItem4") = "40" |
En la colección, deberá eliminar esa entrada y luego volver a crearla.
Aquí hay un ejemplo de código:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary como nuevo script.MyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"Para n = 0 a MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Siguiente nEnd Sub |
El código anterior configura tres elementos dentro del diccionario y luego cambia el valor de "MyItem2" de 20 a 25.
También cambia el valor de "MyItem4" a 40. Tenga en cuenta que en las declaraciones de adición del código, no se agregó "MyItem4". Cuando cambia el valor de una clave que no existe, se crea automáticamente. Esto es extremadamente conveniente, ya que no se activa ningún error, pero significa que debe tener cuidado con los nombres de las claves. Un error de ortografía inadvertido en el nombre de la clave significaría que se crea una nueva clave, y el nombre de la clave original aún tendría el valor anterior.
Esto podría generar fácilmente problemas de integridad en el objeto de diccionario.
Prueba si existe una clave
Puede verificar si existe un valor clave dentro del diccionario
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary como nuevo script.MyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")End Sub |
El código agrega tres elementos a un nuevo objeto de diccionario y luego prueba una clave ("MyItem8") que no está en el diccionario. Esto devuelve False, pero si se hubiera utilizado una de las claves existentes, devolvería True
No se aceptan comodines. El texto de búsqueda también distingue entre mayúsculas y minúsculas de forma predeterminada, pero esto se puede cambiar (ver más adelante en el artículo)
Usar varios valores en un diccionario
A diferencia de una matriz, el objeto de diccionario es solo unidimensional. Esto puede ocasionar problemas si tiene varios valores que desea poner en una clave.
Una forma de evitar esto es concatenar cada valor de elemento usando un carácter delimitador entre cada valor, p. "|"
| 12345678910111213141516171819202122232425262728293031323334 | Sub MultipleValues ()'Crear objeto de diccionario y variablesDim MyDictionary como nuevo scripting.Dictionary, V1 como entero, V2 como cadenaDim V3 como fecha, temperatura como cadena, N como entero'Complete 3 variables para demostrar múltiples valoresV1 = 5V2 = "Ejemplo de varios valores"V3 = "22-julio-2020"'Agregue el valor concatenado al diccionario usando "|" delimitadorMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Capture el valor del diccionario concatenado del diccionario en una variableTemp = MyDictionary ("MyMultipleItem")'Itere a través de la cadena concatenada para separar los valores individualesHacer'Encuentra la posición de un delimitadorN = InStr (Temp, "|")'Si no hay más delimitadores, la salida Do loopSi N = 0 Entonces Salga Hacer'Mostrar texto relativo a la posición del delimitador encontradoMsgBox Izquierda (Temp, N - 1)'Truncar la cadena concatenada al siguiente carácter después del delimitador encontradoTemp = Medio (Temp, N + 1)CírculoEnd Sub |
Otra forma de solucionar este problema es diseñar su propio sistema de sub-secuencias de comandos para los nombres de clave. No hay ninguna razón por la que no deba utilizar corchetes y números en los nombres de las claves
| 1234567891011 | Sub MultipleValues ()Dim MyDictionary como nuevo script.MyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Multiple (2)", "Example of multiple values"MyDictionary.Add "Multiple (3)", "22-Jul-2020"Para N = 1 a 3MsgBox MyDictionary ("Múltiple (" & N & ")")Siguiente NEnd Sub |
Este código agrega tres claves al diccionario, pero cada nombre de clave contiene un número de subguión entre paréntesis. A continuación, puede hacer referencia al nombre de la clave, pero utilizando el número de subprograma concatenado en. Esto es muy similar a utilizar un objeto de matriz
Eliminar elementos
Puede eliminar elementos individuales por referencia al valor clave
| 1 | MyDictionary.Remove ("MyItem2") |
Tenga en cuenta que debido a que los nombres de clave son únicos, esto solo elimina esa clave y el valor de elemento en particular
También puede borrar el diccionario por completo
| 1 | MyDictionary.RemoveAll |
A continuación, se muestra un ejemplo del uso de "Eliminar" en VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary como nuevo script.MyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MyDictionary.Remove ("Item2")Para N = 0 a MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Siguiente NMyDictionary.RemoveAllMsgBox MyDictionary.CountEnd Sub |
El código agrega tres elementos al diccionario y luego elimina "Item2". Luego, recorre el diccionario para demostrar que "Item2" ya no existe
Finalmente, el código elimina todos los elementos del diccionario y muestra el recuento del diccionario, que ahora es cero.
Cambio de la sensibilidad a mayúsculas y minúsculas para búsquedas
Si busca una clave, se distingue entre mayúsculas y minúsculas de forma predeterminada. Sin embargo, puede usar la propiedad "CompareMode" para cambiar esto.
Tenga en cuenta que esto debe hacerse inmediatamente en el código después de crear el objeto de diccionario, pero antes de agregar cualquier dato al diccionario. Una vez que se ha establecido el modo de comparación, no se puede cambiar dentro de ese diccionario.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary como nuevo script.MyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Exists ("elemento2")End Sub |
En este ejemplo, el modo de comparación se establece en "TextCompare", lo que significa que no distingue entre mayúsculas y minúsculas. La declaración "Existe" al final del ejemplo devolverá True, a pesar de que el texto de búsqueda está todo en minúsculas.
En Excel, solo hay dos valores que se pueden usar para el modo de comparación. La comparación binaria distingue entre mayúsculas y minúsculas y la comparación de texto no distingue entre mayúsculas y minúsculas
Si tiene el modo de comparación configurado en Comparación binaria, debe tener cuidado al nombrar sus claves. Si configura un nombre para que tenga una letra mayúscula como primer carácter, cuando esté cambiando el valor, debe asegurarse de que el primer carácter siga siendo mayúscula. Si comienza con un carácter en minúscula, esto se interpretará como una nueva clave y podría generar fácilmente confusión y errores en su diccionario.
Recuerde que si cambia el valor de una clave y el nombre de la clave no existe debido a que se está utilizando una comparación binaria, se agregarán una clave y un valor nuevos al diccionario.
Si usa Text Compare en su lugar, cualquier cambio de valor irá a la clave independientemente del caso. Si intenta agregar el mismo elemento pero escrito con un carácter de caso diferente, obtendrá un error porque ya existe.
Ordenar el diccionario
Al igual que con el objeto de colección, no se proporciona ningún método para poder ordenar el diccionario, ya sea mediante claves o valores de elementos.
Sin embargo, dado que el código VBA se encuentra en un libro de trabajo de Excel, los datos del diccionario se pueden transferir a Excel en forma tabular, y luego se le puede aplicar la función de clasificación de Excel. Luego, el diccionario se puede borrar usando "RemoveAll" y los valores ordenados se agregan de la hoja de trabajo.
Este código ordenará tanto las claves como los valores de los elementos
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary como nuevo diccionarioContador de atenuación siempre que'Crear diccionario con elementos de orden aleatorioMyDictionary.Add "Item5", 5MyDictionary.Add "Item2", 15MyDictionary.Add "Item4", 11MyDictionary.Add "Item1", 2MyDictionary.Add "Item3", 19'Capture la cantidad de elementos en el diccionario para uso futuroContador = MyDictionary.Count'Itere a través del diccionario copiando cada clave y elemento en una celda consecutiva en' Hoja1 '(columna A)Para N = 0 a MyDictionary.Count - 1Hojas ("Hoja1"). Celdas (N + 1, 1) = MyDictionary.Keys (N)Hojas ("Hoja1"). Celdas (N + 1, 2) = MyDictionary.Items (N)Siguiente N'Active la Hoja1 y use la rutina de clasificación de Excel para ordenar los datos en orden ascendenteHojas de cálculo ("Hoja1"). ActivarRango ("A1: B" y MyDictionary.Count) .SeleccionarActiveWorkbook.Worksheets ("Hoja1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Rango (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalCon ActiveWorkbook.Worksheets ("Hoja1"). Ordenar.SetRange Range ("A1: A5").Encabezado = xlGuess.MatchCase = Falso.Orientación = xlTopToBottom.SortMethod = xlPinYin.SolicitarTerminar con'Borrar todos los elementos del diccionarioMyDictionary.RemoveAll'Copie los valores de la celda nuevamente en el objeto de diccionario vacío usando el valor almacenado (Contador) para el' buclePara N = 1 para contrarrestarMyDictionary.Añadir hojas ("Hoja1"). Celdas (N, 1) .Valor, Hojas ("Hoja1"). Celdas (N, 2) .ValorSiguiente N'Repita el diccionario para probar el orden en que los elementos están ahora enPara N = 0 a MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Siguiente N'Borre la hoja de trabajo (Hoja1); si es necesario, elimínela tambiénHojas ("Hoja1"). Rango (celdas (1, 1), celdas (contador, 2)). BorrarEnd Sub |
Este código crea un diccionario con cinco valores de orden aleatorios agregados. Captura el número de elementos en una variable y luego recorre en iteración el diccionario, transfiriendo los valores clave y del elemento a columnas separadas en una hoja de trabajo.
Luego ordena el rango descargado, usando la columna A como campo de clasificación. El diccionario se borra completamente usando el método "RemoveAll", y el código luego itera a través de los valores de celda en la hoja de trabajo agregándolos nuevamente al diccionario.
Finalmente, el código itera a través del diccionario, mostrando la clave y los valores del elemento concatenados para demostrar que la clasificación ha funcionado.
Al cambiar los parámetros en el código de clasificación, los datos podrían ordenarse por valores de artículo.
Copiar una lista de claves en una hoja de trabajo
Puede copiar una lista de todos los valores clave en una hoja de trabajo usando el siguiente código:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary como nuevo script.MyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Hojas ("Hoja1"). Rango ("A1"). Valor = Unir (MyDictionary.Keys, vbLf)End Sub |
Esto producirá el resultado en su hoja de trabajo:
Puede copiar un diccionario completo en una hoja de trabajo usando este código:
| 12345678910 | Copia secundaria en hoja de trabajo ()Dim MyDictionary como nuevo script.MyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Rango ("A1"). Redimensionar (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Rango ("B1"). Redimensionar (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Items)End Sub |
Su hoja de trabajo se verá así:
Comparar un diccionario con una colección
El diccionario es más rápido que una colección.
Una colección ya está dentro de VBA. Un diccionario necesita una referencia al Diccionario de secuencias de comandos de Microsoft para ser agregado o un objeto creado usando enlace tardío
Un elemento de la colección solo se puede escribir una vez y leer muchas veces. En un diccionario, el valor del artículo se puede cambiar. Con una colección, el elemento debe eliminarse y luego el elemento modificado se debe volver a agregar.
La colección funciona con valores de índice, por lo que puede ser difícil determinar qué valor de índice pertenece a dónde. El diccionario funciona con valores clave únicos que se utilizan para localizar un elemento.
Recuperar un solo elemento es más lento en una colección grande que en un diccionario
En una colección, las claves solo se utilizan para buscar datos y no se pueden recuperar. En un diccionario, se puede probar la existencia de claves y se pueden usar para encontrar un elemento en particular.
Las colecciones distinguen entre mayúsculas y minúsculas y esto no se puede cambiar. En un diccionario, el modo de comparación se puede configurar para que se distinga entre mayúsculas y minúsculas.
En una colección, los valores clave deben ser cadenas. En un diccionario pueden ser de cualquier tipo de datos, p. Ej. numérico, fecha, etc.
Eliminar todos los elementos de una colección implica volver a definir el objeto de la colección. El diccionario tiene el método "RemoveAll" para esto.
