VBA Excel – Coleções

Este tutorial demonstrará como usar coleções no VBA.

O que é uma Coleção no VBA?

Uma coleção é um objeto que contém vários itens semelhantes que podem ser facilmente acessados e manipulados, mesmo que haja um grande número de itens dentro da coleção.

Você pode criar suas próprias coleções, mas o VBA também vem com coleções integradas, como a Coleção Sheets que armazena todas as planilhas da pasta de trabalho. Usando um loop For Each, você pode iterar por cada planilha na Coleção Sheets.

Sub TestarWorksheets()
Dim Sh As Worksheet
For Each Sh In Sheets
    MsgBox Sh.Name
    MsgBox Sh.Visible
Next Sh
End Sub

Você também pode endereçar uma planilha específica na coleção usando o valor do índice (sua posição na coleção) ou o nome real da planilha:

MsgBox Sheets(1).Name
MsgBox Sheets("Planilha1").Name

À medida que as planilhas são adicionadas ou excluídas, a coleção Sheets aumenta ou diminui de tamanho.

Observe que, nas coleções do VBA, o número de índice começa com 1 e não com 0.

Coleções Versus Matrizes

Tanto as matrizes quanto as coleções podem armazenar dados, mas têm várias diferenças notáveis:

1. As matrizes são multidimensionais, enquanto as coleções têm apenas uma dimensão. Você pode dimensionar uma matriz com várias dimensões, por exemplo

Dim MinhaArray(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.

2. Quando você preenche sua matriz, precisa de uma linha de código separada para colocar um valor em cada elemento da matriz. Se você tivesse uma matriz bidimensional, precisaria, na verdade, de duas 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, basta usar o método Add para que o novo item seja simplesmente adicionado à coleção e o valor do índice seja automaticamente ajustado para se adequar.
3. Se você precisar excluir um item dos dados, isso será mais complicado na matriz. Você pode definir os valores de um elemento como um valor em branco, mas o elemento em si ainda existe na matriz. Se você estiver usando um loop For Next para iterar pela matriz, o loop retornará um valor em branco, o que exigirá codificação para garantir que o valor em branco seja ignorado. Em uma coleção, você usa os métodos Add ou Remove, e toda a indexação e o redimensionamento são feitos automaticamente. O item que foi removido desaparece completamente. As matrizes são úteis para um tamanho fixo de dados, mas as coleções são melhores para os casos em que a quantidade de dados pode mudar.
4. As coleções são somente leitura, enquanto os valores de 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.
5. Em uma matriz, você só pode usar um único tipo de dados para os elementos, que é definido quando você dimensiona a matriz. Entretanto, na matriz, você pode usar tipos de dados personalizados que você mesmo criou. Você poderia ter uma estrutura de matriz 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 para cada item. Você pode ter um valor numérico, uma data ou uma cadeia de caracteres – o objeto de coleção aceitará qualquer tipo de dados. Se você tentasse colocar um valor de cadeia de caracteres em uma matriz dimensionada como numérica, apareceria uma mensagem de erro.
6. Em geral, 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 (Add e Remove) e duas propriedades (Count e Item), de modo que a programação do objeto não é complicada.
7. As coleções podem usar chaves para localizar dados. Os arrays não têm essa função e exigem código de looping para iterar pelo array e encontrar valores específicos.
8. O tamanho de uma matriz precisa ser definido quando ela é criada pela primeira vez. Você precisa ter uma ideia da quantidade de dados que ele armazenará. Se você precisar aumentar o tamanho da matriz, poderá usar “ReDim” para redimensioná-la, mas precisará usar a palavra-chave “Preserve” se não quiser perder os dados já armazenados na matriz. O tamanho de uma coleção não precisa ser definido. Ele simplesmente aumenta e diminui automaticamente à medida que os itens são adicionados ou removidos.

Escopo de um Objeto Coleção

Em termos de escopo, o objeto de coleção só está disponível enquanto a pasta de trabalho estiver 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 quiser que a coleção esteja disponível para todo o código no módulo de código, será necessário declarar o objeto de coleção na seção Declarar, na parte superior da janela do módulo.

Isso garantirá que todo o seu código dentro desse módulo possa acessar a coleção. Se quiser que qualquer módulo da sua pasta de trabalho acesse a coleção, defina-a como um objeto global.

Global MinhaColecao As New Collection

Criar uma Coleção, Adicionar Itens e Acessar Itens

Um objeto de coleção simples pode ser criado no VBA usando o código a seguir:

Sub CriarColecao()
'Criar Coleção
Dim MinhaColecao As New Collection

'Adicionar itens à coleção
MinhaColecao.Add "Item1"
MinhaColecao.Add "Item2"
MinhaColecao.Add "Item3"
End Sub

O código cria um novo objeto chamado ‘MinhaColeção’ e, em seguida, as linhas de código a seguir usam o método Add para adicionar 3 novos valores.

Loop na Coleção

Em seguida, você pode usar o código para iterar por cada item da coleção:

For Each Item In MinhaColecao
    MsgBox Item
Next Item

Ou esse código obterá o tamanho da coleção com .Count e fará um loop por cada número de índice:

For n = 1 To MinhaColecao.Count
    MsgBox MinhaColecao(n)
Next n

O primeiro loop For Each é mais rápido do que o segundo loop For Next, mas só funciona em uma direção (índice baixo para alto). O For Next Loop tem a vantagem de poder 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, já que o índice mudará à medida que as exclusões ocorrerem.

Adicionar Item à Coleção

O método Add em uma coleção tem 3 parâmetros opcionais: Key (Chave), Before (Antes) e After (Depois).

Você pode usar os parâmetros “Before” e “After” para definir a posição do seu novo item em relação aos outros já existentes na coleção.

Isso é feito especificando o número de índice ao qual você deseja que o novo item seja relativo.

Sub CriarColecao()
Dim MinhaColecao As New Collection
MinhaColecao.Add "Item1"
MinhaColecao.Add "Item2", , 1
MinhaColecao.Add "Item3"

End Sub

Neste exemplo, “Item2” foi especificado para ser adicionado antes do primeiro item indexado na coleção (que é “Item1”). Quando você iterar por essa coleção, “Item2” aparecerá primeiro, seguido por “Item1” e “Item3”.

Quando você especifica um parâmetro “Before” (Antes) ou “After” (Depois), o valor do índice é ajustado automaticamente dentro da coleção, de modo que “Item2” se torna o valor de índice 1 e “Item1” é movido para um valor de índice 2.

Você também pode usar o parâmetro “Key” (Chave) para adicionar um valor de referência que possa ser usado para identificar o item da coleção. Observe que um valor-chave deve ser uma cadeia de caracteres e deve ser exclusivo dentro da coleção.

Sub CriarColecao()
Dim MinhaColecao As New Collection
MinhaColecao.Add "Item1"
MinhaColecao.Add "Item2", "MinhaChave"
MinhaColecao.Add "Item3"

MsgBox MinhaColecao("MinhaChave")
End Sub

o ‘Item2’ recebeu um valor de ‘Key’ de ‘MinhaChave’ para que você possa se referir a esse item usando o valor de ‘MinhaChave’ em vez do número de índice (2).

Observe que o valor ‘Key’ deve ser um valor de cadeia de caracteres. Não pode ser nenhum outro tipo de dados. Observe que a coleção é somente leitura e você não pode atualizar o valor da chave depois que ele for definido. Além disso, você não pode verificar se existe um valor de chave para um item específico na coleção nem visualizar o valor de chave, o que é um pouco inconveniente.

O parâmetro ‘Key’ tem a vantagem adicional de tornar o código mais legível, especialmente se ele estiver sendo entregue a um colega para 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!

Remover um Item da Coleção

Você pode usar o método “Remove” para excluir itens da sua coleção.

MinhaColecao.Remove (2)

Infelizmente, se a coleção tiver um grande número de itens, não será fácil calcular o índice do item que você deseja excluir. É nesse ponto que o parâmetro “Key” é útil quando a coleção está sendo criada.

MinhaColecao.Remove (“MinhaChave”)

Quando um item é removido de uma coleção, os valores de índice são automaticamente redefinidos em toda a coleção. É por isso que o parâmetro “Key” é tão útil quando você está excluindo vários itens de uma vez. Por exemplo, você poderia excluir o item de índice 105 e, instantaneamente, o item de índice 106 se tornaria o índice 105, e tudo acima desse item teria seu valor de índice movido para baixo. Se você usar o parâmetro Key, não precisará 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 novamente a instrução Dim, que cria uma coleção vazia.

Dim MinhaColecao As New Collection

Para remover completamente o objeto de coleção real, você pode definir o objeto como nada.

Set MinhaColecao = Nothing

Isso é útil se a coleção não for mais necessária em seu código. Definir o objeto de coleção como nothing remove todas as referências a ele e libera a memória que ele 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.

Contar 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”.

MsgBox MinhaColecao.Count

Você usaria essa propriedade se estivesse usando um loop For Next para iterar pela coleção, pois ela fornecerá o limite superior do número de índice.

Testar a Coleção para um Valor Específico

Você pode iterar em uma coleção para procurar um valor específico para um item usando um loop For Each.

Sub PesquisarColecao()
Dim MinhaColecao as New Collection

MinhaColecao.Add "Item1"
MinhaColecao.Add "Item2"
MinhaColecao.Add "Item3"

For Each Item In MinhaColecao
        If Item = "Item2" Then
            MsgBox Item & " Encontrado"
        End If
Next
End Sub

O código cria uma pequena coleção e, em seguida, itera por ela procurando um item chamado ‘item2″. Se for encontrado, ele exibirá uma caixa de mensagem informando que encontrou o item específico.

Uma das desvantagens dessa metodologia é que não é possível acessar o valor do índice ou o valor da chave.

Se, em vez disso, você usar um loop For Next, poderá usar o contador For Next para obter o valor do índice, embora ainda não possa obter o valor da “chave”.

Sub PesquisarColecao()
Dim MinhaColecao As New Collection
MinhaColecao.Add "Item1"
MinhaColecao.Add "Item2"
MinhaColecao.Add "Item3"

For n = 1 To MinhaColecao.Count
        If MinhaColecao.Item(n) = "Item2" Then
            MsgBox MinhaColecao.Item(n) & " encontrado na posição de índice " & n
        End If
Next n
End Sub

O contador For Next (n) fornecerá a posição do índice.

Ordenar Coleção

Não há funcionalidade integrada para classificar uma coleção, mas, usando um raciocínio “fora da caixa”, é possível escrever um código para fazer uma classificação, utilizando a função de classificação de planilhas do Excel. Esse código usa uma planilha em branco chamada “PlanilhaClassificar” para fazer a classificação real.

Sub OrdenarColecao()
Dim MinhaColecao As New Collection
Dim Contador As Long

'Crie uma coleção com itens de ordem aleatória
MinhaColecao.Add "Item5"
MinhaColecao.Add "Item2"
MinhaColecao.Add "Item4"
MinhaColecao.Add "Item1"
MinhaColecao.Add "Item3"

'Capturar o número de itens na coleção para uso futuro
Contador = MinhaColecao.Count

'Iterar pela coleção copiando cada item para uma célula consecutiva em "PlanilhaClassificar" (coluna A)
For n = 1 To MinhaColecao.Count
    Sheets("PlanilhaClassificar").Cells(n, 1) = MinhaColecao(n)

Next n
'Ative a PlanilhaClassificar e use a rotina de classificação do Excel para classificar os dados em ordem crescente
Sheets("PlanilhaClassificar").Activate
Range("A1:A" & MinhaColecao.Count).Select
    ActiveWorkbook.Worksheets("PlanilhaClassificar").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("PlanilhaClassificar"). Sort.SortFields.Add2 Key:=Range( _
        "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("PlanilhaClassificar").Sort
        .SetRange Range("A1:A5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'Exclua todos os itens da coleção - observe que esse For Next Loop é executado na ordem inversa
For n = MinhaColecao.Count To 1 Step -1
     MinhaColecao.Remove (n)
Next n

'Copie os valores das células de volta para o objeto de coleção vazio usando o valor armazenado (Contador) para o loop
For n = 1 To Contador
    MinhaColecao.Add Sheets("PlanilhaClassificar").Cells(n, 1).Value

Next n

'Iterar pela coleção para provar a ordem em que os itens estão agora
For Each Item In MinhaColecao
    MsgBox Item

Next Item

'Limpe a planilha (PlanilhaClassificação) - se necessário, exclua-a também
Sheets("PlanilhaClassificar").Range(Cells(1, 1), Cells(Contador, 1)).Clear
End Sub

Esse código cria primeiro uma coleção com os itens adicionados em uma ordem aleatória. Em seguida, ele os copia para a primeira coluna em uma planilha (PlanilhaClassificar).

Em seguida, o código usa a ferramenta 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.

Em seguida, a coleção é esvaziada de dados usando um loop For Next. Observe que a opção Step é usada de modo a limpar do final da coleção até o início. Isso se deve ao fato de que, à medida que a limpeza é feita, os valores de índice são redefinidos; se a limpeza fosse feita desde o início, ela não seria feita 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 loop For Each prova que a coleção está agora em uma boa ordem ascendente.

Infelizmente, isso não trata de nenhum valor de chave que possa ter sido inserido originalmente, pois os valores de chave não podem ser lidos.

Passar uma Coleção para uma Sub/Function

Uma coleção pode ser passada para uma Sub ou uma Function da mesma forma que qualquer outro parâmetro.

Function MinhaFuncao(ByRef MinhaColecao as Collection)

É importante passar a coleção usando “ByRef”. Isso significa que a coleção original é usada. Se a coleção for transmitida usando “ByVal”, isso criará uma cópia da coleção, o que pode ter repercussões infelizes.

Se uma cópia for criada usando “ByVal”, qualquer alteração na coleção dentro da função só ocorrerá 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 erro em seu código.

Retornar uma Coleção a Partir de uma Function

Você pode retornar uma coleção a partir de uma função da mesma forma que retorna qualquer objeto. Você deve usar a palavra-chave Set.

Sub RetornarDaFuncao()
Dim MinhaColecao As Collection
Set MinhaColecao = PreencherColecao
MsgBox MinhaColecao.Count
End Sub

Esse código cria uma Sub que cria um objeto chamado ‘MinhaColecao’ e, em seguida, usa a palavra-chave ‘Set’ para chamar efetivamente a função para preencher essa coleção. Quando isso é feito, ele exibe uma caixa de mensagem para mostrar a contagem de 2 itens.

Function PreencherColecao() As Collection
Dim MinhaColecao As New Collection
MinhaColecao.Add "Item1"
MinhaColecao.Add "Item2"

Set PreencherColecao = MinhaColecao
End Function

A função PreencherColecao cria um novo objeto de coleção e o preenche com 2 itens. Em seguida, ela passa esse objeto de volta para o objeto de coleção criado na Sub original.

Converter uma Coleção em uma Matriz

Talvez você queira converter sua coleção em uma matriz. Talvez você queira armazenar os dados onde eles 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. Enquanto a coleção tem 3 itens, a matriz só precisa ser dimensionada para 2 porque há um elemento 0

Sub ConverterColecaoEmMatriz()
Dim MinhaColecao As New Collection
Dim MinhaArray(2) As String

MinhaColecao.Add "Item1"
MinhaColecao.Add "Item2"
MinhaColecao.Add "Item3"

For n = 1 To MinhaColecao.Count
    MinhaArray(n - 1) = MinhaColecao(n)
 
Next n

For n = 0 To 2
    MsgBox MinhaArray(n)
Next n
End Sub

Converter uma Matriz em uma Coleção

Talvez você queira converter uma matriz em uma coleção. Por exemplo, você pode querer acessar os dados de uma maneira mais rápida e elegante do que usar o código para obter um elemento da matriz.

Lembre-se de que isso só funcionará para uma única dimensão da matriz, pois a coleção tem apenas uma dimensão.

Sub ConverterMatrizEmColecao()
Dim MinhaColecao As New Collection
Dim MinhaArray(2) As String
MinhaArray(0) = "item1"
MinhaArray(1) = "Item2"
MinhaArray(2) = "Item3"

For n = 0 To 2
    MinhaColecao.Add MinhaArray(n)

Next n
For Each Item In MinhaColecao
    MsgBox Item
Next Item
End Sub

Se quiser usar uma matriz multidimensional, você poderá concatenar os valores da matriz para cada linha dentro 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 separar os valores.

Você também poderia mover os dados para a coleção com base no fato 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, a cada quatro valores na coleção seria um novo conjunto de valores.

Você também poderia adicionar valores de matriz para usar como chaves (desde que sejam exclusivos), o que acrescentaria uma maneira fácil de localizar dados específicos.