Pause

J’ai tenu à partager mes connaissances du VBA lorsque j’ai constaté que certaines techniques qui permettaient d’optimiser l’écriture d’un code étaient généralement peu voire pas utilisées.

J’estime avoir atteint mon objectif en vous présentant, entre autres, les principes des variables Tablo et du Dictionnaire… qui m’ont incité à commencer ce blog.

Souhaitant, aujourd’hui, me consacrer à l’apprentissage d’autres langages, je mets, au moins momentanément, un terme à ce blog et vous souhaite beaucoup de réussite en VBA.

[VBA] Etablir une condition en fonction du formatage d’une MFC

Il est fréquent de vouloir conditionner une action aux résultats d’une Mise en Forme Conditionnelle (MFC).
Or une MFC ne formate pas directement une cellule.
Pour vous en convaincre :

sélectionnez la cellule A1
saisissez le chiffre 5

puis, toujours en gardant le curseur sur la cellule A1

(pour Excel 2007) Accueil
Mise en forme conditionnelle
Règles de mise en surbrillance des cellules
Egal à

Une boite de dialogue apparait avec le chiffre 5 dans l’espace de gauche et « Remplissage rouge clair avec texte rouge foncé » dans l’espace de droite. Cliquez sur OK.

Notre cellule A1 devient rouge.
Appliquons à cette page la macro suivante :

Sub couleur()
Dim maCouleur As Long
maCouleur = Range("A1").Interior.Color
Range("B1").Interior.Color = maCouleur
End Sub

Nous enregistrons dans la variable maCouleur le N° de la couleur de fond de la cellule A1 maCouleur = Range("A1").Interior.Color puis allons demander à la macro d’appliquer la même couleur de fond à la cellule B1 Range("B1").Interior.Color = maCouleur. La cellule B1 reste blanche et si vous avez conservé le quadrillage de la feuille, celui-ci a disparu : ceci est caractéristique du coloriage des cellules.

Explications

Nous en déduisons qu’une MFC n’applique ses formats directement sur la cellule mais plutôt sur un « calque » (un peu à la façon de photoshop).

Dans l’exemple suivant, nous avons formaté la colonne A avec une MFC.

Nous récupérons les conditions de la MFC en sélectionnant la colonne A puis

Accueil
Mise en forme conditionnelle
Gérer les règles

MFC1

La boîte de dialogue nous apprend que pour la plage de cellules A1 à A20, le fond de toutes les cellules dont la valeur est inférieure à 5 est colorié en rouge.

MFC2

Nous allons créer une macro qui affichera un « x » à la droite de chaque cellule rouge de notre plage :

Sub MCF()
Dim Cel As Range
MsgBox "A partir de la couleur visible."
For Each Cel In Range("A1:A20")
    If Cel.Interior.Color = vbRed Then a.Offset(, 1) = "x"
Next Cel
End Sub

Pour chaque cellule de la plage A1-A20 For Each Cel In Range("A1:A20"), si la couleur de fond de la cellule est rouge If Cel.Interior.Color = vbRed, alors la cellule immédiatement à droite affiche « x »  Then a.Offset(, 1) = "x".
Comme nous pouvions nous y attendre, il ne se passe rien puisque la macro ne trouve pas de couleur dans la cellule.
Nous allons donc ajouter le code suivant à la suite du précédent :

MsgBox "A partir de la condition de la MFC."
For Each Cel In Range("A1:A20")
    If Cel < 5 Then Cel.Offset(, 1) = "x"
Next Cel

Il ne s’agit donc plus de vérifier la couleur de notre cellule mais de reprendre la condition de la MFC, c’est à dire vérifier si la valeur de la cellule est inférieure à 5 If Cel < 5 Then; et là, notre code fonctionne.

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

[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

[VBA] Lister les fichiers et sous-dossiers d’un dossier

Dans cet article, nous allons voir les outils à notre disposition pour répertorier le contenu d’un dossier.
Nous distinguerons ici deux cas de figure : les fichiers de ce dossier et ses sous-dossiers.

Fichiers

Sub Fichiers()
Application.ScreenUpdating = False
Dim myPath As String, myFile As String

myPath = ThisWorkbook.Path
myFile = Dir(myPath & "\*.xls*")

c = 1
Do While myFile <> ""
    Cells(c, 1) = myFile
    myFile = Dir()
    c = c + 1
Loop

End Sub

Explications

Après avoir sauvegardé le classeur sur le disque dur (de préférence dans un dossier qui contient des fichiers et des sous-dossiers) et lui avoir affecté la macro ci-dessus, nous allons passer à l’étude détaillée de sa construction.

Une fois les variables déclarées, la ligne myPath = ThisWorkbook.Path enregistre dans la variable myPath, le chemin d’accès(.Path) au dossier contenant ce classeur (ThisWorkbook). Nous avons récupéré un chemin d’accès relatif, c’est à dire qui changera en fonction de l’emplacement du fichier dans tel ou tel dossier, par opposition à un chemin d’accès absolu comme myPath = "C:\Utilisateurs\moi" qui ne changera pas, quel que soit l’emplacement de notre classeur.

La variable myFile récupère, le nom du fichier. On reconnait ici la variable myPath qui contient le chemin d’accès auquel nous ajoutons les caractères suivants & "\*.xls*" : & nous permet de concaténer le chemin d’accès au reste des commandes à sa droite afin de définir le chemin d’accès et le nom du fichier.

Entre les guillemets, nous retrouvons les indiquations suivantes : le séparateur \ nous informe du prochain niveau du chemin d’accès; l’extention .xls* indique à la macro de ne considérer que les fichiers Excel; enfin le joker * signifie que * peut représenter un caractère quelconque, une chaîne de caractères quelconques, ou aucun caractère.
Ainsi, notre extension .xls* peut prendre les valeurs xls, xlsx, xlsm, xlsb et le joker * qui précède l’extension de fichier peut avoir n’importe quel nom.

Enfin, la commande Dir(), qui encadre notre expression, dit à la macro de considérer les informations qu’elle contient non pas comme une quelconque chaîne de caractères mais comme un fichier.

Nous entamons notre boucle qui va générer la liste des fichiers Excel dans la première colonne de l’onglet actif.
Do While myFile <> "" ordonne à la macro de boucler (Do While) tant que la valeur (nom du fichier) de la variable myFile n’est pas nulle (<> «  » littéralement : ne vaut pas rien) dans le dossier actif.

La ligne Cells(c, 1) = myFile se charge d’inscrire le nom du fichier dans la feuille Excel.

La commande Dir() n’accepte qu’une seule valeur à la fois; la ligne  myFile = Dir() enregistre dans myFile le nom du fichier .xls* suivant.

Notons que si l’on veut la liste de tous les fichiers, quel que soit leur type, il suffit juste d’écrire myFile = Dir(myPath & "\*")

Cas pratique

Sub Verif()

Application.ScreenUpdating = False
Dim wB As Workbook
Dim myPath As String, myFile As String

myPath = ThisWorkbook.Path & "\"
aa = "Exemple.xls"
myFile = Dir(myPath & aa)

If myFile = "" Then
    Workbooks.Add
    Set wB = ActiveWorkbook
    wB.SaveAs aa
    wB.Close
Else
    MsgBox "Le fichier " & aa & " existe."
End If

End Sub

L’exemple ci-dessus détermine si le fichier d’un dossier existe et le crée s’il ne le trouve pas.
Après aovir défini le chemin d’accès au dossier myPath = ThisWorkbook.Path & "\", enregistré le nom du fichier dans la variable aa aa = "Exemple.xls" et specifié dans la variable myFile que la chaine de caractères (chemin d’accès + nom du fichier) devait être considérée comme représentant un fichier myFile = Dir(myPath & aa); nous définissons une condition qui va déterminer si le fichier Exemple.xls existe dans le dossier auquel appartient le classeur qui contient la macro If myFile = "" Then.
La ligne If myFile = "" Then nous indique que si le fichier n’existe pas, alors on crée un nouveau classeur Workbooks.Add, on l’enregistre dans la variable wB Set wB = ActiveWorkbook, sauvegarde le nouveau classeur du nom enregistré dans aa wB.SaveAs aa et on ferme le classeur wB.Close.
Si le fichier existe Else, un message nous prévient de son existence MsgBox "Le fichier " & aa & " existe".

Dossiers méthode 1

Sub Dossiers()

Application.ScreenUpdating = False
Dim myPath As String, myFolder As String

myPath = ThisWorkbook.Path
myFolder = Dir(myPath & "\*", vbDirectory)

c = 1
Do While myFolder <> ""

    If GetAttr(myPath & "\" & myFolder) = vbDirectory Then
        Cells(c, 2) = myFolder
        c = c + 1
    End If
    myFolder = Dir()

Loop
End Sub

La méthode pour faire la liste des sous-dossiers d’un dossier est très similaire à ce que l’on vient de voir avec les fichiers, à deux détails près.
La première différence consiste à inclure les sous-dossiers dans la liste qui va être générée; il suffit d’ajouter la commande vbDirectory lorsque nous spécifions notre fichier myFolder = Dir(myPath & "\*", vbDirectory).
La seconde insère une condition qui va différencier les fichiers des sous dossiers; nous allons utiliser la commande GetAttr qui nous permet de déterminer les caractéristiques d’un fichier ou d’un dossier If GetAttr(myPath & "\" & myFolder) = vbDirectory Then. Ainsi, la macro n’inscrit que les noms de fichiers.

Dossiers méthode 2

Sub Dossiers2()
'Dans la fenêtre VBA, sélectionner Outils, Références et cocher Microsoft Scripting Runtime
Application.ScreenUpdating = False
Dim myPath As String, mainFolder As Folder
Dim aa As Object

Set aa = CreateObject("scripting.filesystemobject")
myPath = ThisWorkbook.Path

Set mainFolder = aa.GetFolder(myPath)

c = 1
For Each f In mainFolder.SubFolders
    Cells(c, 3) = f
    c = c + 1
Next f

End Sub

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, nommée aa, qui correspond à cet objet Dim aa As Object; nous créons, ensuite, l’objet aa Set aa = CreateObject("scripting.filesystemobject").
Après avoir précisé le chemin d’accès à notre dossier que nous enregistrons dans la variable myPath, nous utilisons la commande Set mainFolder = aa.GetFolder(myPath) pour identifier le dossier contenant notre classeur et l’enregistrer dans la variable mainFolder.
Enfin, la boucle For Each f In mainFolder.SubFolders va lister l’ensemble des sous-dossiers (avec leur chemin d’accès) du dossier contenant notre fichier.

Ce dernier code va nous permettre de voir comment naviguer de dossier en dossier en modifiant le code précédent.

Sub Dossiers3()
'Dans la fenêtre VBA, sélectionner Outils, Références et cocher Microsoft Scripting Runtime
Application.ScreenUpdating = False
Dim myPath As String, myFolder As String, mainFolder As Folder
Dim aa As Object

Set aa = CreateObject("scripting.filesystemobject")
myPath = ThisWorkbook.Path
ChDir (myPath)
ChDir ("..")
myFolder = CurDir(myPath)

Set mainFolder = aa.GetFolder(myFolder)

c = 1
For Each f In mainFolder.SubFolders
    Cells(c, 4) = f
    c = c + 1
Next f

Columns("D").AutoFit
End Sub

Voici la partie du code qui a été ajoutée/modifiée :

ChDir (myPath)
ChDir ("..")
myFolder = CurDir(myPath)
Set mainFolder = aa.GetFolder(myFolder)

la commande ChDir change le répertoire ou le dossier courant par défaut.
Le répertoire courant est normalement le lecteur C; la commande ChDir (myPath) ordonne à la macro de considérer le répertoire contenant notre fichier comme étant le dossier par défaut.
ChDir ("..") signifie à la macro de considérer le dossier « parent » au dossier actuel par défaut, c’est à dire le dossier le contenant, comme étant le nouveau dossier de référence.
La ligne suivante myFolder = CurDir(myPath) nous permet, grâce à la commande CurDir, d’enregistrer notre nouveau chemin d’accès dans la variable myFolder.
C’est, enfin, cette fois-ci, la variable, non pas myPath, comme dans l’exemple précédent, mais myFolder, qui nous permet, ici, dans la ligne suivante, Set mainFolder = aa.GetFolder(myFolder), d’enregistrer le chemin d’accès du dossier parent de celui dans lequel se trouve notre fichier.
La boucle effectue l’affichage des sous-dossiers appartenant au dossier parent du dossier dans lequel se trouve notre fichier.

Juste pour info., voici un petit complément avec le code pour établir la liste des sous-dossiers du dossier parent selon la première méthode :

Sub Dossiers4()
Dim myPath As String, myFolder As String

myPath = ThisWorkbook.Path
ChDir (myPath)
ChDir ("..")
myPath = CurDir(myPath)

myFolder = Dir(myPath & "\*", vbDirectory)

c = 1
Do While myFolder <> ""
    If GetAttr(myPath & "\" & myFolder) = vbDirectory Then
        Cells(c, 5) = myFolder
        c = c + 1
    End If
    myFolder = Dir()
Loop
End Sub

A noter que ce programme semble avoir du mal à reconnaitre certains dossiers Windows mais fonctionne parfaitement avec les dossiers créés par l’utilisateur.

Ci-dessous le fichier au format .xls contenant les exemples de cet article.
Listings fichiers classeurs

[VBA] Sélectionner un tableau sans son en-tête

Voici une petite ligne de code astucieuse qui vous permet de sélectionner la plage entière d’un tableau sur la feuille Excel, hormis son en-tête, ce qui peut être pratique pour intégrer l’ensemble des valeurs du tableau dans une variable, par exemple.

Pour un tableau composé de plusieurs lignes et de plusieurs colonnes et dont l’en-tête commence dans la cellule A1 :

Range("A1").CurrentRegion.Offset(1).Resize(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count).Select

Explications

Nous connaissons maintenant bien Range("A1").CurrentRegion qui sélectionne toutes les cellules faisant partie de la plage de la cellule A1.
La commande Offset(1) représente un décalage vers le bas d’une ligne. Voir l’article suivant pour plus d’infos sur le fonctionnement de la commande Offset.
Nous savons aussi que la commande Resize redimensionne une cellule ou une plage de cellules et qu’elle est suivie du nombre de lignes et du nombre de colonnes permettant de redimensionner notre cellule de référence A1.
Tout logiquement, Range("A1").CurrentRegion.Rows.Count - 1 représente le nombre de lignes de notre plage.
Rows.Count repprésente le nombre de lignes de notre plage Range("A1").CurrentRegion; nous déduisons 1 car nous avons décalé notre plage de 1 vers le bas.

Il en va exactement de même pour le nombre de colonnes Range("A1").CurrentRegion.Columns.Count.
Une petite ligne qui peut s’avérer pratique, à adapter en fonction de vos besoins.

[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

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

Les variables à deux dimensions

Excel possède une fonctionnalité très astucieuse qui permet d’enregistrer simplement et rapidement une plage de données.
Supposons que nous souhaitions enregistrer la plage ci-dessous dans une variable que l’on nommera aa.

Plage VT

il nous suffit juste de taper la commande suivante : aa = Range("A1").CurrentRegion

Voilà, le tour est joué… pour vous en convaincre, nous allons recopier le contenu de la variable aa un peu plus loin dans la feuille à partir de la cellule J1.

Sub PlageVar()

Dim aa As Variant
aa = Range("A1").CurrentRegion
Range("J1").Resize(UBound(aa, 1), UBound(aa, 2)) = aa

End Sub

Les 2 dimensions dont il est question correspondent aux lignes (1ere dimension) et aux colonnes (2eme dimension) de ce tableau enregistrées dans notre variable aa.
Nous avions vu, dans notre article précédent la commande Ubound().
Ajoutons la ligne suivante à notre macro :
MsgBox "Le nombre de lignes enregistré dans notre variable est de : " & UBound(aa, 1) & " et le nombre de colonnes est de : " & UBound(aa, 2)

Le sens de la commande Range("J1").Resize(UBound(aa, 1), UBound(aa, 2)) devient alors limpide : on redimensionne la cellule J1 pour avoir une plage de 20 lignes (UBound(aa, 1)) et 3 colonnes (UBound(aa, 2)).
Une fois la plage définie sur notre feuille Excel, un simple = aa nous permet de faire la copie.

Ajoutons également cette ligne juste après la précédente :
MsgBox "La cellule en 4eme ligne, 3eme colonne a la valeur " & aa(4, 3)

Cet exemple montre que, finalement, l’extraction des données d’une variable est très similaire à celui d’une cellule, il nous suffit de faire suivre le nom de notre valriable de son N° de ligne et de son N° de colonne, rien de plus simple.
Ci-dessous, le code complet :

Option Base 1
Sub PlageVar()

Sheets("PlageVar").Select

Dim aa As Variant
aa = Range("A1").CurrentRegion

Range("J1").Resize(UBound(aa, 1), UBound(aa, 2)) = aa

MsgBox "Le nombre de lignes enregistré dans notre variable est de : " & UBound(aa, 1) & " et le nombre de colonnes est de : " & UBound(aa, 2)
MsgBox "La cellule en 4eme ligne, 3eme colonne a la valeur " & aa(4, 3)

End Sub

Prenons le cas pratique suivant (le fichier Excel contenant tous les exemples de cet article se trouve à la fin de celui-ci) :

Employe mois

Supposons que nous souhaitions extraire tous les employés de la région Europe de ce tableau, voici le code :

Option Base 1
Sub Exp()

Dim aa As Variant, Tablo() As Variant
Dim i As Byte, j as Byte, c As Byte

Application.ScreenUpdating = False
Sheets("Exp").Select
Range("A20").CurrentRegion.ClearContents

aa = Range("A1").CurrentRegion

For i = LBound(aa, 1) To UBound(aa, 1)
    If aa(i, 2) = "Europe" Then
        c = c + 1
        ReDim Preserve Tablo(14, c)
        Tablo(1, c) = aa(i, 1)
        Tablo(2, c) = aa(i, 2)
        Tablo(3, c) = aa(i, 3)
        Tablo(4, c) = aa(i, 4)
        Tablo(5, c) = aa(i, 5)
        Tablo(6, c) = aa(i, 6)
        Tablo(7, c) = aa(i, 7)
        Tablo(8, c) = aa(i, 8)
        Tablo(9, c) = aa(i, 9)
        Tablo(10, c) = aa(i, 10)
        Tablo(11, c) = aa(i, 11)
        Tablo(12, c) = aa(i, 12)
        Tablo(13, c) = aa(i, 13)
        Tablo(14, c) = aa(i, 14)
    End If
Next i
    Range("A20").Resize(UBound(Tablo, 2), UBound(Tablo, 1)) = Application.Transpose(Tablo)

End Sub

Première étape : Nous enregistrons le tableau dans notre variable aa aa = Range("A1").CurrentRegion.
Ensuite, la boucle vérifie, pour chaque employé, la zone à laquelle il appartient For i = LBound(aa, 1) To UBound(aa, 1).
La commande Ubound() nous est maintenant familière, il s’agit du nombre total d’entrées, ici, dans notre première dimension.
La commande Lbound() est nouvelle; elle identifie le N° de la première entrée d’une variable… utile lorsqu’on ne veut pas se préoccuper de savoir si la première entrée a un index de 0 ou de 1… ici, on l’occurence, le problème ne se pose pas parce que nous avons spécifié Option Base 1 en début de code.

Nous définissons ensuite la condition : If aa(i, 2) = "Europe" Then exactement comme nous le ferions pour analyser le contenu de la cellule; nous l’avons vu précédemment il nous suffit simplement de changer la commande Cells et la remplacer par le nom de notre variable pour que la macro travaille au niveau de la mémoire plutôt que des cellules.

Si la condition est vérifiée, nous alimentons une variable tablo qui va enregistrer toutes les informations relatives aux lignes dont la colonne C = »Europe ». Puisque nous ne connaissons pas à l’avance le nombre d’employés de la zone Europe, nous allons bâtir notre tablo au fur et à mesure que le programme rencontrera la valeur Europe; nous allons donc utiliser la commande Redim Preserve, pour redimensionner notre variable tablo.

Important
Dans une variable à 2 dimensions, nous ne pouvons redimensionner qu’une seule des deux dimensions. C’est toujours celle qui est déclarée en 2eme position !
Jusqu’ici, la première dimension de notre variable tablo correspondait au nombre de lignes de notre tableau et la seconde, au nombre de colonnes.

Dans notre cas de figure, le nombre de colonnes restera toujours le même, à savoir 14.
Le nombre de lignes est, lui, inconnu et peut évoluer. La variable c sera incrémentée au fur et à mesure que la condition sera vérifiée et nous servira à redimensionner notre tablo c = c + 1. La commande Redim Preserve se rédige donc ainsi : ReDim Preserve Tablo(14, c)

Nous devons maintenant enregistrer chaque valeur des lignes correspondant au critère « Europe » dans la variable tablo.
C’est ce que font les 14 lignes de code suivantes : Tablo(1, c) = aa(i, 1)
La première valeur de la « ligne » c de notre variable tablo = la 1ere valeur de la « ligne » i de la variable aa (qui correspond à la cellule (i, 1)). et ainsi de suite.

Une fois la boucle terminée, nous allons donc inscrire le résultat de notre recherche.
Nous avons vu, au début de cet article qu’il suffisait de redimensionner une plage à partir d’une cellule et d’y affecter le contenu de notre variable. c’est exactement ce que fait la commande suivante : Range("A20").Resize(UBound(Tablo, 2), UBound(Tablo, 1)) = Application.Transpose(Tablo).

Elle redimmensionne la cellule A20 du nombre total de lignes Resize(UBound(Tablo, 2)(qui est maintenant la 2eme dimension, puisque nous avons inversé le sens de nos dimensions à cause de la contrainte imposée par notre variable tablo de ne pouvoir redimensionner qu’une seule des deux dimensions) et du nombre total de colonnes UBound(Tablo, 1) contenus dans notre tablo.

Puisque nous avons inversé le sens lignes/colonnes lors de l’enregistrement, il va nous falloir récupérer le sens originel de nos dimensions; c’est ce que se charge de faire la commande Application.Transpose(tablo).
Mais nous reviendrons plus en détail sur la copie du contenu des variables sur la feuille Excel dans le 3eme et dernier article dédié aux variables Tableau.

Ce petit exemple nous a permis de mieux comprendre le fonctionnement des variables Tableau, cependant on peut améliorer et simplifier son code.

La première modification va porter sur cette ligne de code : ReDim Preserve Tablo(14, c)
Le nombre de colonnes, 14, est, ici, fixe… mais peut-être qu’à l’avenir, ce document sera amené à évoluer et le nombre de colonnes pourrait changer, donc autant mettre cette valeur sous forme de variable, ainsi, nous n’aurons pas à modifier la macro en cas de changement du tableau. Nous savons maintenant définir le nombre de colonnes de notre variable aa en utilisant la commande Ubound(aa,2); il nous suffit juste de remplacer 14 par cette commande : ReDim Preserve tablo(UBound(aa, 2), c).

L’autre changement consiste à améliorer l’enregistrement de toutes les valeurs d’une ligne dans notre tablo.
Nous avons écrit 14 lignes de commande pour enregistrer les valeurs des 14 colonnes du tableau.
Nous allons donc modifier le code pour contourner cette manipulation assez fastidieuse et introduire une boucle qui enregistrera automatiquement toutes les données relatives à une même ligne dans le tablo.

For j = LBound(aa, 2) To UBound(aa, 2)
    tablo(j, c) = aa(i, j)
Next j

For j = LBound(aa, 2) To UBound(aa, 2) nous dit de boucler entre les N° de la première et la dernière colonne contenues dans la variable aa.

Puis nous remplaçons les N° de colonne par la variable j : tablo(j, c) = aa(i, j), qui, tour à tour, prend le N° de chacune des 14 colonnes de notre exemple et remplace les 14 lignes de code.

Notre nouveau code ressemble à ça :

Option Base 1
Sub Exp()

Dim aa As Variant, tablo() As Variant
Dim i As Byte, j As Byte, c As Byte

Application.ScreenUpdating = False
Sheets("Exp").Select
Range("A20").CurrentRegion.ClearContents

aa = Range("A1").CurrentRegion

For i = LBound(aa, 1) To UBound(aa, 1)
    If aa(i, 2) = "Europe" Then
        c = c + 1
        ReDim Preserve tablo(UBound(aa, 2), c)
        For j = LBound(aa, 2) To UBound(aa, 2)
            tablo(j, c) = aa(i, j)
        Next j

    End If
Next i
    Range("A20").Resize(UBound(tablo, 2), UBound(tablo, 1)) = Application.Transpose(tablo)

End Sub

Rédigé ainsi, le code est plus digeste à lire mais il persiste un inconvénient : pour chaque ligne enregistrée, la commande For i = LBound(aa, 1) To UBound(aa, 1) boucle 14 fois, ce qui, dans l’absolu, peut prendre énormément de temps si le nombre de lignes et de colonnes devient important.

Nous allons donc faire en sorte qu’à chaque fois qu’une ligne doit être enregistrée dans le tablo, elle le soit dans son intégralité.

C’est la commande Index que nous avons déjà étudiée dans nos articles précédents qui va nous permettre de réaliser cette manipulation. Pour rappel, l’index est le N° d’un objet dans une collection d’objets de même nature. Ici, il va s’agir de déterminer une ligne de la variable aa parmi toutes les autres lignes de cette même variable et l’enregistrer dans le tablo.

Nous avons vu, au début de cet article, qu’une variable pouvait contenir une infinité d’informations lorsque nous avons écrit la commande aa = Range("A1").CurrentRegion.
Nous allons procéder exactement de la même façon pour l’enregistrement d’une ligne de aa dans tablo : nous allons donc redéfinir notre variable tablo comme n’ayant qu’une seule dimension et non plus 2, il nous suffit, pour cela de modifier la commande Redim Preserve comme ceci : ReDim Preserve tablo(c).
Nous n’avons plus qu’à enregistrer la ligne entière avec ce code : tablo(c) = Application.Index(aa, i) qui signifie concrêtement : dans la « case » c de la variable tablo, enregistrer la « ligne entière » i de la variable aa.

Il nous reste à modifier la ligne qui permet d’inscrire les données du tablo dans la feuille Excel :
Range("A20").Resize(UBound(tablo), UBound(aa, 2)) = Application.Transpose(Application.Transpose(tablo))

A noter qu’on peut identifier une valeur parmi les 14 de chaque ligne; toujours en utilisant la fonction Index :
Supposons que l’on veuille récupérer la 6eme valeur de la 5eme ligne de notre tablo : MsgBox Application.Index(tablo(5), 6)

Et voici le code complet pour notre dernier exemple :

Option Base 1
Sub Exp()
Dim aa As Variant, tablo() As Variant
Dim i As Byte, j As Byte, c As Byte

Application.ScreenUpdating = False

Sheets("Exp").Select
Range("A20").CurrentRegion.ClearContents

aa = Range("A1").CurrentRegion

For i = LBound(aa, 1) To UBound(aa, 1)
    If aa(i, 2) = "Europe" Then
        c = c + 1
        ReDim Preserve tablo(c) 'cas N° 3
        tablo(c) = Application.Index(aa, i)
    End If
Next i

Range("A20").Resize(UBound(tablo), UBound(aa, 2)) = Application.Transpose(Application.Transpose(tablo))
MsgBox Application.Index(tablo(5), 6)

End Sub

Nous avons fait le tour de ce qu’il faut savoir sur les variables Tableau, dans le dernier volet de cette série d’articles, nous examinerons plus en détail les outils à notre disposition pour retranscrire les valeurs d’une variable sur la feuille Excel.

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