VBA – Trier des Données

Trier des Données dans Excel VBA

Excel dispose d’un excellent moyen de trier une série de données tabulaires à l’aide du ruban de l’interface graphique, et à un moment donné, vous voudrez probablement utiliser cette fonctionnalité dans votre code VBA. Heureusement, c’est très facile à faire.

Vous trouverez la boîte de dialogue dans l’interface graphique en cliquant sur l’icône « Trier » dans le groupe « Trier et filtrer » de l’onglet « Données » du ruban Excel. Vous devez d’abord sélectionner une plage de données tabulaires.

Vous pouvez également utiliser Alt-A-S-S pour afficher la boîte de dialogue d’un tri personnalisé.

La méthode de tri a été considérablement améliorée dans les versions ultérieures d’Excel. Auparavant, le tri était limité à trois niveaux, mais vous pouvez désormais saisir autant de niveaux que vous le souhaitez, et cela s’applique également aux fonctionnalités en VBA.

Vous pouvez intégrer toutes les fonctions de tri proposées dans la boîte de dialogue de tri d’Excel dans votre code VBA. La fonction de tri d’Excel est rapide, plus rapide que tout ce que vous pourriez écrire vous-même en VBA, alors profitez de cette fonctionnalité.

Notez que lorsque vous effectuez un tri en VBA, les paramètres de tri restent les mêmes dans la boîte de dialogue de tri frontale. Ils sont également enregistrés lors de l’enregistrement du classeur.

Si un utilisateur sélectionne la même plage de données tabulaires et clique sur l’icône Tri, il verra tous les paramètres qui ont été saisis par votre code VBA. S’il souhaite effectuer un tri de sa propre conception, il devra d’abord supprimer tous vos niveaux de tri, ce qui sera très ennuyeux pour lui.

En outre, si vous ne modifiez pas les paramètres dans votre code et que vous vous fiez aux valeurs par défaut, vous risquez de constater que l’utilisateur a apporté des modifications qui se répercuteront sur votre tri VBA et donneront des résultats inattendus, ce qui peut être très difficile à déboguer.

Heureusement, il existe une méthode claire en VBA qui permet de redéfinir tous les paramètres de tri afin que l’utilisateur voie une boîte de dialogue de tri vierge

Worksheets("Feuil1").Sort.SortFields.Clear

Une bonne pratique consiste à effacer les paramètres de tri en VBA avant et après l’opération de triage des données effectuée.

Utilisation Pratique de la Méthode de Tri en VBA

Lorsque des données tabulaires sont importées dans Excel, elles sont souvent dans un ordre très aléatoire. Elles peuvent être importées d’un fichier CSV (valeurs séparées par des virgules) ou provenir d’un lien vers une base de données ou une page Web. Vous ne pouvez pas compter sur le fait qu’elles soient dans un ordre fixe d’une importation à l’autre.

Si vous présentez ces données à un utilisateur dans votre feuille de calcul, celui-ci risque de trouver difficile de regarder et de comprendre une énorme quantité de données qui, en termes d’ordre, sont éparpillées dans tous les sens. Il peut vouloir regrouper les données, ou couper et coller certaines sections de celles-ci dans une autre application.

Il peut aussi vouloir voir, par exemple, l’employé le mieux payé ou celui qui a le plus d’ancienneté.

Grâce à la méthode Sort de VBA, vous pouvez proposer des options permettant à l’utilisateur de trier facilement les données.

Exemple de Données pour Démontrer le Triage d’Excel avec VBA

Nous avons d’abord besoin d’un échantillon de données à saisir dans une feuille de calcul, afin que le code puisse démontrer toutes les possibilités offertes par VBA.

Copiez ces données dans une feuille de calcul (appelée « Feuil1 ») exactement comme indiqué.

Notez que vous avez utilisé des couleurs d’arrière-plan et des couleurs de police différentes pour les cellules, car elles peuvent également être utilisées comme paramètres de tri. Le tri à l’aide des couleurs de cellule et de police sera démontré plus loin dans l’article. Notez également que dans la cellule E3, le nom du département est tout en minuscules.

Vous n’avez pas besoin des couleurs d’intérieur de cellule et de police si vous ne souhaitez pas utiliser les exemples de tri par couleur de cellule et de police.

Enregistrement d’une Macro pour le Triage VBA

Le code VBA pour le tri peut être assez compliqué, il peut parfois être utile d’effectuer le tri à partir de l’interface graphique d’Excel et d’enregistrer une macro pour vous montrer comment le code fonctionne.

Malheureusement, la fonction d’enregistrement peut générer une quantité énorme de code car elle définit pratiquement tous les paramètres disponibles, même si les valeurs par défaut de nombreux paramètres sont acceptables pour votre opération de tri.

Cependant, elle vous donne une très bonne idée de ce qu’implique l’écriture d’un code de tri VBA, et l’un des avantages est que le code enregistré fonctionnera toujours dans votre cas. Votre propre code devra peut-être être testé et débogué pour fonctionner correctement.

N’oubliez pas que pour une opération effectuée en VBA, il n’existe pas de fonction d’annulation. Il est donc judicieux de faire une copie des données du tableau sur une autre feuille de calcul avant de commencer à écrire votre code de tri.

À titre d’exemple, si vous effectuez un tri simple sur l’échantillon de données ci-dessus, en triant par Employé, l’enregistrement générera le code suivant :

Sub Macro1()

Range("A1:E6").Select

ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Add2 Key:=Range("A2:A6") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Feuil1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
    
End Sub

Il s’agit d’un gros morceau de code, dont une grande partie est inutile en raison de l’utilisation de paramètres par défaut. Cependant, si vous êtes pressé par le temps pour terminer un projet et que vous avez besoin d’un code qui fonctionne rapidement, vous pouvez facilement coller ce code dans votre propre module VBA.

Toutefois, si vous souhaitez rendre votre code compréhensible et plus élégant, d’autres options sont disponibles.

Code VBA pour Effectuer un Tri à un Seul Niveau

Si vous souhaitez trier l’exemple de code en fonction de l’employé uniquement, comme précédemment lors de l’enregistrement d’une macro, le code est très simple :

Sub TriageÀUnNiveau()

Worksheets("Feuil1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("A1"), Header:=xlYes

End Sub

Ce code est beaucoup plus facile à comprendre que le code enregistré car il accepte les valeurs par défaut, par exemple le tri ascendant, et il n’est donc pas nécessaire de définir les paramètres par défaut. Cela suppose que vous ayez utilisé une instruction « Clear » au préalable.

La méthode « Clear » est utilisée initialement pour s’assurer que tous les paramètres de tri de cette feuille de calcul sont remis aux valeurs par défaut. Il se peut qu’un utilisateur ait précédemment défini les paramètres à des valeurs différentes ou qu’un tri antérieur dans VBA les ait modifiés. Il est important de commencer avec les valeurs par défaut lors du tri, sinon vous pourriez facilement vous retrouver avec des résultats inattendus.

La méthode Clear ne réinitialise pas le paramètre Header, et il est conseillé de l’inclure dans votre code, sinon Excel pourrait essayer de deviner si une ligne d’en-tête est présente ou non.

Exécutez ce code avec les données d’exemple et votre feuille de calcul ressemblera à ceci :

Code VBA pour Effectuer un Tri Multi-Niveaux

Vous pouvez ajouter autant de niveaux de tri que nécessaire dans votre code. Supposons que vous souhaitiez trier d’abord par département, puis par date de début, mais dans l’ordre croissant pour le département et dans l’ordre décroissant pour la date de début :

Sub TriageMultiNiveaux()

Worksheets("Feuil1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

End Sub

Notez qu’il y a maintenant deux clés dans l’instruction de tri (Key1 et Key2). La clé 1 (colonne E du département) est triée en premier lieu, puis la clé 2 (colonne C de la date d’embauche) est triée en fonction du premier tri.

Il existe également deux paramètres d’ordre. Order1 est associé à Key1 (Département) et Order2 est associé à Key2 (Date d’embauche). Il est important de veiller à ce que les clés et les commandes soient en phase les unes avec les autres.

Exécutez ce code avec les données de l’échantillon et votre feuille de calcul ressemblera à ceci :

La colonne Département (E) est en ordre croissant, et la colonne Date de début (C) est en ordre décroissant.

L’effet de ce tri est le plus visible lorsqu’on examine Marine Dubois (ligne 3) et Charles Grégoire (ligne 4). Ils sont tous deux dans le domaine des finances, mais Marine Dubois a commencé avant Charles Grégoire et les dates sont présentées par ordre décroissant.

Si la plage de données tabulaires peut être de n’importe quelle longueur, vous pouvez utiliser l’objet UsedRange pour définir la plage de tri. Cela ne fonctionnera que si la feuille de calcul ne contient que des données tabulaires, car toute valeur en dehors des données donnera des résultats incorrects pour le nombre de lignes et de colonnes.

Sub TriageMultiNiveaux()

Worksheets("Feuil1").Sort.SortFields.Clear

Worksheets("Feuil1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

End Sub

Le problème est évité si vous utilisez la méthode « End(xlDown) » pour définir la plage de tri. S’il y a une cellule vide au milieu des données, tout ce qui suit cette cellule vide ne sera pas inclus, alors que UsedRange va jusqu’à la dernière cellule active de la feuille de calcul.

Tri par Couleur de Cellule

Depuis Excel 2007, il est possible de trier en fonction de la couleur de remplissage d’une cellule, ce qui offre une grande souplesse lors de la conception de votre code de tri en VBA.

Sub TriageParCouleurDeCelluleNiveauSimple()

Worksheets("Feuil1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Feuil1").Sort
    .SetRange Range("A1:E6")
    .Apply
End With

End Sub

Ce code va trier la plage de données de l’échantillon (A2:A6) en fonction de la couleur de remplissage de la cellule. Notez qu’il existe désormais un paramètre supplémentaire appelé « SortOn » dont la valeur est « xlSortOnCellColor ».

Notez que le paramètre ‘SortOn’ ne peut être utilisé que par un objet Worksheet et non par un objet Range.

De ce fait, le code est plus compliqué que pour un tri utilisant les valeurs des cellules.

Ce code utilise une valeur clé pour le tri qui couvre toute la plage de données, mais vous pouvez spécifier des colonnes individuelles comme clé pour le tri par couleur d’arrière-plan, et utiliser plusieurs niveaux comme indiqué précédemment.

Après avoir exécuté ce code, votre feuille de calcul ressemblera maintenant à ceci :

Tri par Couleur de Police

La fonction de tri d’Excel VBA offre encore plus de flexibilité, une fonctionnalité supplémentaire intéressante est que vous pouvez trier par couleur de police :

Sub TriageParCouleurDePoliceNiveauSimple()

Worksheets("Feuil1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Feuil1").Sort.SortFields.Add(Range("A2:A6"), _
    xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)

With ActiveWorkbook.Worksheets("Feuil1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub

Le code pour le tri par couleur de police est beaucoup plus compliqué que pour la couleur d’arrière-plan des cellules. Le paramètre ‘SortOn’ prend maintenant la valeur de ‘xlSortOnFontColor’.

Notez que vous devez spécifier l’orientation en tant que ‘xlTopToBottom’ et que vous devez spécifier une couleur pour le tri. Celle-ci est spécifiée en termes de RVB (rouge, vert, bleu) avec des valeurs allant de 0 à 255.

Après avoir exécuté ce code avec les données de l’échantillon, votre feuille de calcul ressemblera maintenant à ceci :

Le tri par couleurs en VBA est beaucoup plus compliqué qu’un tri à plusieurs niveaux, mais si votre code de tri ne fonctionne pas (ce qui peut arriver s’il manque un paramètre ou si vous n’avez pas saisi le code correctement), vous pouvez toujours vous rabattre sur l’enregistrement d’une macro et intégrer le code enregistré dans votre VBA.

Utilisation d’Autres Paramètres dans le Tri VBA

Il existe un certain nombre de paramètres facultatifs que vous pouvez utiliser dans votre code VBA pour personnaliser votre tri.

SortOn

SortOn permet de déterminer si le tri doit utiliser les valeurs des cellules, les couleurs d’arrière-plan des cellules ou les couleurs de la police des cellules. Le paramètre par défaut est Valeurs des cellules.

SortOn = xlSortOnValues

Order

Order permet de choisir si le tri sera effectué par ordre croissant ou décroissant. Le paramètre par défaut est Ascendant.

Order = xlAscending

DataOption

DataOption choisit la façon dont le texte et les nombres sont triés. Le paramètre xlSortNormal trie les données numériques et textuelles séparément. Le paramètre xlSortTextAsNumbers traite le texte comme des données numériques pour le tri. La valeur par défaut est xlSortNormal.

DataOption = xlSortNormal

Header

Header permet de spécifier si la plage de données tabulaires comporte une ligne d’en-tête ou non. S’il y a une ligne d’en-tête, vous ne voulez pas qu’elle soit incluse dans le tri.

Les valeurs des paramètres sont xlYes, xlNo et xlYesNoGuess. xlYesNoGuess laisse à Excel le soin de déterminer s’il existe une ligne d’en-tête, ce qui peut facilement conduire à des résultats incohérents. L’utilisation de cette valeur n’est pas recommandée.

La valeur par défaut est xlNo (pas de ligne d’en-tête dans les données). Avec les données importées, il y a généralement une ligne d’en-tête. Veillez donc à définir ce paramètre à xlYes.

Header = xlYes

MatchCase

Ce paramètre détermine si le tri est sensible à la casse ou non. Les valeurs possibles sont True ou False. Si la valeur est False, les valeurs minuscules sont considérées comme identiques aux valeurs majuscules. Si la valeur est True, le tri affichera la différence entre les valeurs en majuscules et en minuscules. La valeur par défaut est False.

MatchCase = False

Orientation

Ce paramètre détermine si le tri s’effectuera vers le bas à travers les lignes, ou à travers toutes les colonnes. La valeur par défaut est xlTopToBottom (tri par rangées). Vous pouvez utiliser xlLeftToRight si vous souhaitez effectuer un tri horizontal. Les valeurs telles que xlRows et xlColumns ne fonctionnent pas pour ce paramètre.

Orientation = xlTopToBottom

SortMethod

Ce paramètre est uniquement utilisé pour le tri des langues chinoises. Il a deux valeurs, xlPinYin et xlStroke. xlPinYin est la valeur par défaut.

xlPinYin trie en utilisant l’ordre de tri phonétique chinois des caractères. xlStroke trie en fonction de la quantité de traits dans chaque caractère.

Si vous enregistrez une macro de tri, ce paramètre sera toujours inclus dans le code, et vous vous êtes peut-être demandé ce qu’il signifiait. Cependant, à moins que vous ne traitiez des données en chinois, il est peu utile.

SortMethod = xlPinYin

Utilisation d’un Événement de Double-Clic pour Trier des Données Tabulaires

Parmis toutes les fonctionnalités que Microsoft a inclus dans les méthodes de tri pour VBA, il n’a pas prévu de moyen simple pour double-cliquer sur un en-tête de colonne et trier l’ensemble des données tabulaires sur la base de cette colonne particulière.

Il s’agit d’une fonction très utile, et il est facile d’écrire le code pour la réaliser.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Il est supposé que les données commencent à la cellule A1

' Création de trois variable pour capturer la colonne ciblée (sélectionnée) ainsi que
' la colonne et la rangée maximale de la plage de données
Dim Col As Integer, RCol As Long, RRow As Long

'Vérifie que l'utilisation a double-cliqué sur la rangée de l'entête – Rangée 1 sinon la procédure est terminée
If Target.Row <> 1 Then Exit Sub

' Capture le nombre de rangée dans les données tabulaires à l'aide de l'objet "UsedRange"
RCol = ActiveSheet.UsedRange.Columns.Count

' Capture le nombre de colonnes dans les données tabulaires à l'aide de l'objet "UsedRange"
RRow = ActiveSheet.UsedRange.Rows.Count

'Vérifie que l'utilisateur n'a pas cliqué sur une colonne à l'extérieur de la plage de données tabulaires
If Target.Column > RCol Then Exit Sub

'Capture le numéro de colonne que l'utilisateur a sélectionné
Col = Target.Column

'Supprime les paramètres de tris qui aurait pu être définis précédemment
ActiveSheet.Sort.SortFields.Clear

'Tri la plage de données tel que délimité par l'objet UsedRange
'Tri les données tabulaires en utilisant la colonne sur laquelle l'utilisateur a double-cliqué comme clé
ActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Sort Key1:=Cells(1, Col), Header:=xlYes

'Sélectionne la cellule A1 - C'est pour s'assurer que l'utilisateur ne reste pas en mode d'édition une fois le tri complété
ActiveSheet.Range("A1").Select

End Sub

Ce code doit être placé dans l’événement de double-clic sur la feuille contenant les données tabulaires. Pour ce faire, cliquez sur le nom de la feuille de calcul dans la fenêtre de l’explorateur de projet (dans le coin supérieur gauche de l’écran VBE), puis sélectionnez ‘Worksheet’ dans la première liste déroulante de la fenêtre de code. Sélectionnez « BeforeDoubleClick » dans la deuxième liste déroulante, et vous pouvez alors entrer votre code.

Notez qu’aucun nom, plage ou référence de cellule n’est codé en dur dans ce code, à l’exception du déplacement du curseur vers la cellule A1 à la fin du code. Le code est conçu pour obtenir toutes les informations nécessaires à partir des coordonnées de la cellule sur laquelle l’utilisateur a doublement cliqué et de la taille de la plage de données du tableau.

La taille de la plage de données tabulaires n’a pas d’importance. Le code récupère toutes les informations requises et peut être utilisé sur des données situées n’importe où dans votre classeur sans avoir à coder les valeurs en dur. La seule hypothèse est qu’il existe une ligne d’en-tête dans les données tabulaires et que la plage de données commence à la cellule A1, mais la position de départ de la plage de données peut être facilement modifiée dans le code.

Tous les utilisateurs seront impressionnés par cette nouvelle fonctionnalité de tri !

Extension de la Fonction de Tri à l’Aide de VBA

Microsoft a permis une très grande flexibilité dans le tri en utilisant un large éventail de paramètres. Toutefois, avec VBA, vous pouvez aller encore plus loin.

Supposons que vous vouliez trier toutes les valeurs avec une police en gras en haut de vos données. Il n’y a aucun moyen de le faire directement dans Excel, mais vous pouvez écrire du code VBA pour le faire :

Sub TriParPoliceEnGras()

'Crée des variables pour contenir le nombre de lignes et de colonnes des données tabulaires
Dim RRow As Long, RCol As Long, N As Long

'Désactive la mise à jour de l'affichage écran pour que l'utilisateur ne voit pas les changements
' de données que la macro utilise pour faire le tri
Application.ScreenUpdating = False

'Capture le nombre de colonne dans la plage de données tabulaires
RCol = ActiveSheet.UsedRange.Columns.Count

'Capture le nombre de rangée dans le plage de données tabulaires
RRow = ActiveSheet.UsedRange.Rows.Count

'Boucle pour chaque rangée de la plage de données à l'exception de la ligne d'entête
For N = 2 To RRow
    'Si une cellule contient une police en gras, place un 0 devant la valeur de la cellule
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = "0" & ActiveSheet.Cells(N, 1).Value
    End If
Next N

'Supprime les paramètres de tris existants
ActiveSheet.Sort.SortFields.Clear

'Triage des données tabulaires. Toutes les valeurs commencant par 0 vont se déplacer au début de la plage
ActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Sort Key1:=Cells(1, 1), Header:=xlYes

'Boucle pour chaque rangée de la plage de données à l'exception de la ligne d'entête
For N = 2 To RRow
    'Si une cellule contient une police en gras, enlève le 0 ajouté précédemment pour rétablir
    '   les valeurs originales de ces cellules
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = Mid(ActiveSheet.Cells(N, 1).Value, 2)
    End If
Next N

'Active la mise à jour de l'écran
Application.ScreenUpdating = True

End Sub

Le code détermine la taille de la plage de données tabulaires à l’aide de l’objet ‘UsedRange’, puis parcourt toutes les lignes de cette plage. Lorsqu’une police en gras est trouvée, un zéro de tête est placé devant la valeur de la cellule.

Un tri est ensuite effectué. Comme le tri est effectué par ordre croissant, tout ce qui est précédé d’un zéro est placé en tête de liste.

Le code parcourt ensuite toutes les lignes et supprime les zéros de tête, ce qui rétablit les valeurs d’origine des données.

Ce code trie en utilisant les polices en gras comme critère, mais vous pourriez facilement utiliser d’autres caractéristiques de cellules de la même manière, par exemple, la police italique, la taille du texte, la police soulignée, le nom de la police, etc.