Coleção VBA | Como criar um objeto de coleção no VBA?

Objeto de coleção Excel VBA

Na codificação VBA, além da coleção existente de itens em um grupo, também podemos criar nossos próprios grupos de coleção. Em nossos muitos artigos, falamos sobre variáveis de objeto e, neste tutorial, examinaremos o objeto de coleção VBA em detalhes.

Se você leu nosso artigo anterior “Arrays de VBA”, isso será muito mais fácil de entender. Arrays são usados para agrupar variáveis sob o mesmo teto, da mesma forma, Collection também é usado para armazenar um grupo de variáveis.

Coleções são usadas para armazenar objetos. Eles são muito mais flexíveis do que os arrays VBA, enquanto os arrays têm um limite de tamanho fixo, mas as coleções não têm nenhum limite de tamanho fixo em um determinado momento e nem mesmo exigem redimensionamento manual.

A coleção VBA é muito semelhante ao “Dicionário VBA”, mas o dicionário requer uma referência de objeto externo para ser configurada na janela de referência de objeto. Com o VBA Dictionary, precisamos definir o tipo de referência como “Microsoft Scripting Runtime”, mas a coleção não requer nenhum desses ajustes extras.

Como criar um objeto de coleção no VBA?

Para começar com a coleção, primeiro precisamos declarar a variável como “Coleção”.

Você pode baixar este modelo de coleção VBA aqui - modelo Excel de coleção VBA

Código:

 Sub Coleção_Exemplo () Dim Col As Coleção End Sub

Uma vez que a coleção é uma variável de objeto, precisamos definir a referência do objeto criando uma nova instância.

Código:

 Sub Collection_Example () Dim Col As Collection Set Col = New Collection End Sub

Agora com a variável, podemos acessar todos os métodos da variável de coleção “Col”.

Código:

 Sub Coleção_Exemplo () Dim Col Como Conjunto de Coleção Col = Nova Coleção Col. End Sub

Antes de usar esses métodos, precisamos declarar uma variável como uma string.

Código:

 Sub Collection_Example () Dim Col As Collection Set Col = Nova coleção Dim ColResult As String End Sub

Agora use a variável “Col” e escolha o método “Adicionar”.

Código:

 Sub Collection_Example () Dim Col As Collection Set Col = Nova Coleção Col.Add End Sub

No método Add, temos alguns parâmetros. Vamos supor que estejamos armazenando nomes de marcas de celular com seu preço médio de venda no mercado.

Em Item, o argumento insere o preço do celular.

Código:

 Sub Coleção_Exemplo () Dim Col Como Conjunto de Coleção Col = Nova Coleção Col.Adicionar Item: = 15000, End Sub

Em seguida, em Argumento- chave, insira o nome da marca do celular.

Código:

 Sub Collection_Example () Dim Col As Collection Set Col = New Collection Col.Add Item: = 15000, Chave: = "Redmi" End Sub

Agora para a variável “ColResult”, vamos armazenar o resultado da variável do objeto “Col”.

Código:

 Sub Collection_Example () Dim Col As Collection Set Col = Nova Coleção Col.Add Item: = 15000, Chave: = "Redmi" ColResult = Col (End Sub

Quando você abre o parêntese da variável “Col”, podemos ver o argumento como Índice para este argumento, precisamos fornecer o valor do argumento chave do método de adição da coleção, ou seja, o nome da marca do celular.

Código:

 Sub Collection_Example () Dim Col As Collection Set Col = Nova Coleção Col.Add Item: = 15000, Chave: = "Redmi" ColResult = Col ("Redmi") End Sub

Agora vamos mostrar o resultado na caixa de mensagem em VBA.

Código:

 Sub Collection_Example () Dim Col As Collection Set Col = Nova Coleção Col.Add Item: = 15000, Chave: = "Redmi" ColResult = Col ("Redmi") MsgBox ColResult End Sub

Ok, terminamos quando rodarmos o código devemos ver o preço da marca móvel “Redmi”.

Melhor compreensão dos parâmetros chave e de item

Tenho certeza de que não é fácil entender os parâmetros do objeto Collection. Deixe-me explicar com um exemplo simples.

Imagine que você tem um cardápio de frutas com o nome e preço das frutas. Suponha que você esteja pesquisando o preço da fruta “Maçã” pelo nome da fruta.

Para pesquisar o preço da fruta, precisamos mencionar o nome da fruta, ou seja, na linguagem VBA da coleção, o nome da fruta é a chave e o preço da fruta é “Item”.

É como aplicar a função VLOOKUP ou HLOOKUP, com base no valor de pesquisa, buscaremos os dados necessários no banco de dados. Aqui, o valor de pesquisa é Key e o resultado é Item.

Exemplo Avançado

Imagine que você é o gerente de uma loja de varejo e é responsável por gerenciar as consultas dos clientes. Uma dessas consultas do cliente é uma consulta sobre o preço do produto.

Você precisa permitir que o cliente pesquise o preço do produto com informações completas. Da mesma forma, você precisa mostrar a mensagem caso nenhum dado seja encontrado. Abaixo está o código de exemplo que apresentará a caixa de entrada na frente do usuário e que ele deverá inserir o nome do produto que procura, se o produto estiver na coleção mostrará o preço do referido produto ou então mostre a mensagem como “O produto que você está procurando não existe”.

Código:

 Sub Collection_Example2() Dim ItemsCol As Collection Dim ColResult As String Set ItemsCol = New Collection ItemsCol.Add Key:="Apple", Item:=150 ItemsCol.Add Key:="Orange", Item:=75 ItemsCol.Add Key:="Water Melon", Item:=45 ItemsCol.Add Key:="Mush Millan", Item:=85 ItemsCol.Add Key:="Mango", Item:=65 ColResult = Application.InputBox(Prompt:="Please Enter the Fruit Name") If ItemsCol(ColResult)  "" Then MsgBox "The Price of the Fruit " & ColResult & " is : " & ItemsCol(ColResult) Else MsgBox "Price of the Fruit You are Looking for Doesn't Exists in the Collection" End If End Sub