VBA ArrayList – Mega Guia Atualizado para 2022

Utilizar uma ArrayList do VBA

Um ArrayList é um objeto VBA que pode ser utilizado para armazenar valores. É semelhante a um objeto Collection, mas tem uma flexibilidade muito maior do ponto de vista da programação. Vamos discutir algumas diferenças entre ArrayLists e Collections e Arrays.

• O objeto Collection tem apenas dois métodos (Add, Remove) e duas propriedades (Count, Item), enquanto uma ArrayList tem muitas mais.
• O objeto Collection é apenas de leitura. Uma vez que os valores tenham sido adicionados, o valor indexado não pode ser alterado, enquanto que numa Array List, a edição é possível.
• O objeto ArrayList expande-se e contrai-se em tamanho de acordo com o número de itens que contém. Não precisa de ser dimensionado antes de ser utilizado como uma Array.
• O ArrayList é unidimensional (tal como o objeto Collection) e o tipo de dados predefinido é Variant, o que significa que aceita qualquer tipo de dados, quer sejam numéricos, texto ou data.

De muitas formas, a Array List resolve uma série de deficiências do objeto Collection. Ele é certamente muito mais flexível no que pode fazer.

O objeto Array List não faz parte da biblioteca VBA padrão. É possível usá-lo em seu código VBA do Excel usando a vinculação tardia ou antecipada.

Sub ExemploVinculacaoTardia()
Dim MinhaLista As Object
Set MinhaLista = CreateObject("System.Collections.ArrayList")
End Sub

Sub ExemploVinculacaoAntecipada()
Dim MinhaLista As New ArrayList
End Sub

Para utilizar o exemplo de vinculação antecipada, você deve primeiro introduzir uma referência no VBA para o arquivo ‘mscorlib.tlb’

Para isso, selecione ‘Ferramentas | Referências’ na janela do Editor do Visual Basic (VBE). Aparecerá uma janela pop-up com todas as referências disponíveis. Role para baixo até ‘mscorlib.dll’ e marque a caixa ao lado dela. Clique em OK e a biblioteca passa a fazer parte do seu projeto:

Uma das grandes desvantagens de um objeto Array List é que ele não tem ‘Intellisense’. Normalmente, quando estamos utilizando um objeto em VBA, como um Range, vemos uma lista pop-up de todas as propriedades e métodos disponíveis. Isto não acontece com um objeto Array List e, por vezes, é necessário verificar cuidadosamente se o método ou a propriedade foram escritos corretamente.

Além disso, se pressionar F2 na janela do VBE e pesquisar por ‘arraylist’, não aparece nada, o que não é muito útil para um programador.

O seu código será executado consideravelmente mais rápido com a vinculação antecipada, porque é tudo compilado antecipadamente. Com a vinculação tardia, o objeto tem de ser compilado à medida que o código é executado

Distribuindo seu Aplicativo Excel que Contém uma ArrayList

Como já foi dito, o objeto ArrayList não faz parte do Excel VBA. Isso significa que qualquer um dos seus colegas a quem você distribuir o aplicativo deve ter acesso ao arquivo ‘mscorlib.tlb’

Este arquivo está normalmente localizado em:

C:\Windows\Microsoft.NET\Framework\v4.0.30319

Pode valer a pena escrever algum código (utilizando o método Dir) para verificar se este arquivo existe quando um usuário carrega a aplicação, de modo a que tenha uma “caminho suave” se não for encontrado. Se ele não estiver presente e o código for executado, ocorrerão erros.

Além disso, o usuário tem de ter instalada a versão correta do .Net Framework. Mesmo que o usuário tenha uma versão posterior, a V3.5 tem de estar instalada, caso contrário a aplicação não funcionará

Escopo de um Objeto ArrayList

Em termos de escopo, o objeto Array List só está disponível enquanto a pasta de trabalho estiver aberta. Não é salvo quando a pasta de trabalho é salva. Se a pasta de trabalho for reaberta, o objeto ArrayList tem de ser recriado utilizando o código VBA.

Se quiser que a sua Array List esteja disponível para todo o código no seu módulo de código, tem de declarar o objeto Array List na secção Declare, no topo da janela do módulo

Isso garantirá que todo o seu código dentro desse módulo possa acessar a Array List. Se quiser que qualquer módulo do seu arquivo acesse o objeto Array List, defina-o como um objeto global.

Global MinhaColecao As New ArrayList

Preenchimento e Leitura da ArrayList

A ação mais básica que se pretende realizar é criar uma Array List, colocar alguns dados na mesma e, em seguida, provar que os dados podem ser lidos. Todos os exemplos de código neste artigo assumem que se está utilizando a vinculação antecipada e que se adicionou ‘mscorlib.tlb’ às referências VBA, conforme descrito acima.

Sub ExemploArrayList()
'Criar novo objeto Array List
Dim MinhaLista As New ArrayList

'Adicionar itens à lista
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"

'Iterar pela array list para verificar os valores
For N = 0 To MinhaLista.Count - 1
    MsgBox MinhaLista(N)
Next N

End Sub

Este exemplo cria um novo objeto ArrayList, preenche-o com 3 itens e itera através da lista, apresentando cada item.

Observe que o índice do ArrayList começa em 0, não em 1, portanto, é necessário subtrair 1 do valor Count

Também é possível usar um loop ‘For…Each’ para ler os valores:

Sub ExemploArrayList()
'Criar novo objeto Array List
Dim MinhaLista As New ArrayList

'Adicionar itens à lista
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"

'Iterar pela array list para verificar os valores
For Each I In MinhaLista
    MsgBox I
Next I

End Sub

Edição e Modificação de Itens em uma ArrayList

Uma das principais vantagens de uma Array List em relação a uma Collection é que os itens da lista podem ser editados e alterados dentro do seu código. O objeto Collection é apenas de leitura, enquanto o objeto Array List é de leitura/escrita.

Sub ExemploArrayList()
'Criar novo objeto Array List
Dim MinhaLista As New ArrayList

'Adicionar itens à lista
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"

'Mudar item 1 from ‘Item2’ to ‘Modificado’
MinhaLista(1) = "Modificado"

'Iterar pela array list to mostrar que a alteração funcionou
For Each I In MinhaLista
    'Mostrar o nome do item
    MsgBox I
Next I

End Sub

Neste exemplo, o segundo item, ‘Item2’, é alterado para o valor ‘Modificado’ (lembre-se de que o índice começa em 0). Quando a iteração é executada no final do código, o novo valor é apresentado.

Adicionando uma matriz de valores a uma ArrayList

Pode introduzir valores na sua Array List utilizando uma matriz que contenha uma lista destes valores ou referências a valores de células numa planilha

Sub ExemploAdicionarMatriz()
'Criar objeto Array List
Dim MinhaLista As New ArrayList

'Iterar através dos valores da matriz, adicionando-os à array list
For Each v In Array("A1", "A2", "A3")
    'Adicionar cada valor da matriz à lista		
    MinhaLista.Add v
Next

'itera pelos valores do array com referências de planilha, adicionando-os ao array list
For Each v In Array(Range("A5").Value, Range("A6").Value)
    MinhaLista.Add v
Next

'Iterar pela array list verificar valores
For N = 0 To MinhaLista.Count - 1
   'Mostrar item da lista
    MsgBox MinhaLista.Item(N)
Next N

End Sub

Leitura/Recuperação de um Intervalo de Itens de uma ArrayList

Ao utilizar o método GetRange numa Array List, você pode especificar um intervalo de itens consecutivos a serem recuperados. Os dois parâmetros necessários são a posição do índice inicial e o número de itens a serem recuperados. O código preenche um segundo objeto Array List com o subconjunto de itens que podem ser lidos separadamente.

Sub ExemploLerIntervalo()
'Definir objetos
Dim MinhaLista As New ArrayList, MinhaLista1 As Object

'Adicionar itens ao objeto 'MinhaLista'
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"
MinhaLista.Add "Item6"
MinhaLista.Add "Item4"
MinhaLista.Add "Item7"

'Capturar 4 itens em ‘MinhaLista’ iniciando pela posição índice 2
Set MinhaLista1 = MinhaLista.GetRange(2, 4)

'Iterar através do objeto 'MinhaLista1' para mostrar o subconjunto de itens
For Each I In MinhaLista1
   'Mostrar o nome do item
    MsgBox I
Next I

End Sub

Pesquisa de Itens em uma ArrayList

Você pode testar se um item nomeado está na sua lista utilizando o método ‘Contains’. Ele retornará True ou False

MsgBox MinhaLista.Contains("Item2")

Também pode encontrar a posição atual do índice utilizando o método ‘IndexOf’. É necessário especificar o índice inicial para a pesquisa (normalmente 0). O valor de retorno é o índice da primeira instância do item encontrado. Pode então utilizar um ciclo para alterar o ponto de partida para o valor de índice seguinte para encontrar mais instâncias se existirem vários valores duplicados.

Se o valor não for encontrado, é devolvido um valor de -1

Este exemplo demonstra a utilização de ‘Contains’, item não encontrado, e um ciclo através da ArrayList para encontrar a posição de todos os itens duplicados:

Sub ExemploPesquisarLista()
'Definir array list and variáveis
Dim MinhaLista As New ArrayList, Sp As Integer, Pos As Integer

'Adicionar novos itens, incluindo um duplicado
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"
MinhaLista.Add "Item1"


'Testar se o 'Item2' está na lista - retorna Verdadeiro
MsgBox MinhaLista.Contains("Item2")

'Obter o índice de um valor inexistente - retorna -1
MsgBox MinhaLista.IndexOf("Item", 0)

'Definir a posição inicial da pesquisa como zero
Sp = 0

'Iterar pela lista para obter todas as posições de 'Item1"
Do
     'Obter a posição do índice do próximo 'Item1' com base na posição na variável 'Sp'
    Pos = MinhaLista.IndexOf("Item1", Sp)
   'Se nenhuma outra instância de 'Item1' for encontrada, saia do loop
    If Pos = -1 Then Exit Do
   'Exibir a próxima instância encontrada e a posição do índice
    MsgBox MinhaLista(Pos) & " no índice " & Pos
   'Adicione 1 ao último valor de índice encontrado - isso agora se torna a nova posição inicial para a próxima pesquisa
    Sp = Pos + 1
Loop

End Sub

Note-se que o texto de pesquisa utilizado é sensível a maiúsculas e minúsculas e não são aceitos caracteres curinga.

Inserir e Remover Itens

Se você não quiser adicionar os seus itens no final da lista, pode inseri-los numa determinada posição de índice para que o novo item fique no meio da lista. Os números de índice serão automaticamente ajustados para os itens subsequentes.

Sub ExemploInserir()
'Definir objeto array list
Dim MinhaLista As New ArrayList

'Adicionar itens ao array list
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"
MinhaLista.Add "Item1"

'Inserir 'Item6' na posição índice 2
MinhaLista.Insert 2, "Item6"

'Iterar pelos itens na array list para mostrar a nova ordem e a posição no índice
For N = 0 To MinhaLista.Count - 1
    MsgBox MinhaLista(N) & " Índice " & N
Next N

End Sub

Neste exemplo, o ‘Item6’ é adicionado à lista na posição de índice 2, por este motivo o ‘item3’, que estava na posição de índice 2, passa agora para a posição de índice 3.

Um item individual pode ser removido utilizando o método “Remove”.

MinhaLista.Remove "Item"

Note que não é produzido nenhum erro se o nome do item não for encontrado. Todos os números de índice subsequentes serão alterados para se adequarem à remoção.

Se souber a posição de índice do item, pode utilizar o método ‘RemoveAt’, por exemplo

MinhaLista.RemoveAt 2

Note que, se a posição do índice dado for maior do que o número de itens na lista da matriz, então será retornado um erro.

Você pode remover um intervalo de valores da lista utilizando o método ‘RemoveRange’. Os parâmetros são o índice inicial e depois o número de itens a remover, por exemplo

MinhaLista.RemoveRange 3, 2

Tenha em mente que o seu código apresentará um erro se o número de itens deslocados do valor inicial for superior ao número de itens na lista da matriz.

Tanto no método “RemoveAt” como no método “RemoveRange”, seria aconselhável algum código para verificar se os números de índice especificados são superiores ao número total de itens na lista da matriz, de modo a detectar eventuais erros. A propriedade “Count” indica o número total de itens na ArrayList.

Sub ExamploRemover()
'Definir objeto array list
Dim MinhaLista As New ArrayList
'Adicionar itens ao array list
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"
MinhaLista.Add "Item1"
MinhaLista.Add "Item4"
MinhaLista.Add "Item5"
'Inserir ‘Item6’ na posição índice 2
MinhaLista.Insert 2, "Item6"
'Remover ‘Item2’
MinhaLista.Remove "Item2"
'Remover ‘Item’ – isso não existe na array list, mas não apresenta erro
MinhaLista.Remove "Item"
'Remover item ma posição índice 2
MinhaLista.RemoveAt 2
'Remover 2 itens consecutivos iniciando na posição índice 2
MinhaLista.RemoveRange 3, 2
'Iterar pelo array list para mostrar o que resta e em que posição de índice está agora
For N = 0 To MinhaLista.Count - 1
    MsgBox MinhaLista(N) & " Índice " & N
Next N
End Sub

Note que se estiver utilizando a propriedade ‘RemoveAt’ para remover um item numa posição específica, assim que esse item for removido, todas as posições de índice subsequentes serão alteradas. Se tiver múltiplas remoções utilizando a posição de índice, então uma boa ideia é começar com o número de índice mais elevado e recuar até à posição zero, de modo a remover sempre o item correto. Desta forma, não terá o problema

Ordenação de uma ArrayList

Outra grande vantagem sobre uma coleção é que você pode ordenar os itens por ordem ascendente ou descendente.

O objeto ArrayList é o único objeto no Excel VBA com um método de ordenação. O método de ordenação é muito rápido e isso pode ser uma consideração importante para usar uma Array List.

No objeto coleção, era necessário pensar um pouco “fora da caixa” para ordenar todos os itens, mas com uma Array List é muito simples.

O método ‘Sort’ ordena por ordem crescente e o método ‘Reverse’ ordena por ordem decrescente.

Sub ExemploArrayList()
'Criar Objeto Array List
Dim MinhaLista As New ArrayList
'Adicionar itens em uma ordem não classificada
MinhaLista.Add "Item1"
MinhaLista.Add "Item3"
MinhaLista.Add "Item2"
'Classificar os itens em ordem crescente
MinhaLista.Sort
'Iterar pelos itens para mostrar a ordem crescente
For Each I In MinhaLista
    'Exibir nome do item
    MsgBox I
Next I
'Classificar os itens em ordem decrescente
MinhaLista.Reverse
'Iterar pelos itens para mostrar a ordem decrescente
For Each I In MinhaLista
    'Exibir nome do item
    MsgBox I
Next I
End Sub

Clonagem de uma ArrayList

Uma Array List tem a possibilidade de criar um clone ou uma cópia de si própria. Isto é útil se um usuário fizer alterações aos itens utilizando um front end e o seu código VBA, mas precisa de manter uma cópia dos itens no seu estado original como cópia de segurança.

Isto pode fornecer ao usuário uma funcionalidade “Desfazer”. O usuário pode ter efetuado as alterações e querer voltar à lista original.

Sub ExamploClonagem()
'Definir dois objetos – array list e um objeto
Dim MinhaLista As New ArrayList, MinhaLista1 As Object
'Preencher o primeiro objeto com itens
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"
'Copiar MinhaLista para MinhaLista1
Set MinhaLista1 = MinhaLista.Clone
'Iterar pelo MinhaLista1 para provar a clonagem
For Each I In MinhaLista1
    'Exibir nome do item
    MsgBox I
Next I
End Sub

a ‘MinhaLista1’ contém agora todos os itens da ‘MinhaLista’ pela mesma ordem

Copiando uma ArrayList para um Objeto Array Convencional do VBA

É possível usar um método simples para copiar a Array List para uma Array VBA normal:

Sub ExemploArray()
'Criar um objeto ArrayList e um objeto Array padrão
Dim MinhaLista As New ArrayList, NovaArray As Variant
'Preencher a ArrayList com itens
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"
'Copiar a ArrayList para a nova Array
NovaArray = MinhaLista.ToArray
'Iterar pela nova Array - observe que a contagem do ArrayList fornece o índice máximo
For N = 0 To MinhaLista.Count - 1 
    'Display item name
    MsgBox NovaArray(N)
Next N
End Sub

Copiar uma ArrayList para um Intervalo de Planilha

Você pode copiar a sua Array List para uma planilha e uma referência de célula específicas sem ter de iterar através da Array List. Só é necessário especificar a primeira referência de célula

Sub ExemploIntervalo()
'Criar um novo objeto ArrayList
Dim MinhaLista As New ArrayList
'Adicionar itens à lista
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"
'Limpar a planilha de destino
Sheets("Planilha1").UsedRange.Clear
'Copiar itens em uma linha
Sheets("Planilha1").Range("A1").Resize(1, MinhaLista.Count).Value = MinhaLista.toArray
'Copiar itens em uma coluna
Sheets("Planilha1").Range("A5").Resize(MinhaLista.Count, 1).Value =  _
WorksheetFunction.Transpose(MinhaLista.toArray)
End Sub

Esvaziar Todos os Itens de uma ArrayList

Existe uma função simples (Clear) para limpar completamente a Array List

Sub ExemploEsvaziarLista()
'Criar um objeto ArrayList
Dim MinhaLista As New ArrayList
'Adicionar novos itens
MinhaLista.Add "Item1"
MinhaLista.Add "Item2"
MinhaLista.Add "Item3"
'Exibir contagem de itens
MsgBox MinhaLista.Count
'Esvaziar todos os itens
MinhaLista.Clear
'Mostrar a contagem de itens para provar que o clear funcionou
MsgBox MinhaLista.Count
End Sub

Este exemplo cria itens numa Array List e, em seguida, limpa a Array List. As caixas de mensagem são apresentadas antes e depois do número de itens na Array List.

Resumo dos métodos de ArrayList para Excel VBA

Tarefa	Parâmetros	Exemplos
Adicionar / Editar item	Valor	MinhaLista.Add “Item1”
		MinhaLista(4)= “Item2”
Clonar uma ArrayList	Nenhum	Dim MinhaLista As Object
		Set MinhaLista2 = MinhaLista.Clone
Copiar para uma Array	Nenhum	Dim MinhaArray As Variant
		MinhaArray = MinhaLista.ToArray
Copiar para um intervalo (linha) da planilha	Nenhum	Sheets(“Planilha1”).Range(“A1”).Resize(1, MinhaLista.Count).Value = MinhaLista.ToArray
Copiar para um intervalo da planilha (coluna)	Nenhum	Sheets(“Planilha1”).Range(“A3”).Resize(MinhaLista.Count, 1).Value = WorksheetFunction.Transpose(MinhaLista.ToArray)
Criar	“System.Collections.ArrayList”	Dim MinhaLista As Object
		Set MinhaLista = CreateObject(“System.Collections.ArrayList”)
Declare	N/A	Dim MinhaLista As Object
Procurar / verificar se o item existe	Item a encontrar	MinhaLista.Contains(“Item2”)
Encontrar a posição de um item na ArrayList	1. Item a localizar.	Dim NrIndice As Long
	2. Posição a partir da qual se inicia a pesquisa.	NrIndice = MinhaLista.IndexOf(“Item3”, 0)
		NrIndice = MinhaLista.IndexOf(“Item5”, 3)
Obter o número de itens	Nenhum	MsgBox MinhaLista.Count
Inserir item	1. Índice – posição a inserir.	MinhaLista.Insert 0, “Item5”
	2 Valor – objeto ou valor a inserir.	MinhaLista.Insert 4, “Item7”
Ler item	Índice – número inteiro longo	MsgBox MinhaLista.Item(0)
		MsgBox MinhaLista.Item(4)
Ler o último item adicionado	Índice – número inteiro longo	MsgBox MinhaLista.Item(list.Count – 1)
Ler o primeiro item adicionado	Índice – número inteiro longo	MsgBox MinhaLista.Item(0)
Ler todos os itens(For Each)	N/A	Dim Elemento As Variant
		For each Elemento in MinhaLista
		MsgBox Elemento
		Next Elemento
Ler todos os itens(For)	Índice – número inteiro longo	Dim i As Long
		For i = 0 To MinhaLista.Count – 1
		MsgBox i
		Next i
Remover todos os itens	Nenhum	MinhaLista.Clear
Remover item na posição	Posição do índice onde o item se encontra	MinhaLista.RemoveAt 5
Remover item por nome	O item a remover da ArrayList	MinhaLista.Remove “Item3”
Remover um intervalo de itens	1. Índice – posição inicial.	MinhaLista.RemoveRange 4,3
	2. Count – o número de itens a remover.
Ordenar por ordem descendente	Nenhum	MinhaLista.Reverse
Ordenar por ordem ascendente	Não	MinhaLista.Sort