Matrices de VBA

En VBA, un Formación es una única variable que puede contener varios valores. Piense en una matriz como un rango de celdas: cada celda puede almacenar un valor. Las matrices pueden ser unidimensionales (piense en una sola columna), bidimensionales (piense en varias filas y columnas) o multidimensionales. Se puede acceder a los valores de la matriz por su posición (número de índice) dentro de la matriz.

Hoja rápida de matriz VBA

Matrices

DescripciónCódigo VBACrearDim arr (1 a 3) como variante
arr (1) = "uno"
arr (2) = "dos"
arr (3) = "tres"Crear desde ExcelDim arr (1 a 3) como variante
Atenuar celda como rango, i como entero
i = LBound (arr)
Para cada celda del rango ("A1: A3")
yo = yo + 1
arr (i) = cell.value
Siguiente celdaLeer todos los artículosDim i as Long
Para i = LBound (arr) a UBound (arr)
MsgBox arr (i)
Siguiente yoBorrarBorrar arrMatriz a cadenaDim sName como cadena
sName = Join (arr, ":")Aumentar el tamañoReDim Preserve arr (0 a 100)Valor ajustadoarr (1) = 22

Ejemplos rápidos de matriz VBA

Veamos un ejemplo completo antes de profundizar en los detalles:

12345678910

Sub ArrayExample ()Dim strNames (1 a 4) como cadenastrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "José"msgbox strNames (3)End Sub

Aquí hemos creado la matriz de cadenas unidimensional: strNames con tamaño cuatro (puede contener cuatro valores) y asignados los cuatro valores. Por último, mostramos el tercer valor en un cuadro de mensaje.

En este caso, el beneficio de usar una matriz es pequeño: solo se requiere una declaración de variable en lugar de cuatro.

Sin embargo, veamos un ejemplo que mostrará el verdadero poder de una matriz:

12345678

Sub ArrayExample2 ()Dim strNames (1 a 60000) como cadenaDim i tan largoPara i = 1 a 60000strNames (i) = Celdas (i, 1) .ValueSiguiente yoEnd Sub

Aquí hemos creado una matriz que puede contener 60.000 valores y rápidamente rellenamos la matriz de la columna A de una hoja de trabajo.

Beneficios de la matriz? - ¡Velocidad!

Puede pensar en matrices similares a las hojas de cálculo de Excel:

• Cada celda (o elemento de una matriz) puede contener su propio valor
• Se puede acceder a cada celda (o elemento en una matriz) por su posición de fila y columna.
  • Hoja de trabajo Ej. celdas (1,4) .value = "Fila 1, Columna 4"
  • Array Ex. arrVar (1,4) = "Fila 1, Columna 4"

Entonces, ¿por qué molestarse con las matrices? ¿Por qué no leer y escribir valores directamente en las celdas de Excel? Una palabra: ¡Velocidad!

Leer / escribir en celdas de Excel es un proceso lento. ¡Trabajar con matrices es mucho más rápido!

Crear / declarar una matriz (Dim)

Nota: Las matrices pueden tener varias "dimensiones". Para simplificar las cosas, comenzaremos trabajando solo con matrices unidimensionales. Más adelante en el tutorial le presentaremos matrices de múltiples dimensiones.

Matriz estática

Matrices estáticas son matrices que no pueden cambiar de tamaño. En cambio, Matrices dinámicas puede cambiar de tamaño. Se declaran de forma ligeramente diferente. Primero, veamos las matrices estáticas.

Nota: Si su matriz no cambia de tamaño, use una matriz estática.

Declarar una variable de matriz estática es muy similar a declarar una variable regular, excepto que debe definir el tamaño de la matriz. Hay varias formas diferentes de establecer el tamaño de una matriz.

Puede declarar explícitamente las posiciones inicial y final de una matriz:

123456789101112

Sub StaticArray1 ()'Crea una matriz con posiciones 1, 2, 3, 4Dim arrDemo1 (1 a 4) como cadena'Crea una matriz con posiciones 4, 5, 6, 7Dim arrDemo2 (4 a 7) siempre que'Crea una matriz con posiciones 0,1,2,3Dim arrDemo3 (0 a 3) siempre queEnd Sub

O puede ingresar solo el tamaño de la matriz:

123456

Sub StaticArray2 ()'Crea una matriz con posiciones 0,1,2,3Dim arrDemo1 (3) como cadenaEnd Sub

¡Importante! Tenga en cuenta que, de forma predeterminada, las matrices comienzan en la posición 0. Entonces Dim arrDemo1 (3) crea una matriz con las posiciones 0,1,2,3.

Puedes declarar Opción Base 1 en la parte superior de su módulo para que la matriz comience en la posición 1 en su lugar:

12345678

Opción Base 1Sub StaticArray3 ()'Crea una matriz con posiciones 1, 2, 3Dim arrDemo1 (3) como cadenaEnd Sub

Sin embargo, encuentro que es mucho más fácil (y menos confuso) declarar explícitamente las posiciones inicial y final de las matrices.

¿Cansado de buscar ejemplos de código VBA? ¡Prueba AutoMacro!

Matriz dinámica

Matrices dinámicas son matrices cuyo tamaño se puede cambiar (o cuyo tamaño no necesita ser definido).

Hay dos formas de declarar una matriz dinámica.

Matrices variantes

La primera forma de declarar una matriz dinámica es configurando la matriz para escribir Variante.

1	Dim arrVar () como variante

Con un Variante Array, no es necesario definir el tamaño de la matriz. El tamaño se ajustará automáticamente. Solo recuerde que la matriz comienza con la posición 0 (a menos que agregue Option Base 1 en la parte superior de su módulo)

12345678910111213

Sub VariantArray ()Dim arrVar () como variante'Definir valores (tamaño = 0,1,2,3)arrVar = Matriz (1, 2, 3, 4)'Cambiar valores (tamaño = 0,1,2,3,4)arrVar = Array ("1a", "2a", "3a", "4a", "5a")'Posición de salida 4 ("5a")MsgBox arrVar (4)End Sub

Matrices dinámicas no variantes

Con matrices no variantes, debe definir el tamaño de la matriz antes de asignar valores a la matriz. Sin embargo, el proceso para crear la matriz es ligeramente diferente:

1234567

Sub DynamicArray1 ()Dim arrDemo1 () como cadena'Cambia el tamaño de la matriz con posiciones 1, 2, 3, 4ReDim arrDemo1 (1 a 4)End Sub

Primero declara la matriz, similar a la matriz estática, excepto que omite el tamaño de la matriz:

1	Dim arrDemo1 () como cadena

Ahora, cuando desee establecer el tamaño de la matriz, use el ReDim comando para dimensionar la matriz:

12	'Cambia el tamaño de la matriz con posiciones 1, 2, 3, 4ReDim arrDemo1 (1 a 4)

ReDim cambia el tamaño de la matriz. Lea a continuación la diferencia entre ReDim y ReDim Preserve.

ReDim vs. ReDim Preserve

Cuando usa el ReDim comando borra todos los valores existentes de la matriz. En su lugar, puedes usar ReDim Preserve para preservar los valores de la matriz:

12	'Cambia el tamaño de la matriz con posiciones 1, 2, 3, 4 (conservando los valores existentes)ReDim Preserve arrDemo1 (1 a 4)

Declaración simplificada de matrices

Es posible que se sienta abrumado después de leer todo lo anterior. Para simplificar las cosas, trabajaremos principalmente con matrices estáticas durante el resto del artículo.

Establecer valores de matriz

Establecer valores de matriz es muy fácil.

Con una matriz estática, debe definir cada posición de la matriz, una a la vez:

12345678

Sub ArrayExample ()Dim strNames (1 a 4) como cadenastrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "José"End Sub

Con un Variant Array puede definir todo el arreglo con una línea (solo es práctico para arreglos pequeños):

123456

Sub ArrayExample_1Line ()Dim strNames () como variantestrNames = Array ("Shelly", "Steve", "Neema", "Jose")End Sub

Si intenta definir un valor para una ubicación de matriz que no existe, recibirá un error de subíndice fuera de rango:

1	strNames (5) = "Shannon"

En la sección "Asignar rango a la matriz" a continuación, le mostraremos cómo utilizar un bucle para asignar rápidamente una gran cantidad de valores a las matrices.

Obtener valor de matriz

Puede obtener valores de matriz de la misma manera. En el siguiente ejemplo, escribiremos valores de matriz en las celdas:

1234	Rango ("A1"). Valor = strNames (1)Rango ("A2"). Valor = strNames (2)Rango ("A3"). Valor = strNames (3)Rango ("A4"). Valor = strNames (4)

Programación VBA | ¡Code Generator funciona para usted!

Asignar rango a matriz

Para asignar un rango a una matriz, puede usar un bucle:

12345678

Sub RangeToArray ()Dim strNames (1 a 60000) como cadenaDim i tan largoPara i = 1 a 60000strNames (i) = Celdas (i, 1) .ValueSiguiente yoEnd Sub

Esto recorrerá las celdas A1: A60000, asignando los valores de celda a la matriz.

Matriz de salida a rango

O puede usar un bucle para asignar una matriz a un rango:

123	Para i = 1 a 60000Celdas (i, 1) .Valor = strNames (i)Siguiente yo

Esto hará lo contrario: asigne valores de matriz a las celdas A1: A60000

Matrices 2D / multidimensionales

Hasta ahora hemos trabajado exclusivamente con matrices unidimensionales (1D). Sin embargo, las matrices pueden tener hasta 32 dimensiones.

Piense en una matriz 1D como una sola fila o columna de celdas de Excel, una matriz 2D como una hoja de cálculo completa de Excel con varias filas y columnas, y una matriz 3D es como un libro de trabajo completo, que contiene varias hojas, cada una con varias filas y columnas (usted también podría pensar en una matriz 3D como un cubo de Rubik).

Ejemplos de matrices multidimensionales

Ahora demostremos ejemplos de trabajo con matrices de diferentes dimensiones.

Programación VBA | ¡Code Generator funciona para usted!

Ejemplo de matriz 1D

Este procedimiento combina los ejemplos de matrices anteriores en un solo procedimiento, lo que demuestra cómo se pueden utilizar las matrices en la práctica.

1234567891011121314

Sub ArrayEx_1d ()Dim strNames (1 a 60000) como cadenaDim i tan largo'Asignar valores a la matrizPara i = 1 a 60000strNames (i) = Celdas (i, 1) .ValueSiguiente yo'Valores de matriz de salida a rangoPara i = 1 a 60000Hojas ("Salida"). Celdas (i, 1) .Valor = strNames (i)Siguiente yoEnd Sub

Ejemplo de matriz 2D

Este procedimiento contiene un ejemplo de una matriz 2D:

123456789101112131415161718

Sub ArrayEx_2d ()Dim strNames (1 a 60000, 1 a 10) como cadenaDim i tan largo, j tan largo'Asignar valores a la matrizPara i = 1 a 60000Para j = 1 a 10strNames (i, j) = Celdas (i, j) .ValueSiguiente jSiguiente yo'Valores de matriz de salida a rangoPara i = 1 a 60000Para j = 1 a 10Hojas ("Salida"). Celdas (i, j) .Valor = strNames (i, j)Siguiente jSiguiente yoEnd Sub

Ejemplo de matriz 3D

Este procedimiento contiene un ejemplo de una matriz 3D para trabajar con varias hojas:

12345678910111213141516171819202122

Sub ArrayEx_3d ()Dim strNames (1 a 60000, 1 a 10, 1 a 3) como cadenaDim i tan largo, j tan largo, k tan largo'Asignar valores a la matrizPara k = 1 a 3Para i = 1 a 60000Para j = 1 a 10strNames (i, j, k) = Hojas ("Hoja" & k) .Células (i, j) .ValorSiguiente jSiguiente yoSiguiente k'Valores de matriz de salida a rangoPara k = 1 a 3Para i = 1 a 60000Para j = 1 a 10Hojas ("Salida" & k) .Células (i, j) .Valor = strNames (i, j, k)Siguiente jSiguiente yoSiguiente kEnd Sub

Longitud / tamaño de la matriz

Hasta ahora, le presentamos los diferentes tipos de matrices y le enseñamos cómo declarar las matrices y obtener / establecer valores de matriz. A continuación, nos centraremos en otros temas necesarios para trabajar con matrices.

Programación VBA | ¡Code Generator funciona para usted!

Funciones UBound y LBound

El primer paso para obtener la longitud / tamaño de una matriz es usar las funciones UBound y LBound para obtener los límites superior e inferior de la matriz:

123456

Sub UBoundLBound ()Dim strNames (1 To 4) As StringMsgBox UBound (strNames)MsgBox LBound (strNames)End Sub

Restar los dos (y sumar 1) te dará la longitud:

1	GetArrLength = UBound (strNames) - LBound (strNames) + 1

Función de longitud de matriz

A continuación, se muestra una función para obtener la longitud de una matriz de una sola dimensión:

1234567

Función pública GetArrLength (a As Variant) As LongSi está vacío (a) EntoncesGetArrLength = 0DemásGetArrLength = UBound (a) - LBound (a) + 1Terminara siFunción final

¿Necesita calcular el tamaño de una matriz 2D? Consulte nuestro tutorial: Calcular el tamaño de la matriz.

Bucle a través de la matriz

Hay dos formas de recorrer una matriz. El primero recorre los números enteros correspondientes a las posiciones numéricas de la matriz. Si conoce el tamaño de la matriz, puede especificarlo directamente:

12345678910111213

Sub ArrayExample_Loop1 ()Dim strNames (1 To 4) As StringDim i tan largostrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "José"Para i = 1 a 4MsgBox strNames (i)Siguiente yoEnd Sub

Sin embargo, si no conoce el tamaño de la matriz (si la matriz es dinámica), puede usar las funciones LBound y UBound de la sección anterior:

12345678910111213

Sub ArrayExample_Loop2 ()Dim strNames (1 To 4) As StringDim i tan largostrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "José"Para i = LBound (strNames) a UBound (strNames)MsgBox strNames (i)Siguiente yoEnd Sub

Para cada bucle de matriz

El segundo método es con For Each Loop. Esto recorre cada elemento de la matriz:

12345678910111213

Sub ArrayExample_Loop3 ()Dim strNames (1 To 4) As StringElemento tenuestrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "José"Para cada artículo en strNamesElemento de MsgBoxProximo articuloEnd Sub

For Each Array Loop funcionará con matrices multidimensionales además de matrices unidimensionales.

Programación VBA | ¡Code Generator funciona para usted!

Bucle a través de matriz 2D

También puede usar las funciones UBound y LBound para recorrer una matriz multidimensional. En este ejemplo recorreremos una matriz 2D. Observe que las funciones UBound y LBound le permiten especificar qué dimensión de la matriz encontrar los límites superior e inferior (1 para la primera dimensión, 2 para la segunda dimensión).

1234567891011121314151617181920

Sub ArrayExample_Loop4 ()Dim strNames (1 a 4, 1 a 2) como cadenaDim i tan largo, j tan largostrNames (1, 1) = "Shelly"strNames (2, 1) = "Steve"strNames (3, 1) = "Neema"strNames (4, 1) = "José"strNames (1, 2) = "Shelby"strNames (2, 2) = "Steven"strNames (3, 2) = "Nemo"strNames (4, 2) = "Jesse"Para j = LBound (strNames, 2) To UBound (strNames, 2)Para i = LBound (strNames, 1) To UBound (strNames, 1)MsgBox strNames (i, j)Siguiente yoSiguiente jEnd Sub

Otras tareas de matriz

Matriz clara

Para borrar una matriz completa, use la declaración de borrado:

1	Borrar strNames

Ejemplo de uso:

12345678910

Sub ArrayExample ()Dim strNames (1 a 4) como cadenastrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "José"Borrar strNamesEnd Sub

Alternativamente, también puede ReDim la matriz para cambiar su tamaño, borrando parte de la matriz:

1	ReDim strNames (1 a 2)

Esto cambia el tamaño de la matriz al tamaño 2, eliminando las posiciones 3 y 4.

Count Array

Puede contar el número de posiciones en cada dimensión de una matriz utilizando las funciones UBound y LBound (discutidas anteriormente).

También puede contar el número de elementos ingresados ​​(o elementos que cumplen con ciertos criterios) recorriendo la matriz.

Este ejemplo recorrerá una matriz de objetos y contará el número de cadenas que no están en blanco encontradas en la matriz:

123456789101112131415

Sub ArrayLoopandCount ()Dim strNames (1 To 4) As StringDim i tan largo, n tan largostrNames (1) = "Shelly"strNames (2) = "Steve"Para i = LBound (strNames) a UBound (strNames)Si strNames (i) "" Entoncesn = n + 1Terminara siSiguiente yoMsgBox n & "valores no en blanco encontrados".End Sub

Programación VBA | ¡Code Generator funciona para usted!

Eliminar duplicados

En algún momento, es posible que desee eliminar los duplicados de una matriz. Desafortunadamente, VBA no tiene una función incorporada para hacer esto. Sin embargo, hemos escrito una función para eliminar duplicados de una matriz (es demasiado largo para incluirlo en este tutorial, pero visite el enlace para obtener más información).

Filtrar

La función de filtro VBA le permite filtrar una matriz. Lo hace creando una nueva matriz con solo los valores filtrados. A continuación se muestra un ejemplo rápido, pero asegúrese de leer el artículo para obtener más ejemplos para diferentes necesidades.

1234567891011121314

Sub Filter_Match ()'Definir matrizDim strNames como variantestrNames = Array ("Steve Smith", "Shannon Smith", "Ryan Johnson")'Matriz de filtrosDim strSubNames como variantestrSubNames = Filter (strNames, "Smith")'Count Filtered ArrayMsgBox "Encontrado" & UBound (strSubNames) - LBound (strSubNames) + 1 & "nombres".End Sub

Función IsArray

Puede probar si una variable es una matriz utilizando la función IsArray:

123456789101112

Sub IsArrayEx ()'Crea una matriz con posiciones 1, 2, 3Dim arrDemo1 (3) como cadena'Crea una variable de cadena regularDim str como cadenaMsgBox IsArray (arrDemo1)MsgBox IsArray (str)End Sub

Unirse a la matriz

Puede "unir" rápidamente una matriz completa junto con la función Unir:

123456789101112

Sub Array_Join ()Dim strNames (1 To 4) As StringDim joinNames como cadenastrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "José"joinNames = Unir (strNames, ",")MsgBox joinNamesEnd Sub

Programación VBA | ¡Code Generator funciona para usted!

Dividir cadena en matriz

La función de división de VBA dividirá una cadena de texto en una matriz que contiene valores de la cadena original. Veamos un ejemplo:

123456789

Sub Array_Split ()Dim nombres () como cadenaDim joinNames como cadenajoinNames = "Shelly, Steve, Nema, Jose"Nombres = Dividir (joinNames, ",")Nombres de MsgBox (1)End Sub

Aquí dividimos esta cadena de texto "Shelly, Steve, Nema, Jose" en una matriz (tamaño 4) usando el delimitador de coma (, ").

Matriz constante

Una matriz no poder declararse como una constante en VBA. Sin embargo, puede solucionar esto creando una función para usar como una matriz:

123456789

'Definir ConstantArrayFunción ConstantArray ()ConstantArray = Matriz (4, 12, 21, 100, 5)Función final'Recuperar valor de ConstantArraySub RetrieveValues ​​()MsgBox ConstantArray (3)End Sub

Copiar matriz

No hay una forma incorporada de copiar una matriz usando VBA. En su lugar, necesitará usar un bucle para asignar los valores de una matriz a otra.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 a 100) tan largoDim Arr2 (1 a 100) tan largoDim i tan largo'Crear Array1Para i = 1 a 100Arr1 (i) = iSiguiente yo'CopyArray1 a Array2Para i = 1 a 100Arr2 (i) = Arr1 (i)Siguiente yoMsgBox Arr2 (74)End Sub

Transponer

No hay una función VBA incorporada que le permita transponer una matriz. Sin embargo, hemos escrito una función para transponer una matriz 2D. Lea el artículo para obtener más información.

Programación VBA | ¡Code Generator funciona para usted!

Matriz de retorno de función

Una pregunta común que tienen los desarrolladores de VBA es cómo crear una función que devuelva una matriz. Creo que la mayoría de las dificultades se resuelven utilizando Variant Arrays. Hemos escrito un artículo sobre el tema: Matriz de retorno de funciones de VBA.

Uso de matrices en Access VBA

La mayoría de los ejemplos de Array anteriores funcionan exactamente igual en Access VBA que en Excel VBA. La única diferencia importante es que cuando desee completar una matriz utilizando datos de Access, deberá recorrer el objeto RecordSet en lugar del objeto Range.

1234567891011121314151617181920212223

Sub RangeToArrayAccess ()En caso de error, reanudar siguienteDim strNames () como cadenaDim i tan largoDim iCount siempre queDim dbs como base de datosDim rst As RecordsetEstablecer dbs = CurrentDbEstablecer rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Con la primera.MoveLast.MoveFirstiCount = .RecordCountReDim strNames (1 a iCount)Para i = 1 a iCountstrNames (i) = rst.Fields ("ClientName").MoveNextSiguiente yoTerminar conprimer cierreEstablecer rst = NadaEstablecer dbs = NadaEnd Sub

Tutoriales de matrices
Array Mega-Guía	sí
Obtener tamaño de matriz
Matriz clara
Matriz de filtros
Transponer matriz
Matriz de retorno de función
Eliminar duplicados