Objetos de Dicionário do VBA (ANTIGO)

Uso de um Dicionário VBA

Um dicionário do VBA opera de forma semelhante a um objeto de coleção, mas tem mais propriedades e métodos e oferece mais flexibilidade.

O dicionário armazena os dados na memória e pode ser facilmente manipulado. Não há necessidade de cálculo automático, backup em segundo plano e atualização de tela, portanto, seu código será executado consideravelmente mais rápido.

O objeto de dicionário funciona de forma semelhante a um dicionário normal que você usaria se quisesse descobrir o significado de uma palavra. Cada entrada no objeto de dicionário tem um valor de “chave” e um valor de “item”. Você usa a “chave” e o valor da chave para procurar o valor do item no objeto de dicionário, de modo semelhante ao que usaria em um dicionário convencional.

Devido à forma como o objeto de dicionário funciona, os valores de chave devem ser todos exclusivos, da mesma forma que em um dicionário convencional. Imagine se você abrisse seu dicionário convencional para procurar o significado de uma palavra e encontrasse a palavra listada mais de uma vez com duas definições totalmente diferentes. Você ficaria muito confuso!

Os valores-chave geralmente são texto ou números, ou ambos. Os usuários geralmente acham mais fácil lembrar os nomes das chaves como texto em vez de apenas números.

Em comparação com um objeto de coleção, o objeto de coleção é somente leitura. Ele tem apenas dois métodos (Add e Remove) e duas propriedades (Count e Item). Depois que um item é adicionado a um objeto de coleção, ele só pode ser removido, mas não editado, o que é um procedimento complicado se o valor de um item precisar ser alterado.

Um objeto de dicionário mudará de tamanho automaticamente para se adequar ao número de itens contidos nele. Ele não precisa ser definido em tamanho, como uma matriz convencional.

O objeto de dicionário é unidimensional e o tipo de dados é “Variant”, portanto, qualquer tipo de dados pode ser inserido nele, por exemplo, numérico, texto, data.

O dicionário do VBA não é nativo do Excel e precisa ser acessado por meio de vinculação antecipada ou tardia ao definir o objeto de dicionário.

Sub ExemploVinculacaoAntecipada()
 Dim MeuDicionario As New Scripting.Dictionary
End Sub

Sub ExemploVinculacaoTardia()
 Dim MeuDicionario As Object
 Set MeuDicionario = CreateObject("Scripting.Dictionary")
End Sub

Se você usar a ligação antecipada, deverá adicionar uma referência à biblioteca ‘Microsoft Scripting Runtime’

Para isso, selecione “Ferramentas | Referências” na barra de menus da janela do Visual Basic Editor (VBE) e uma janela pop-up será exibida com uma lista de bibliotecas disponíveis.

Role para baixo até “Microsoft Scripting Runtime” e marque a caixa ao lado dela. Clique em OK e essa biblioteca passará a fazer parte de seu projeto VBA e poderá ser referenciada usando a vinculação antecipada. Todos os exemplos de código neste artigo usarão a vinculação antecipada.

Seu código será executado consideravelmente mais rápido com a associação antecipada, pois tudo é compilado antecipadamente. Com a ligação tardia, o objeto precisa ser compilado à medida que o código é executado.

A biblioteca Scripting Runtime tem “Intellisense”. Ao escrever o código, você verá listas de métodos e propriedades disponíveis, o que ajuda a evitar erros de ortografia, que podem causar bugs no programa.

Além disso, se você pressionar F2 no VBE e selecionar a biblioteca “Scripting”, verá todos os métodos e propriedades disponíveis e os parâmetros necessários para cada um.

 

Distribuição do Aplicativo do Excel que Contém um Dicionário

Como já foi mencionado, a biblioteca Scripting Runtime não faz parte do Excel VBA, portanto, se você distribuir seu aplicativo para outros usuários, eles deverão ter acesso à biblioteca Scripting Runtime em seus computadores. Se não tiverem, ocorrerá um erro.

É uma boa ideia incluir algum código VBA para verificar se essa biblioteca está presente quando o aplicativo do Excel é carregado. Você pode usar o comando “Dir” para fazer isso no evento “Workbook Open”.

O local do arquivo é C:\Windows\SysWOW64\scrrun.dll.

 

Escopo de um Objeto Dictionary

O objeto Dictionary só está disponível enquanto a pasta de trabalho do Excel estiver aberta. Ele não é salvo quando a pasta de trabalho é salva.

Se o dicionário tiver que estar disponível para todas as rotinas do módulo, será necessário declará-lo (Dim) na seção Declare, na parte superior do módulo.

Você o define como um objeto global se quiser que o dicionário seja usado em todo o código.

Global MeuDicionario As New Dictionary

Preenchimento e Leitura de seu Dicionário

Para começar, você precisa criar um dicionário, preenchê-lo com alguns dados e, em seguida, iterar por ele para provar que os dados existem.

Sub PreencherLerDicionario()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.Add "MeuItem1", 10
 MeuDicionario.Add "MeuItem2", 20
 MeuDicionario.Add "MeuItem3", 30

 For n = 0 To MeuDicionario.Count - 1
 MsgBox MeuDicionario.Keys(n) & " " & MeuDicionario.Items(n)
 Next n
End Sub

Esse código cria um novo objeto de dicionário chamado ‘MeuDicionario’ e o preenche com três itens. O método Add tem dois parâmetros, Key e Item, e ambos são obrigatórios

Os tipos de dados para Key e Item são ambos do tipo “Variant”, portanto, aceitam qualquer tipo de dados – numérico, texto, data etc.

O primeiro item do dicionário pode ser adicionado da seguinte forma:

MeuDicionario.Add 10, "MeuItem1"

Os valores foram invertidos entre Key e Item, mas isso ainda funcionaria, embora a chave de pesquisa agora seja 10.

Entretanto, é importante entender que o valor da chave é o valor de pesquisa no dicionário. Ele funciona de forma muito semelhante à função VLOOKUP do Excel. Como todas as chaves precisam ter valores exclusivos, você pode especificar um valor de chave e retornar instantaneamente o valor do item para essa chave.

Observe que o índice do dicionário começa em 0, portanto, você precisa subtrair 1 da contagem do dicionário usada no loop For…Next

Você também pode usar um loop For…Each para ler os valores no dicionário:

Sub PreencherLerDicionario()
 Dim MeuDicionario As New Scripting.Dictionary, I As Variant

 MeuDicionario.Add "MyItem1", 10
 MeuDicionario.Add "MyItem2", 20
 MeuDicionario.Add "MyItem3", 30

 For Each I In MeuDicionario.Keys
 MsgBox I & " " & MeuDicionario(I)
 Next I
End Sub

Esse código itera por cada item e exibe a chave e o valor do item.

Uso do Número de Índice do Item

Você pode usar o número de índice de uma chave ou item para ler o valor.

Sub NumeroIndice()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.CompareMode = TextCompare

 MeuDicionario.Add "Item1", 10
 MeuDicionario.Add "Item2", 20
 MeuDicionario.Add "Item3", 30

 MsgBox MeuDicionario.Keys(2)
 MsgBox MeuDicionario.Items(1)
End Sub

Esse código retornará a chave ‘item3’, pois o índice começa em 0, e o valor do item, 20.

Você pode se referir a valores individuais de chaves ou itens dentro das coleções Keys ou Items usando os números de índice.

Filtragem do Dicionário

Não há um método direto para fazer isso, mas é muito simples escrever um código para fazê-lo:

Sub FiltrarDicionario()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.Add "AAItem1", 10
 MeuDicionario.Add "BBItem2", 20
 MeuDicionario.Add "BBItem3", 30

 For Each I In Filter(MeuDicionario.Keys, "BB")
 MsgBox MeuDicionario.Item(I)
 Next I
End Sub

O valor do filtro só funciona a partir do início do valor da chave. Não é possível usar curingas no filtro. Esse código retornará os dois valores de item com nomes de chave que começam com ‘BB’.

Isso lhe dará um subconjunto do dicionário com base no valor do filtro, que poderá ser transferido para outro dicionário ou para uma planilha. Com um planejamento cuidadoso dos nomes das chaves, certificando-se de que haja um prefixo significativo para cada um, você poderá facilmente dividir o dicionário em várias partes componentes.

Alteração de um Valor de Item de uma Chave

O objeto dicionário tem uma grande vantagem sobre uma coleção, pois o valor do item pode ser alterado, por exemplo

MeuDicionario("MeuItem4") = "40"

Na coleção, você precisaria excluir essa entrada e depois recriá-la.

Aqui está um exemplo de código:

Sub PreencherLerDicionario()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.Add "MeuItem1", 10
 MeuDicionario.Add "MeuItem2", 20
 MeuDicionario.Add "MeuItem3", 30
 MeuDicionario("MeuItem2") = "25"
 MeuDicionario("MeuItem4") = "40"

 For n = 0 To MeuDicionario.Count - 1
 MsgBox MeuDicionario.Keys(n) & " " & MeuDicionario.Items(n)
 Next n
End Sub

O código acima define três itens no dicionário e, em seguida, altera o valor de ‘MeuItem2’ de 20 para 25.

Ele também altera o valor de ‘MeuItem4’ para 40. Observe que nas instruções add do código, nenhum ‘MeuItem4’ foi adicionado. Quando você altera o valor de uma chave que não existe, ela é criada automaticamente. Isso é extremamente conveniente, pois nenhum erro é acionado, mas significa que você precisa ser cuidadoso com os nomes das chaves. Um erro ortográfico inadvertido no nome da chave significaria que uma nova chave seria criada e o nome da chave original ainda teria o valor antigo.

Isso poderia facilmente levar a problemas de integridade no objeto de dicionário.

Testar se uma Chave Existe

Você pode verificar se um valor de chave existe no dicionário.

Sub VerificarExisteDicionario()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.Add "MeuItem1", 10
 MeuDicionario.Add "MeuItem2", 20
 MeuDicionario.Add "MeuItem3", 30

 MsgBox MeuDicionario.Exists("MeuItem8")
End Sub

O código adiciona três itens a um novo objeto de dicionário e, em seguida, testa uma chave (“MeuItem8”) que não está no dicionário. Isso retorna Falso, mas se uma das chaves existentes tivesse sido usada, retornaria Verdadeiro.

Não são aceitos caracteres curinga. O texto da pesquisa também diferencia maiúsculas de minúsculas por padrão, mas isso pode ser alterado (veja mais adiante no artigo).

 

Uso de Vários Valores em um Dicionário

Ao contrário de uma matriz, o objeto dicionário é unidimensional. Isso pode gerar problemas se você tiver vários valores que deseja colocar em uma chave.

Uma maneira de contornar esse problema é concatenar cada valor de item usando um caractere delimitador entre cada valor, por exemplo, ‘|’

Sub MultiplosValores()

 'Criar objeto de dicionário e variáveis
 Dim MeuDicionario As New Scripting.Dictionary, V1 As Integer, V2 As String
 Dim V3 As Date, Temp As String, N As Integer

 'Preencher 3 variáveis para demonstrar valores múltiplos
 V1 = 5
 V2 = "Exemplo de valores múltiplos"
 V3 = "22-Jul-2020"

 'Adicionar o valor concatenado ao dicionário usando o delimitador "|"
 MeuDicionario.Add "MeuItemMultiplo", V1 & "|" & V2 & "|" & V3 & "|"


 'Capture o valor do dicionário concatenado em uma variável
 Temp = MeuDicionario("MeuItemMultiplo")

 'Itere pela cadeia de caracteres concatenada para separar os valores individuais
 Do

 'Localize a posição de um delimitador
 N = InStr(Temp, "|")

 'Se não houver mais delimitadores, saia do loop Do
 If N = 0 Then Exit Do

 'Exibir o texto relativo à posição do delimitador encontrado
 MsgBox Left(Temp, N - 1)

 'Truncar a cadeia concatenada para o próximo caractere após o delimitador encontrado 
 Temp = Mid(Temp, N + 1)
 Loop
End Sub

Outra maneira de contornar esse problema é criar seu próprio sistema de subtítulos para nomes de chaves. Não há motivo para não usar colchetes e números nos nomes das chaves

Sub MultiplosValores()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.Add "Multiplo(1)", 5
 MeuDicionario.Add "Multiplo(2)", "Exemplo de valores múltiplos"
 MeuDicionario.Add "Multiplo(3)", "22-Jul-2020"

 For N = 1 To 3
 MsgBox MeuDicionario("Multiplo(" & N & ")")
   Next N
End Sub

Esse código adiciona três chaves ao dicionário, mas cada nome de chave contém um número de referência entre parênteses. Assim, você pode se referir ao nome da chave, mas usando o número de referência concatenado. Isso é muito semelhante ao uso de um objeto de matriz

Exclusão de Itens

Você pode remover itens individuais por referência ao valor da chave.

MeuDicionario.Remove ("MeuItem2")

Observe que, como os nomes das chaves são exclusivos, isso remove apenas uma chave e um valor de item específicos

Você também pode limpar o dicionário completamente

MeuDicionario.RemoveAll

Aqui está um exemplo de uso de “Remove” no VBA:

Sub RemoverValores()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.Add "Item1", 10
 MeuDicionario.Add "Item2", 20
 MeuDicionario.Add "Item3", 30
 MeuDicionario.Remove ("Item2")

 For N = 0 To MeuDicionario.Count - 1
 MsgBox MeuDicionario.Keys(N) & " " & MeuDicionario.Items(N)
 Next N

 MeuDicionario.RemoveAll

 MsgBox MeuDicionario.Count
End Sub

O código adiciona três itens ao dicionário e, em seguida, remove ‘Item2’. Na sequência, ele itera pelo dicionário para provar que ‘Item2’ não existe mais.

Por fim, o código remove todos os itens do dicionário e exibe a contagem do dicionário, que agora é zero.

 

Alteração da Sensibilidade a Maiúsculas e Minúsculas nas Pesquisas

Se você fizer uma pesquisa de uma chave, ela diferencia maiúsculas de minúsculas por padrão. Entretanto, você pode usar a propriedade “CompareMode” para alterar isso.

Observe que isso deve ser feito imediatamente no código após a criação do objeto de dicionário, mas antes de adicionar qualquer dado ao dicionário. Uma vez que o modo de comparação tenha sido definido, ele não poderá ser alterado dentro do dicionário.

Sub Mudar_Sensibilidade_Maiusculas_Minusculas()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.CompareMode = TextCompare
 MeuDicionario.Add "Item1", 10
 MeuDicionario.Add "Item2", 20
 MeuDicionario.Add "Item3", 30

 MsgBox MeuDicionario.Exists("item2")
End Sub

Neste exemplo, o modo de comparação está definido como “TextCompare”, o que significa que não diferencia maiúsculas de minúsculas. A instrução ‘Exists’ no final do exemplo retornará Verdadeiro, apesar do fato de o texto da pesquisa estar todo em minúsculas.

No Excel, há apenas dois valores que podem ser usados para o modo de comparação. A comparação binária diferencia maiúsculas de minúsculas e a comparação de texto não diferencia maiúsculas de minúsculas.

Se o modo de comparação estiver definido como Comparação binária, será necessário ter cuidado ao nomear as chaves. Se você definir um nome para ter uma letra maiúscula como primeiro caractere, quando estiver alterando o valor, deverá certificar-se de que o primeiro caractere ainda seja maiúsculo. Se você começar com um caractere minúsculo, isso será interpretado como uma nova chave e poderá facilmente gerar confusão e erros no dicionário.

Lembre-se de que se você alterar um valor para uma chave e o nome da chave não existir devido ao uso de uma comparação binária, uma nova chave e um novo valor serão adicionados ao dicionário.

Se, em vez disso, você usar a comparação de texto, todas as alterações de valor serão aplicadas à chave, independentemente do caso. Se você tentar adicionar o mesmo item, mas escrito com um caractere de caixa diferente, receberá um erro porque ele já existe.

Classificação do Dicionário

Assim como no caso do objeto de coleção, não há nenhum método fornecido para classificar o dicionário, seja usando chaves ou valores de itens.

Entretanto, como o código VBA está em uma pasta de trabalho do Excel, os dados do dicionário podem ser transferidos para o Excel em forma de tabela e, em seguida, o recurso de classificação do Excel pode ser aplicado a eles. O dicionário pode então ser limpo com o uso de “RemoveAll” e os valores classificados podem ser adicionados a partir da planilha.

Este código classificará tanto as chaves quanto os valores dos itens

Sub ClassificarMeuDicionario()

 Dim MeuDicionario As New Dictionary
 Dim Contador As Long

 'Crie o dicionário com itens em ordem aleatória
 MeuDicionario.Add "Item5", 5
 MeuDicionario.Add "Item2", 15
 MeuDicionario.Add "Item4", 11
 MeuDicionario.Add "Item1", 2
 MeuDicionario.Add "Item3", 19

 'Capturar o número de itens no dicionário para uso futuro
 Contador = MeuDicionario.Count
    
 'Iterar pelo dicionário copiando cada chave e item para uma célula consecutiva em 'Planilha1' (coluna A)
 For N = 0 To MeuDicionario.Count - 1
     Sheets("Planilha1").Cells(N + 1, 1) = MeuDicionario.Keys(N)
     Sheets("Planilha1").Cells(N + 1, 2) = MeuDicionario.Items(N)
 Next N

 'Ative a Planilha1 e use a rotina de classificação do Excel para classificar os dados em ordem crescente
 Sheets("Planilha1").Activate
 Range("A1:B" & MeuDicionario.Count).Select

 ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add2 Key:=Range( _
     "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
     xlSortNormal
 With ActiveWorkbook.Worksheets("Planilha1").Sort
    .SetRange Range("A1:A5")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
 End With

 'Limpar todos os itens do dicionário
 MeuDicionario.RemoveAll

 'Copiar os valores das células de volta para o objeto de dicionário vazio usando o valor armazenado (Contador) para o 'loop
 For N = 1 To Contador
     MeuDicionario.Add Sheets("Planilha1").Cells(N, 1).Value, Sheets("Planilha1").Cells(N, 2).Value
 Next N

 'Iterar pelo dicionário para provar a ordem em que os itens estão agora
 For N = 0 To MeuDicionario.Count - 1
     MsgBox MeuDicionario.Keys(N) & " " & MeuDicionario.Items(N)
 Next N

 'Limpe a planilha (Planilha1) - se necessário, exclua-a também
 Sheets("Planilha1").Range(Cells(1, 1), Cells(Contador, 2)).Clear
End Sub

Esse código cria um dicionário com cinco valores de ordem aleatória adicionados. Ele captura o número de itens em uma variável e, em seguida, itera pelo dicionário, transferindo os valores da chave e do item para colunas separadas em uma planilha.

Em seguida, ele classifica o intervalo baixado, usando a coluna A como campo de classificação. O dicionário é completamente limpo usando o método “RemoveAll” e o código itera pelos valores das células na planilha, adicionando-os novamente ao dicionário.

Por fim, o código itera pelo dicionário, exibindo os valores da chave e do item concatenados para provar que a classificação funcionou.

Ao alterar os parâmetros no código de classificação, os dados podem ser classificados por valores de item.

Cópia de uma Lista de Chaves para uma Planilha

Você pode copiar uma lista de todos os valores-chave em uma planilha usando o código a seguir:

Sub CopiarListaChaves()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.CompareMode = TextCompare
 MeuDicionario.Add "Item1", 10
 MeuDicionario.Add "Item2", 20
 MeuDicionario.Add "Item3", 30

 Sheets("Planilha1").Range("A1").Value = Join(MeuDicionario.Keys, vbLf)
End Sub

Isso produzirá o resultado em sua planilha:

Você pode copiar um dicionário inteiro em uma planilha usando este código:

Sub CopiarParaPlanilha()
 Dim MeuDicionario As New Scripting.Dictionary

 MeuDicionario.Add "Item1", 10
 MeuDicionario.Add "Item2", 20
 MeuDicionario.Add "Item3", 30

 Range("A1").Resize(MeuDicionario.Count, 1) = WorksheetFunction.Transpose(MeuDicionario.Keys)
 Range("B1").Resize(MeuDicionario.Count, 1) = WorksheetFunction.Transpose(MeuDicionario.Items)
End Sub

Sua planilha terá a seguinte aparência:

Comparação de um Dicionário com uma Coleção

O Dicionário é mais rápido do que uma Coleção.

Uma coleção já está dentro do VBA. Um Dicionário precisa de uma referência ao Microsoft Scripting Dictionary para ser adicionado ou de um objeto criado usando a vinculação tardia.

Um item de coleção só pode ser gravado uma vez e lido várias vezes. Em um Dicionário , o valor do item pode ser alterado. Com uma Coleção, o item precisa ser removido e, em seguida, o item alterado deve ser adicionado novamente.

A coleção trabalha com valores de índice, o que pode dificultar a identificação de qual valor de índice pertence a um lugar. O Dicionário trabalha com valores-chave exclusivos que são usados para localizar um item.

A recuperação de um único item é mais lenta em uma grande coleção do que em um dicionário.

Em uma coleção, as chaves são usadas apenas para pesquisar dados e não podem ser recuperadas. Em um dicionário, as chaves podem ser testadas quanto à existência e podem ser usadas para localizar um item específico.

As coleções diferenciam maiúsculas de minúsculas e isso não pode ser alterado. Em um dicionário, o modo de comparação pode ser definido para dar sensibilidade a maiúsculas e minúsculas ou não.

Em uma Coleção, os valores-chave devem ser strings. Em um Dicionário , eles podem ser de qualquer tipo de dados, por exemplo, numérico, data, etc

A remoção de todos os itens de uma coleção envolve a redefinição do objeto Collection. O Dicionário tem o método “RemoveAll” para isso.