O que é uma matriz VBA no Excel e como programar um

O VBA faz parte da suíte do Microsoft Office por muitos anos. Embora não tenha a funcionalidade e o poder completos de um aplicativo VB completo, o VBA oferece aos usuários do escritório a flexibilidade de integrar produtos de escritório e automatizar o trabalho que você faz neles.

Uma das ferramentas mais poderosas disponíveis no VBA é a capacidade de carregar uma gama inteira de dados em uma única variável chamada uma matriz. Ao carregar dados dessa maneira, você pode manipular ou executar cálculos nessa faixa de dados de várias maneiras.

Você pode colocar dados em cada seção desta "caixa" em qualquer ordem que desejar.

No início do seu script VBA, você precisa definir esta "caixa" definindo sua matriz VBA. Então.

Dim Arrmyarray (1 a 6) como string

Mais adiante no seu programa, você pode colocar dados em qualquer seção desta matriz, referenciando a seção dentro dos parênteses.

Arrmyarray (1) = "Ryan Dube"

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

Dim Arrmyarray (1 a 6,1 a 2) como inteiro

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 desta matriz com dados da seguinte forma.

Arrmyarray (1,2) = 3

Isso carregará um 3 na célula B1.

Uma matriz pode conter qualquer tipo de dados como uma variável regular, como cordas, booleanos, números inteiros, carros alegóricos e muito mais.

O número dentro dos parênteses também pode ser uma variável. Os programadores geralmente usam um loop for para contar através de todas as seções de uma matriz e carregar células de dados na planilha na matriz. Você verá como fazer isso mais tarde 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 uma matriz multidimensional.

Como exemplo, vejamos uma planilha de vendas de produtos, onde você deseja puxar 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 a partir do canto superior esquerdo em 1. Portanto, a coluna representante é 3, a coluna do item é 4 e a coluna total é 7.

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

Dim Arrmyarray (1 a 11, 1 a 3) como corda
Dim i como inteiro, j como inteiro

Para i = 2 a 12
Para J = 1 a 3
Arrmyarray (i-1, j) = células (i, j).Valor
Próximo j
Proximo eu

Você notará que, para pular a linha do cabeçalho, o número da linha no primeiro para a aparência 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 células (i, j).Valor.

Onde inserir seu script de matriz VBA

Para colocar um script VBA no Excel, você precisa usar o editor VBA. Você pode acessar isso selecionando o Desenvolvedor menu e selecionando Exibir código na seção de controles da fita.

Se você não vê o desenvolvedor no menu, precisará adicioná -lo. Para fazer isso, selecione Arquivo e Opções Para abrir a janela Opções do Excel.

Alterar os comandos de escolha de suspenso para Todos os comandos. Selecione Desenvolvedor No menu esquerdo e selecione o Adicionar botão para movê -lo para o painel à direita. Selecione a caixa de seleção para ativá -la e selecione OK terminar.

Quando a janela do editor de código abrir, verifique se a folha em que seus dados estão estão selecionados no painel esquerdo. Selecione Planilha no menu suspenso esquerdo e Ativar Na 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á através das 12 linhas e carregará o nome do representante da coluna 3, o item da coluna 4 e a venda total da coluna 7.

Uma vez que os dois loops terminarem, a matriz bidimensional ArrmyArray contém todos os dados que você especificou na folha original.

Manipulando matrizes no Excel VBA

Digamos que você queira aplicar um imposto sobre vendas de 5% a todos os preços finais de venda e depois escrever todos os dados em uma nova folha.

Você pode fazer isso adicionando outro para loop após o primeiro, com um comando para escrever os resultados para uma nova folha.

Para k = 2 a 12
Folhas ("Sheet2").Células (k, 1).Valor = armyarray (k - 1, 1)
Folhas ("Sheet2").Células (k, 2).Valor = armyarray (k - 1, 2)
Folhas ("Sheet2").Células (k, 3).Valor = armyarray (k - 1, 3)
Folhas ("Sheet2").Células (k, 4).Valor = armyarray (k - 1, 3) * 0.05
Próximo k

Isso "descarregará" toda a matriz no Sheet2, com uma linha adicional contendo o total multiplicado por 5% para o valor do imposto.

A folha resultante ficará assim.

Como você pode ver, as matrizes VBA no Excel são extremamente úteis e tão versáteis quanto qualquer outro truque do Excel. 

O exemplo acima é um uso muito simples para uma matriz. Você pode criar matrizes muito maiores e executar classificação, média ou muitas outras funções contra os dados que você armazena neles.

Se você quiser ser criativo real, pode até criar duas matrizes contendo uma variedade de células a partir de duas folhas diferentes e executar cálculos entre elementos de cada matriz.

Os aplicativos são limitados apenas pela sua imaginação.