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

Una de las herramientas más poderosas disponibles en VBA es la capacidad de cargar un rango completo 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.

Entonces, ¿qué es una matriz de VBA ? En este artículo responderemos esa pregunta y le mostraremos cómo usar uno en su propio script VBA^{(your own VBA script)} .

¿Qué es una matriz VBA?

Usar una matriz de VBA en ^(VBA)Excel es muy simple, pero comprender el concepto de matrices puede ser un poco complejo si nunca ha usado una.

Piense^(Think) en una matriz como una caja con secciones dentro. Una matriz unidimensional es una caja con una línea de secciones. Una matriz bidimensional es una caja con dos líneas de secciones.

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

Al comienzo de su secuencia de comandos de VBA , debe definir esta "caja" definiendo su matriz de 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 To 6) As String

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

arrMyArray(1) = "Ryan Dube"

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

Dim arrMyArray(1 To 6,1 to 2) As Integer

El primer número representa la fila y el segundo 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 cadenas, booleanos, enteros, flotantes y más.

El número dentro del paréntesis también puede ser una variable. Los programadores suelen utilizar un For Loop para contar todas las secciones de una matriz y cargar celdas de datos en la hoja de cálculo en la matriz. Verá cómo hacerlo 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 puede querer 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, en la que desea extraer 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^{(rows and columns)} comenzando desde la parte superior izquierda en 1. Entonces, la columna de representación es 3, la columna de elementos 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 To 11, 1 To 3) As String
Dim i As Integer, j As Integer

For i = 2 To 12
   For j = 1 To 3
      arrMyArray(i-1, j) = Cells(i, j).Value
   Next j
Next i

Notará que para omitir la fila del encabezado, el número de fila en el primer For 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 Cells (i, j).Value.

Dónde insertar su secuencia de comandos de matriz VBA^{(Your VBA Array Script)}

Para programar un script de VBA en ^(VBA)Excel , debe usar el editor de VBA . Puede acceder a esto seleccionando el menú Desarrollador y seleccionando ^(Developer)Ver código^{(View Code)} en la sección Controles^(Controls) de la cinta.

Si no ve el Desarrollador^(Developer) en el menú, deberá agregarlo. Para hacer esto, seleccione Archivo^(File) y Opciones^(Options) para abrir la ventana de Opciones de Excel.

Cambie los comandos de selección del menú desplegable a Todos los comandos^{(All Commands)} . Seleccione Desarrollador^(Developer) en el menú de la izquierda y seleccione el botón Agregar^(Add) para moverlo al panel de la derecha. Seleccione la casilla de verificación para habilitarlo y seleccione Aceptar^(OK) para finalizar.

Cuando se abra la ventana del Editor de código^{(Code Editor)} , asegúrese de que la hoja en la que se encuentran sus datos esté seleccionada en el panel izquierdo. Seleccione Hoja^(Worksheet) de trabajo en el menú desplegable de la izquierda y Activar^(Activate) en el de la derecha. 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 secuencias de comandos dentro de esta subrutina.

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

Una vez que ambos bucles For finalizan, la matriz bidimensional arrMyArray contiene todos los datos que especificó de la hoja original.

Manipulación de matrices en Excel VBA

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

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

For k = 2 To 12
   Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
   Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
   Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
   Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

Esto "descargará" toda la matriz en la Hoja2^(Sheet2) , con una fila adicional que contiene el total multiplicado por el 5 % del monto del impuesto.

La hoja resultante se verá así.

Como puede ver, las matrices de VBA en ^(VBA)Excel son extremadamente útiles y tan versátiles como cualquier otro truco de Excel^{(any other Excel trick)} . 

El ejemplo anterior es un uso muy simple para una matriz. Puede crear arreglos mucho más grandes y realizar clasificaciones, promedios o muchas otras funciones con los datos que almacena en ellos.

Si desea ser realmente creativo, incluso podría crear dos matrices que contengan un rango de celdas^{(containing a range of cells)} de dos hojas diferentes y realizar cálculos entre los elementos de cada matriz.

Las aplicaciones están limitadas únicamente por su imaginación.

What Is a VBA Array in Excel and How to Program One

VBA has been part of the Microsoft Office suite for many years. While it doesn’t have thе full functionality and power of a full VB application, VBA provides Office users with the flexibility to integrate Office products and aυtomate the work you do in them.

One of the most powerful tools available in VBA is the ability to load an entire range of data into a single variable called an array. By loading data in this way, you can manipulate or perform calculations on that range of data in a variety of ways.

So what is a VBA array? In this article we’ll answer that question, and show you how to use one in your own VBA script.

What Is a VBA Array?

Using a VBA array in Excel is very simple, but understanding the concept of arrays can be a little bit complex if you’ve never used one.

Think of an array like a box with sections inside it. A one-dimensional array is a box with one line of sections. A two-dimensional array is a box with two lines of sections.

You can put data into each section of this “box” in any order you like.

At the beginning of your VBA script, you need to define this “box” by defining your VBA array. So to create an array that can hold one set of data (a one-dimension array), you would write the following line.

Dim arrMyArray(1 To 6) As String

Later in your program, you can place data into any section of this array by referencing the section inside of parentheses.

arrMyArray(1) = "Ryan Dube"

You can create a two-dimensional array using the following line.

Dim arrMyArray(1 To 6,1 to 2) As Integer

The first number represents the row, and the second is the column. So the above array could hold a range with 6 rows and 2 columns.

You can load any element of this array with data as follows.

arrMyArray(1,2) = 3

This will load a 3 into the cell B1.

An array can hold any type of data as a regular variable, such as strings, boolean, integers, floats and more.

The number inside the parenthesis can also be a variable. Programmers commonly use a For Loop to count through all sections of an array and load data cells in the spreadsheet into the array. You’ll see how to do this later in this article.

How to Program a VBA Array in Excel

Let’s take a look at a simple program where you may want to load information from a spreadsheet into a multidimensional array.

As an example, let’s look at a product sales spreadsheet, where you want to pull the sales rep’s name, item, and total sale out of the spreadsheet.

Note that in VBA when you reference rows or columns, you count rows and columns starting from the top left at 1. So the rep column is 3, the item column is 4, and the total column is 7.

To load these three columns over all 11 rows, you’ll need to write the following script.

Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer

For i = 2 To 12
   For j = 1 To 3
      arrMyArray(i-1, j) = Cells(i, j).Value
   Next j
Next i

You’ll notice that in order to skip the header row, the row number in the first For look needs to start at 2 rather than 1. This means you need to subtract 1 for the array row value when you’re loading the cell value into the array using Cells(i, j).Value.

Where to Insert Your VBA Array Script

To place program a VBA script in Excel, you need to use the VBA editor. You can access this by selecting the Developer menu and selecting View Code in the Controls section of the ribbon.

If you don’t see the Developer in the menu, you’ll need to add it. To do this, select File and Options to open the Excel Options window.

Change the choose commands from dropdown to All Commands. Select Developer from the left menu and select the Add button to move it to the pane on the right. Select the checkbox to enable it and select OK to finish.

When the Code Editor window opens, make sure the sheet your data is in is selected in the left pane. Select Worksheet in the left dropdown and Activate in the right. This will create a new subroutine called Worksheet_Activate().

This function will run whenever the spreadsheet file is opened. Paste the code into the script pane inside this subroutine.

This script will work through the 12 rows and will load the rep name from column 3, the item from column 4, and the total sale from column 7.

Once both For loops are finished, the two dimensional array arrMyArray contains all of the data you specified from the original sheet.

Manipulating Arrays in Excel VBA

Let’s say you want to apply a 5% sales tax to all of the final sale prices, and then write all of the data to a new sheet.

You can do this by adding another For loop after the first, with a command to write the results to a new sheet.

For k = 2 To 12
   Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
   Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
   Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
   Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

This will “unload” the entire array into Sheet2, with an additional row containing the total multiplied by 5% for the tax amount.

The resulting sheet will look like this.

As you can see, VBA arrays in Excel are extremely useful and as versatile as any other Excel trick. 

The example above is a very simple use for an array. You can create much larger arrays and perform sorting, averaging, or many other functions against the data you store in them.

If you want to get real creative, you could even create two arrays containing a range of cells from two different sheets, and perform calculations between elements of each array.

The applications are limited only by your imagination.

Related posts

• Cómo eliminar líneas en blanco en Excel

• Cómo usar la función Hablar celdas de Excel

• Cómo insertar una hoja de cálculo de Excel en un documento de Word

• Cómo usar el análisis What-If de Excel

• Cómo arreglar una fila en Excel

• Cómo compartir un archivo de Excel para facilitar la colaboración

• Cómo abrir Word y Excel en modo seguro

• Una guía avanzada de VBA para MS Excel

• 10 consejos y trucos de Excel para 2019

• 4 formas de convertir Excel a Google Sheets

• Hojas de cálculo de Google vs Microsoft Excel: ¿cuáles son las diferencias?

• Conservar las referencias de celda al copiar una fórmula en Excel

• Cómo calcular el puntaje Z en Excel

• Cómo insertar rápidamente varias filas en Excel

• Cómo corregir errores #N/A en fórmulas de Excel como BUSCARV

• Cómo quitar líneas de cuadrícula en Excel

• Cómo alfabetizar en Excel

• Cómo mover columnas en Excel

• 3 formas de dividir una celda en Excel

• Cuándo usar Index-Match en lugar de VLOOKUP en Excel