¿Qué es una matriz de VBA en Excel y cómo programar uno?

VBA ha sido parte de Microsoft Office Suite durante muchos años. Si bien no tiene la funcionalidad completa y el poder de una aplicación VB completa, VBA proporciona a los usuarios de oficina la flexibilidad para integrar los productos de oficina y automatizar el trabajo que realiza en ellos.

Una de las herramientas más potentes disponibles en VBA es la capacidad de cargar una gama completa de datos en una sola variable llamada matriz. Al cargar datos de esta manera, puede manipular o realizar cálculos en ese rango de datos de varias maneras.

Puede poner datos en cada sección de este "cuadro" en cualquier orden que desee.

Al comienzo de su guión de VBA, debe definir esta "caja" definiendo su matriz VBA. Entonces, para crear una matriz que pueda contener un conjunto de datos (una matriz de una dimensión), escribiría la siguiente línea.

Dim arrmyarray (1 a 6) como cadena

Más adelante en su programa, puede colocar datos en cualquier sección de esta matriz haciendo referencia a la sección dentro de los paréntesis.

arrmyarray (1) = "Ryan Dube"

Puede crear una matriz bidimensional utilizando la siguiente línea.

Dim arrmyarray (1 a 6,1 a 2) como entero

El primer número representa la fila, y la segunda es la columna. Entonces, la matriz anterior podría contener un rango con 6 filas y 2 columnas.

Puede cargar cualquier elemento de esta matriz con datos de la siguiente manera.

arrmyarray (1,2) = 3

Esto cargará un 3 en la celda B1.

Una matriz puede contener cualquier tipo de datos como una variable regular, como cuerdas, booleanos, enteros, carrozas y más.

El número dentro del paréntesis también puede ser una variable. Los programadores comúnmente usan un bucle para contar a través de todas las secciones de una matriz y celdas de datos de carga en la hoja de cálculo en la matriz. Verás cómo hacer esto más adelante en este artículo.

Cómo programar una matriz VBA en Excel

Echemos un vistazo a un programa simple en el que desee cargar información de una hoja de cálculo en una matriz multidimensional.

Como ejemplo, veamos una hoja de cálculo de ventas de productos, donde desea sacar el nombre del representante de ventas, el artículo y la venta total de la hoja de cálculo.

Tenga en cuenta que en VBA cuando hace referencia a filas o columnas, cuenta filas y columnas que comienzan desde arriba a la izquierda en 1. Entonces, la columna de repetición es 3, la columna del elemento es 4 y la columna total es 7.

Para cargar estas tres columnas en las 11 filas, deberá escribir el siguiente script.

Dim arrmyarray (1 a 11, 1 a 3) como cadena
Dim I como entero, j como entero

Para i = 2 a 12
Para j = 1 a 3
arrmyarray (i-1, j) = celdas (i, j).Valor
Siguiente J
Siguiente yo

Notará que para omitir la fila del encabezado, el número de fila en el primero para look debe comenzar en 2 en lugar de 1. Esto significa que debe restar 1 para el valor de la fila de la matriz cuando está cargando el valor de la celda en la matriz usando las celdas (i, j).Valor.

Dónde insertar su guión de matriz VBA

Para colocar un script VBA en Excel, debe usar el editor VBA. Puede acceder a esto seleccionando el Desarrollador Menú y selección Ver código En la sección Controles de la cinta.

Si no ve al desarrollador en el menú, deberá agregarlo. Para hacer esto, seleccione Archivo y Opción Para abrir la ventana Opciones de Excel.

Cambie los comandos de elección del desplegable a Todos los comandos. Seleccionar Desarrollador desde el menú izquierdo y seleccione el Agregar botón para moverlo al panel a la derecha. Seleccione la casilla de verificación para habilitarla y seleccionar DE ACUERDO para terminar.

Cuando se abra la ventana del editor de código, asegúrese de que la hoja en sus datos esté seleccionada en el panel izquierdo. Seleccionar Hoja de cálculo en el menú desplegable de la izquierda y Activar en lo correcto. Esto creará una nueva subrutina llamada Worksheet_activate ().

Esta función se ejecutará cada vez que se abra el archivo de hoja de cálculo. Pegue el código en el panel de script dentro de esta subrutina.

Este script funcionará a través de las 12 filas y cargará el nombre de la repetición de la columna 3, el artículo de la columna 4 y la venta total de la columna 7.

Una vez que ambos para los bucles están terminados, la matriz bidimensional ArrmyArray contiene todos los datos que especificó de la hoja original.

Manipulaciones de matrices en Excel VBA

Supongamos que desea aplicar un impuesto sobre las ventas del 5% a todos los precios de la venta final, y luego escribir todos los datos en una nueva hoja.

Puede hacer esto agregando otro para el bucle después del primero, con un comando para escribir los resultados en una nueva hoja.

Para k = 2 a 12
Hojas ("Hoja2").Células (k, 1).Valor = arrmyarray (k - 1, 1)
Hojas ("Hoja2").Células (k, 2).Valor = arrmyarray (k - 1, 2)
Hojas ("Hoja2").Células (k, 3).Valor = arrmyarray (k - 1, 3)
Hojas ("Hoja2").Células (k, 4).Valor = arrmyarray (k - 1, 3) * 0.05
Siguiente K

Esto "descargará" toda la matriz en la hoja2, con una fila adicional que contiene el total multiplicado por 5% para el monto del impuesto.

La hoja resultante se verá así.

Como puede ver, las matrices VBA en Excel son extremadamente útiles y versátiles como cualquier otro truco de Excel. 

El ejemplo anterior es un uso muy simple para una matriz. Puede crear matrices mucho más grandes y realizar clasificación, promedio o muchas otras funciones contra los datos que almacena en ellos.

Si desea ser realmente creativo, incluso podría crear dos matrices que contengan una gama de celdas de dos hojas diferentes y realizar cálculos entre elementos de cada matriz.

Las aplicaciones están limitadas solo por su imaginación.