Uma coleção é um objeto que contém vários itens semelhantes. Eles podem ser facilmente acessados e manipulados, mesmo se houver um grande número de itens na coleção.
Já existem coleções integradas com o Excel VBA. Um exemplo é a coleção Folhas. Para cada planilha em uma pasta de trabalho, há um item na coleção Planilhas.
As coleções integradas têm muito mais propriedades e métodos disponíveis para você, mas eles não estão disponíveis em suas próprias coleções que você cria.
Por exemplo, você pode usar a coleção para obter informações sobre uma planilha específica. Por exemplo, você pode ver o nome da planilha e também se ela está visível ou não. Usando um loop For Each, você pode iterar em cada planilha da coleção.
| 1234567 | Sub TestWorksheets ()Dim Sh como planilhaPara cada Sh nas folhasMsgBox Sh.NameMsgBox Sh.VisiblePróximo ShEnd Sub |
Você também pode endereçar uma planilha específica na coleção usando o valor do índice ou o nome real da planilha:
| 12 | Folhas MsgBox (1) .NomeFolhas MsgBox ("Folha1"). Nome |
Conforme as planilhas são adicionadas ou excluídas, a coleção de planilhas aumenta ou diminui de tamanho.
Observe que, com coleções VBA, o número do índice começa com 1 e não com 0
Coleções Versus Matrizes
Arrays e coleções são semelhantes em suas funções, pois são metodologias que permitem o armazenamento de uma grande quantidade de dados que podem ser facilmente referenciados por meio de código. Eles, no entanto, têm uma série de diferenças na maneira como operam:
- As matrizes são multidimensionais, enquanto as coleções são apenas de uma dimensão. Você pode dimensionar uma matriz com várias dimensões, por exemplo,
| 1 | Dim MyArray (10, 2) As String |
Isso cria uma matriz de 10 linhas com 2 colunas, quase como uma planilha. Uma coleção é efetivamente uma única coluna. A matriz é útil se você precisar armazenar uma série de itens de dados que se relacionam entre si, por exemplo, nome e endereço. O nome estaria na primeira dimensão da matriz e o endereço na segunda dimensão.
- Ao preencher sua matriz, você precisa de uma linha separada de código para colocar um valor em cada elemento da matriz. Se você tivesse uma matriz bidimensional, na verdade precisaria de 2 linhas de código - uma linha para endereçar a primeira coluna e uma linha para endereçar a segunda coluna. Com o objeto Collection, você simplesmente usa o método Add para que o novo item seja adicionado à coleção e o valor do índice seja ajustado automaticamente para se adequar.
- Se você precisar excluir um item de dados, será mais complicado no array. Você pode definir os valores de um elemento para um valor em branco, mas o próprio elemento ainda existe na matriz. Se você estiver usando um loop For Next para iterar pelo array, o loop retornará um valor em branco, que precisará de codificação para garantir que o valor em branco seja ignorado. Em uma coleção, você usa os métodos Adicionar ou Remover, e toda a indexação e redimensionamento é feita automaticamente. O item removido desaparece completamente. Os arrays são úteis para um tamanho fixo de dados, mas as coleções são melhores para onde a quantidade de dados está sujeita a alterações.
- As coleções são somente leitura, enquanto os valores da matriz podem ser alterados usando o VBA. Com uma coleção, você teria que remover o valor a ser alterado primeiro e, em seguida, adicionar o novo valor alterado.
- Em uma matriz, você só pode usar um único tipo de dados para os elementos que são definidos quando você dimensiona a matriz. No entanto, na matriz, você pode usar tipos de dados personalizados que você mesmo criou. Você poderia ter uma estrutura de array muito complicada usando um tipo de dados personalizado que, por sua vez, tem vários tipos de dados personalizados abaixo dele. Em uma coleção, você pode adicionar tipos de dados de uso de dados para cada item. Você pode ter um valor numérico, uma data ou uma string - o objeto de coleção terá qualquer tipo de dados. Se você tentasse colocar um valor de string em uma matriz dimensionada como numérica, seria produzida uma mensagem de erro.
- Geralmente, as coleções são mais fáceis de usar do que as matrizes. Em termos de codificação, quando você cria um objeto de coleção, ele tem apenas dois métodos (Adicionar e Remover) e duas propriedades (Contar e Item), portanto, o objeto não é complicado de programar.
- As coleções podem usar chaves para localizar dados. Os arrays não têm essa função e requerem código de loop para iterar por meio do array para encontrar valores específicos.
- O tamanho de um array precisa ser definido quando ele é criado pela primeira vez. Você precisa ter uma ideia de quantos dados ele armazenará. Se precisar aumentar o tamanho da matriz, você pode usar ‘ReDim’ para redimensioná-la, mas você precisa usar a palavra-chave ‘Preservar’ se não quiser perder os dados já mantidos na matriz. Um tamanho de coleção não precisa ser definido. Ele apenas aumenta e diminui automaticamente conforme os itens são adicionados ou removidos.
Escopo de um objeto de coleção
Em termos de escopo, o objeto de coleção 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, a coleção precisará ser recriada usando o código VBA.
Se você deseja que sua coleção esteja disponível para todo o código em seu módulo de código, você precisa declarar o objeto de coleção na seção Declare na parte superior da janela do módulo
Isso garantirá que todo o seu código nesse módulo possa acessar a coleção. Se você quiser que qualquer módulo em sua pasta de trabalho acesse a coleção, defina-o como um objeto global
| 1 | Global MyCollection como nova coleção |
Criando uma coleção, adicionando itens e acessando itens
Um objeto de coleção simples pode ser criado no VBA usando o seguinte código:
| 123456 | Sub CreateCollection ()Dim MyCollection como nova coleçãoMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"End Sub |
O código dimensiona um novo objeto chamado ‘MyCollection’ e, em seguida, as seguintes linhas de código usam o método Add para adicionar 3 novos valores.
Você pode então usar o código para iterar por meio de sua coleção para acessar os valores
| 123 | Para cada item em MyCollectionItem MsgBoxPróximo item |
Você também pode iterar em sua coleção usando um For Next Loop:
| 123 | Para n = 1 para MyCollection.CountMsgBox MyCollection (n)Próximo n |
O código obtém o tamanho da coleção usando a propriedade Count e, em seguida, usa isso iniciando um valor 1 para indexar cada item
O For Each Loop é mais rápido do que o loop For Next, mas só funciona em uma direção (índice baixo a alto). O For Next Loop tem a vantagem de que você pode usar uma direção diferente (índice alto para baixo) e também pode usar o método Step para alterar o incremento. Isso é útil quando você deseja excluir vários itens, pois será necessário executar a exclusão do final da coleção para o início, pois o índice será alterado à medida que as exclusões ocorrerem.
O método Add em uma coleção tem 3 parâmetros opcionais - Chave, Antes e Depois
Você pode usar os parâmetros ‘Antes’ e ‘Depois’ para definir a posição de seu novo item em relação aos outros já na coleção
Isso é feito especificando o número do índice ao qual você deseja que seu novo item seja relativo.
| 123456 | Sub CreateCollection ()Dim MyCollection como nova coleçãoMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"End Sub |
Neste exemplo, ‘Item2’ foi especificado para ser adicionado antes do primeiro item indexado na coleção (que é ‘Item1’). Quando você itera por meio desta coleção, ‘Item2’ aparecerá primeiro de tudo, seguido por ‘Item1’ e ‘Item3’
Quando você especifica um parâmetro ‘Antes’ ou ‘Depois’, o valor do índice é automaticamente ajustado dentro da coleção para que ‘Item2’ se torne o valor de índice 1 e ‘Item1’ seja movido para um valor de índice 2
Você também pode usar o parâmetro ‘Chave’ para adicionar um valor de referência que você pode usar para identificar o item da coleção. Observe que um valor de chave deve ser uma string e deve ser exclusivo na coleção.
| 1234567 | Sub CreateCollection ()Dim MyCollection como nova coleçãoMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"MsgBox MyCollection ("MyKey")End Sub |
‘Item2’ recebeu um valor de ‘Chave’ de ‘MyKey’ para que você possa se referir a esse item usando o valor ‘MyKey’ em vez do número do índice (2)
Observe que o valor 'Chave' deve ser um valor de string. Não pode ser qualquer outro tipo de dados. Observe que a coleção é somente leitura e você não pode atualizar o valor da chave depois de configurada. Além disso, você não pode verificar se existe um valor-chave para um item específico na coleção ou visualizar o valor-chave, o que é um pouco inconveniente.
O parâmetro ‘Key’ tem a vantagem adicional de tornar seu código mais legível, especialmente se ele estiver sendo entregue a um colega para dar suporte, e você não precisa iterar por toda a coleção para encontrar esse valor. Imagine se você tivesse uma coleção de 10.000 itens como seria difícil fazer referência a um item específico!
Removendo um item de uma coleção
Você pode usar o método ‘Remover’ para excluir itens de sua coleção.
| 1 | MyCollection.Remove (2) |
Infelizmente, não é fácil se a coleção tiver um grande número de itens para calcular o índice do item que você deseja excluir. É aqui que o parâmetro ‘Key’ se torna útil quando a coleção está sendo criada
| 1 | MyCollection.Remove (“MyKey”) |
Quando um item é removido de uma coleção, os valores do índice são redefinidos automaticamente em toda a coleção. É aqui que o parâmetro ‘Chave’ é tão útil quando você está excluindo vários itens de uma vez. Por exemplo, você pode excluir o índice de item 105 e, instantaneamente, o índice de item 106 torna-se índice 105, e tudo acima deste item tem seu valor de índice movido para baixo. Se você usar o parâmetro Key, não há necessidade de se preocupar com qual valor de índice precisa ser removido.
Para excluir todos os itens da coleção e criar uma nova coleção, use a instrução Dim novamente, que cria uma coleção vazia.
| 1 | Dim MyCollection como nova coleção |
Para remover completamente o objeto da coleção real, você pode definir o objeto para nada
| 1 | Definir MyCollection = Nothing |
Isso é útil se a coleção não for mais exigida pelo seu código. Definir o objeto de coleção como nada remove todas as referências a ele e libera a memória que estava usando. Isso pode ter implicações importantes na velocidade de execução do seu código, se um objeto grande estiver na memória e não for mais necessário.
Conte o número de itens em uma coleção
Você pode descobrir facilmente o número de itens em sua coleção usando a propriedade ‘Count’
| 1 | MsgBox MyCollection.Count |
Você usaria essa propriedade se estivesse usando um For Next Loop para iterar pela coleção, pois ele fornecerá o limite superior para o número do índice.
Coleta de teste para um valor específico
Você pode iterar por meio de uma coleção para pesquisar um valor específico para um item usando um For Each Loop
| 123456789101112 | Sub SearchCollection ()Dim MyCollection como nova coleçãoMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Para cada item em MyCollectionSe Item = "Item2" EntãoItem MsgBox e "Encontrado"Fim sePróximoEnd Sub |
O código cria uma pequena coleção e, em seguida, itera por ela em busca de um item chamado ‘item2”. Se for encontrado, exibe uma caixa de mensagem informando que encontrou o item específico
Uma das desvantagens desta metodologia é que você não pode acessar o valor do índice ou o valor da chave
Se você usar um For Next Loop, você pode usar o contador For Next para obter o valor do índice, embora você ainda não possa obter o valor ‘Chave’
| 123456789101112 | Sub SearchCollection ()Dim MyCollection como nova coleçãoMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Para n = 1 para MyCollection.CountIf MyCollection.Item (n) = "Item2" ThenMsgBox MyCollection.Item (n) & "encontrado na posição do índice" & nFim sePróximo nEnd Sub |
O contador do próximo (n) fornecerá a posição do índice
Classificando uma coleção
Não há nenhuma funcionalidade embutida para classificar uma coleção, mas usando algum pensamento "fora da caixa", o código pode ser escrito para fazer uma classificação, utilizando a função de classificação de planilha do Excel. Este código usa uma planilha em branco chamada ‘SortSheet’ para fazer a classificação real.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection ()Dim MyCollection como nova coleçãoDim Counter As Long‘Construir coleção com itens de pedido aleatórioMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Add "Item3"‘Capture o número de itens na coleção para uso futuroCounter = MyCollection.Count‘Itere através da coleção copiando cada item para uma célula consecutiva em‘ SortSheet ’(coluna A)Para n = 1 para MyCollection.CountPlanilhas ("SortSheet"). Células (n, 1) = MyCollection (n)Próximo n‘Ative a planilha de classificação e use a rotina de classificação do Excel para classificar os dados em ordem crescentePlanilhas ("SortSheet"). AtiveIntervalo ("A1: A" & MyCollection.Count) .SelecioneActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 Key: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalCom ActiveWorkbook.Worksheets ("SortSheet"). Sort.SetRange Range ("A1: A5").Header = xlGuess.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplicarTerminar com‘Exclua todos os itens da coleção - observe que este For Next Loop é executado na ordem inversaPara n = MyCollection.Count to 1 Step -1MyCollection.Remove (n)Próximo n‘Copie os valores da célula de volta para o objeto de coleção vazio usando o valor armazenado (Contador) para o‘ loopPara n = 1 para combaterMyCollection.Add Sheets ("SortSheet"). Cells (n, 1) .ValuePróximo n‘Repita a coleção para provar a ordem em que os itens estão agoraPara cada item em MyCollectionItem MsgBoxPróximo item‘Limpe a planilha (planilha de classificação) - se necessário, exclua-a tambémPlanilhas ("SortSheet"). Intervalo (Células (1, 1), Células (Contador, 1)). LimparEnd Sub |
Este código primeiro cria uma coleção com os itens adicionados em uma ordem aleatória. Em seguida, ele os copia para a primeira coluna de uma planilha (SortSheet).
O código então usa a função de classificação do Excel para classificar os dados na coluna em ordem crescente. O código também pode ser modificado para classificar em ordem decrescente.
A coleção é então esvaziada de dados usando um For Next Loop. Observe que a opção step é usada para limpar do final da coleção para o início. Isso ocorre porque, à medida que ele limpa, os valores do índice são redefinidos, se fosse limpo desde o início, não seria limpo corretamente (o índice 2 se tornaria o índice 1)
Finalmente, usando outro For Next Loop, os valores dos itens são transferidos de volta para a coleção vazia
Um outro For Each Loop prova que a coleção agora está em boa ordem crescente.
Infelizmente, isso não lida com quaisquer valores-chave que possam ter sido inseridos originalmente, uma vez que os valores-chave não podem ser lidos
Passando uma coleção para uma sub / função
Uma coleção pode ser passada para uma sub ou função da mesma forma que qualquer outro parâmetro
| 1 | Função MyFunction (ByRef MyCollection as Collection) |
É importante passar a coleção usando ‘ByRef’. Isso significa que a coleção original é usada. Se a coleção for passada usando 'ByVal', isso criará uma cópia da coleção que pode ter repercussões infelizes
Se uma cópia for criada usando 'ByVal', então qualquer coisa que altere a coleção dentro da função só acontece na cópia e não no original. Por exemplo, se dentro da função, um novo item for adicionado à coleção, ele não aparecerá na coleção original, o que criará um bug em seu código.
Retornando uma coleção de uma função
Você pode retornar uma coleção de uma função da mesma maneira que retorna qualquer objeto. Você deve usar a palavra-chave Definir
| 12345 | Sub ReturnFromFunction ()Dim MyCollection As CollectionDefinir MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
Este código cria uma sub-rotina que cria um objeto chamado ‘MyCollection’ e, em seguida, usa a palavra-chave ‘Set’ para efetivamente chamar a função para preencher essa coleção. Uma vez feito isso, ele exibe uma caixa de mensagem para mostrar a contagem de 2 itens
| 1234567 | Função PopulateCollection () como coleçãoDim MyCollection como nova coleçãoMyCollection.Add "Item1"MyCollection.Add "Item2"Definir PopulateCollection = MyCollectionFunção Final |
A função PopulateCollection cria um novo objeto de coleção e o preenche com 2 itens. Em seguida, ele passa esse objeto de volta para o objeto de coleção criado na sub-rotina original.
Convertendo uma coleção em uma matriz
Você pode querer converter sua coleção em um array. Você pode querer armazenar os dados onde possam ser alterados e manipulados. Este código cria uma pequena coleção e a transfere para uma matriz
Observe que o índice da coleção começa em 1, enquanto o índice da matriz começa em 0. Considerando que a coleção tem 3 itens, a matriz só precisa ser dimensionada para 2 porque há um elemento 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection como nova coleçãoDim MyArray (2) As StringMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Para n = 1 para MyCollection.CountMyArray (n - 1) = MyCollection (n)Próximo nPara n = 0 a 2MsgBox MyArray (n)Próximo nEnd Sub |
Convertendo uma matriz em uma coleção
Você pode querer converter uma matriz em uma coleção. Por exemplo, você pode desejar acessar os dados de uma maneira mais rápida e elegante do que usando o código para obter um elemento do array.
Tenha em mente que isso só funcionará para uma única dimensão da matriz porque a coleção tem apenas uma dimensão
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection como nova coleçãoDim MyArray (2) As StringMyArray (0) = "item1"MyArray (1) = "Item2"MeuArray (2) = "Item3"Para n = 0 a 2MyCollection.Add MyArray (n)Próximo nPara cada item em MyCollectionItem MsgBoxPróximo itemEnd Sub |
Se você quiser usar uma matriz multidimensional, poderá concatenar os valores da matriz para cada linha da matriz usando um caractere delimitador entre as dimensões da matriz, de modo que, ao ler o valor da coleção, você possa usar programaticamente o caractere delimitador para separe os valores.
Você também pode mover os dados para a coleção na base de que o valor da primeira dimensão é adicionado (índice 1) e, em seguida, o valor da próxima dimensão é adicionado (índice 2) e assim por diante.
Se a matriz tivesse, digamos, 4 dimensões, cada quarto valor na coleção seria um novo conjunto de valores.
Você também pode adicionar valores de matriz para usar como chaves (desde que sejam exclusivos), o que adicionaria uma maneira fácil de localizar dados específicos.
