Una colección es un objeto que contiene varios elementos similares. Se puede acceder a estos y manipularlos fácilmente, incluso si hay una gran cantidad de elementos dentro de la colección.
Ya hay colecciones integradas con Excel VBA. Un ejemplo es la colección Sheets. Por cada hoja de trabajo dentro de un libro, hay un elemento dentro de la colección Sheets.
Las colecciones integradas tienen muchas más propiedades y métodos disponibles para usted, pero estos no están disponibles en sus propias colecciones que crea.
Por ejemplo, puede utilizar la colección para obtener información sobre una hoja de trabajo en particular. Por ejemplo, puede ver el nombre de la hoja de trabajo y también si está visible o no. Mediante el uso de un bucle For Each, puede iterar a través de cada hoja de trabajo de la colección.
| 1234567 | Hojas de trabajo de prueba secundaria ()Dim Sh como hoja de trabajoPara cada Sh en hojasMsgBox Sh.NameMsgBox Sh.VisibleSiguiente ShEnd Sub |
También puede abordar una hoja de trabajo específica en la colección utilizando el valor del índice o el nombre real de la hoja de trabajo:
| 12 | MsgBox Sheets (1) .NameMsgBox Sheets ("Sheet1"). Nombre |
A medida que se agregan o eliminan hojas de trabajo, la colección de Hojas crece o se reduce de tamaño.
Tenga en cuenta que con las colecciones de VBA, el número de índice comienza con 1, no con 0
Colecciones versus matrices
Las matrices y las colecciones son similares en sus funciones, ya que ambas son metodologías que permiten almacenar una gran cantidad de datos que luego se pueden referenciar fácilmente mediante código. Sin embargo, tienen una serie de diferencias en la forma en que operan:
- Las matrices son multidimensionales, mientras que las colecciones son de una sola dimensión. Puede dimensionar una matriz con varias dimensiones, p. Ej.
| 1 | Dim MyArray (10, 2) como cadena |
Esto crea una matriz de 10 filas con 2 columnas, casi como una hoja de trabajo. Una colección es efectivamente una sola columna. La matriz es útil si necesita almacenar varios elementos de datos que se relacionan entre sí, p. Ej. nombre y dirección. El nombre estaría en la primera dimensión de la matriz y la dirección en la segunda dimensión.
- Cuando completa su matriz, necesita una línea de código separada para poner un valor en cada elemento de la matriz. Si tuviera una matriz bidimensional, en realidad necesitaría 2 líneas de código: una línea para abordar la primera columna y una línea para abordar la segunda columna. Con el objeto Colección, simplemente use el método Agregar para que el nuevo elemento se agregue a la colección y el valor del índice se ajuste automáticamente para adaptarse.
- Si necesita eliminar un elemento de datos, entonces es más complicado en la matriz. Puede establecer los valores de un elemento en un valor en blanco, pero el elemento en sí todavía existe dentro de la matriz. Si está utilizando un bucle For Next para iterar a través de la matriz, el bucle devolverá un valor en blanco, que necesitará codificación para asegurarse de que se ignore el valor en blanco. En una colección, utiliza los métodos Agregar o Eliminar, y toda la indexación y el cambio de tamaño se encargan automáticamente. El elemento que se ha eliminado desaparece por completo. Las matrices son útiles para un tamaño fijo de datos, pero las colecciones son mejores cuando la cantidad de datos puede cambiar.
- Las colecciones son de solo lectura, mientras que los valores de la matriz se pueden cambiar mediante VBA. Con una colección, primero tendría que eliminar el valor que se va a cambiar y luego agregar el nuevo valor modificado.
- En una matriz, solo puede usar un solo tipo de datos para los elementos que se establecen cuando dimensiona la matriz. Sin embargo, en la matriz puede utilizar tipos de datos personalizados que haya diseñado usted mismo. Podría tener una estructura de matriz muy complicada utilizando un tipo de datos personalizado que, a su vez, tiene varios tipos de datos personalizados debajo. En una colección, puede agregar tipos de datos de uso de datos para cada elemento. Puede tener un valor numérico, una fecha o una cadena; el objeto de colección tomará cualquier tipo de datos. Si intentaba poner un valor de cadena en una matriz que estaba dimensionada como numérica, produciría un mensaje de error.
- Las colecciones son generalmente más fáciles de usar que las matrices. En términos de codificación, cuando crea un objeto de colección, solo tiene dos métodos (Agregar y Eliminar) y dos propiedades (Recuento e Elemento), por lo que el objeto no es complicado de programar.
- Las colecciones pueden usar claves para localizar datos. Las matrices no tienen esta función y requieren un código de bucle para iterar a través de la matriz para encontrar valores específicos.
- El tamaño de una matriz debe definirse cuando se crea por primera vez. Debe tener una idea de la cantidad de datos que va a almacenar. Si necesita aumentar el tamaño de la matriz, puede usar "ReDim" para cambiar su tamaño, pero debe usar la palabra clave "Preservar" si no desea perder los datos que ya se encuentran en la matriz. No es necesario definir un tamaño de colección. Simplemente crece y se encoge automáticamente a medida que se agregan o eliminan elementos.
Alcance de un objeto de colección
En términos de alcance, el objeto de colección solo está disponible mientras el libro de trabajo está abierto. No se guarda cuando se guarda el libro. Si se vuelve a abrir el libro de trabajo, la colección debe volver a crearse utilizando el código VBA.
Si desea que su colección esté disponible para todo el código en su módulo de código, entonces necesita declarar el objeto de colección en la sección Declarar en la parte superior de la ventana del módulo.
Esto asegurará que todo su código dentro de ese módulo pueda acceder a la colección. Si desea que cualquier módulo dentro de su libro de trabajo acceda a la colección, defínalo como un objeto global
| 1 | MyCollection global como nueva colección |
Crear una colección, agregar elementos y acceder a elementos
Se puede crear un objeto de colección simple en VBA usando el siguiente código:
| 123456 | Sub CreateCollection ()Dim MyCollection como nueva colecciónMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"End Sub |
El código dimensiona un nuevo objeto llamado "MyCollection" y luego las siguientes líneas de código usan el método Add para agregar 3 nuevos valores.
Luego puede usar código para iterar a través de su colección para acceder a los valores
| 123 | Para cada artículo de MyCollectionElemento de MsgBoxProximo articulo |
También puede iterar a través de su colección usando un For Next Loop:
| 123 | Para n = 1 a MyCollection.CountMsgBox MyCollection (n)Siguiente n |
El código obtiene el tamaño de la colección usando la propiedad Count y luego usa esto comenzando un valor 1 para indexar cada elemento
El bucle For Each es más rápido que el bucle For Next pero solo funciona en una dirección (índice bajo a alto). For Next Loop tiene la ventaja de que puede usar una dirección diferente (índice alto a bajo) y también puede usar el método Step para cambiar el incremento. Esto es útil cuando desea eliminar varios elementos, ya que deberá ejecutar la eliminación desde el final de la colección hasta el inicio, ya que el índice cambiará a medida que se realicen las eliminaciones.
El método Agregar en una colección tiene 3 parámetros opcionales: Clave, Antes y Después
Puede utilizar los parámetros "Antes" y "Después" para definir la posición de su nuevo artículo en relación con los demás que ya están en la colección.
Esto se hace especificando el número de índice con el que desea que sea relativo su nuevo artículo.
| 123456 | Sub CreateCollection ()Dim MyCollection como nueva colecciónMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"End Sub |
En este ejemplo, se ha especificado que "Item2" se agregue antes del primer elemento indexado de la colección (que es "Item1"). Cuando recorra esta colección, aparecerá "Item2" en primer lugar, seguido de "Item1" y "Item3".
Cuando especifica un parámetro "Antes" o "Después", el valor del índice se ajusta automáticamente dentro de la colección para que "Elemento2" se convierta en un valor de índice de 1 y "Elemento1" se mueva a un valor de índice de 2
También puede usar el parámetro "Clave" para agregar un valor de referencia que puede usar para identificar el elemento de la colección. Tenga en cuenta que el valor de una clave debe ser una cadena y debe ser único dentro de la colección.
| 1234567 | Sub CreateCollection ()Dim MyCollection como nueva colecciónMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"MsgBox MyCollection ("MyKey")End Sub |
"Item2" ha recibido un valor de "Key" de "MyKey" para que pueda hacer referencia a ese elemento utilizando el valor de "MyKey" en lugar del número de índice (2)
Tenga en cuenta que el valor "Clave" debe ser un valor de cadena. No puede ser ningún otro tipo de datos. Tenga en cuenta que la colección es de solo lectura y no puede actualizar el valor de la clave una vez que se ha establecido. Además, no puede verificar si existe un valor clave para un elemento específico en la colección o ver el valor clave, lo cual es un poco inconveniente.
El parámetro "Clave" tiene la ventaja adicional de hacer que su código sea más legible, especialmente si se lo entrega a un colega para que lo respalde, y no tiene que recorrer toda la colección para encontrar ese valor. ¡Imagínese lo difícil que sería hacer referencia a un elemento específico si tuviera una colección de 10,000 elementos!
Eliminar un artículo de una colección
Puede utilizar el método "Eliminar" para eliminar elementos de su colección.
| 1 | MyCollection.Eliminar (2) |
Desafortunadamente, no es fácil si la colección tiene una gran cantidad de elementos para calcular el índice del elemento que desea eliminar. Aquí es donde el parámetro "Clave" resulta útil cuando se crea la colección.
| 1 | MyCollection.Remove ("MyKey") |
Cuando se elimina un elemento de una colección, los valores del índice se restablecen automáticamente durante toda la colección. Aquí es donde el parámetro "Clave" es tan útil cuando se eliminan varios elementos a la vez. Por ejemplo, puede eliminar el índice de elemento 105, e instantáneamente el índice de elemento 106 se convierte en el índice 105, y todo lo que está por encima de este elemento tiene su valor de índice bajado. Si usa el parámetro Key, no hay necesidad de preocuparse por qué valor de índice debe eliminarse.
Para eliminar todos los elementos de la colección y crear una nueva colección, vuelve a utilizar la instrucción Dim, que crea una colección vacía.
| 1 | Dim MyCollection como nueva colección |
Para eliminar el objeto de colección real por completo, puede establecer el objeto en nada
| 1 | Establecer MyCollection = Nada |
Esto es útil si su código ya no requiere la colección. Establecer el objeto de colección en nada elimina todas las referencias a él y libera la memoria que estaba usando. Esto puede tener implicaciones importantes en la velocidad de ejecución de su código, si hay un objeto grande en la memoria que ya no es necesario.
Cuente el número de elementos de una colección
Puede averiguar fácilmente la cantidad de elementos de su colección mediante la propiedad "Recuento".
| 1 | MsgBox MyCollection.Count |
Usaría esta propiedad si estuviera usando un For Next Loop para recorrer la colección, ya que le proporcionará el límite superior para el número de índice.
Recopilación de pruebas para un valor específico
Puede iterar a través de una colección para buscar un valor específico para un elemento usando un For Each Loop
| 123456789101112 | Sub SearchCollection ()Dim MyCollection como nueva colecciónMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Para cada artículo de MyCollectionSi Item = "Item2" EntoncesElemento MsgBox y "Encontrado"Terminara sipróximoEnd Sub |
El código crea una pequeña colección y luego la recorre en iteración buscando un elemento llamado "elemento2". Si lo encuentra, muestra un cuadro de mensaje que indica que ha encontrado el elemento específico
Uno de los inconvenientes de esta metodología es que no se puede acceder al valor del índice ni al valor clave.
Si usa un For Next Loop en su lugar, puede usar el contador For Next para obtener el valor del índice, aunque todavía no puede obtener el valor de la "Clave"
| 123456789101112 | Sub SearchCollection ()Dim MyCollection como nueva colecciónMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Para n = 1 a MyCollection.CountSi MyCollection.Item (n) = "Item2" EntoncesMsgBox MyCollection.Item (n) & "encontrado en la posición del índice" & nTerminara siSiguiente nEnd Sub |
El contador For Next (n) proporcionará la posición del índice
Ordenar una colección
No hay una funcionalidad incorporada para ordenar una colección, pero usando un pensamiento "fuera de la caja", se puede escribir código para hacer una clasificación, utilizando la función de clasificación de la hoja de cálculo de Excel. Este código utiliza una hoja de trabajo en blanco llamada "SortSheet" para realizar la clasificación real.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection ()Dim MyCollection como nueva colecciónContador de atenuación siempre que"Crear una colección con elementos de orden aleatorioMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Add "Item3"'Capture la cantidad de elementos de la colección para usarlos en el futuroContador = MyCollection.Count"Itera a través de la colección copiando cada elemento en una celda consecutiva en" SortSheet "(columna A)Para n = 1 a MyCollection.CountHojas ("Ordenar hoja"). Celdas (n, 1) = Mi colección (n)Siguiente n‘Active la hoja de clasificación y use la rutina de clasificación de Excel para ordenar los datos en orden ascendenteHojas de cálculo ("Ordenar hoja"). ActivarRango ("A1: A" y MyCollection.Count) .SeleccionarActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 Clave: = Rango (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalCon ActiveWorkbook.Worksheets ("SortSheet"). Ordenar.SetRange Range ("A1: A5").Encabezado = xlGuess.MatchCase = Falso.Orientación = xlTopToBottom.SortMethod = xlPinYin.SolicitarTerminar con‘Elimina todos los elementos de la colección; ten en cuenta que este For Next Loop se ejecuta en orden inversoPara n = MyCollection.Count To 1 Step -1MyCollection.Remove (n)Siguiente n"Copie los valores de la celda de nuevo en el objeto de colección vacío utilizando el valor almacenado (Contador) para el" buclePara n = 1 para contrarrestarMyCollection.Add Sheets ("SortSheet"). Cells (n, 1) .ValueSiguiente n"Repite la colección para comprobar el orden en el que están ahora los artículos.Para cada artículo de MyCollectionElemento de MsgBoxProximo articulo‘Borre la hoja de trabajo (hoja de clasificación); si es necesario, elimínela tambiénHojas ("Ordenar hoja"). Rango (celdas (1, 1), celdas (contador, 1)). BorrarEnd Sub |
Este código primero crea una colección con los elementos agregados en un orden aleatorio. Luego los copia en la primera columna de una hoja de trabajo (SortSheet).
Luego, el código usa la función de clasificación de Excel para ordenar los datos de la columna en orden ascendente. El código también podría modificarse para clasificar en orden descendente.
Luego, la colección se vacía de datos usando un For Next Loop. Tenga en cuenta que la opción de paso se utiliza para que se borre desde el final de la colección hasta el principio. Esto se debe a que, a medida que se borra, los valores del índice se restablecen; si se borra desde el principio, no se borraría correctamente (el índice 2 se convertiría en el índice 1).
Finalmente, utilizando otro For Next Loop, los valores de los elementos se transfieren de nuevo a la colección vacía.
Otro For Each Loop demuestra que la colección está ahora en buen orden ascendente.
Desafortunadamente, esto no se refiere a ningún valor de clave que se haya ingresado originalmente, ya que los valores de clave no se pueden leer.
Pasar una colección a una subfunción / función
Una colección se puede pasar a un sub o una función de la misma manera que cualquier otro parámetro
| 1 | Función MyFunction (ByRef MyCollection como colección) |
Es importante pasar la colección usando "ByRef". Esto significa que se utiliza la colección original. Si la colección se pasa usando "ByVal", esto crea una copia de la colección que puede tener repercusiones desafortunadas.
Si se crea una copia usando "ByVal", cualquier cosa que cambie la colección dentro de la función solo ocurre en la copia y no en el original. Por ejemplo, si dentro de la función, se agrega un nuevo elemento a la colección, este no aparecerá en la colección original, lo que creará un error en su código.
Devolver una colección de una función
Puede devolver una colección de una función de la misma forma que devuelve cualquier objeto. Debe utilizar la palabra clave Establecer
| 12345 | Sub ReturnFromFunction ()Dim MyCollection como colecciónEstablecer MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
Este código crea una subrutina que crea un objeto llamado "MyCollection" y luego usa la palabra clave "Set" para llamar efectivamente a la función para completar esa colección. Una vez hecho esto, muestra un cuadro de mensaje para mostrar el recuento de 2 elementos
| 1234567 | Función PopulateCollection () como colecciónDim MyCollection como nueva colecciónMyCollection.Add "Item1"MyCollection.Add "Item2"Establecer PopulateCollection = MyCollectionFunción final |
La función PopulateCollection crea un nuevo objeto de colección y lo llena con 2 elementos. Luego devuelve este objeto al objeto de colección creado en la subrutina original.
Convertir una colección en una matriz
Es posible que desee convertir su colección en una matriz. Es posible que desee almacenar los datos donde se puedan modificar y manipular. Este código crea una pequeña colección y luego la transfiere a una matriz
Observe que el índice de la colección comienza en 1 mientras que el índice de la matriz comienza en 0. Mientras que la colección tiene 3 elementos, la matriz solo necesita dimensionarse a 2 porque hay un elemento 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection como nueva colecciónDim MyArray (2) como cadenaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Para n = 1 a MyCollection.CountMyArray (n - 1) = Mi colección (n)Siguiente nPara n = 0 a 2MsgBox MyArray (n)Siguiente nEnd Sub |
Conversión de una matriz en una colección
Es posible que desee convertir una matriz en una colección. Por ejemplo, es posible que desee acceder a los datos de una manera más rápida y elegante que usando código para obtener un elemento de matriz.
Tenga en cuenta que esto solo funcionará para una única dimensión de la matriz porque la colección tiene solo una dimensión
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection como nueva colecciónDim MyArray (2) como cadenaMyArray (0) = "elemento1"MyArray (1) = "Elemento2"MyArray (2) = "Elemento3"Para n = 0 a 2MyCollection.Add MyArray (n)Siguiente nPara cada artículo de MyCollectionElemento de MsgBoxProximo articuloEnd Sub |
Si desea utilizar una matriz multidimensional, puede concatenar los valores de la matriz juntos para cada fila dentro de la matriz utilizando un carácter delimitador entre las dimensiones de la matriz, de modo que al leer el valor de la colección, podría utilizar mediante programación el carácter delimitador para separe los valores.
También puede mover los datos a la colección sobre la base de que se agrega el valor de la primera dimensión (índice 1), y luego se agrega el valor de la siguiente dimensión (índice 2) y así sucesivamente.
Si la matriz tuviera, digamos, 4 dimensiones, uno de cada cuatro valores de la colección sería un nuevo conjunto de valores.
También puede agregar valores de matriz para usar como claves (siempre que sean únicos), lo que agregaría una manera fácil de ubicar datos específicos.
