[VBA] La variable « Dictionnaire »

Outre sa rapidité, le « Dictionnaire » est une variable particulière qui a pour fonction de gérer les doublons.
Il intervient dans une grande variété de cas de figures et fait partie des outils indispensables à maîtriser lorsqu’on travaille avec des bases de données.
Le dictionnaire est une variable qui va enregistrer une série de « clés » (Key) uniques; chaque clé composant le dictionnaire est accompagnée d’un « élément » (Item), dont la valeur est modifiable.

Dans l’exemple ci-dessous, qui va préciser l’utilité et le fonctionnement du dictionnaire, nous souhaitons calculer le montant total des factures pour chaque client, sachant que la première facture donne automatiquement lieu à une réduction de 10%.

Dictionnary

Option Base 1
Sub test()
'Dans la fenêtre VBA, sélectionner Outils, Références et cocher Microsoft Scripting Runtime
Dim Dico As Object
Dim c As Byte
Dim ii As Variant, jj As Variant
Set Dico = CreateObject("scripting.dictionary")

c = 2
Do Until IsEmpty(Cells(c, 1))
If Not Dico.exists(Cells(c, 1).Value) Then
    Dico(Cells(c, 1).Value) = 0.9 * Cells(c, 3)
Else
    Dico(Cells(c, 1).Value) = Dico(Cells(c, 1).Value) + Cells(c, 3)
End If
c = c + 1
Loop

Range("E1").Resize(Dico.Count, 1) = Application.Transpose(Dico.keys)
Range("F1").Resize(Dico.Count, 1) = Application.Transpose(Dico.items)

Range("G1") = Application.Index(Dico.keys, 3)
Range("H1") = Application.Index(Dico.items, 3)

ii = Dico.keys
jj = Dico.items

Range("I1") = ii(3)
Range("J1") = jj(3)

Set Dico = Nothing: Erase ii: Erase jj
End Sub

Explications

Nous allons, en premier lieu, nous rendre dans la fenêtre VBA, sélectionner Outils, Références et cocher Microsoft Scripting Runtime : nous venons d’accéder à une « librairie » qui nous permet d’avoir des fonctionnalités supplémentaires.

La première étape consiste à créer un « objet » : nous commençons par déclarer la variable que nous nommons Dico Dim Dico As Object; nous créons, ensuite, l’objet Dico Set Dico = CreateObject("scripting.filesystemobject").

A partir d’une boucle classique, nous allons ensuite enregistrer chaque nouveau client de la colonne A dans le dictionnaire. la condition If Not Dico.exists(Cells(c, 1).Value) Then vérifie si la valeur de notre cellule existe déjà dans le dictionnaire ou pas. Son utilisation est très simple; le nom de l’objet créé (Dico) est suivi de la commande .exists et de la valeur à vérifier (Cells(c, 1).Value). Il est préférable de préciser que l’on cherche à enregistrer la valeur (Value) de la cellule (Cells(c,1)) afin que le dictionnaire ne la confonde pas avec la plage de la même cellule.
Cette condition que nous venons de mettre en place n’est, en général, pas nécessaire mais elle est utile dans notre cas de figure.

La commande suivante mérite quelques explications : Dico(Cells(c, 1).Value) = 0.9 * Cells(c, 3). Cela veut dire que si la valeur de notre cellule en colonne A apprait pour la première fois (donc la condition évoquée par la ligne de code précédente) alors la valeur de la cellule de la colonne C correspondante doit être enregistrée à 90% de sa valeur (0.9 * Cells(c, 3)).
Nous devons maintenant comprendre les informations que nous avons de part et d’autre de notre signe =
La partie de gauche Dico(Cells(c, 1).Value) est la clé.
La clé est une valeur unique (pour la première ligne de notre tableau, notre clé = CLient 1), c’est à dire qui ne sera enregistrée qu’une seule fois et qui fait office de « case variable » dans laquelle nous allons enregistrer la valeur de la facture en colonne C -10%.
Autrement dit, notre variable Client 1 a une valeur de 0.9 * 100 donc Client 1 (Key) = 90 (Item)
Pareil, pour la deuxième ligne de notre tableau : Client 15 (Key) = 99 (Item)
etc…
Le principe est exactement le même que celui ou c=c+1 : à gauche, la variable et à droite, le calcul reprenant l’ancienne valeur de cette même variable et l’actualisant; c’est ce qui se passe lorsque notre condition n’est plus vérifiée, autrement dit si notre Key existe déjà, dans la ligne suivante : Dico(Cells(c, 1).Value) = Dico(Cells(c, 1).Value) + Cells(c, 3).

Reprenons l’exemple de notre premier client : Client 1 = 90
Lorsque la boucle va, à nouveau, rencontrer la valeur Client 1 dans notre tableau, en ligne N° 15, alors Client 1 = Client 1 + 230, autrement dit Client 1 = 90 + 230, donc Client 1 (Key) = 320 (Item).

Nous transposons ensuite les résultats de notre dictionnaire dans sur la feuille Excel.
Range("E1").Resize(Dico.Count, 1) = Application.Transpose(Dico.keys)
Range("F1").Resize(Dico.Count, 1) = Application.Transpose(Dico.items)

La transposition se fait ici en deux étapes, la première concernant les Keys et la seconde, les Item.
Voir l’article suivant pour de plus amples détails concernant la transposition.
La seule différence avec une transposition de variable tableau, est que la commande Ubound est remplacée par Dico.Count qui a exactement la même fonction et qui compte le nombre d’éléments que contient le dictionnaire.

On peut également extraire une valeur unique, Key ou Item : nous disposons de deux outils pour cela.
La première consiste à identifier la valeur voulue à partir de son index, c’est à dire son rang dans la série d’éléments de même nature à laquelle elle appartient.
Range("G1") = Application.Index(Dico.keys, 3)
Range("H1") = Application.Index(Dico.items, 3)

La seconde méthode que l’on rencontre consiste à enregistrer l’ensemble des Key dans une variable et l’ensemble des Items, dans une autre.
ii = Dico.keys
jj = Dico.items

L’extraction se fait ensuite selon le même principe, même si la syntaxe est différente; on va redemander le N° d’index de la valeur à extraire; à noter qu’ici l’Option Base 1 ne fonctionne pas et que la première valeur est la valeur N° 0.

Range("I1") = ii(3)
Range("J1") = jj(3)

Il ne nous reste plus qu’à libérer la mémoire de nos variables :
Set Dico = Nothing: Erase ii: Erase jj

Ci-dessous le fichier au format .xls contenant les exemples de cet article.
Dictionnaire

Publicités

[VBA] Boostez la vitesse d’exécution de vos macros en utilisant les « variables Tableau » 3/3

Retranscrire les données d’une variable sur la feuille Excel

Le dernier article de cette série s’attachera à retranscrire les données contenues dans une variable sur la feuille Excel.
Ce sujet a déjà été partiellement abordé dans les articles précédents à des fins d’exemples; nous allons maintenant étudier en détail les mécanismes de cette fonction.

Reprenons l’exemple de l’article précédent

Plage VT

Supposons que nous devions recopier ce tableau à partir de la cellule E1.
Une méthode consiste à enregistrer le tableau dans une variable nommée aa puis à recopier chaque valeur contenue dans la variable par l’intermédiaire de boucles entrelacées.

Option Base 1
Sub Boucle()
Dim aa As Variant
aa = Range("A1").CurrentRegion

For a = LBound(aa) To UBound(aa)
    For b = LBound(aa, 2) To UBound(aa, 2)
        Cells(a, b + 4) = aa(a, b)
    Next b
Next a
End Sub

La première boucle For a = LBound(aa) To UBound(aa) traite chaque ligne du tableau enregistré dans notre variable et le seconde, For b = LBound(aa, 2) To UBound(aa, 2), chaque colonne de chaque ligne.
Cette macro recopie le tableau, cellule par cellule; la méthode reste chronophage.

La seconde méthode enregistre le tableau dans la variable aa et retranscrit la variable immédiatement dans la feuille Excel.

Option Base 1
Sub Boucle2()
Dim aa As Variant, Tablo() As Variant
Dim c As Byte
aa = Range("A1").CurrentRegion

Range("E1").Resize(UBound(aa, 1), UBound(aa, 2)) = aa
End Sub

Il est nécessaire de définir avec précision les dimensions de la plage qui va accueillir les valeurs du tableau.
Nous savons que la commande Ubound(aa,1) nous permet de définir le nombre total de lignes de la variable et que le nombre total de colonnes est obtenu grâce à la commande Ubound(aa,2).
Il suffit donc de redimensionner la cellule E1 par le nombre de lignes et par le nombre de colonnes pour définir la plage d’accueil Range("E1").Resize(UBound(aa, 1), UBound(aa, 2)) et de coller l’information avec =aa.

Il est parfois nécessaire d’inverser le sens lignes/colonnes :

Range("E1").Resize(UBound(aa, 2), UBound(aa, 1)) = Application.Transpose(aa)

On inverse donc l’ordre des commandes de redimensionnement et l’on fait précéder la variable aa de la commande Application.Transpose qui indique à Excel d’inverser le sens lignes/colonnes.

On peut choisir de ne copier q’une ligne :

Range("E1").Resize(1, UBound(aa, 2)) = Application.Index(aa, 5)

C’est la fonction Index qui définit le classement d’un objet parmi une collection d’objets similaires, suivi du nom de la variable et du N° de ligne (ici, la ligne N° 5) qui nous permet de recopier une ligne bien précise.

Le principe est exactement le même pour recopier une colonne :

Range("E1").Resize(UBound(aa, 1), 1) = Application.Index(aa, , 2)

La colonne 2 est, ici, recopiée : la double virgule (aa, , 2) indique au programme qu’il sagit d’une colonne.

On peut aussi décider de copier une ligne en sens vertical :

Range("E1").Resize(UBound(aa, 2), 1) = Application.Transpose(Application.Index(aa, 5))

ou, à l’inverse, copier une colonne dans le sens horizontal :

Range("E1").Resize(1, UBound(aa, 1)) = Application.Transpose(Application.Index(aa, , 2))

Il est aussi possible de copier plusieurs lignes de la variable aa dans une variable tablo et retranscrire les lignes sélectionnées sur la feuille Excel; ici nous enregistrons les lignes paires dans le tablo :

For a = LBound(aa) To UBound(aa)'dela première à la dernière ligne du tableau dans la variable aa
    If a Mod 2 = 0 Then'si le N° de ligne est pair
        c = c + 1'incrémente la variable c de 1
        ReDim Preserve Tablo(c)'redimensionne le tablo
        Tablo(c) = Application.Index(aa, a)'enregistrer toute la ligne dans le tablo
    End If
Next a
Range("E1").Resize(UBound(Tablo, 1), UBound(aa, 2)) = Application.Transpose(Application.Transpose(Tablo))'transposition

Nous remarquons que la commande Application.Transpose est doublée : la ligne de code Range("E1").Resize(UBound(Tablo, 1), UBound(aa, 2)) = Tablo ne fonctionne pas.

Voici comment enregistrer certaines lignes (toujours les lignes paires) de la variable aa et transposer (inverser le sens lignes/colonnes) leur écriture :

c = 0
For a = LBound(aa) To UBound(aa)
    If a Mod 2 = 0 Then
        c = c + 1
        ReDim Preserve Tablo(c)
        Tablo(c) = Application.Index(aa, a)
    End If
Next a
Range("E1").Resize(UBound(aa, 2), UBound(Tablo, 1)) = Application.Transpose(Tablo)

Encore une fois, il nous suffit d’inverser les commandes de redimensionnement et d’appliquer la transposition.

Juste pour information, voici une alternative simple qui procède à l’inscription des colonnes voulues sur la feuille Excel sans les enregistrer dans la variable tablo au préalable.

c = 0
For a = LBound(aa, 2) To UBound(aa, 2)
    If a Mod 2 = 1 Then
        c = c + 1
        Cells(1, 4 + c).Resize(UBound(aa), 1) = Application.Index(aa, , a)
    End If
Next a

Voici le même résultat, en employant le tablo :

c = 0
For a = LBound(aa, 2) To UBound(aa, 2)
    If a Mod 2 = 1 Then
        c = c + 1
        ReDim Preserve Tablo(c)
        Tablo(c) = Application.Transpose(Application.Index(aa, , a))
    End If
Next a
Range("E1").Resize(UBound(aa, 1), UBound(Tablo, 1)) = Application.Transpose(Tablo)

Ici, nous usons d’un artifice pour pouvoir obtenir le résultat voulu : lorsque nous enregistrons nos colonnes dans le tablo, nous les enregistrons en tant que lignes, c’est pour cela que nous utilisons la commande de transposition une première fois dans la ligne de code Tablo(c) = Application.Transpose(Application.Index(aa, , a)) puis nous retransposons lors de l’inscription dans la feuille Excel les informations du tablo.

Enfin, pour faire une vraie transposition de certaines des colonnes retranscrites en lignes, il nous faut encore jongler avec la commande de transposition :

c = 0
For a = LBound(aa, 2) To UBound(aa, 2)
    If a Mod 2 = 1 Then
        c = c + 1
        ReDim Preserve Tablo(c)
        Tablo(c) = Application.Transpose(Application.Index(aa, , a))
    End If
Next a
Range("E1").Resize(UBound(Tablo, 1), UBound(aa, 1)) = Application.Transpose(Application.Transpose(Tablo))

Il nous faut faire une première transposition lors de l’enregistrement de la colonne dans le tablo, puis une double transposition lors de l’inscription sur la feuille Excel.

Nous venons de faire un tour assez complet des possiblités de retranscription des données d’une variable sur la feuille Excel.

Cette série d’articles est maintenant complète… certains concepts peuvent paraître un peu abstraits, au premier abord, mais avec un peu de pratique, les outils décrits dans ces 3 billets vous permettront de gérer rapidement et efficacement de grandes quantités de données à traiter.

Ci-dessous le fichier au format .xls contenant les exemples de cet article.
variables-tablo-3