Arrays VBA

No VBA, um Variedade é uma única variável que pode conter vários valores. Pense em uma matriz como um intervalo de células: cada célula pode armazenar um valor. Os arrays podem ser unidimensionais (pense em uma única coluna), bidimensionais (pense em várias linhas e colunas) ou multidimensionais. Os valores do array podem ser acessados ​​por sua posição (número de índice) dentro do array.

VBA Array Quick Sheet

Matrizes

DescriçãoCódigo VBACrioDim arr (1 a 3) como variante
arr (1) = “um”
arr (2) = “dois”
arr (3) = “três”Criar a partir do ExcelDim arr (1 a 3) como variante
Dim cell As Range, i As Integer
i = LBound (arr)
Para cada célula no intervalo (“A1: A3”)
i = i + 1
arr (i) = cell.value
Próxima célulaLeia todos os itensDim i tão longo
Para i = LBound (arr) To UBound (arr)
MsgBox arr (i)
Proximo euApagarApagar arrArray para StringDim sName As String
sName = Join (arr, “:”)Aumentar o tamanhoReDim Preserve chega (0 a 100)Definir valorarr (1) = 22

Exemplos rápidos de VBA Array

Vejamos um exemplo completo antes de entrarmos em detalhes:

12345678910

Sub ArrayExample ()Dim strNames (1 a 4) como stringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"msgbox strNames (3)End Sub

Aqui, criamos a matriz de string unidimensional: strNames com tamanho quatro (pode conter quatro valores) e atribuímos os quatro valores. Por último, exibimos o terceiro valor em uma caixa de mensagem.

Nesse caso, o benefício de usar um Array é pequeno: apenas uma declaração de variável é necessária em vez de quatro.

No entanto, vamos dar uma olhada em um exemplo que mostrará o verdadeiro poder de uma matriz:

12345678

Sub ArrayExample2 ()Dim strNames (1 a 60000) como stringDim i enquantoPara i = 1 a 60.000strNames (i) = Células (i, 1) .ValorProximo euEnd Sub

Aqui, criamos uma matriz que pode conter 60.000 valores e rapidamente populamos a matriz da coluna A de uma planilha.

Benefícios do array? - Velocidade!

Você pode pensar em matrizes semelhantes às planilhas do Excel:

• Cada célula (ou item em uma matriz) pode conter seu próprio valor
• Cada célula (ou item em uma matriz) pode ser acessada por sua posição de linha e coluna.
  • Planilha Ex. células (1,4). valor = “Linha 1, Coluna 4”
  • Array Ex. arrVar (1,4) = “Linha 1, Coluna 4”

Então, por que se preocupar com Arrays? Por que não apenas ler e gravar valores diretamente nas células do Excel? Uma palavra: Velocidade!

Ler / escrever em células do Excel é um processo lento. Trabalhar com Arrays é muito mais rápido!

Criar / Declarar um Array (Dim)

Nota: As matrizes podem ter várias “dimensões”. Para manter as coisas simples, começaremos trabalhando apenas com matrizes unidimensionais. Posteriormente no tutorial, apresentaremos a você matrizes de múltiplas dimensões.

Matriz Estática

Matrizes estáticas são matrizes que não podem mudar de tamanho. Por outro lado, Matrizes Dinâmicas pode mudar de tamanho. Eles são declarados de forma ligeiramente diferente. Primeiro, vamos dar uma olhada em matrizes estáticas.

Observação: se o tamanho do seu array não mudar, use um array estático.

Declarar uma variável de matriz estática é muito semelhante a declarar uma variável regular, exceto que você deve definir o tamanho da matriz. Existem várias maneiras de definir o tamanho de uma matriz.

Você pode declarar explicitamente as posições inicial e final de uma matriz:

123456789101112

Sub StaticArray1 ()'Cria um array com as posições 1,2,3,4Dim arrDemo1 (1 a 4) como string'Cria uma matriz com as posições 4,5,6,7Dim arrDemo2 (4 a 7) enquanto'Cria array com posições 0,1,2,3Dim arrDemo3 (0 a 3) enquantoEnd Sub

Ou você pode inserir apenas o tamanho da matriz:

123456

Sub StaticArray2 ()'Cria array com posições 0,1,2,3Dim arrDemo1 (3) As StringEnd Sub

Importante! Observe que, por padrão, os Arrays começam na posição 0. Então Dim arrDemo1 (3) cria uma matriz com posições 0,1,2,3.

Você pode declarar Opção Base 1 no topo do seu módulo para que a matriz comece na posição 1:

12345678

Opção Base 1Sub StaticArray3 ()'Cria matriz com posições 1,2,3Dim arrDemo1 (3) As StringEnd Sub

No entanto, acho que é muito mais fácil (e menos confuso) apenas declarar explicitamente as posições inicial e final das matrizes.

Cansado de procurar exemplos de código VBA? Experimente o AutoMacro!

Matriz Dinâmica

Matrizes Dinâmicas são matrizes cujo tamanho pode ser alterado (ou cujo tamanho não precisa ser definido).

Existem duas maneiras de declarar um Dynamic Array.

Variant Arrays

A primeira maneira de declarar um Dynamic Array é definindo o array para o tipo Variante.

1	Dim arrVar () como variante

Com um Variante Array, você não precisa definir o tamanho da matriz. O tamanho será ajustado automaticamente. Apenas lembre-se de que o Array começa com a posição 0 (a menos que você adicione Option Base 1 no topo do seu módulo)

12345678910111213

Sub VariantArray ()Dim arrVar () como variante'Definir Valores (Tamanho = 0,1,2,3)arrVar = Matriz (1, 2, 3, 4)'Alterar Valores (Tamanho = 0,1,2,3,4)arrVar = Matriz ("1a", "2a", "3a", "4a", "5a")'Posição de saída 4 ("5a")MsgBox arrVar (4)End Sub

Matrizes dinâmicas não variantes

Com matrizes não variantes, você deve definir o tamanho da matriz antes de atribuir valores à matriz. No entanto, o processo para criar a matriz é um pouco diferente:

1234567

Sub DynamicArray1 ()Dim arrDemo1 () como string'Resizes array com posições 1,2,3,4ReDim arrDemo1 (1 a 4)End Sub

Primeiro você declara a matriz, semelhante à matriz estática, exceto que você omite o tamanho da matriz:

1	Dim arrDemo1 () como string

Agora, quando você deseja definir o tamanho do array, você usa o ReDim comando para dimensionar a matriz:

12	'Resizes array com posições 1,2,3,4ReDim arrDemo1 (1 a 4)

ReDim redimensiona a matriz. Leia abaixo a diferença entre ReDim e ReDim Preserve.

ReDim vs. ReDim Preserve

Quando você usa o ReDim comando você limpa todos os valores existentes da matriz. Em vez disso, você pode usar ReDim Preserve para preservar os valores da matriz:

12	'Resizes array com as posições 1,2,3,4 (preservando os valores existentes)Preservar ReDim arrDemo1 (1 a 4)

Declaração de matrizes simplificada

Você pode estar se sentindo oprimido depois de ler tudo acima. Para manter as coisas simples, trabalharemos principalmente com matrizes estáticas no restante do artigo.

Definir valores de matriz

Definir os valores do array é muito fácil.

Com uma matriz estática, você deve definir cada posição da matriz, uma de cada vez:

12345678

Sub ArrayExample ()Dim strNames (1 a 4) como stringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"End Sub

Com um Variant Array, você pode definir todo o array com uma linha (prático apenas para pequenos arrays):

123456

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

Se você tentar definir um valor para um local de matriz que não existe, receberá um erro Subscrito fora do intervalo:

1	strNames (5) = "Shannon"

Na seção ‘Atribuir intervalo ao array’ abaixo, mostraremos como usar um loop para atribuir rapidamente um grande número de valores aos arrays.

Obter valor de matriz

Você pode buscar valores de array da mesma maneira. No exemplo abaixo, escreveremos valores de matriz nas células:

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

Programação VBA | O Code Generator funciona para você!

Atribuir intervalo à matriz

Para atribuir um intervalo a uma matriz, você pode usar um loop:

12345678

Sub RangeToArray ()Dim strNames (1 a 60000) como stringDim i enquantoPara i = 1 a 60.000strNames (i) = Células (i, 1) .ValorProximo euEnd Sub

Isso percorrerá as células A1: A60000, atribuindo os valores das células à matriz.

Output Array to Range

Ou você pode usar um loop para atribuir uma matriz a um intervalo:

123	Para i = 1 a 60.000Células (i, 1) .Value = strNames (i)Proximo eu

Isso fará o inverso: atribuir valores de matriz às células A1: A60000

Matrizes 2D / multidimensionais

Até agora, trabalhamos exclusivamente com matrizes unidimensionais (1D). No entanto, as matrizes podem ter até 32 dimensões.

Pense em uma matriz 1D como uma única linha ou coluna de células do Excel, uma matriz 2D como uma planilha inteira do Excel com várias linhas e colunas, e uma matriz 3D é como uma pasta de trabalho inteira, contendo várias planilhas, cada uma contendo várias linhas e colunas (Você também pode pensar em uma matriz 3D como um cubo de Rubik).

Exemplos de matriz multidimensional

Agora vamos demonstrar exemplos de como trabalhar com matrizes de diferentes dimensões.

Programação VBA | O Code Generator funciona para você!

Exemplo de array 1D

Este procedimento combina os exemplos de array anteriores em um procedimento, demonstrando como você pode usar arrays na prática.

1234567891011121314

Sub ArrayEx_1d ()Dim strNames (1 a 60000) como stringDim i enquanto'Atribuir valores à matrizPara i = 1 a 60.000strNames (i) = Células (i, 1) .ValorProximo eu'Valores de matriz de saída para intervaloPara i = 1 a 60.000Folhas ("Saída"). Células (i, 1) .Valor = strNames (i)Proximo euEnd Sub

Exemplo de array 2D

Este procedimento contém um exemplo de uma matriz 2D:

123456789101112131415161718

Sub ArrayEx_2d ()Dim strNames (1 a 60000, 1 a 10) como stringDim i As Long, j As Long'Atribuir valores à matrizPara i = 1 a 60.000Para j = 1 a 10strNames (i, j) = Células (i, j) .ValorPróximo jProximo eu'Valores de matriz de saída para intervaloPara i = 1 a 60.000Para j = 1 a 10Folhas ("Saída"). Células (i, j) .Valor = strNames (i, j)Próximo jProximo euEnd Sub

Exemplo de matriz 3D

Este procedimento contém um exemplo de uma matriz 3D para trabalhar com várias folhas:

12345678910111213141516171819202122

Sub ArrayEx_3d ()Dim strNames (1 a 60000, 1 a 10, 1 a 3) como stringDim i As Long, j As Long, k As Long'Atribuir valores à matrizPara k = 1 a 3Para i = 1 a 60.000Para j = 1 a 10strNames (i, j, k) = Folhas ("Folha" & k). Células (i, j) .ValorPróximo jProximo euPróximo k'Valores de matriz de saída para intervaloPara k = 1 a 3Para i = 1 a 60.000Para j = 1 a 10Folhas ("Saída" & k). Células (i, j) .Valor = strNames (i, j, k)Próximo jProximo euPróximo kEnd Sub

Comprimento / tamanho da matriz

Até agora, apresentamos os diferentes tipos de matrizes e ensinamos como declarar as matrizes e obter / definir valores de matrizes. A seguir, vamos nos concentrar em outros tópicos necessários para trabalhar com matrizes.

Programação VBA | O Code Generator funciona para você!

Funções UBound e LBound

A primeira etapa para obter o comprimento / tamanho de uma matriz é usar as funções UBound e LBound para obter os limites superior e inferior da matriz:

123456

Sub UBoundLBound ()Dim strNames (1 a 4) como stringMsgBox UBound (strNames)MsgBox LBound (strNames)End Sub

Subtraindo os dois (e adicionando 1), você terá o comprimento:

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

Função de comprimento de matriz

Aqui está uma função para obter o comprimento de uma matriz unidimensional:

1234567

Função pública GetArrLength (a As Variant) As LongIf IsEmpty (a) ThenGetArrLength = 0OutroGetArrLength = UBound (a) - LBound (a) + 1Fim seFunção Final

Precisa calcular o tamanho de um array 2D? Confira nosso tutorial: Calculate Size of Array.

Loop Through Array

Existem duas maneiras de percorrer um array. O primeiro percorre os inteiros correspondentes às posições numéricas da matriz. Se você souber o tamanho do array, pode especificá-lo diretamente:

12345678910111213

Sub ArrayExample_Loop1 ()Dim strNames (1 a 4) como stringDim i enquantostrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Para i = 1 a 4MsgBox strNames (i)Proximo euEnd Sub

No entanto, se você não souber o tamanho da matriz (se a matriz for dinâmica), você pode usar as funções LBound e UBound da seção anterior:

12345678910111213

Sub ArrayExample_Loop2 ()Dim strNames (1 a 4) como stringDim i enquantostrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Para i = LBound (strNames) para UBound (strNames)MsgBox strNames (i)Proximo euEnd Sub

Para cada Array Loop

O segundo método é com um For Each Loop. Isso percorre cada item da matriz:

12345678910111213

Sub ArrayExample_Loop3 ()Dim strNames (1 a 4) como stringItem DimstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Para cada item em strNamesItem MsgBoxPróximo itemEnd Sub

O For Each Array Loop funcionará com arrays multidimensionais, além de arrays unidimensionais.

Programação VBA | O Code Generator funciona para você!

Loop Through 2D Array

Você também pode usar as funções UBound e LBound para percorrer uma matriz multidimensional. Neste exemplo, faremos um loop em uma matriz 2D. Observe que as funções UBound e LBound permitem que você especifique qual dimensão da matriz encontrar os limites superior e inferior (1 para a primeira dimensão, 2 para a segunda dimensão).

1234567891011121314151617181920

Sub ArrayExample_Loop4 ()Dim strNames (1 a 4, 1 a 2) como stringDim i As Long, j As LongstrNames (1, 1) = "Shelly"strNames (2, 1) = "Steve"strNames (3, 1) = "Neema"strNames (4, 1) = "Jose"strNames (1, 2) = "Shelby"strNames (2, 2) = "Steven"strNames (3, 2) = "Nemo"strNames (4, 2) = "Jesse"Para j = LBound (strNames, 2) Para UBound (strNames, 2)Para i = LBound (strNames, 1) Para UBound (strNames, 1)MsgBox strNames (i, j)Proximo euPróximo jEnd Sub

Outras tarefas de array

Clear Array

Para limpar uma matriz inteira, use a instrução Erase:

1	Apagar strNames

Exemplo de uso:

12345678910

Sub ArrayExample ()Dim strNames (1 a 4) como stringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Apagar strNamesEnd Sub

Como alternativa, você também pode ReDim a matriz para redimensioná-la, limpando parte da matriz:

1	ReDim strNames (1 a 2)

Isso redimensiona a matriz para o tamanho 2, excluindo as posições 3 e 4.

Count Array

Você pode contar o número de posições em cada dimensão de uma matriz usando as funções UBound e LBound (discutidas acima).

Você também pode contar o número de itens inseridos (ou itens que atendem a certos critérios) percorrendo a matriz.

Este exemplo fará um loop por uma matriz de objetos e contará o número de strings não vazias encontradas na matriz:

123456789101112131415

Sub ArrayLoopandCount ()Dim strNames (1 a 4) como stringDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"Para i = LBound (strNames) para UBound (strNames)Se strNames (i) "" Entãon = n + 1Fim seProximo euMsgBox n & "valores não em branco encontrados."End Sub

Programação VBA | O Code Generator funciona para você!

Remover Duplicados

Em algum momento, você pode querer remover duplicatas de um Array. Infelizmente, o VBA não tem um recurso interno para fazer isso. No entanto, escrevemos uma função para remover duplicatas de um Array (é muito longo para incluir neste tutorial, mas visite o link para saber mais).

Filtro

A função de filtro VBA permite filtrar um array. Ele faz isso criando uma nova matriz apenas com os valores filtrados. Abaixo está um exemplo rápido, mas certifique-se de ler o artigo para mais exemplos para diferentes necessidades.

1234567891011121314

Sub Filter_Match ()'Definir MatrizDim strNames As VariantstrNames = Array ("Steve Smith", "Shannon Smith", "Ryan Johnson")'Filter ArrayDim strSubNames como variantestrSubNames = Filtro (strNames, "Smith")'Count Filtered ArrayMsgBox "Found" & UBound (strSubNames) - LBound (strSubNames) + 1 & "nomes."End Sub

Função IsArray

Você pode testar se uma variável é uma matriz usando a função IsArray:

123456789101112

Sub IsArrayEx ()'Cria matriz com posições 1,2,3Dim arrDemo1 (3) As String'Cria variável de string regularDim str As StringMsgBox IsArray (arrDemo1)MsgBox IsArray (str)End Sub

Junte-se ao Array

Você pode “juntar” rapidamente um array inteiro com a função Join:

123456789101112

Sub Array_Join ()Dim strNames (1 a 4) como stringDim joinNames As StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Join (strNames, ",")MsgBox joinNamesEnd Sub

Programação VBA | O Code Generator funciona para você!

Dividir String em Matriz

A função de divisão do VBA dividirá uma string de texto em uma matriz contendo valores da string original. Vejamos um exemplo:

123456789

Sub Array_Split ()Dim Names () As StringDim joinNames As StringjoinNames = "Shelly, Steve, Nema, Jose"Nomes = Dividir (joinNames, ",")Nomes de MsgBox (1)End Sub

Aqui, dividimos esta string de texto “Shelly, Steve, Nema, Jose” em uma matriz (tamanho 4) usando um delimitador de vírgula (, ”).

Const Array

Uma matriz não pode ser declarado como uma constante no VBA. No entanto, você pode contornar isso criando uma função para usar como um Array:

123456789

'Define ConstantArrayFunção ConstantArray ()ConstantArray = Array (4, 12, 21, 100, 5)Função Final'Retrive ConstantArray ValueSub RetrieveValues ​​()MsgBox ConstantArray (3)End Sub

Copiar Matriz

Não há uma maneira embutida de copiar um Array usando VBA. Em vez disso, você precisará usar um loop para atribuir os valores de uma matriz a outra.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 a 100) enquantoDim Arr2 (1 a 100) enquantoDim i enquanto'Criar Matriz1Para i = 1 a 100Arr1 (i) = iProximo eu'CopyArray1 para Array2Para i = 1 a 100Arr2 (i) = Arr1 (i)Proximo euMsgBox Arr2 (74)End Sub

Transpor

Não há função VBA embutida para permitir que você transponha uma matriz. No entanto, escrevemos uma função para transpor um array 2D. Leia o artigo para saber mais.

Programação VBA | O Code Generator funciona para você!

Matriz de retorno de função

Uma dúvida comum que os desenvolvedores de VBA têm é como criar uma função que retorna um array. Acho que a maioria das dificuldades são resolvidas usando Variant Arrays. Escrevemos um artigo sobre o tópico: Matriz de retorno de função VBA.

Usando Arrays no Access VBA

A maioria dos exemplos de Array acima funcionam exatamente da mesma forma no Access VBA e no Excel VBA. A única diferença principal é que, quando você deseja preencher uma matriz usando dados do Access, precisa percorrer o objeto RecordSet em vez do objeto Range.

1234567891011121314151617181920212223

Sub RangeToArrayAccess ()On Error Resume NextDim strNames () As StringDim i enquantoDim iCount enquantoDim dbs como banco de dadosDim rst As RecordsetDefinir dbs = CurrentDbDefina rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Com o primeiro.MoveLast.MoveFirstiCount = .RecordCountReDim strNames (1 para iCount)Para i = 1 para iCountstrNames (i) = rst.Fields ("ClientName").MoveNextProximo euTerminar comprimeiro.FecharDefinir rst = NadaDefinir dbs = NothingEnd Sub

Tutoriais de matriz
Array Mega-Guide	sim
Obter tamanho da matriz
Clear Array
Matriz de Filtro
Transpor Array
Matriz de retorno de função
Remover Duplicados