O VBA^(VBA) faz parte do pacote Microsoft Office há muitos anos. Embora não tenha todas as funcionalidades e o poder de um aplicativo VB completo, o VBA oferece aos usuários do Office a flexibilidade de integrar os produtos do Office^(Office) e automatizar o trabalho que você faz neles.

Uma das ferramentas mais poderosas disponíveis no VBA é a capacidade de carregar um intervalo inteiro de dados em uma única variável chamada array. Ao carregar dados dessa maneira, você pode manipular ou realizar cálculos nesse intervalo de dados de várias maneiras.

Então, o que é uma matriz VBA ? Neste artigo, responderemos a essa pergunta e mostraremos como usar um em seu próprio script VBA^{(your own VBA script)} .

O que é uma matriz VBA?

Usar uma matriz VBA no ^(VBA)Excel é muito simples, mas entender o conceito de matrizes pode ser um pouco complexo se você nunca usou uma.

Pense^(Think) em uma matriz como uma caixa com seções dentro dela. Uma matriz unidimensional é uma caixa com uma linha de seções. Uma matriz bidimensional é uma caixa com duas linhas de seções.

Você pode colocar dados em cada seção desta “caixa” na ordem que desejar.

No início do seu script VBA , você precisa definir essa “caixa” definindo seu array VBA . Então, para criar um array que possa conter um conjunto de dados (um array de uma dimensão), você escreveria a seguinte linha.

Dim arrMyArray(1 To 6) As String

Mais tarde em seu programa, você pode colocar dados em qualquer seção deste array referenciando a seção dentro dos parênteses.

arrMyArray(1) = "Ryan Dube"

Você pode criar uma matriz bidimensional usando a linha a seguir.

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

O primeiro número representa a linha e o segundo é a coluna. Portanto, a matriz acima pode conter um intervalo com 6 linhas e 2 colunas.

Você pode carregar qualquer elemento dessa matriz com dados da seguinte maneira.

arrMyArray(1,2) = 3

Isso carregará um 3 na célula B1.

Um array pode conter qualquer tipo de dado como uma variável regular, como strings, boolean, inteiros, floats e muito mais.

O número entre parênteses também pode ser uma variável. Os programadores geralmente usam um For Loop para contar todas as seções de um array e carregar células de dados da planilha no array. Você verá como fazer isso mais adiante neste artigo.

Como programar uma matriz VBA no Excel

Vamos dar uma olhada em um programa simples onde você pode querer carregar informações de uma planilha em um array multidimensional.

Como exemplo, vejamos uma planilha de vendas de produtos, na qual você deseja extrair o nome, o item e a venda total do representante de vendas da planilha.

Observe que no VBA quando você faz referência a linhas ou colunas, você conta linhas e colunas^{(rows and columns)} começando do canto superior esquerdo em 1. Portanto, a coluna rep é 3, a coluna item é 4 e a coluna total é 7.

Para carregar essas três colunas em todas as 11 linhas, você precisará escrever o script a seguir.

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

Você notará que para pular a linha do cabeçalho, o número da linha no primeiro For look precisa começar em 2 em vez de 1. Isso significa que você precisa subtrair 1 para o valor da linha da matriz quando estiver carregando o valor da célula na matriz usando Cells (i, j).Value.

Onde inserir seu script de matriz VBA^{(Your VBA Array Script)}

Para programar um script VBA no ^(VBA)Excel , você precisa usar o editor VBA . Você pode acessar isso selecionando o menu Desenvolvedor e selecionando ^(Developer)Exibir código^{(View Code)} na seção Controles^(Controls) da faixa de opções.

Se você não vir o Desenvolvedor^(Developer) no menu, precisará adicioná-lo. Para fazer isso, selecione Arquivo^(File) e Opções^(Options) para abrir a janela Opções do Excel.

Altere os comandos de escolha do menu suspenso para Todos os comandos^{(All Commands)} . Selecione Desenvolvedor^(Developer) no menu esquerdo e selecione o botão Adicionar^(Add) para movê-lo para o painel à direita. Marque a caixa de seleção para habilitá-la e selecione OK para finalizar.

Quando a janela do Editor de código^{(Code Editor)} for aberta, verifique se a planilha em que seus dados estão está selecionada no painel esquerdo. Selecione Planilha^(Worksheet) na lista suspensa à esquerda e Ativar^(Activate) à direita. Isso criará uma nova sub-rotina chamada Worksheet_Activate ().

Esta função será executada sempre que o arquivo de planilha for aberto. Cole o código no painel de script dentro desta sub-rotina.

Este script funcionará nas 12 linhas e carregará o nome do representante da coluna 3, o item da coluna 4 e a venda total da coluna 7.

Quando os dois loops For são concluídos, o array bidimensional arrMyArray contém todos os dados que você especificou da planilha original.

Manipulando Arrays no Excel VBA

Digamos que você queira aplicar um imposto sobre vendas de 5% a todos os preços finais de venda e, em seguida, gravar todos os dados em uma nova planilha.

Você pode fazer isso adicionando outro loop For após o primeiro, com um comando para gravar os resultados em uma nova planilha.

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

Isso “descarregará” todo o array em Sheet2 , com uma linha adicional contendo o total multiplicado por 5% do valor do imposto.

A folha resultante ficará assim.

Como você pode ver, os arrays VBA no ^(VBA)Excel são extremamente úteis e versáteis como qualquer outro truque do Excel^{(any other Excel trick)} . 

O exemplo acima é um uso muito simples para um array. Você pode criar arrays muito maiores e realizar ordenação, média ou muitas outras funções em relação aos dados armazenados neles.

Se você quiser ser realmente criativo, você pode até criar duas matrizes contendo um intervalo de células^{(containing a range of cells)} de duas planilhas diferentes e realizar cálculos entre os elementos de cada matriz.

As aplicações são limitadas apenas pela sua imaginação.

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 dоesn’t have the full funсtionality and pоwer of а full VB aрplication, VBA provides Office users with the flexibility tо integrate Office prоducts and automate 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

• Como excluir linhas em branco no Excel

• Como usar o recurso Speak Cells do Excel

• Como inserir uma planilha do Excel em um documento do Word

• Como usar a análise de variações hipotéticas do Excel

• Como corrigir uma linha no Excel

• Um guia avançado de VBA para MS Excel

• Use nomes de intervalo dinâmico no Excel para listas suspensas flexíveis

• Planilhas Google vs Microsoft Excel – Quais são as diferenças?

• Como remover linhas de grade no Excel

• Preservar referências de células ao copiar uma fórmula no Excel

• Como abrir o Word e o Excel no modo de segurança

• Como classificar por data no Excel

• 5 maneiras de converter texto em números no Excel

• Classificação básica de dados de uma coluna e várias colunas em planilhas do Excel

• Como pesquisar no Excel

• Usando a ferramenta de busca de meta de análise de variações hipotéticas do Excel

• Como calcular o Z-Score no Excel

• Como alfabetizar no Excel

• 10 dicas e truques do Excel para 2019

• Como usar instruções if e if aninhadas no Excel