Um objeto ArrayList é semelhante a um objeto Collection, mas tem muito mais métodos e propriedades e, portanto, muito mais flexibilidade do ponto de vista da programação.
Um objeto Collection tem apenas dois métodos (Add, Remove) e duas propriedades (Count, Item), enquanto uma Array List tem muitos mais. Além disso, o objeto Collection é somente leitura. Uma vez que os valores foram adicionados, o valor indexado não pode ser alterado, enquanto em uma lista de matrizes, a edição é possível.
Muitos dos métodos Array List usam parâmetros. Ao contrário de muitos dos métodos VBA padrão, nenhum desses parâmetros é opcional. Além disso, alguns dos métodos e propriedades nem sempre são capitalizados quando inseridos da mesma forma que no Excel VBA. No entanto, eles ainda funcionam.
O objeto ArrayList se expande e contrai em tamanho de acordo com a quantidade de itens que ele contém. Ele não precisa ser dimensionado antes de ser usado como um Array.
A Array List é unidimensional (igual ao objeto Collection) e o tipo de dados padrão é Variant, o que significa que aceitará qualquer tipo de dado, seja numérico, texto ou data.
De muitas maneiras, a Array List aborda uma série de deficiências do objeto Collection. Certamente é muito mais flexível no que pode fazer.
O objeto Array List não faz parte da biblioteca VBA padrão. Você pode usá-lo em seu código VBA do Excel usando vinculação tardia ou antecipada
| 1234 | Sub LateBindingExample ()Dim MyList As ObjectDefinir MyList = CreateObject ("System.Collections.ArrayList")End Sub |
| 123 | Sub EarlyBindingExample ()Dim MyList As New ArrayListEnd Sub |
Para usar o exemplo de ligação inicial, você deve primeiro inserir uma referência em VBA para o arquivo ‘mscorlib.tlb’
Você pode fazer isso selecionando ‘Ferramentas | Referências 'da janela Editor do Visual Basic (VBE). Uma janela pop-up aparecerá com todas as referências disponíveis. Role para baixo até ‘mscorlib.dll’ e marque a caixa ao lado dele. Clique em OK e essa biblioteca agora faz parte do seu projeto:
Uma das grandes desvantagens de um objeto Array List é que ele não tem "Intellisense". Normalmente, quando estiver usando um objeto no VBA, como um intervalo, você verá uma lista pop-up de todas as propriedades e métodos disponíveis. Isso não é obtido com um objeto Array List e, às vezes, é necessário uma verificação cuidadosa para ter certeza de que você digitou o método ou propriedade corretamente.
Além disso, se você pressionar F2 na janela VBE e pesquisar em ‘arraylist’, nada será exibido, o que não é muito útil para um desenvolvedor.
Seu código será executado consideravelmente mais rápido com a vinculação inicial, porque tudo é compilado antecipadamente. Com a vinculação tardia, o objeto deve ser compilado enquanto o código é executado
Distribuindo seu aplicativo Excel contendo uma lista de matrizes
Como já apontado, o objeto ArrayList não faz parte do Excel VBA. Isso significa que qualquer um de seus colegas para quem você distribui o aplicativo deve ter acesso ao arquivo ‘mscorlib.tlb’
Este arquivo normalmente está localizado em:
C: \ Windows \ Microsoft.NET \ Framework \ v4.0.30319
Pode valer a pena escrever algum código (usando o método Dir) para verificar se este arquivo existe quando um usuário carrega o aplicativo, de modo que experimente um ‘soft landing’ se não for encontrado. Se não estiver presente e o código for executado, ocorrerão erros.
Além disso, o usuário deve ter a versão correta do .Net Framework instalada. Mesmo se o usuário tiver uma versão posterior, a V3.5 deve ser instalada, caso contrário, seu aplicativo não funcionará
Escopo de um objeto de lista de matriz
Em termos de escopo, o objeto Array List está disponível apenas enquanto a pasta de trabalho está aberta. Ele não é salvo quando a pasta de trabalho é salva. Se a pasta de trabalho for reaberta, o objeto Array List precisa ser recriado usando o código VBA.
Se você deseja que sua Lista de Array esteja disponível para todo o código em seu módulo de código, então você precisa declarar o objeto Lista de Array na seção Declare no topo da janela do módulo
Isso garantirá que todo o seu código nesse módulo possa acessar a Lista de matrizes. Se você quiser que qualquer módulo em sua pasta de trabalho acesse o objeto Array List, defina-o como um objeto global
| 1 | Global MyCollection As New ArrayList |
Preenchendo e lendo de sua lista de matrizes
A ação mais básica que você deseja realizar é criar uma lista de arrays, colocar alguns dados nela e então provar que os dados podem ser lidos. Todos os exemplos de código neste artigo presumem que você está usando a ligação inicial e adicionou ‘mscorlib.tlb’ às referências VBA, conforme descrito acima
| 123456789101112 | Sub ArrayListExample ()‘Criar novo objeto de lista de matrizDim MyList As New ArrayList‘Adicionar itens à listaMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Itere através da lista de array para provar os valoresPara N = 0 para MyList.Count - 1MsgBox MyList (N)Próximo NEnd Sub |
Este exemplo cria um novo objeto ArrayList, preenche-o com 3 itens e itera através da lista exibindo cada item.
Observe que o índice ArrayList começa em 0, não 1, então você precisa subtrair 1 do valor de Count
Você também pode usar um loop ‘For… Each’ para ler os valores:
| 123456789101112 | Sub ArrayListExample ()‘Criar novo objeto de lista de matrizDim MyList As New ArrayList‘Adicionar itens à listaMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Itere através da lista de array para provar os valoresPara Cada I Na Minha ListaMsgBox IProximo euEnd Sub |
Editando e Alterando Itens em uma Lista de Array
Uma das principais vantagens de uma Array List sobre uma Collection é que os itens da lista podem ser editados e alterados no código. O objeto Collection é somente leitura, enquanto o objeto Array List é leitura / gravação
| 123456789101112131415 | Sub ArrayListExample ()‘Criar novo objeto de lista de matrizDim MyList As New ArrayList‘Adicionar itens à listaMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Altere o item 1 de‘ Item2 ’para‘ Alterado ’MyList (1) = "Alterado"‘Itere através da lista de array para provar que a mudança funcionouPara Cada I Na Minha Lista‘Exibir nome do itemMsgBox IProximo euEnd Sub |
Neste exemplo, o segundo item, ‘Item2’ é alterado para o valor ‘Alterado’ (lembre-se de que o índice começa em 0). Quando a iteração é executada no final do código, o novo valor será exibido
Adicionando uma matriz de valores a uma lista de matrizes
Você pode inserir valores em sua lista de matrizes usando uma matriz que contém uma lista desses valores ou referências a valores de células em uma planilha
| 123456789101112131415161718 | Sub AddArrayExample ()‘Criar objeto de lista ArrayDim MyList As New ArrayList'Iterar por meio de valores de matriz, adicionando-os à lista de matrizesPara cada v na matriz ("A1", "A2", "A3")‘Adicione cada valor da matriz à listaMyList.Add vPróximo'Iterar por meio de valores de matriz com referências de planilha, adicionando-os à lista de matrizPara Cada v Em Matriz (Faixa ("A5"). Valor, Faixa ("A6"). Valor)MyList.Add vPróximo‘Itere através da lista de array para provar os valoresPara N = 0 para MyList.Count - 1‘Exibir item da listaMsgBox MyList.Item (N)Próximo NEnd Sub |
Lendo / recuperando uma série de itens de uma lista de matrizes
Usando o método GetRange em uma Lista de matrizes, você pode especificar uma série de itens consecutivos a serem recuperados. Os dois parâmetros necessários são a posição inicial do índice 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.
| 123456789101112131415161718 | Sub ReadRangeExample ()‘Definir objetosDim MyList As New ArrayList, MyList1 As Object‘Adicionar itens ao objeto‘ MyList ’MyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item6"MyList.Add "Item4"MyList.Add "Item7"‘Capture 4 itens em‘ MyList ’começando na posição de índice 2Definir MyList1 = MyList.GetRange (2, 4)‘Itere através do objeto‘ MyList1 ’para exibir o subconjunto de itensPara cada I em MyList1‘Exibir nome do itemMsgBox IProximo euEnd Sub |
Procurando por itens em uma lista de matrizes
Você pode testar se um item nomeado está em sua lista usando o método ‘Contém’. Isso retornará verdadeiro ou falso
| 1 | MsgBox MyList.Contains ("Item2") |
Você também pode encontrar a posição real do índice usando o método ‘IndexOf’. Você precisa especificar o índice inicial para a pesquisa (geralmente 0). O valor de retorno é o índice da primeira instância do item encontrado. Você pode então usar um loop para alterar o ponto de partida para o próximo valor de índice para localizar outras instâncias, se houver vários valores duplicados.
Se o valor não for encontrado, um valor de -1 é retornado
Este exemplo demonstra o uso de ‘Contém’, item não encontrado, e percorre a lista de matriz para encontrar a posição de todos os itens duplicados:
| 1234567891011121314151617181920212223242526 | Sub SearchListExample ()‘Definir lista de matriz e variáveisDim MyList As New ArrayList, Sp As Integer, Pos As Integer‘Adicionar novos itens, incluindo uma duplicataMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"‘Teste para“ Item2 ”estando na lista - retorna VerdadeiroMsgBox MyList.Contains ("Item2")‘Obter índice de valor inexistente - retorna -1MsgBox MyList.IndexOf ("Item", 0)‘Defina a posição inicial da pesquisa para zeroSp = 0‘Itere através da lista para obter todas as posições do‘ Item1 ”Fazer‘Obtenha a posição de índice do próximo‘ Item1 ’com base na posição na variável‘ Sp ’Pos = MyList.IndexOf ("Item1", Sp)‘Se nenhuma outra instância de‘ Item1 ’for encontrada, saia do loopSe Pos = -1, então saia e faça‘Exibir a próxima instância encontrada e a posição do índiceMsgBox MyList (Pos) & "no índice" & Pos‘Adicione 1 ao último valor de índice encontrado - agora se torna a nova posição inicial para a próxima pesquisaSp = Pos + 1CicloEnd Sub |
Observe que o texto de pesquisa usado faz distinção entre maiúsculas e minúsculas e curingas não são aceitos.
Inserindo e removendo itens
Se não quiser adicionar seus itens ao final da lista, você pode inseri-los em uma posição de índice específica para que o novo item fique no meio da lista. Os números do índice serão ajustados automaticamente para os itens subsequentes.
| 123456789101112131415 | Sub InsertExample ()‘Definir objeto de lista de matrizDim MyList As New ArrayList‘Adicionar itens à lista de matrizMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"‘Insira‘ Item6 ’na posição de índice 2MyList.Insert 2, "Item6"'Iterar pelos itens na lista de matriz para mostrar a nova ordem e posição de índicePara N = 0 para MyList.Count - 1MsgBox MyList (N) & "Índice" & NPróximo NEnd Sub |
Neste exemplo, ‘Item6’ é adicionado à lista na posição 2 do índice, então o ‘item3’ que estava na posição 2 do índice agora se move para a posição 3 do índice
Um item individual pode ser removido usando o método ‘Remover’.
| 1 | MyList.Remove "Item" |
Observe que nenhum erro será produzido se o nome do item não for encontrado. Todos os números de índice subsequentes serão alterados para se adequar à remoção.
Se você souber a posição do índice do item, pode usar o método ‘RemoveAt’, por exemplo
| 1 | MyList.RemoveAt 2 |
Observe que se a posição do índice fornecida for maior do que o número de itens na lista da matriz, um erro será retornado.
Você pode remover um intervalo de valores da lista usando o método ‘RemoveRange’. Os parâmetros são o índice inicial e o número de itens a serem removidos.
| 1 | MyList.RemoveRange 3, 2 |
Observe que você obterá um erro em seu código se o número de itens deslocados do valor inicial for maior do que o número de itens na lista da matriz.
Em ambos os métodos 'RemoveAt' e 'RemoveRange', algum código seria aconselhável verificar se os números de índice especificados são maiores do que o número total de itens na lista de matriz, a fim de detectar quaisquer erros possíveis. A propriedade ‘Count’ dará o número total de itens na lista da matriz.
| 12345678910111213141516171819202122232425 | Sub RemoveExample ()‘Definir objeto de lista de matrizDim MyList As New ArrayList‘Adicionar itens à lista de matrizMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"MyList.Add "Item4"MyList.Add "Item5"‘Insira‘ Item6 ’na posição de índice 2MyList.Insert 2, "Item6"‘Remover‘ Item2 ’MyList.Remove "Item2"‘Remover‘ Item ’- não existe na lista de matriz, mas não apresenta erroMyList.Remove "Item"‘Remova o item da posição de índice 2MyList.RemoveAt 2‘Remova 2 itens consecutivos começando na posição de índice 2MyList.RemoveRange 3, 2‘Itere através da lista de arrays para mostrar o que resta e em que posição de índice está agoraPara N = 0 para MyList.Count - 1MsgBox MyList (N) & "Índice" & NPróximo NEnd Sub |
Observe que se você estiver usando ‘RemoveAt’ para remover um item em uma posição específica, assim que esse item for removido, todas as posições de índice subsequentes serão alteradas. Se você tiver várias remoções usando a posição do índice, uma boa ideia é começar com o número do índice mais alto e voltar para a posição zero para que você sempre remova o item correto. Desta forma, você não terá o problema
Classificando uma lista de matrizes
Outra grande vantagem sobre uma coleção é que você pode classificar os itens em ordem crescente ou decrescente.
O objeto Array List é o único objeto no Excel VBA com um método de classificação. O método de classificação é muito rápido e isso pode ser uma consideração importante ao usar uma lista de matrizes.
No objeto de coleção, algum pensamento "fora da caixa" foi necessário para classificar todos os itens, mas com uma lista de matriz, é muito simples.
O método ‘Sort’ classifica em ordem crescente, e o método ‘Reverse’ classifica em ordem decrescente.
| 12345678910111213141516171819202122 | Sub ArrayListExample ()‘Criar objeto Array ListDim MyList As New ArrayList‘Adicionar itens em uma ordem não classificadaMyList.Add "Item1"MyList.Add "Item3"MyList.Add "Item2"‘Classifique os itens em ordem crescenteMyList.Sort‘Repita os itens para mostrar a ordem crescentePara Cada I Na Minha Lista‘Exibir nome do itemMsgBox IProximo eu‘Classifique os itens em ordem decrescenteMyList.Reverse‘Repita os itens para mostrar a ordem decrescentePara Cada I Na Minha Lista‘Exibir nome do itemMsgBox IProximo euEnd Sub |
Clonando uma lista de matrizes
Uma lista de arrays tem a facilidade de criar um clone ou uma cópia de si mesma. Isso é útil se um usuário faz alterações nos itens usando um front end e seu código VBA, mas você precisa manter uma cópia dos itens em seu estado original como backup.
Isso pode fornecer ao usuário um recurso de "Desfazer". Eles podem ter feito as alterações e desejam voltar à lista original.
| 123456789101112131415 | Sub CloneExample ()‘Definir dois objetos - lista de matriz e um objetoDim MyList As New ArrayList, MyList1 As Object‘Preencher o primeiro objeto com itensMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Copiar Mylist para MyList1Definir MyList1 = MyList.Clone‘Itere através de MyList1 para provar a clonagemPara cada I em MyList1‘Exibir nome do itemMsgBox IProximo euEnd Sub |
‘MyList1’ agora contém todos os itens de ‘MyList’ na mesma ordem
Copiando uma matriz de lista em um objeto de matriz VBA convencional
Você pode usar um método simples para copiar a lista de matrizes em uma matriz VBA normal:
| 123456789101112131415 | Sub ArrayExample ()‘Criar objeto de lista de matriz e um objeto de matriz padrãoDim MyList As New ArrayList, NewArray As Variant‘Preencher lista de matriz com itensMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Copie a lista da matriz para a nova matrizNewArray = MyList.ToArray‘Itere através da nova matriz - observe que a contagem da lista de matriz fornece o índice máximoPara N = 0 para MyList.Count - 1‘Exibir nome do itemMsgBox NewArray (N)Próximo NEnd Sub |
Copiando uma matriz de lista em um intervalo de planilha
Você pode copiar sua lista de arrays para uma planilha específica e referência de célula sem a necessidade de iterar através da lista de arrays. Você só precisa especificar a primeira referência de célula
| 123456789101112131415 | Sub RangeExample ()‘Criar novo objeto de lista de matrizDim MyList As New ArrayList‘Adicionar itens à listaMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Limpe a planilha de destinoFolhas ("Folha1"). UsedRange.Clear‘Copie os itens em uma linhaSheets ("Sheet1"). Range ("A1"). Resize (1, MyList.Count) .Value = MyList.toArray‘Copiar itens para baixo em uma colunaSheets ("Sheet1"). Range ("A5"). Resize (MyList.Count, 1) .Value = _WorksheetFunction.Transpose (MyList.toArray)End Sub |
Esvaziar todos os itens de uma lista de matrizes
Existe uma função simples (Clear) para limpar a lista de arrays completamente
| 1234567891011121314 | Sub ClearListExample ()‘Criar objeto de lista de matrizDim MyList As New ArrayList‘Adicionar novos itensMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"‘Mostrar contagem de itensMsgBox MyList.Count‘Limpar todos os itensMyList.Clear‘Mostrar contagem de itens para provar que a limpeza funcionouMsgBox MyList.CountEnd Sub |
Este exemplo cria itens em uma lista de matrizes e, em seguida, limpa a lista de matrizes. As caixas de mensagem provam antes e depois do número de itens na lista de array.
Resumo dos métodos de lista de matrizes para Excel VBA
| Tarefa | Parâmetros | Exemplos |
| Adicionar / Editar item | Valor | MyList.Add “Item1” |
| MinhaLista (4) = “Item2” | ||
| Clonar uma lista de matrizes | Nenhum | Dim MyList As Object |
| Definir MyList2 = MyList.Clone | ||
| Copiar para Array | Nenhum | Dim MyArray As Variant |
| MyArray = MyList.ToArray | ||
| Copiar para um intervalo de planilha (linha) | Nenhum | Folhas (“Folha1”). Intervalo (“A1”). Redimensionar (1, MinhaLista.Contagem) .Valor = MinhaLista.ParaArray |
| Copiar para um intervalo de planilha (coluna) | Nenhum | Folhas (“Folha1”). Intervalo (“A3”). Redimensionar (MinhaLista.Contagem, 1) .Valor = FolhaFunção.Transpor (MinhaLista.ParaArray) |
| Crio | “System.Collections.ArrayList” | Dim MyList As Object |
| Definir MyList = CreateObject (“System.Collections.ArrayList”) | ||
| Declarar | N / D | Dim MyList As Object |
| Encontre / verifique se o item existe | Artigo para encontrar | MyList.Contains (“Item2”) |
| Encontre a posição de um item na ArrayList | 1. Item a ser encontrado. | Dim IndexNo As Long |
| 2. Posição a partir da qual iniciar a pesquisa. | IndexNo = MyList.IndexOf (“Item3”, 0) | |
| IndexNo = MyList.IndexOf (“Item5”, 3) | ||
| Obtenha o número de itens | Nenhum | MsgBox MyList.Count |
| Inserir Item | 1. Índice - posição para inserir. | MyList.Insert 0, “Item5” |
| 2 Valor - objeto ou valor a inserir. | MyList.Insert 4, “Item7” | |
| Leia o item | Índice - inteiro longo | MsgBox MyList.Item (0) |
| MsgBox MyList.Item (4) | ||
| Ler o item adicionado por último | Índice - inteiro longo | MsgBox MyList.Item (list.Count - 1) |
| Leia o item adicionado primeiro | Índice - inteiro longo | MsgBox MyList.Item (0) |
| Leia todos os itens (para cada um) | N / D | Elemento Dim como Variante |
| Para cada elemento em MyList | ||
| Elemento MsgBox | ||
| Próximo elemento | ||
| Leia todos os itens (para) | Índice - inteiro longo | Dim i enquanto |
| Para i = 0 para MyList.Count - 1 | ||
| MsgBox i | ||
| Proximo eu | ||
| Remover todos os itens | Nenhum | MyList.Clear |
| Remover o item na posição | Posição do índice onde o item está | MyList.RemoveAt 5 |
| Remover o item pelo nome | O item a ser removido da ArrayList | MyList.Remove “Item3” |
| Remover uma série de itens | 1. Índice - posição inicial. | MyList.RemoveRange 4,3 |
| 2. Contagem - o número de itens a serem removidos. | ||
| Classificar em ordem decrescente | Nenhum | MyList.Reverse |
| Classificar em ordem crescente | Não | MyList.Sort |
