Usando um dicionário VBA
Um dicionário VBA opera de maneira 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 maneira semelhante a um dicionário normal que você usaria se quiser 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' o valor da chave para pesquisar o valor do item no objeto de dicionário, de maneira semelhante a que usaria um dicionário convencional.
Devido à maneira como o objeto de dicionário funciona, os valores-chave devem ser todos únicos, da mesma forma que em um dicionário convencional. Imagine se você abrisse seu dicionário convencional para pesquisar 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 teclas como texto, em vez de apenas números.
Comparando com um objeto de coleção, o objeto de coleção é somente leitura. Possui apenas dois métodos (Adicionar e Remover) e duas propriedades (Contagem 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 dentro dele. Não precisa ser definido em tamanho, como uma matriz convencional
O objeto de dicionário é unidimensional e o tipo de dados é ‘Variante’, portanto, qualquer tipo de dados pode ser inserido nele, por exemplo numérico, texto, data
O dicionário VBA não é nativo do Excel e precisa ser acessado por ligação inicial ou tardia ao definir o objeto de dicionário
| 123 | Sub EarlyBindingExample ()Dim MyDictionary como novo Scripting.DictionaryEnd Sub |
| 1234 | Sub LateBindingExample ()Dim MyDictionary As ObjectDefinir MyDictionary = CreateObject ("Scripting.Dictionary")End Sub |
Se você usar a vinculação inicial, deverá adicionar uma referência à biblioteca ‘Microsoft Scripting Runtime’
Você pode fazer isso selecionando ‘Ferramentas | Referências "na barra de menu da janela Editor do Visual Basic (VBE) e uma janela pop-up aparecerá com uma lista de bibliotecas disponíveis.
Role para baixo até ‘Microsoft Scripting Runtime’ e marque a caixa ao lado dele. Clique em OK e esta biblioteca agora faz parte do seu projeto VBA e pode ser referenciada usando vinculação antecipada. Todos os exemplos de código neste artigo usarão a vinculação inicial.
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
A biblioteca Scripting Runtime tem "Intellisense". Conforme você escreve seu código, você verá listas de métodos e propriedades disponíveis, o que ajuda a prevenir erros de ortografia, que irão causar bugs em seu programa
Além disso, se você pressionar F2 dentro do VBE e selecionar a biblioteca 'Scripting', verá todos os métodos e propriedades disponíveis e os parâmetros necessários para cada
Distribuindo seu aplicativo Excel contendo um dicionário
Como já apontado, a biblioteca Scripting Runtime não faz parte do Excel VBA, portanto, se você distribuir seu aplicativo a outros usuários, eles devem ter acesso à biblioteca Scripting Runtime em seus computadores. Caso contrário, ocorrerá um erro.
É uma boa ideia incluir algum código VBA para verificar se essa biblioteca está presente quando o aplicativo Excel é carregado. Você pode usar o comando ‘Dir’ para fazer isso no evento ‘Workbook Open’
A localização do arquivo é C: \ Windows \ SysWOW64 \ scrrun.dll
Escopo de um objeto de dicionário
O objeto Dicionário está disponível apenas enquanto a pasta de trabalho do Excel está aberta. Ele não é salvo quando a pasta de trabalho é salva.
Se o seu dicionário deve estar disponível para todas as rotinas dentro do seu módulo, você precisa declará-lo (Dim) na seção Declare no topo do módulo
Você o define como um objeto global se quiser que seu dicionário seja usado em todo o código.
| 1 | Global MyDictionary como novo dicionário |
Preenchendo e lendo de seu dicionário
Para começar, você precisa criar um dicionário, preenchê-lo com alguns dados e, em seguida, iterar por meio dele para provar que os dados existem
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Para n = 0 Para MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Próximo nEnd Sub |
Este código cria um novo objeto de dicionário chamado ‘MyDictionary’ e o preenche com três itens. O método Add tem dois parâmetros - chave e item, e ambos são obrigatórios
Os tipos de dados para Chave e Item são variantes, portanto, aceitam qualquer tipo de dados - numérico, texto, data, etc.
O primeiro item do dicionário pode ser adicionado como:
| 1 | MyDictionary.Add 10, "MyItem1" |
Os valores foram invertidos entre Chave e Item, mas ainda funcionaria, embora a chave de pesquisa agora se tornasse 10.
No entanto, é importante entender que o valor-chave é o valor de pesquisa no dicionário. Funciona de maneira muito semelhante à função VLOOKUP do Excel. Como todas as chaves precisam ter valores exclusivos, você pode especificar um valor-chave e retornar instantaneamente o valor do item para essa chave.
Observe que o índice do dicionário começa em 0, então você precisa subtrair 1 da contagem do dicionário usado no loop For… Next
Você também pode usar um loop For … Each para ler os valores no dicionário:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.Dictionary, I As VariantMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Para cada I em MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Proximo euEnd Sub |
Este código irá iterar através de cada item e exibir a chave do item e o valor do item
Usando o Número de Índice do Item
Você pode usar o número do índice de uma chave ou item para ler o valor
| 123456789101112 | Sub IndexNumbers ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Keys (2)MsgBox MyDictionary.Items (1)End Sub |
Este código retornará a chave ‘item3’ conforme o índice começa em 0, e o valor do item 20
Você pode se referir a chaves individuais ou valores de item dentro das coleções de Chaves ou Itens usando os números de índice.
Filtrando o Dicionário
Não existe um método direto para fazer isso, mas é bastante simples escrever o código para fazer isso:
| 1234567891011 | Sub FilterDictionary ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.Add "AAItem1", 10MyDictionary.Add "BBItem2", 20MyDictionary.Add "BBItem3", 30Para cada filtro I In (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Proximo euEnd Sub |
O valor do filtro funciona apenas a partir do início do valor da chave. Você não pode usar curingas no filtro. Este código retornará os dois valores de item com nomes de chave começando com ‘BB’
Isso fornecerá um subconjunto do dicionário com base no valor do seu filtro, que poderá então transferir para outro dicionário ou planilha. Com um planejamento cuidadoso dos nomes das chaves, certificando-se de que haja um prefixo significativo para cada uma, você poderá facilmente dividir o dicionário em várias partes componentes.
Mudando o valor de um item de uma chave
O objeto de dicionário tem uma grande vantagem sobre uma coleção, pois o valor do item pode ser alterado, por exemplo,
| 1 | MyDictionary ("MyItem4") = "40" |
Na coleção, você precisaria excluir essa entrada e recriá-la.
Aqui está um exemplo de código:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"Para n = 0 Para MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Próximo nEnd Sub |
O código acima configura três itens no dicionário e, em seguida, altera o valor de ‘MyItem2’ de 20 para 25.
Ele também altera o valor de ‘MyItem4’ para 40. Observe que nas instruções add do código, nenhum ‘MyItem4’ 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 ter cuidado com seus nomes de chave. Um erro de grafia inadvertido no nome da chave significaria que uma nova chave foi 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.
Teste se existe uma chave
Você pode verificar se existe um valor-chave no dicionário
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")End Sub |
O código adiciona três itens a um novo objeto de dicionário e, em seguida, testa uma chave (‘MyItem8’) que não está no dicionário. Isso retorna False, mas se uma das chaves existentes fosse usada, ele retornaria True
Caracteres curinga não são aceitos. O texto de pesquisa também diferencia maiúsculas de minúsculas por padrão, mas isso pode ser alterado (veja mais adiante no artigo)
Usando vários valores em um dicionário
Ao contrário de uma matriz, o objeto de dicionário é unidimensional apenas. Isso pode causar problemas se você tiver vários valores que deseja colocar em uma chave.
Uma maneira de contornar isso é concatenar cada valor de item usando um caractere delimitador entre cada valor, por exemplo, ‘|’
| 12345678910111213141516171819202122232425262728293031323334 | Sub MultipleValues ()'Criar objeto de dicionário e variáveisDim MyDictionary As New Scripting.Dictionary, V1 As Integer, V2 As StringDim V3 como data, Temp como string, N como inteiro'Preencher 3 variáveis para demonstrar vários valoresV1 = 5V2 = "Exemplo de vários valores"V3 = "22 de julho de 2020"'Adicione o valor concatenado ao dicionário usando "|" delimitadorMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 e "|" & V3 e "|"'Capture o valor do dicionário concatenado do dicionário em uma variávelTemp = MyDictionary ("MyMultipleItem")'Iterar através da string concatenada para separar os valores individuaisFazer'Encontre a posição de um delimitadorN = InStr (Temp, "|")'Se não houver mais delimitadores, a saída Do loopSe N = 0, então saia e faça'Mostra o texto relativo à posição do delimitador encontradoMsgBox Left (Temp, N - 1)'Trunca a string concatenada para o próximo caractere após o delimitador encontradoTemp = Mid (Temp, N + 1)CicloEnd Sub |
Outra maneira de contornar esse problema é projetar seu próprio sistema de subscripting para nomes de chaves. Não há razão para que você não use colchetes e números em nomes de chaves
| 1234567891011 | Sub MultipleValues ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Multiple (2)", "Exemplo de vários valores"MyDictionary.Add "Multiple (3)", "22-Jul-2020"Para N = 1 a 3MsgBox MyDictionary ("Multiple (" & N & ")")Próximo NEnd Sub |
Este código adiciona três chaves ao dicionário, mas cada nome de chave contém um número de subscript entre colchetes. Você pode então referir-se ao nome da chave, mas usando o número do subscrito concatenado em. Isso é muito semelhante a usar um objeto de matriz
Excluindo Itens
Você pode remover itens individuais por referência ao valor-chave
| 1 | MyDictionary.Remove (“MyItem2”) |
Observe que, como os nomes das chaves são únicos, isso remove apenas aquela chave e valor de item específicos
Você também pode limpar o dicionário completamente
| 1 | MyDictionary.RemoveAll |
Aqui está um exemplo do uso de ‘Remover’ no VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MyDictionary.Remove ("Item2")Para N = 0 Para MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Próximo NMyDictionary.RemoveAllMsgBox MyDictionary.CountEnd Sub |
O código adiciona três itens ao dicionário e, em seguida, remove 'Item2'. Em seguida, itera através do 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 para pesquisas
Se você fizer uma pesquisa por uma chave, ela diferencia maiúsculas de minúsculas por padrão. No entanto, você pode usar a propriedade ‘CompareMode’ para alterar isso.
Observe que isso deve ser feito imediatamente no código, depois de criar o objeto de dicionário, mas antes de adicionar quaisquer dados ao dicionário. Uma vez que o modo de comparação foi definido, ele não pode ser alterado dentro desse dicionário.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Exists ("item2")End Sub |
Neste exemplo, o modo de comparação é definido como ‘TextCompare’, o que significa que não diferencia maiúsculas de minúsculas. A declaração ‘Exists’ no final do exemplo retornará True, apesar do fato de que o texto da pesquisa está todo em letras minúsculas.
No Excel, existem 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 você tiver o modo de comparação definido como Comparação binária, será necessário ter cuidado ao nomear suas chaves. Se você definir um nome para ter uma letra maiúscula como o primeiro caractere, ao alterar o valor, você deve certificar-se de que o primeiro caractere ainda está em maiúsculas. Se você começar com um caractere minúsculo, isso será interpretado como uma nova chave e pode facilmente levar a confusão e erros em seu dicionário
Lembre-se de que se você alterar o valor de 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 você usar a comparação de texto em vez disso, quaisquer alterações de valor irão para a chave, independentemente do caso. Se você tentar adicionar o mesmo item, mas escrito com um caractere diferente de maiúsculas e minúsculas, receberá um erro porque ele já existe.
Classificando o Dicionário
Tal como acontece com o objeto de coleção, não há nenhum método fornecido para poder classificar o dicionário, seja usando chaves ou valores de item.
No entanto, 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 tabular e, em seguida, o recurso de classificação do Excel pode ser aplicado a eles. O dicionário pode então ser limpo usando 'RemoveAll' e os valores classificados adicionados da planilha.
Este código irá classificar as chaves e os valores dos itens
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary como novo dicionárioDim Counter As Long'Construir dicionário com itens de ordem aleatóriaMyDictionary.Add "Item5", 5MyDictionary.Add "Item2", 15MyDictionary.Add "Item4", 11MyDictionary.Add "Item1", 2MyDictionary.Add "Item3", 19'Capture o número de itens no dicionário para uso futuroCounter = MyDictionary.Count'Iterar através do dicionário copiando cada chave e item para uma célula consecutiva na' Planilha1 '(coluna A)Para N = 0 Para MyDictionary.Count - 1Folhas ("Folha1"). Células (N + 1, 1) = MyDictionary.Keys (N)Folhas ("Folha1"). Células (N + 1, 2) = MyDictionary.Items (N)Próximo N'Ative a Planilha1 e use a rotina de classificação do Excel para classificar os dados em ordem crescentePlanilhas ("Folha1"). AtiveRange ("A1: B" & MyDictionary.Count) .SelecioneActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalCom ActiveWorkbook.Worksheets ("Planilha1"). Classificar.SetRange Range ("A1: A5").Header = xlGuess.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplicarTerminar com'Limpar todos os itens do dicionárioMyDictionary.RemoveAll'Copie os valores da célula de volta para o objeto de dicionário vazio usando o valor armazenado (Contador) para o' loopPara N = 1 para combaterMyDictionary.Add Sheets ("Sheet1"). Cells (N, 1) .Value, Sheets ("Sheet1"). Cells (N, 2) .ValuePróximo N'Iterar através do dicionário para provar a ordem em que os itens estão agoraPara N = 0 Para MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Próximo N'Limpe a planilha (Folha1) - se necessário, exclua-a tambémFolhas ("Folha1"). Intervalo (Células (1, 1), Células (Contador, 2)). LimparEnd Sub |
Este 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 por meio do dicionário, transferindo os valores da chave e do item em colunas separadas em uma planilha.
Em seguida, ele classifica o intervalo baixado, usando a coluna A como o campo de classificação. O dicionário é limpo completamente usando o método ‘RemoveAll’, e o código então itera através dos valores das células na planilha, adicionando-os de volta ao dicionário.
Finalmente, o código itera por meio do dicionário, exibindo os valores de chave e de 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.
Copiando uma lista de chaves para uma planilha
Você pode copiar uma lista de todos os valores-chave em uma planilha usando o seguinte código:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Sheets ("Sheet1"). Range ("A1"). Value = Join (MyDictionary.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:
| 12345678910 | Sub CopyIntoWorksheet ()Dim MyDictionary como novo Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Range ("A1"). Resize (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Range ("B1"). Resize (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Items)End Sub |
Sua planilha ficará assim:
Comparando 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 Dicionário de Scripts da Microsoft para ser adicionado ou um objeto criado usando ligação tardia
Um item da coleção só pode ser escrito uma vez e lido várias vezes. Em um dicionário, o valor do item pode ser alterado. Com uma coleção, o item deve ser removido e, em seguida, o item alterado adicionado de volta.
A coleção trabalha com valores de índice, o que pode ser difícil de determinar qual valor de índice pertence a onde. O Dicionário trabalha com valores-chave únicos que são usados para localizar um item
A recuperação de um único item é mais lenta em uma coleção grande 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 ter sua existência testada e podem ser usadas para encontrar um item específico.
As coleções diferenciam maiúsculas de minúsculas e não podem ser alteradas. Em um dicionário, o modo de comparação pode ser definido para dar diferenciação ou não diferenciação de maiúsculas e minúsculas
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
Remover todos os itens em uma Coleção envolve a redefinição do objeto Coleção. O Dicionário tem o método ‘RemoveAll’ para isso.
