Matrices VBA

Antes de comenzar, permítanme compartir un pequeño secreto con ustedes... Realmente no me gustan los arreglos VBA. Simplemente parece haber demasiadas rarezas en su funcionamiento. En comparación con otros lenguajes de programación, VBA parece complicar demasiado las matrices. Si sientes lo mismo, entonces estás en el lugar correcto. Esta publicación contiene muchos ejemplos de código que cubrirán la mayoría de sus casos de uso.

Gracias a Jon Peltier por sugerir cómo puedo mejorar esta publicación.

Tabla de contenido

• ¿Qué es una matriz y cuándo usarla?
• Matrices estáticas versus dinámicas
• Declarar una matriz como variable
• Crear una matriz estática
• Crear una matriz estática de dos dimensiones
• Crear una matriz dinámica
• Ubicaciones de índice
• Índice del primer elemento.
• Índice del último elemento.
• Asignar valores a una matriz
• Asignar valores a elementos individualmente.
• Asignar valores a elementos con una lista de matriz
• Asignar valores a elementos con una cadena.
• Asignar valores a elementos de un rango.
• Convertir matrices a cadenas y rangos
• Convertir matriz a cadena
• Convertir matriz a rango
• Recorriendo cada elemento de una matriz
• Bucle for: matriz unidimensional
• Bucle for: matriz multidimensional
• Para cada bucle: matriz unidimensional
• Para cada bucle: matriz multidimensional
• Comprobar si un valor está en una matriz
• Encuentra el índice de un elemento en una matriz.
• Cambiar el tamaño de una matriz
• Cambiar tamaño y valores en blanco
• Cambiar el tamaño de la matriz y mantener los valores existentes
• Ordenar el orden de la matriz
• Orden de matriz inverso
• Filtrar una matriz
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0017 VBA Arrays.zip

Tener acceso

¿Qué es una matriz y cuándo usarla?

Una matriz es una lista de variables del mismo tipo. Por ejemplo, una lista de nombres de proveedores sería una matriz.

Supongamos que tenemos una lista de 5 proveedores que pueden cambiar cada mes. Mire la captura de pantalla a continuación como ejemplo:

Para mantener la lista de proveedores, podríamos crear 5 variables y luego asignar valores de una hoja de trabajo a cada variable. Así es como podría verse el código:

Sub ListSuppliers()'Crear las variablesDim Proveedor1 Como StringDim Proveedor2 Como StringDim Proveedor3 Como StringDim Proveedor4 Como StringDim Proveedor5 Como String'Asigne valores a los proveedoresProveedor1 = ActiveSheet.Range("A2").Offset(0, 0).Value2Supplier2 = ActiveSheet. Rango("A2").Offset(1, 0).Value2Supplier3 = ActiveSheet.Range("A2").Offset(2, 0).Value2Supplier4 = ActiveSheet.Range("A2").Offset(3, 0). Value2Supplier5 = ActiveSheet.Range("A2").Offset(4, 0).Value2End Sub

Eso no parece tan malo, ¿verdad? Ahora imaginemos que tenemos que enumerar 1.000 proveedores, o 10.000 proveedores; Ese será un día muy aburrido de codificación. A menos, por supuesto, que usemos una matriz.

Además, ¿qué pasa si tenemos un número desconocido de proveedores? ¿Qué vas a hacer entonces? Necesitaríamos crear más variables de las que necesitamos solo para asegurarnos de que haya suficiente espacio. Nuevamente podemos recurrir a una matriz VBA.

Mire el código a continuación; crea una matriz para contener 10.000 proveedores, poblada a partir de 10.000 celdas en la columna A. No es necesario que lo comprenda en esta etapa; en su lugar, déjese impresionar por lo limpio y ordenado que está. Es difícil creer que una matriz VBA que contiene una lista de 10.000 elementos requiera menos código que una lista de cinco variables.

Sub ListSuppliersArray() Proveedores atenuados (1 a 10000) Como StringDim i As LongFor i = LBound(Proveedores) To UBound(Proveedores) Proveedores(i) = ActiveSheet.Range("A2").Offset(i - 1, 0). Valor2Siguiente iEnd Sub

Usando el VBA anterior, no importa si hay 1, 20, 50, 1000 o 10 000 elementos, el código tendrá la misma longitud. Ésta es la ventaja de las matrices; No tenemos que escribir el mismo código una y otra vez. En su lugar, podemos escribir un fragmento de código que agregue todos los elementos en una matriz.

Pero la cosa no termina ahí. Si los valores a asignar están en un rango contiguo, podemos reducir el código a unas pocas líneas. Mire la macro a continuación; Se asigna un rango de 10 000 celdas a un tipo de variable Variante, que crea automáticamente una matriz de 10 000 elementos (no es necesario realizar bucles). Cosas increíbles, ¿verdad?

Sub ListSuppliersArray() Atenuar proveedores como VariantSuppliers = ActiveSheet.Range("A2:A10001").Value2End Sub

Bien, ahora que entendemos los beneficios de las matrices VBA, aprendamos a usarlas.

Matrices estáticas versus dinámicas

Las matrices vienen en dos formas:

• Estático: una matriz con un número fijo de elementos
• Dinámico: una matriz donde el número de elementos se determina a medida que se ejecuta la macro.

La diferencia entre los dos es cómo se crean. Después de eso, acceder a valores, recorrer elementos y otras acciones es exactamente lo mismo.

Declarar una matriz como variable

Las matrices se declaran de la misma manera que las variables de valor único. La diferencia fundamental es que al declarar una matriz, los paréntesis se suelen utilizar después del nombre de la variable.

Declarar una sola variable

'Declarar una cadena como una única variableDim myVariable As String

Declarar una variable de matriz

'Declarar una cadena como una matrizDim myArray(1 a 5) As String

Las matrices, al igual que otras variables, pueden ser de cualquier tipo de variable. En una matriz se pueden incluir números enteros, cadenas, objetos y rangos, etc.

Usando variante como una matriz

Una variable declarada como Variante puede contener cualquier tipo de datos. Curiosamente, un tipo Variant también puede convertirse en una matriz si le asignamos una matriz.

Mire el código a continuación. Primero, se crea una variable estándar con un tipo de datos Variant y luego se asigna una matriz a la variable. Como resultado, la variable se ha convertido en una matriz y puede tratarse de la misma manera que otras matrices.

Dim arrayAsVariant como variante arrayAsVariant = Array("Alpha", "Bravo", "Charlie")

Crear una matriz estática

La siguiente macro crea una matriz estática con 5 elementos (1, 2, 3, 4 5).

Sub CreateStaticArray()'Crea una matriz estática con 5 elementos (1, 2, 3, 4, 5)Dim arr(1 a 5) como LongEnd Sub

De forma predeterminada, las matrices tienen base 0, lo que significa que comienzan a contar en 0, en lugar de 1. La siguiente macro crea una matriz estática con 6 elementos (0, 1, 2, 3, 4, 5). Observe que la matriz se crea con 5 dentro del paréntesis, pero debido a la base 0, en realidad se crean 6 elementos.

Sub CreateStaticArrayStartingAtZero()'Crea una matriz estática con 6 elementos (0, 1, 2, 3, 4, 5)Dim arr(5) As LongEnd Sub

Podemos convertir matrices en base 1 (es decir, el conteo comienza en 1) insertando el siguiente código en la parte superior del módulo de código.

Opción base 1

Crear una matriz estática de dos dimensiones

Las matrices pueden contener múltiples dimensiones (o submatrices). Esto es muy parecido a tener datos en filas y columnas. En el código siguiente, hemos creado una matriz estática de 3 elementos, cada uno de los cuales es su propia matriz que contiene otros 3 elementos.

Sub Create2DimensionStaticArray()Dim arr(1 a 3, 1 a 3) As Stringarr(1, 1) = "Alpha"arr(1, 2) = "Apple"arr(1, 3) = "Ant"arr(2, 1) = "Bravo"arr(2, 2) = "Ball"arr(2, 3) = "Bat"arr(1, 1) = "Charlie"arr(2, 2) = "Can"arr(3, 3) = "Gato"Fin Sub

No estamos limitados a solo dos dimensiones, ¡VBA nos permite hasta 60! No creo que haya usado más de 3, pero es bueno saber que hay muchos de repuesto.

Crear una matriz dinámica

El problema con las matrices estáticas es que necesitamos saber cuántos elementos se requieren cuando creamos la matriz. Pero a menudo no sabemos la cantidad de elementos, o tal vez queremos agregar y eliminar elementos de la matriz a medida que avanzamos. En cambio, podemos recurrir a matrices dinámicas.
NOTA – El término “matriz dinámica” en Excel y VBA no es el mismo; son metodologías completamente diferentes.
La siguiente macro crea inicialmente una matriz dinámica sin tamaño. Luego, más adelante en la macro, se cambia el tamaño de la matriz, usando ReDim , para crear 5 elementos, comenzando en 1.

Sub CreateDynamicArray()'Crear la matrizDim arr() As Long'Cambiar el tamaño de la matriz más adelante en la macroReDim arr(1 To 5)End Sub

Se puede cambiar el tamaño de una matriz dinámica muchas veces durante la ejecución de la macro (lo veremos más adelante en esta publicación).

Ubicaciones de índice

Cada elemento de una matriz tiene un número de índice (es decir, la posición en la matriz).

Índice del primer elemento.

La siguiente macro muestra el número de índice del primer elemento de una matriz.

Sub GetIndexOfFirstElement()'Crear el arrayDim arr As Variantarr = Array("Alpha", "Bravo", "Charlie")'Obtener el número de índice del primer elementoMsgBox LBound(arr)End Sub

LBound() es una función que devuelve el elemento más bajo de la matriz.

Índice del último elemento.

La siguiente macro muestra el número de índice del último elemento de una matriz.

Sub GetIndexOfLastElement()'Crear el arrayDim arr As Variantarr = Array("Alpha", "Bravo", "Charlie")'Obtener el número de índice del último elemento elementMsgBox UBound(arr)End Sub

UBound() es una función que devuelve el elemento más alto de la matriz.

Asignar valores a una matriz

Después de crear una matriz, ya sea dinámica o estática, necesitamos una forma de asignar valores a los elementos individuales.

Asignar valores a elementos individualmente.

La siguiente macro crea una matriz estática y luego asigna valores a cada elemento individualmente.

Sub AssignFixedValuesToArray()Dim arr(1 a 5) As Stringarr(1) = "Alpha"arr(2) = "Bravo"arr(3) = "Charlie"arr(4) = "Delta"arr(5) = " Eco"Fin Sub

Asignar valores a elementos con una lista de matriz

La siguiente macro demuestra cómo asignar valores a una matriz dinámica basada en una lista de valores.

Sub AssignValuesFromListToArray()'El tipo debe ser Variante para que el método funcioneDim arr As Variantarr = Array("Alpha", "Bravo", "Charlie")End Sub

El comando Array() es una forma breve de agregar valores a una matriz.

Asignar valores a elementos con una cadena.

La siguiente macro divide una cadena en una matriz.

Sub SplitStringIntoArray()Dim arr As VariantDim myString As String'Crear lista con un separador común entre cada elementomyString = "Alpha, Bravo, Charlie, Delta, Echo"'Convierte la lista en un arrayarr = Split(myString, ", ") Subtítulo final

Asignar valores a elementos de un rango.

La siguiente macro crea una matriz bidimensional directamente a partir de un rango.

Sub ReadRangeToArray()Dim arr As Variantarr = ActiveSheet.Range("A1:C3").Value2End Sub

Cuando se utiliza este método, la matriz creada siempre contendrá dos dimensiones (al igual que las filas y columnas del rango). Por lo tanto, incluso si el rango de origen es una sola fila o columna, la matriz seguirá conteniendo dos dimensiones.

Convertir matrices a cadenas y rangos

Una vez obtenida una matriz, podemos convertirla en una cadena o mostrar los valores en un rango.

Convertir matriz a cadena

El siguiente código crea una matriz y luego usa la función Join para convertirla en una cadena.

Sub JoinArrayIntoString()Dim arr As VariantDim joinString As String'Crear un arrayarr = Array("Alpha", "Bravo", "Charlie")'Convertir la matriz en una cadena, cada elemento separado por una comajoinedString = Join(arr, ", ")Fin Sub

Convertir matriz a rango

Se puede escribir una matriz bidimensional en las celdas de una hoja de trabajo en dirección horizontal o vertical.

Sub WriteArrayToRange()Dim arr As Variantarr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")'Escribir matriz en columnasActiveSheet.Range("D1:H1") = arr'Alternative, escriba una matriz en las filas'ActiveSheet.Range("D1:D5") = Application.Transpose(arr)End Sub

Recorriendo cada elemento de una matriz

Hay dos formas de recorrer los elementos de una matriz:

• Bucle For: uso de las funciones LBound y UBound para determinar el número de veces que se realizará el bucle.
• Para cada bucle: recorre cada elemento de la matriz

NOTA – El bucle For Each sólo puede leer los elementos de una matriz; no se puede utilizar para cambiar los valores asignados a los elementos.

Bucle for: matriz unidimensional

El siguiente ejemplo crea una matriz de una sola dimensión y luego recorre cada elemento de la matriz.

Sub ForLoopThroughArray()Dim arr As VariantDim i As Longarr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")'Recorrido desde el límite inferior al límite superior de los elementos en arrayFor i = LBound(arr) A UBound(arr) MsgBox arr(i)Siguiente iEnd Sub

Bucle for: matriz multidimensional

También se puede utilizar un bucle For para matrices multidimensionales, como se muestra en el código siguiente.

Sub ForLoopThrough2DimensionArray()Dim arr(1 a 3, 1 a 3) As StringDim i As LongDim j As Longarr(1, 1) = "Alpha"arr(1, 2) = "Apple"arr(1, 3) = " Ant"arr(2, 1) = "Bravo"arr(2, 2) = "Bola"arr(2, 3) = "Bat"arr(3, 1) = "Charlie"arr(3, 2) = " Can"arr(3, 3) = "Cat"For i = LBound(arr) A UBound(arr) For j = LBound(arr, 2) A UBound(arr, 2) MsgBox arr(i, j) Siguiente jSiguiente iEnd Sub

Para cada bucle: matriz unidimensional

El bucle For Each funciona en una matriz única o multidimensional. Sin embargo, sólo puede leer datos de una matriz, no puede asignar valores a una matriz.

Sub ForEachLoopThroughArray()Dim arr As VariantDim arrElement As Variantarr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")'Recorre la matriz usando For Each métodoFor Each arrElement In arr MsgBox arrElementNext arrElementEnd Sub

Para cada bucle: matriz multidimensional

El siguiente ejemplo sirve para ilustrar que el bucle For Each es idéntico tanto para matrices unidimensionales como multidimensionales.

Sub ForEachLoopThrough2DimensionArray()Dim arr(1 a 3, 1 a 3) As StringDim arrElement As Variantarr(1, 1) = "Alpha"arr(1, 2) = "Apple"arr(1, 3) = "Ant"arr (2, 1) = "Bravo"arr(2, 2) = "Ball"arr(2, 3) = "Bat"arr(3, 1) = "Charlie"arr(3, 2) = "Can"arr (3, 3) = "Cat"'Recorre la matriz para cada arrElement en arr MsgBox arrElementNext arrElementEnd Sub

Comprobar si un valor está en una matriz

A menudo necesitamos buscar en una matriz para descubrir si existe un elemento. La siguiente es una función reutilizable para buscar en una matriz un valor específico.

El resultado de la función puede ser:

• Verdadero = El valor buscado está en la matriz
• Falso = El valor buscado no está en la matriz

La función toma dos argumentos (1) la matriz y (2) el valor a encontrar.

Función IsValueInArray(arr como variante, buscar como variante) Como BooleanDim arrElement como variante'Recorrer la matriz para cada arrElement en arr Si arrElement = buscar Entonces IsValueInArray = Verdadero Salir de la función Fin IfNext arrElementIsValueInArray = FalseEnd Función

El siguiente es un ejemplo de cómo llamar a la función anterior; le dice a la función que busque la cadena "Bravo" dentro de la matriz. El resultado devuelto es Verdadero si se encuentra, o Falso en caso contrario.

Sub UseFunctionValueInArray()Dim arr As VariantDim arrElement As Variantarr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")MsgBox IsValueInArray(arr, "Bravo")End Sub

Encuentra el índice de un elemento en una matriz.

En las secciones anteriores devolvimos Verdadero o Falso dependiendo de si existe un elemento. Pero muchas veces eso no es suficiente, queremos saber dónde está en la matriz. La siguiente es una función reutilizable que encuentra un valor en una matriz y luego devuelve la posición del índice:

El resultado de la función puede ser:

• Número devuelto = La posición de índice del valor buscado
• Falso = El valor buscado no fue encontrado

La función toma dos argumentos el valor a buscar y la matriz a buscar.

Función PositionInArray(arr como variante, buscar como variante) Como VariantDim i As LongFor i = LBound(arr) To UBound(arr) Si arr(i) = buscar Entonces PositionInArray = i Salir de la función Fin IfNext iPositionInArray = FalseEnd Función

A continuación se muestra cómo utilizar la función anterior; si la cadena "Bravo" se encuentra dentro de la matriz, devolverá la posición del índice, o False si no se encuentra.

Sub UseFunctionPositionInArray()Dim arr As VariantDim arrElement As Variantarr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")MsgBox PositionInArray(arr, "Bravo")End Sub

Cambiar el tamaño de una matriz

Como hemos visto anteriormente, las matrices dinámicas se declaran sin tamaño. Luego, más adelante en el código, se usa ReDim para dimensionar la matriz. ReDim se puede utilizar muchas veces durante la macro para cambiar el tamaño de una matriz dinámica.

Las matrices estáticas no se pueden cambiar de tamaño; intentar hacerlo genera un error.

Al cambiar el tamaño de una matriz con ReDim , los valores asignados se borrarán. Para mantener los valores existentes debemos usar el comando ReDim Preserve .

Cambiar tamaño y valores en blanco

La siguiente macro crea y luego cambia el tamaño de una matriz. Después de eso, el código recorre la matriz para demostrar que después de un ReDim los valores se borran.

Sub ResizeArraySize()Dim arr As VariantDim arrElement As Variantarr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")'La matriz cambiará de tamaño al perder todos los valores anterioresReDim arr(0 a 5)' Recorra la matriz usando el método For Each: todos los elementos en blancoFor Each arrElement In arr MsgBox arrElementNext arrElementEnd Sub

Cambiar el tamaño de la matriz y mantener los valores existentes

La siguiente macro crea y luego cambia el tamaño de una matriz usando ReDim Preserve . Como lo demuestra el bucle For Each, al utilizar ReDim Preserve , los valores se mantienen.

Sub ResizeArraySizeKeepValues()Dim arr As VariantDim arrElement As Variantarr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")'La matriz cambiará de tamaño al perder todos los valores anterioresReDim Preserve arr(0 a 5) 'Agrega valor adicional en la matrizarr(5) = "Foxtrot"'Recorre la matriz usando el método For Each - todos los elementos en blancoFor Each arrElement In arr MsgBox arrElementNext arrElementEnd Sub

Ordenar el orden de la matriz

La siguiente función ordena una matriz alfabéticamente. La función toma un único argumento, la matriz que se va a ordenar.

Clasificación de funcionesArrayBubbleSort(arr como variante)Dim i As LongDim j As LongDim temp As VariantFor i = LBound(arr) To UBound(arr) - 1 For j = i + 1 To UBound(arr) If arr(i) arr(j) Entonces temp = arr(j) arr(j) = arr(i) arr(i) = temp End If Next jNext iSortingArrayBubbleSort = arrEnd Función

El siguiente es un ejemplo de cómo utilizar la función anterior.

Sub CallBubbleSort()Dim arr As Variantarr = Array("Charlie", "Delta", "Bravo", "Echo", "Alpha")arr = SortingArrayBubbleSort(arr)End Sub

Orden de matriz inverso

La siguiente función invierte el orden de una matriz. La función toma el nombre de una matriz como único argumento.

Función ReverseArray(arr Como Variante)Atenuar temp Como VarianteDim i Como LargoDim arSize Como LargoDim arrMid Como LongarrSize = UBound(arr)arrMid = (UBound(arr) - LBound(arr)) 2 + LBound(arr)For i = LBound(arr ) A arrMid temp = arr(arrSize) arr(arrSize) = arr(i) arr(i) = temp arrSize = arrSize - 1Siguiente iReverseArray = arrEnd Función

El siguiente código es un ejemplo de cómo utilizar la función anterior.

Sub CallReverseArray()Dim arr As Variantarr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")arr = ReverseArray(arr)End Sub

Filtrar una matriz

Junto con LBound, UBound, Split y Join, otra función incorporada útil es Filter.

La función Filtro devuelve una matriz que incluye solo los elementos que contienen una subcadena. En el siguiente ejemplo, la matriz filteredArr solo incluye los elementos que contienen la letra "o",

Sub FilterArray()Dim arr As VariantDim filteredArr As VariantDim arrElement As Variantarr = Array("Alpha", "Bravo", "Charlie", "Delta", "Echo")'Matriz de filtro para cualquier elemento con la letra "o"filteredArr = Filter(arr, "o")'Recorre la matriz filtrada para cada arrElement en filteredArr MsgBox arrElementNext arrElementEnd Sub

La función Filtro tiene 4 argumentos:

Filtro (SourceArray, Match, [Incluir], [CompareType])

• SourceArray: la matriz original
• Coincidencia: la subcadena a coincidir
• Incluir (el valor predeterminado es Verdadero si se excluye el argumento)
  • Verdadero = incluye los elementos coincidentes
  • Falso = excluir los elementos coincidentes
• CompareType Include (el valor predeterminado es 0 si se excluye el argumento):
  • 0 = vbBinaryCompare: la coincidencia distingue entre mayúsculas y minúsculas
  • 1 = vbTextCompare: la coincidencia no distingue entre mayúsculas y minúsculas

Conclusión

Con suerte, esta publicación cubre la mayoría de sus necesidades. Sin embargo, los arreglos VBA son un tema muy amplio, así que utilice los foros en línea para hacer preguntas específicas que esta publicación no responde.

---