Transformer un tableau en une seule colonne en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Transformer un tableau en une liste

C'est à l'occasion de cette nouvelle astuce que nous allons apprendre à créer une fonction VBA Excel capable de transformer un tableau en une liste de données. En d'autres termes, il est question de placer les valeurs à plat, les unes à la suite des autres, dans une seule ligne ou dans une seule colonne.



Sur l'exemple illustré par la capture, l'utilisateur travaille à partir d'un tableau fait de quatre colonnes et de dix lignes. Sur la droite, il appelle une nouvelle fonction VBA que nous nommerons tabEnListe. En premier argument, il lui transmet la plage de cellules du tableau à transformer. En second il lui fournit une indication pour la liste à restituer, V pour verticale et H pour horizontale. A validation, toutes les informations du tableau d'origine sont automatiquement réorganisées les unes en dessous des autres, donc à la verticale lorsque le second paramètre fourni est la lettre V.

Classeur Excel à télécharger
Pour créer cette nouvelle fonction, nous suggérons d'appuyer l'étude sur ce tableau des équipes à transformer.

• Télécharger le fichier tableau-en-liste-de-donnees.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• En bas du menu contextuel, choisir la commande Propriétés,
• Dans la boîte de dialogue, cocher la case intitulée Débloquer et valider par Ok,
• Puis, double cliquer sur le fichier pour l'ouvrir dans Excel,

Nous découvrons le tableau des équipes entre les colonnes B et F et entre les lignes 3 et 13. Le traitement de la fonction VBA Excel à construire doit débuter à partir de la cellule I4, pour réorganiser les données du tableau, soit les unes en dessous des autres, soit les unes à côté des autres.

La fonction VBA et ses arguments
La fonction que nous devons construire a besoin de deux renseignements. Il s'agit premièrement de la plage de cellules représentant le tableau à réorganiser sous forme de liste continue. Il s'agit ensuite d'une indication textuelle définissant si les données doivent être disposées à l'horizontale ou à la verticale. Cette fonction VBA doit donc être signée avec deux paramètres en attente.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Module1,
• Dans la feuille de code au centre, créer la fonction tabEnListe, comme suit :

Function tabEnListe(leTab As Range, mode As String) As Variant

End Function

Fort naturellement, nous typons le premier paramètre comme une plage de cellules (As Range) et le second comme un texte (As String). La fonction quant à elle prendra son type véritable au moment de l'affectation des données, pour retourner une plage continue de valeurs. C'est la raison pour laquelle nous la typons comme un Variant.

Les variables
Malgré ces paramètres, nous avons besoin de quelques variables supplémentaires. L'une d'entre elles est majeure. Elle est destinée à être transformée en tableau de variables pour récolter les données les unes après les autres et ainsi les restituer à plat par le biais de la fonction.

• Dans les bornes de la fonction, ajouter les déclarations et affectations suivantes :

...
Dim liste As Variant
Dim cellule As Range: Dim i As Integer

ReDim liste(1 To leTab.Cells.Count)
i = 1
...

Nous typons l'objet liste comme un Variant. C'est implicitement qu'il prend ensuite sont type grâce à la fonction Redim qui le transforme en un tableau de variables d'une ligne et d'autant de colonnes qu'il y a de cellules (Cells.Count) dans la plage (leTab) passée en premier paramètre de la fonction. Nous exploiterons l'objet cellule pour précisément parcourir toutes les cellules de cette plage. Le compteur i sera utilisé pour suivre le processus et stocker les données récoltées dans une rangée suivante du tableau de variables.

Parcourir les cellules du tableau
Justement pour récolter toutes ces données dans l'ordre, nous devons parcourir chaque cellule du tableau passé en premier paramètre de la fonction. Pour cela, rien de tel qu'une bonne vieille boucle For Each.

• A la suite du code VBA, créer la boucle For Each suivante :

...
For Each cellule In leTab

Next cellule
...

Nous engageons donc notre objet cellule dans une boucle pour parcourir la collection des cellules détenues par le tableau passé en paramètre de la fonction.

Récolter les données du tableau
Nous devons maintenant exploiter cette boucle pour récolter chaque valeur que le tableau contient et les stocker dans le tableau linéaire de variables.

• Dans les bornes de la boucle, ajouter les instructions VBA suivantes :

...
liste(i) = cellule.Value
i = i + 1
...

A chaque passage dans cette boucle, nous prélevons la valeur de la cellule en cours d'analyse (cellule.Value) pour la stocker dans le tableau de variables (liste(i)), sur la rangée suivante (i = i + 1). C'est ainsi que nous les replaçons toutes les unes à la suite des autres.

Restituer la liste à plat
Puisque les données sont désormais enfilées à la queue leuleu dans le tableau de variables, c'est ainsi que nous devons les restituer par le biais de la fonction. Mais il reste à savoir si elles doivent être disposées à l'horizontale ou à la verticale. Tout dépend de la lettre passée par l'utilisateur (H ou V) en second paramètre de cette fonction. Donc un test s'impose.

• Après la boucle, créer l'instruction conditionnelle suivante :

...
Next cellule

If mode = "v" Then tabEnListe = WorksheetFunction.Transpose(liste) Else tabEnListe = liste

End Function
...

Si la lettre v est passée, nous exploitons la fonction de feuille de calcul Tranpose pour renverser les données et les présenter à la verticale. Dans le cas contraire, nous les laissons telles quelles, soit les unes à côté des autres.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Cliquer sur la cellule I4 pour la sélectionner,
• Taper le symbole égal (=) pour amorcer la construction de la formule,
• Inscrire le nom de la nouvelle fonction suivi d'une parenthèse, soit : tabEnListe(,
• Désigner le tableau à transformer par son nom (membres) ou par sa plage de cellules,
• Taper un point-virgule (;) pour passer dans l'argument de l'orientation,
• Taper la lettre v entre guillemets ("v") pour un agencement dans une seule colonne,
• Fermer la parenthèse de la fonction tabEnListe,
• Puis, valider la formule par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, toutes les informations du tableau d'origine sont désormais organisées les unes en dessous des autres, dans une seule colonne donc. Et si vous veniez à modifier le paramètre v, pour le remplacer par la lettre h, vous obtiendriez le même résultat mais avec des informations réorganisées les unes à côté des autres, soit sur une même ligne.

Parcourir les cellules par colonnes
Néanmoins et cela ne vous a sans doute pas échappé, l'ordre alphabétique d'origine des informations a été perdu.



Cela tient au fait qu'une boucle For Each parcourt chaque ligne d'un tableau les unes après les autres et pour chacune, toutes les cellules dans l'ordre. Ici, pour conserver l'ordre alphabétique du tableau d'origine, nous avons besoin de parcourir toutes les cellules, colonne à colonne.

Pour cela, nous devons utiliser un tableau de variables représentant la plage de cellules à analyser. Grâce à lui nous pourrons étudier toutes ses colonnes et pour chacune, parcourir toutes ses lignes. La variable cellule doit disparaître.

• Adapter le précédent code VBA, selon les instructions en gras, comme suit :

Function tabEnListe(leTab As Range, mode As String) As Variant
Dim liste As Variant: Dim tableau As Variant
Dim i As Integer: Dim j As Byte: Dim k As Integer

ReDim liste(1 To leTab.Cells.Count)
tableau = leTab: k = 1

For j = 1 To UBound(tableau, 2)
For i = 1 To UBound(tableau)
liste(k) = leTab(i, j)
k = k + 1
Next i
Next j

If mode = "v" Then tabEnListe = WorksheetFunction.Transpose(liste) Else tabEnListe = liste

End Function

Maintenant, si vous revenez sur la feuille, que vous sélectionnez la cellule I4, que vous enfoncez la touche F2 pour activer la modification de la formule et que vous la validez de nouveau par la touche Entrée du clavier, vous constatez que les données sont toujours réorganisées sous forme de liste continue, dans une seule colonne. Mais de plus, l'ordre alphabétique est conservé puisque le tableau est parcouru colonne à colonne et non plus ligne à ligne.