Utiliser un dictionnaire VBA
Un dictionnaire VBA fonctionne de la même manière qu'un objet de collection, mais il a plus de propriétés et de méthodes et offre plus de flexibilité
Le dictionnaire stocke les données en mémoire et peut être facilement manipulé. Aucun calcul automatique, sauvegarde en arrière-plan et actualisation de l'écran n'est requis, votre code s'exécutera donc beaucoup plus rapidement.
L'objet dictionnaire fonctionne de la même manière qu'un dictionnaire normal que vous utiliseriez si vous souhaitez découvrir le sens d'un mot. Chaque entrée de l'objet dictionnaire a une valeur « clé » et une valeur « élément ». Vous utilisez la « clé » la valeur de la clé pour rechercher la valeur de l'élément dans l'objet dictionnaire, de la même manière que vous utiliseriez un dictionnaire conventionnel.
En raison du fonctionnement de l'objet dictionnaire, les valeurs clés doivent toutes être uniques, de la même manière que dans un dictionnaire conventionnel. Imaginez si vous ouvriez votre dictionnaire conventionnel pour rechercher le sens d'un mot et trouviez le mot répertorié plus d'une fois avec deux définitions totalement différentes. Vous seriez très confus !
Les valeurs clés sont généralement du texte ou des nombres, ou les deux. Les utilisateurs trouvent souvent plus facile de se souvenir des noms de touches sous forme de texte plutôt que de simples chiffres.
Comparé à un objet de collection, l'objet de collection est en lecture seule. Il n'a que deux méthodes (Add et Remove) et deux propriétés (Count et Item). Une fois qu'un élément a été ajouté à un objet de collection, il peut uniquement être supprimé, mais pas modifié, ce qui est une procédure fastidieuse si la valeur d'un élément doit être modifiée.
Un objet de dictionnaire changera de taille automatiquement pour s'adapter au nombre d'éléments qu'il contient. Il n'a pas besoin d'être défini en taille, comme un tableau conventionnel
L'objet dictionnaire est unidimensionnel et le type de données est « Variant », de sorte que n'importe quel type de données peut y être entré, par ex. numérique, texte, date
Le dictionnaire VBA n'est pas natif d'Excel et doit être accessible par liaison anticipée ou tardive lors de la définition de l'objet dictionnaire
| 123 | Sub EarlyBindingExample()Dim MyDictionary en tant que nouveau Scripting.DictionaryFin du sous-marin |
| 1234 | Sub LateBindingExample()Dim MyDictionary en tant qu'objetDéfinir MyDictionary = CreateObject("Scripting.Dictionary")Fin du sous-marin |
Si vous utilisez la liaison anticipée, vous devez ajouter une référence à la bibliothèque « Microsoft Scripting Runtime »
Pour ce faire, sélectionnez « Outils | Références » dans la barre de menus de la fenêtre Visual Basic Editor (VBE) et une fenêtre contextuelle apparaîtra avec une liste des bibliothèques disponibles.
Faites défiler jusqu'à « Microsoft Scripting Runtime » et cochez la case à côté. Cliquez sur OK et cette bibliothèque fait maintenant partie de votre projet VBA et peut être référencée à l'aide d'une liaison anticipée. Tous les exemples de code dans cet article utiliseront la liaison anticipée.
Votre code s'exécutera considérablement plus rapidement avec une liaison anticipée, car tout est compilé à l'avance. Avec la liaison tardive, l'objet doit être compilé pendant l'exécution du code
La bibliothèque Scripting Runtime a 'Intellisense'. Au fur et à mesure que vous écrivez votre code, vous verrez apparaître des listes de méthodes et de propriétés disponibles, ce qui permet d'éviter les erreurs d'orthographe, ce qui provoquera des bogues dans votre programme
De plus, si vous appuyez sur F2 dans le VBE et sélectionnez la bibliothèque 'Scripting', vous verrez toutes les méthodes et propriétés disponibles, ainsi que les paramètres requis pour chacun.
Distribuer votre application Excel contenant un dictionnaire
Comme déjà indiqué, la bibliothèque Scripting Runtime ne fait pas partie d'Excel VBA, donc si vous distribuez votre application à d'autres utilisateurs, ils doivent avoir accès à la bibliothèque Scripting Runtime sur leur ordinateur. Si ce n'est pas le cas, une erreur se produira.
C'est une bonne idée d'inclure du code VBA pour vérifier que cette bibliothèque est présente lorsque votre application Excel est chargée. Vous pouvez utiliser la commande « Dir » pour le faire sur l'événement « Workbook Open »
L'emplacement du fichier est C:\Windows\SysWOW64\scrrun.dll
Portée d'un objet de dictionnaire
L'objet Dictionnaire n'est disponible que lorsque le classeur Excel est ouvert. Il n'est pas enregistré lorsque le classeur est enregistré.
Si votre dictionnaire doit être disponible pour toutes les routines de votre module, vous devez le déclarer (Dim) dans la section Declare tout en haut du module
Vous le définissez comme un objet global si vous souhaitez que votre dictionnaire soit utilisé dans tout votre code.
| 1 | Global MyDictionary comme nouveau dictionnaire |
Remplir et lire à partir de votre dictionnaire
Pour commencer, vous devez créer un dictionnaire, le remplir avec des données, puis le parcourir pour prouver que les données existent
| 1234567891011 | Sub PopulateReadDictionary()Dim MyDictionary en tant que nouveau Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Pour n = 0 Vers MyDictionary.Count - 1MsgBox MyDictionary.Keys(n) & " " & MyDictionary.Items(n)Suivant nFin du sous-marin |
Ce code crée un nouvel objet de dictionnaire appelé « MyDictionary », puis le remplit avec trois éléments. La méthode Add a deux paramètres - Key et Item, et ils sont tous les deux requis
Les types de données pour la clé et l'élément sont tous deux des variantes, ils accepteront donc tout type de données - numérique, texte, date, etc.
Le premier élément du dictionnaire pourrait être ajouté comme suit :
| 1 | MyDictionary.Add 10, "MyItem1" |
Les valeurs ont été inversées entre Key et Item, mais cela fonctionnerait toujours, bien que la clé de recherche devienne maintenant 10.
Cependant, il est important de comprendre que la valeur clé est la valeur de recherche dans le dictionnaire. Cela fonctionne de manière très similaire à la fonction RECHERCHEV dans Excel. Étant donné que toutes les clés doivent avoir des valeurs uniques, vous pouvez spécifier une valeur de clé et renvoyer instantanément la valeur de l'élément pour cette clé.
Notez que l'index du dictionnaire commence à 0, vous devez donc soustraire 1 du nombre de dictionnaire utilisé dans la boucle For… Next
Vous pouvez également utiliser une boucle For… Each pour lire les valeurs dans le dictionnaire :
| 1234567891011 | Sub PopulateReadDictionary()Dim MyDictionary en tant que nouveau Scripting.Dictionary, I en tant que varianteMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Pour chaque I dans MyDictionary.KeysMsgBox I & " " & MyDictionary(I)Ensuite jeFin du sous-marin |
Ce code parcourra chaque élément et affichera la clé de l'élément et la valeur de l'élément
Utilisation du numéro d'index de l'article
Vous pouvez utiliser le numéro d'index d'une clé ou d'un élément pour lire la valeur
| 123456789101112 | Numéros de sous-index()Dim MyDictionary en tant que nouveau Scripting.DictionaryMyDictionary.CompareMode = TextComparerMonDictionnaire.Ajouter "Item1", 10MonDictionnaire.Ajouter "Item2", 20MonDictionnaire.Ajouter "Item3", 30MsgBox MyDictionary.Keys(2)MsgBox MyDictionary.Items(1)Fin du sous-marin |
Ce code renverra la clé 'item3' car l'index commence à 0, et la valeur de l'élément 20
Vous pouvez faire référence à des valeurs de clé ou d'élément individuelles dans les collections Keys ou Items à l'aide des numéros d'index.
Filtrage du dictionnaire
Il n'y a pas de méthode directe pour le faire, mais il est assez simple d'écrire du code pour le faire :
| 1234567891011 | Sub FilterDictionary()Dim MyDictionary en tant que nouveau Scripting.DictionaryMonDictionnaire.Ajouter "AAItem1", 10MonDictionnaire.Ajouter "BBItem2", 20MonDictionnaire.Ajouter "BBItem3", 30Pour chaque I dans le filtre (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item(I)Ensuite jeFin du sous-marin |
La valeur du filtre ne fonctionne qu'à partir du début de la valeur de la clé. Vous ne pouvez pas utiliser de caractères génériques dans le filtre. Ce code renverra les deux valeurs d'élément avec des noms de clé commençant par « BB »
Cela vous donnera un sous-ensemble du dictionnaire en fonction de votre valeur de filtre, que vous pourrez ensuite transférer vers un autre dictionnaire ou une feuille de calcul. Avec une planification minutieuse des noms de clés, en veillant à ce qu'il y ait un préfixe significatif pour chacun, vous pourrez facilement diviser le dictionnaire en différentes parties.
Modification d'une valeur d'élément d'une clé
L'objet dictionnaire a un gros avantage sur une collection en ce que la valeur de l'élément peut être modifiée, par ex.
| 1 | MonDictionnaire("MonItem4") = "40" |
Dans la collection, vous devrez supprimer cette entrée, puis la recréer.
Voici un exemple de code :
| 12345678910111213 | Sub PopulateReadDictionary()Dim MyDictionary en tant que nouveau Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MonDictionnaire.Ajouter "MonItem3", 30MonDictionnaire("MonItem2") = "25"MonDictionnaire("MonItem4") = "40"Pour n = 0 Vers MyDictionary.Count - 1MsgBox MyDictionary.Keys(n) & " " & MyDictionary.Items(n)Suivant nFin du sous-marin |
Le code ci-dessus configure trois éléments dans le dictionnaire, puis modifie la valeur de « MyItem2 » de 20 à 25.
Il modifie également la valeur de 'MyItem4' à 40. Notez que dans les instructions add du code, aucun 'MyItem4' n'a été ajouté. Lorsque vous modifiez la valeur d'une clé qui n'existe pas, elle est automatiquement créée. C'est extrêmement pratique, car aucune erreur n'est déclenchée, mais cela signifie que vous devez faire attention aux noms de vos clés. Une faute d'orthographe par inadvertance dans le nom de la clé signifierait qu'une nouvelle clé est créée et que le nom de la clé d'origine aurait toujours l'ancienne valeur.
Cela pourrait facilement conduire à des problèmes d'intégrité dans l'objet dictionnaire.
Tester si une clé existe
Vous pouvez vérifier si une valeur clé existe dans le dictionnaire
| 123456789 | Sous-CheckExistsDictionary()Dim MyDictionary en tant que nouveau Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MonDictionnaire.Ajouter "MonItem3", 30MsgBox MyDictionary.Exists("MyItem8")Fin du sous-marin |
Le code ajoute trois éléments à un nouvel objet de dictionnaire, puis teste une clé ("MyItem8") qui n'est pas dans le dictionnaire. Cela renvoie False, mais si l'une des clés existantes avait été utilisée, cela renverrait True
Les caractères génériques ne sont pas acceptés. Le texte de recherche est également sensible à la casse par défaut, mais cela peut être modifié (voir plus loin dans l'article)
Utilisation de plusieurs valeurs dans un dictionnaire
Contrairement à un tableau, l'objet dictionnaire est unidimensionnel. Cela peut entraîner des problèmes si vous avez plusieurs valeurs que vous souhaitez associer à une clé.
Une façon de contourner cela consiste à concaténer chaque valeur d'élément à l'aide d'un caractère délimiteur entre chaque valeur, par ex. '|'
| 12345678910111213141516171819202122232425262728293031323334 | Sous-valeurs multiples()'Créer un objet de dictionnaire et des variablesDim MyDictionary en tant que nouveau Scripting.Dictionary, V1 en tant qu'entier, V2 en tant que chaîneDim V3 en tant que date, Temp en tant que chaîne, N en tant qu'entier'Remplir 3 variables pour démontrer plusieurs valeursV1 = 5V2 = "Exemple de valeurs multiples"V3 = "22-juil-2020"'Ajoute la valeur concaténée au dictionnaire en utilisant "|" délimiteurMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Capturer la valeur du dictionnaire concaténé du dictionnaire dans une variableTemp = MyDictionary("MyMultipleItem")'Itérer à travers la chaîne concaténée pour séparer les valeurs individuellesFaire'Rechercher la position d'un délimiteurN = InStr(Temp, "|")'S'il n'y a plus de délimiteurs, la boucle de sortie DoSi N = 0 Alors Quitter Do'Affiche le texte relatif à la position du délimiteur trouvéMsgBox Gauche (Temp, N - 1)'Tronque la chaîne concaténée au caractère suivant après le délimiteur trouvéTemp = Milieu (Temp, N + 1)BoucleFin du sous-marin |
Une autre façon de contourner ce problème est de concevoir votre propre système de sous-script pour les noms de clé. Il n'y a aucune raison pour laquelle vous ne devriez pas utiliser de crochets et de chiffres dans les noms de clé
| 1234567891011 | Sous-valeurs multiples()Dim MyDictionary en tant que nouveau Scripting.DictionaryMonDictionnaire.Ajouter "Multiple(1)", 5MyDictionary.Add "Multiple(2)", "Exemple de valeurs multiples"MyDictionary.Add "Multiple(3)", "22-Jul-2020"Pour N = 1 à 3MsgBox MyDictionary("Multiple(" & N & ")")N suivantFin du sous-marin |
Ce code ajoute trois clés au dictionnaire, mais chaque nom de clé contient un numéro de sous-script entre parenthèses. Vous pouvez alors vous référer au nom de la clé, mais en utilisant le numéro de sous-script concaténé. Ceci est très similaire à l'utilisation d'un objet tableau
Supprimer des éléments
Vous pouvez supprimer des éléments individuels par référence à la valeur clé
| 1 | MonDictionnaire.Supprimer ("MonItem2") |
Notez que parce que les noms de clé sont uniques, cela ne supprime qu'une clé et une valeur d'élément particulières
Vous pouvez également effacer complètement le dictionnaire
| 1 | MonDictionnaire.SupprimerTout |
Voici un exemple d'utilisation de « Supprimer » dans VBA :
| 12345678910111213141516 | Sous SupprimerValeurs()Dim MyDictionary en tant que nouveau Scripting.DictionaryMonDictionnaire.Ajouter "Item1", 10MonDictionnaire.Ajouter "Item2", 20MonDictionnaire.Ajouter "Item3", 30MonDictionnaire.Supprimer ("Item2")Pour N = 0 à MyDictionary.Count - 1MsgBox MyDictionary.Keys(N) & " " & MyDictionary.Items(N)N suivantMonDictionnaire.SupprimerToutMsgBox MyDictionary.CountFin du sous-marin |
Le code ajoute trois éléments au dictionnaire, puis supprime « Item2 ». Il parcourt ensuite le dictionnaire pour prouver que « Item2 » n'existe plus
Enfin, le code supprime tous les éléments du dictionnaire et affiche le nombre de dictionnaires, qui est désormais nul.
Modification de la sensibilité à la casse pour les recherches
Si vous recherchez une clé, elle est sensible à la casse par défaut. Cependant, vous pouvez utiliser la propriété 'CompareMode' pour changer cela.
Notez que cela doit être fait immédiatement dans le code après avoir créé l'objet dictionnaire, mais avant d'ajouter des données dans le dictionnaire. Une fois le mode de comparaison défini, il ne peut pas être modifié dans ce dictionnaire.
| 12345678910 | Sub ChangeCaseSensitivity()Dim MyDictionary en tant que nouveau Scripting.DictionaryMyDictionary.CompareMode = TextComparerMonDictionnaire.Ajouter "Item1", 10MonDictionnaire.Ajouter "Item2", 20MonDictionnaire.Ajouter "Item3", 30MsgBox MyDictionary.Exists("item2")Fin du sous-marin |
Dans cet exemple, le mode de comparaison est défini sur « TextCompare », ce qui signifie qu'il n'est pas sensible à la casse. L'instruction 'Exists' à la fin de l'exemple renverra True, malgré le fait que le texte recherché est tout en minuscules.
Dans Excel, seules deux valeurs peuvent être utilisées pour le mode de comparaison. La comparaison binaire est sensible à la casse et la comparaison de texte n'est pas sensible à la casse
Si vous avez défini le mode de comparaison sur Comparaison binaire, vous devez être prudent lorsque vous nommez vos clés. Si vous définissez un nom pour qu'il ait une lettre majuscule comme premier caractère, lorsque vous modifiez la valeur, vous devez vous assurer que le premier caractère est toujours en majuscule. Si vous commencez par un caractère minuscule, cela sera interprété comme une nouvelle clé et pourrait facilement entraîner des confusions et des erreurs dans votre dictionnaire
N'oubliez pas que si vous modifiez une valeur pour une clé et que le nom de la clé n'existe pas en raison de l'utilisation d'une comparaison binaire, une nouvelle clé et une nouvelle valeur seront ajoutées au dictionnaire.
Si vous utilisez la comparaison de texte à la place, toute modification de valeur ira à la clé quelle que soit la casse. Si vous essayez d'ajouter le même élément mais orthographié avec un caractère de casse différent, vous obtiendrez une erreur car il existe déjà.
Trier le dictionnaire
Comme pour l'objet de collection, aucune méthode n'est fournie pour pouvoir trier le dictionnaire, que ce soit à l'aide de clés ou de valeurs d'élément.
Cependant, étant donné que le code VBA se trouve dans un classeur Excel, les données du dictionnaire peuvent être transférées dans Excel sous forme de tableau, puis la fonction de tri Excel peut y être appliquée. Le dictionnaire peut ensuite être effacé à l'aide de « Supprimer tout » et les valeurs triées ajoutées à partir de la feuille de calcul.
Ce code va trier à la fois les clés et les valeurs des éléments
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sous-trierMonDictionnaire()Dim MyDictionary comme nouveau dictionnaireCompteur dim aussi long'Construire un dictionnaire avec des éléments d'ordre aléatoireMonDictionnaire.Ajouter "Item5", 5MonDictionnaire.Ajouter "Item2", 15MonDictionnaire.Ajouter "Item4", 11MonDictionnaire.Ajouter "Item1", 2MonDictionnaire.Ajouter "Item3", 19'Capturer le nombre d'éléments dans le dictionnaire pour une utilisation futureCompteur = MonDictionnaire.Compte'Itérer dans le dictionnaire en copiant chaque clé et élément dans une cellule consécutive sur 'Sheet1' (colonne A)Pour N = 0 à MyDictionary.Count - 1Sheets("Sheet1").Cells(N + 1, 1) = MyDictionary.Keys(N)Sheets("Sheet1").Cells(N + 1, 2) = MyDictionary.Items(N)N suivant'Activez la feuille Sheet1 et utilisez la routine de tri Excel pour trier les données par ordre croissantFeuilles("Feuille1").ActiverPlage ("A1:B" & MyDictionary.Count).SélectionnezActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key :=Range( _"A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _xlTriNormalAvec ActiveWorkbook.Worksheets("Sheet1").Tri.SetRange Range("A1:A5").En-tête = xlDevinez.MatchCase = Faux.Orientation = xlHautVersBas.SortMethod = xlPinYin.AppliquerTerminer par'Effacer tous les éléments du dictionnaireMonDictionnaire.SupprimerTout'Copier les valeurs des cellules dans l'objet dictionnaire vide en utilisant la valeur stockée (Compteur) pour la 'bouclePour N = 1 Au compteurMonDictionnaire.Ajouter des feuilles("Feuille1").Cellules(N, 1).Valeur, Feuilles("Feuille1").Cellules(N, 2).ValeurN suivant'Itérer dans le dictionnaire pour prouver l'ordre dans lequel les éléments sont maintenant dansPour N = 0 à MyDictionary.Count - 1MsgBox MyDictionary.Keys(N) & " " & MyDictionary.Items(N)N suivant'Effacer la feuille de calcul (Feuille1) - si nécessaire, supprimez-la égalementFeuilles("Feuille1").Plage(Cellules(1, 1), Cellules(Compteur, 2)).EffacerFin du sous-marin |
Ce code crée un dictionnaire avec cinq valeurs d'ordre aléatoire ajoutées. Il capture le nombre d'éléments dans une variable, puis parcourt le dictionnaire, transférant les valeurs de clé et d'élément dans des colonnes distinctes sur une feuille de calcul.
Il trie ensuite la plage téléchargée, en utilisant la colonne A comme champ de tri. Le dictionnaire est complètement effacé à l'aide de la méthode « RemoveAll », et le code parcourt ensuite les valeurs des cellules de la feuille de calcul en les ajoutant au dictionnaire.
Enfin, le code parcourt le dictionnaire, affichant les valeurs de clé et d'élément concaténées pour prouver que le tri a fonctionné.
En modifiant les paramètres dans le code de tri, les données pourraient être triées par valeurs d'élément.
Copie d'une liste de clés dans une feuille de calcul
Vous pouvez copier une liste de toutes les valeurs clés dans une feuille de calcul à l'aide du code suivant :
| 12345678910 | Sous CopyKeyList()Dim MyDictionary en tant que nouveau Scripting.DictionaryMyDictionary.CompareMode = TextComparerMonDictionnaire.Ajouter "Item1", 10MonDictionnaire.Ajouter "Item2", 20MonDictionnaire.Ajouter "Item3", 30Sheets("Sheet1").Range("A1").Value = Join(MyDictionary.Keys, vbLf)Fin du sous-marin |
Cela produira le résultat dans votre feuille de calcul :
Vous pouvez copier un dictionnaire entier dans une feuille de calcul en utilisant ce code :
| 12345678910 | Sub CopyIntoWorksheet()Dim MyDictionary en tant que nouveau Scripting.DictionaryMonDictionnaire.Ajouter "Item1", 10MonDictionnaire.Ajouter "Item2", 20MonDictionnaire.Ajouter "Item3", 30Range("A1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Keys)Range("B1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Items)Fin du sous-marin |
Votre feuille de calcul ressemblera à ceci :
Comparer un dictionnaire avec une collection
Le dictionnaire est plus rapide qu'une collection.
Une collection est déjà dans VBA. Un dictionnaire a besoin d'une référence au dictionnaire de scripts Microsoft pour être ajouté ou d'un objet créé à l'aide d'une liaison tardive
Un élément de collection ne peut être écrit qu'une seule fois et lu plusieurs fois. Dans un dictionnaire, la valeur de l'élément peut être modifiée. Avec une collection, l'élément doit être supprimé, puis l'élément modifié doit être rajouté.
La collection fonctionne sur les valeurs d'index, ce qui peut être difficile de déterminer quelle valeur d'index appartient où. Le dictionnaire fonctionne sur des valeurs clés uniques qui sont utilisées pour localiser un élément
La récupération d'un seul élément est plus lente dans une grande collection que dans un dictionnaire
Dans une collection, les clés ne sont utilisées que pour rechercher des données et ne sont pas récupérables. Dans un dictionnaire, les clés peuvent être testées pour l'existence et peuvent être utilisées pour trouver un élément particulier.
Les collections sont sensibles à la casse et cela ne peut pas être modifié. Dans un dictionnaire, le mode de comparaison peut être défini pour donner la sensibilité à la casse ou non à la casse
Dans une collection, les valeurs clés doivent être des chaînes. Dans un dictionnaire, ils peuvent être de n'importe quel type de données, par ex. numérique, date, etc.
La suppression de tous les éléments d'une collection implique la redéfinition de l'objet Collection. Le dictionnaire a la méthode « RemoveAll » pour cela.
