Trier les tableaux par le code Visual Basic

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


Sujets que vous pourriez aussi aimer :


Trier les tableaux en VBA Excel

Dans cette formation, nous allons apprendre à trier les données d'un tableau Excel par le code Visual Basic. L'intérêt est de pouvoir offrir dynamiquement, des organisations de données et des vues différentes. Comme nous le verrons dans la prochaine formation, consistant à créer le jeu du pendu sous Excel, l'intérêt est tout trouvé. Par un tri changeant de la source de données, nous proposerons les termes à retrouver, de façon aléatoire. Ainsi, d'une partie à une autre, le joueur sera testé sur de nouvelles questions.



Le tableau présenté par la capture ci-dessus est d'ailleurs la source de données pour ce jeu du pendu, sur le thème des anciens dessins animés.

Générer le code par une macro automatique
Comme toujours, pour reproduire toutes les manipulations nous proposons de récupérer le classeur d'origine.

• Télécharger le classeur Excel trier-tableaux-donnees.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

Vous notez l'extension xlsm de ce classeur afin de pouvoir gérer le code Visual Basic. La formation pour débuter la programmation en VBA Excel, nous avait appris à afficher le ruban développeur et à créer des macros automatiques, pour nous inspirer du code transcrit.

Nous souhaitons trier le tableau sur l'une de ses trois colonnes. Bien entendu, les autres colonnes doivent être liées dans le tri.

• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour afficher son ruban,
• Sur la gauche, dans la section Code, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, nommer la macro : trier_tableau,
• Puis, cliquer sur le bouton Ok,

A ce stade, Excel enregistre la moindre de nos actions afin de les transcrire en code VBA. Nous devons donc réaliser les manipulations strictement nécessaires. Pour être trié, un tableau doit d'abord être sélectionné.

• Cliquer sur la première cellule du tableau, soit B2,
• Tout en maintenant la touche MAJ (Shift) enfoncée, cliquer sur la dernière cellule, soit D42,
• Puis, cliquer sur l'onglet Accueil en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Edition sur la droite du ruban, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Tri personnalisé,

Une boîte de dialogue apparaît. Comme en atteste la figure ci-dessus et la sélection recalibrée automatiquement, Excel a pris conscience de la présence d'une ligne d'entête, à ne pas intégrer dans le tri. Un tri sur la colonne dont le titre est Expression, soit la troisième colonne du tableau, est proposé. Nous allons conserver cette suggestion. Mais nous allons préférer un tri décroissant à un tri croissant.

• Dans la liste déroulante de la section Ordre, choisir De Z à A, soit décroissant,
• Valider ce choix en cliquant sur le bouton Ok,

Les données sont instantanément réorganisées selon un tri décroissant sur la colonne Expression. Comme nous le constatons, les deux autres colonnes du tableau sont liées dans cette réorganisation. En d'autres termes, la colonne expression n'a pas été triée indépendamment des colonnes Num et Définition. Le tri étant terminé, nous devons impérativement arrêter l'enregistrement de la macro. Nous pourrons ensuite consulter le code ainsi produit pour le comprendre, l'adapter et le faire évoluer.

• Cliquer sur l'onglet Développeur en haut de la fenêtre pour activer son ruban,
• Dans la section Code sur la gauche du ruban, cliquer sur le bouton Arrêter l'enregistrement,

La macro et son code Visual Basic existent désormais mais ne sont pas matérialisés. Nous allons établir la liaison à l'aide d'un bouton.

• Dans la section Contrôles du ruban Développeur, cliquer sur le bouton Insérer,
• Sélectionner le premier contrôle en haut à gauche de la liste, soit le bouton,
• Puis, tracer un rectangle sur la feuille, à droite du tableau, pour le matérialiser,
• Dans la boîte de dialogue qui surgit, sélectionner la macro trier_tableau,
• Puis, cliquer sur Ok,
• Modifier l'intitulé du bouton en : Trier le tableau,
• Cliquer dans une cellule vide pour désélectionner le bouton,

A ce stade, si nous cliquons sur le bouton que nous venons de créer, rien de spectaculaire ne se produit. Le tableau est sélectionné par le code VBA attaché au bouton, mais son tri ne change pas. Tout cela est fort logique puisque la macro a pour but de trier décroissant le tableau sur la colonne Expression. Or, les données sont déjà ainsi triées.



Modifier le code Visual Basic généré par une macro Excel
Nous devons en effet accéder au code VBA de la macro attachée au bouton. Nous pourrons ainsi le consulter et comprendre les méthodes employées afin d'essayer de l'adapter pour atteindre nos objectifs, à savoir enclencher des tris aléatoires.

• Cliquer droit sur le bouton Trier le tableau,
• Dans le menu contextuel, choisir Affecter une macro,
• Dans la boîte de dialogue qui apparaît, cliquer sur le bouton Modifier,

Nous basculons ainsi dans l'éditeur de code Visual Basic, entre les bornes de la procédure trier_tableau, soit le code de la macro attachée au bouton. Si la bascule automatique dans l'éditeur VBA ne s'opère pas, vous pouvez réaliser le raccourci clavier ALT + F11.



Les deux premières instructions retranscrivent des actions réalisées à la souris pendant l'enregistrement de la macro. Il s'agit de la sélection de la cellule B2, et d'un défilement vertical (Scroll) pour atteindre les cellules du bas. Ces deux lignes sont inutiles.

• Supprimer les deux instructions suivantes du code VBA :

Range('B2').Select
ActiveWindow.SmallScroll Down:=24

La troisième instruction en revanche est essentielle : Range('B2:D42').Select. Il s'agit de la sélection complète du tableau à trier. L'objet VBA Excel Range permet de désigner une plage de cellules passée en paramètre, soit B2:D42. Sa méthode Select comme son nom l'indique, permet de sélectionner la plage de cellules désignée par l'objet Range.

La quatrième instruction retranscrit de nouveau un défilement réalisé à la souris où à l'aide de l'ascenseur vertical. Elle n'est donc pas utile.

• Supprimer l'instruction suivante du code VBA :

ActiveWindow.SmallScrollDown:=-48

Puis vient une ligne de code particulière :

ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Clear

La méthode Clear permet de supprimer tous les objets SortFields stockés. Il s'agit des préférences de tris mémorisés. Comme nous débutons un nouveau tri, il convient en effet de les supprimer d'abord. Donc nous conservons cette instruction.

L'instruction qui suit est fondamentale :

ActiveWorkbook.Worksheets('Mots').Sort.SortFields.AddKey := Range('D3:D42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

C'est elle qui définit sur quelle colonne et dans quel ordre est effectué le tri, grâce à la méthode Add de la collection SortFields. Cette méthode propose un grand nombre d'arguments dont la colonne de tri : Key := Range('D3:D42'). La colonne D est bien la colonne des expressions. Notez l'absence de la ligne 2 exclue du tri, puisqu'il s'agit du titre de la colonne. On y trouve aussi l'indication sur l'ordre du tri : Order:=xlDescending. Il est bien décroissant comme nous l'avions paramétré.

Le bloc With qui suit est lui aussi fondamental. C'est lui qui indique, entre autres, quelles sont les colonnes liées dans le tri, grâce à la méthode SetRange :.SetRange Range('B2:D42'). Tout le tableau est ainsi concerné par la réorganisation pour laquelle le tri est défini sur la colonne D.

With ActiveWorkbook.Worksheets('Mots').Sort
.SetRange Range('B2:D42')
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Un bloc With, comme nous l'avions appris dans la formation pour débuter la programmation sous Excel, permet d'énumérer les méthodes et propriétés d'un objet VBA, afin d'optimiser les lignes de code. La propriété Header notamment (.Header = xlYes) confirme bien la présence d'entêtes à ne pas inclure dans le tri. La propriété MatchCase (.MatchCase = False) ainsi réglée, permet d'ignorer les différences de casse (Majuscules-Minuscules), dans le tri. L'emploi de SortMethod concerne les langues asiatiques. Il est inutile pour nous.

• Dans le bloc With, supprimer l'instruction suivante :

.SortMethod= xlPinYin

Enfin, la méthode Apply permet d'appliquer le tri sur le tableau, selon tous les paramètres définis en amont.

Nous allons tester les effets d'une petite modification dans le code VBA sur le résultat du tri du tableau sur la feuille Excel. L'instruction concernée est celle située avant le bloc With, permettant de définir la colonne et l'ordre de tri.

• Modifier le paramètre Key:=Range('D3:D42'), par : Key:=Range('C3:C42'),
• De même, modifier le paramètre Order:=xlDescending par : Order:=xlAscending,
• Enregistrer les modifications (CTRL + S),

Ainsi, en même temps que nous changeons la colonne de tri qui devient la colonne C des définitions, nous changeons son ordre qui devient croissant.

• Basculer sur la feuille Excel (ALT + F11),
• Cliquer dans une cellule vide pour désélectionner le bouton,
• Et enfin, cliquer sur le bouton Trier le tableau,

Les modifications imposées par le code VBA se visualisent aussitôt. Le tableau est désormais trié croissant sur la colonne C des définitions. De plus, toutes les autres colonnes restent liées dans le tri, grâce aux instructions du bloc With. En revanche, la sélection complète du tableau est conservée active à l'issue du traitement, ce qui apparaît disgracieux. Pour pallier le défaut, il suffit d'ajouter à la fin du code, une instruction permettant d'activer une cellule isolée, la cellule A1 par exemple.

• Basculer dans l'éditeur de code VBA,
• A la fin de la procédure, avant le End Sub, ajouter la ligne suivante :

Range('A1').Select

L'instruction est triviale. Comme toujours, l'objet Range permet de pointer sur la cellule dont la référence est passée en paramètre. Sa méthode Select permet d'activer sa cellule. Si vous cliquez de nouveau sur le bouton de la feuille Excel, vous remarquez que la sélection du tableau est bien désactivée à la fin du tri.

Trier les données aléatoirement
Comme nous l'avons évoqué au début de ce support, les raisons et intérêts de trier un tableau au hasard, sont multiples. Dans notre cas, il s'agit d'une préparation de l'application du jeu du pendu que nous souhaitons développer sous Excel. Nous souhaitons que les mots à trouver, soient dévoilés aléatoirement au joueur.

Nous pouvons faire varier le tri sur trois colonnes et pour chacune d'entre elles, modifier l'ordre, croissant ou décroissant. 6 possibilités de tris s'offrent à nous. Nous proposons donc de générer un nombre aléatoire entier, compris entre les chiffres 1 et 6. Ainsi, selon la valeur retournée, un tri correspondant sera enclenché. Dans la formation précédente, nous avons d'ailleurs appris à générer un nombre aléatoire entier, compris entre une valeur minimum et une valeur maximum, grâce à la fonction VBA Rnd. En s'instruisant de l'aide en ligne, nous apprenons la syntaxe suivante :

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Upperbound représente la plus grande valeur (6 dans notre cas) tandis que lowerbound représente la plus petite (1 dans notre cas). Une fois les opérations faites, nous en déduisons que le calcul suivant : nb_alea = (6 * Rnd + 1), doit retourner un nombre entier aléatoire compris entre 1 et 6 inclus. La fonction VBA Int permet de retourner la partie entière du nombre qui lui est passé en paramètre. Ce nombre aléatoire doit être manipulé pour enclencher les tris. Pour cela, il doit d'abord être stocké dans une variable à déclarer.

• Basculer dans l'éditeur de code VBA (ALT + F11),
• En début de procédure, avant toute ligne de code, ajouter les instructions suivantes :

Dim nb_alea As Byte

nb_alea = (6 * Rnd + 1)
MsgBox nb_alea

Nous déclarons tout d'abord la variable nb_alea comme un entier court (Byte). Le Byte permet de stocker une valeur comprise entre 0 et 255, soit largement au-dessus des capacités nécessaires pour envisager 6 déclinaisons de tris. Puis, nous affectons cette variable à la valeur générée par la syntaxe permettant de produire un entier aléatoire. La fonction MsgBox qui suit est temporaire. Elle permet de réaliser un petit contrôle afin de nous assurer que le nombre est bien borné tel que nous le souhaitons.

• Enregistrer les modifications et basculer sur la feuille Excel,
• Cliquer sur le bouton Trier le tableau,

Une boîte de dialogue, commandée par la fonction VBA MsgBox apparaît. Elle affiche bien un nombre entier compris entre 1 et 6. Si nous validons cette boîte de message et réitérons l'opération, nous obtenons quasi-systématiquement un nombre différent, toujours entier et toujours compris entre les bornes définies dans le code VBA.

• Revenir dans l'éditeur de code Visual Basic,
• Supprimer la ligne du MsgBox,

Il s'agit désormais d'exploiter cette valeur issue du hasard, pour générer des tris aléatoires. C'est l'instruction VBA sur la colonne et l'ordre detri, avant le bloc With que nous devons adaptée selon le cas. Et ces cas peuvent s'énumérer facilement grâce à l'instruction VBA Select Case.

• Remplacer l'instruction de tri située avant le bloc With par le code suivant :

Select Case nb_alea
Case 1: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('C3:C42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 2: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('C3:C42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Case 3: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('D3:D42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 4: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('D3:D42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Case 5: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('B3:B42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 6: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('B3:B42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
End Select

Selon chaque cas (Case), nous faisons varier le tri soit sur la colonne, soit sur l'ordre.

• Enregistrer les modifications et basculer sur la feuille Excel,
• Cliquer plusieurs fois de suite sur le bouton Trier le tableau,

Comme nous le constatons, à chaque clic, selon la valeur générée aléatoirement, le tri sur le tableau change, grâce à l'instruction Select Case et l'adaptation des paramètres de tris. Le code complet de la procédure que nous avons ainsi générée est le suivant :

Sub trier_tableau()
Dim nb_alea As Byte

nb_alea = (6 * Rnd + 1)

Range('B2:D42').Select
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Clear

Select Case nb_alea
Case 1: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('C3:C42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 2: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('C3:C42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Case 3: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('D3:D42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 4: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('D3:D42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Case 5: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('B3:B42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 6: ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('B3:B42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
End Select

With ActiveWorkbook.Worksheets('Mots').Sort
.SetRange Range('B2:D42')
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With

Range('A1').Select
End Sub