I. Introduction▲
Dans une autre documentation destinée aux développeurs en VBA pour Excel, Fonctions en VBA pour gérer les Tableaux Structurés d'Excel, je présentais les avantages que les tableaux structurés procurent aux utilisateurs, entre autres :
- un large éventail de styles automatiques de présentation, permettant une mise en surbrillance une ligne sur deux pour faciliter la lecture ;
- des formules plus simples à concevoir et à relire, car elles incluent le nom des colonnes auxquelles elles font référence ;
- une recopie automatique des formules à l’ajout d’une nouvelle ligne ;
- la possibilité d’inclure très facilement une ligne de totaux, ou inversement, de la masquer.
Mais, j’avais omis d’évoquer l’usage des segments, pourtant plébiscités par les utilisateurs en raison de leur simplicité d’utilisation pour filtrer les données : en cliquant tout simplement sur un élément du segment.
Pour ceux qui ne les connaissent pas encore, précisons :
- qu’ils s’obtiennent en sélectionnant le tableau source, puis dans le menu « Création de tableau », choisir « Insérer un segment », Microsoft fournit une aide qui en dit plus ;
- qu’ils ne peuvent que filtrer les données égales à la sélection, et ne permettent pas d’avoir des conditions avancées, comme « contient », « plus petit que », etc., mais c’est justement cette simplicité que les utilisateurs apprécient ;
- que plusieurs éléments d’un segment peuvent être sélectionnés (en maintenant la touche [Ctrl] enfoncée lors du clic, ou en cliquant préalablement sur l’icône « Sélection multiple » du segment) ;
- que le filtre peut être effacé en cliquant sur l’icône « Effacer le filtre » du segment ;
- qu’ils s’appliquent aussi bien aux tableaux structurés qu’aux tableaux et graphiques croisés dynamiques.
Si les utilisateurs peuvent facilement ajouter manuellement des segments pour filtrer les données d’un tableau structuré, vous allez constater, dans cette documentation, que l’utilisation du VBA permettra aux développeurs d’en décupler les avantages…
C’est pourquoi nous allons étudier des fonctions en VBA qui permettent de gérer les segments : pour savoir s’ils existent, les créer, les déplacer, modifier certains de leurs paramètres, ou les supprimer.
Puis des fonctions qui permettent de gérer les éléments de ces segments : pour identifier ceux qui sont sélectionnés, déclencher ou inhiber leur sélection, et d’autres fonctions qui peuvent être bien utiles…
Nous verrons ensuite des exemples de mise en pratique de ces fonctions, et nous serons capables d’utiliser un segment pour masquer les colonnes (et non plus les lignes) d’un tableau structuré.
Il existe un second type de filtre, qui ne s’applique qu’aux tableaux et graphiques croisés dynamiques : les chronologies.
Les chronologies sont utilisées sur les dates pour filtrer une période, avec une échelle de temps qui peut être, au choix : les jours, les mois, les trimestres, les années.
La sélection de la période se fait en cliquant sur la barre de la chronologie. Elle peut être étendue en maintenant le clic enfoncé, ou avec la combinaison de touches [Ctrl]+[Majuscule].
Bien que les chronologies ne s’appliquent pas nativement aux tableaux structurés, nous verrons comment, grâce au VBA, nous pouvons filtrer les dates d’un tableau structuré avec une chronologie.
Un exemple de segments et de chronologie qui servira de fil rouge pour cette documentation :
Pour une meilleure ergonomie, je vous invite à configurer les paramètres des segments (cliquez sur le segment, puis dans le menu « Segment », choisissez « Paramètre des segments ») pour :
- afficher l’en-tête ;
- utiliser un tri croissant ;
- masquer les éléments sans données.
Vous trouverez d’autres configurations dans l’onglet « Segment » du ruban :
Pour figer la position et les dimensions du segment, faire un clic gauche dessus pour ouvrir le menu contextuel et cliquer sur « Taille et propriétés… » ; dans la mise en forme du segment, à la rubrique « Position et disposition » cocher « Désactiver le redimensionnement et le déplacement », et à la rubrique « Propriétés » cocher « Ne pas déplacer ou dimensionner avec les cellules ».
Voir la fonction SegmentFiger.
II. Utiliser les segments sur un tableau structuré▲
Nous allons distinguer les fonctions pour gérer les segments de celles pour gérer les éléments des segments.
II-1. Fonctions pour gérer les segments▲
Avant de passer aux travaux pratiques, il est nécessaire de faire une présentation des fonctions, que vous trouverez dans le fichier joint et en annexe, qui permettent de gérer les segments. Nous ne détaillerons pas ici le code source en VBA.
II-1-A. SegmentExiste▲
La fonction SegmentExiste renseigne si un segment existe.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données, si vide la recherche porte sur NomColonne ;
• NomColonne : le nom de la colonne liée au segment, si vide la recherche porte sur NomTableau.
La fonction renvoie : True si le segment existe, False dans le cas contraire.
Exemple pour afficher un message si le segment « Prof » du tableau « TS_Eleves » n’existe pas :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentExiste()
'------------------------------------------------------------------------------------------------------
If SegmentExiste("TS_Eleves", "Prof") = False Then MsgBox "Le segment Prof doit être ajouté"
End SubII-1-B. SegmentCreer▲
La fonction SegmentCreer crée un segment pour un tableau.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• Cellule : un « Range » qui représente la cellule où sera placé le segment créé.
La fonction renvoie : True si le segment a été créé, False dans le cas contraire.
Exemple pour créer le segment « Prof » du tableau « TS_Eleves » et le placer en cellule « E2 » (dans la feuille du tableau) :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentCreer()
'------------------------------------------------------------------------------------------------------
Call SegmentCreer("TS_Eleves", "Prof", [E2])
End Sub
La largeur du segment est ajustée en se basant sur la largeur de la colonne source.
Les paramètres sont configurés pour : afficher l’en-tête ; utiliser un tri croissant ; masquer les éléments sans donnée.
II-1-C. SegmentStyle▲
La fonction SegmentStyle modifie le style (la couleur) d’un segment.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• NouveauStyle : l’une des 12 valeurs de l’énumération « SlicerCachesStyle », c’est-à-dire de « SlicerStyleLight1 » à « SlicerStyleLight6 » pour les 6 styles clairs, et de « SlicerStyleDark1 » à « SlicerStyleDark6 » pour les 6 styles foncés.
La fonction renvoie : True si tout s’est bien passé, False dans le cas contraire.
Exemple pour donner le style « SlicerStyleDark1 » au segment « Prof » du tableau « TS_Eleves » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentStyle()
'------------------------------------------------------------------------------------------------------
Call SegmentStyle("TS_Eleves", "Prof", SlicerStyleDark1)
End SubII-1-D. SegmentBoutons▲
La fonction SegmentBoutons configure la dimension des boutons d’un segment.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• NbColonne : (optionnel) si différent de zéro, le nombre de boutons à afficher sur une ligne ;
• Hauteur : (optionnel) si différent de zéro, la hauteur (en points) des boutons du segment ;
• Largeur : (optionnel) si différent de zéro, la largeur (en points) des boutons du segment.
La fonction renvoie : True si tout s’est bien passé, False dans le cas contraire.
Exemple pour afficher deux boutons par ligne dans le segment « Salle » du tableau « TS_Eleves ». Les boutons ont une hauteur de 18,5 points et une largeur de 80 points :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentBoutons()
'------------------------------------------------------------------------------------------------------
Call SegmentBoutons("TS_Eleves", "Salle", 2, 18.5, 80)
End SubII-1-E. SegmentTaille▲
La fonction SegmentTaille configure la taille d’un segment.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• NbBouton : (optionnel) si différent de zéro, ajuste la hauteur du segment pour contenir le nombre de boutons indiqué dans cet argument ; si -1 est passé alors ajuste la hauteur du segment pour contenir tous les boutons ;
• Largeur : (optionnel) si différent de zéro, la largeur (en points) du segment.
La fonction renvoie : True si tout s’est bien passé, False dans le cas contraire.
Exemple pour ajuster la taille du segment « Prof » du tableau « TS_Eleves » pour afficher tous les boutons, avec une largeur de 120 points :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentTaille()
'------------------------------------------------------------------------------------------------------
Call SegmentTaille("TS_Eleves", "Prof", -1, 120)
End SubII-1-F. SegmentDeplacer▲
La fonction SegmentDeplacer déplace un segment sur une autre feuille (en conservant sa taille et certaines de ses propriétés).
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• NomFeuilleDestination : le nom de la feuille de destination.
La fonction renvoie : True si tout s’est bien passé, False dans le cas contraire.
Exemple pour déplacer le segment « Prof » du tableau « TS_Eleves » sur la feuille « Menu » (du classeur actif) :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentDeplacer()
'------------------------------------------------------------------------------------------------------
Call SegmentDeplacer("TS_Eleves", "Prof", "Menu")
End Sub
Il n’existe pas de méthode en VBA pour déplacer un segment. La solution de contournement est de le supprimer puis de le créer sur une autre feuille.
Manuellement, il faut copier le segment, le supprimer, le coller sur une autre feuille.
II-1-G. SegmentSupprimer▲
La fonction SegmentSupprimer supprime un segment.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment.
La fonction renvoie : True si tout s’est bien passé, False dans le cas contraire.
Exemple pour supprimer le segment « Prof » du tableau « TS_Eleves » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentSupprimer()
'------------------------------------------------------------------------------------------------------
Call SegmentSupprimer("TS_Eleves", "Prof")
End SubII-1-H. SegmentEstFiltre▲
La fonction SegmentEstFiltre renseigne si un segment est filtré ou non.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment.
La fonction renvoie : True si le segment existe et est filtré, False dans le cas contraire.
Exemple pour savoir si le segment « Prof » du tableau « TS_Eleves » est filtré :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentEstFiltre()
'------------------------------------------------------------------------------------------------------
If SegmentEstFiltre("TS_Eleves", "Prof") = True Then MsgBox "Prof est filtré" Else MsgBox "Prof n'est pas filtré"
End SubII-1-I. SegmentFiger▲
La fonction SegmentFiger permet de figer la position d'un segment.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• Figer : True s'il faut figer la position et la dimension du segment, False s'il faut tout libérer ;
• Placement : la propriété du placement suivant l'énumération XlPlacement.
La fonction renvoie : True si tout s'est bien passé, False dans le cas contraire.
Exemple pour figer le segment « Salle » du tableau « TS_Eleves » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentFiger()
'------------------------------------------------------------------------------------------------------
Call SegmentFiger("TS_Eleves", "Salle", True, xlFreeFloating)
End SubII-2. Fonctions pour gérer les éléments d’un segment▲
Ce chapitre présente des fonctions qui servent à la gestion des éléments des segments.
Comme pour le chapitre précédent, l’objectif est de fournir des fonctionnalités qui pourront vous être utiles.
Nous ne détaillerons pas le code VBA, que vous retrouverez dans le fichier joint et en annexe.
II-2-A. SegmentElementExiste▲
La fonction SegmentElementExiste renseigne si un élément d’un segment existe.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• Item : le nom de l’élément du segment.
La fonction renvoie : True si l’élément existe, False dans le cas contraire.
Exemple pour afficher un message si l’élément « DUPOND » n’existe pas dans le segment « Prof » du tableau « TS_Eleves » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentElementExiste()
'------------------------------------------------------------------------------------------------------
If SegmentElementExiste("TS_Eleves", "Prof", "DUPOND") = False Then MsgBox "Le prof DUPOND n'existe pas"
End SubII-2-B. SegmentCompterElements▲
La fonction SegmentCompterElements renvoie le nombre d’éléments d’un segment.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• QueLesActifs : si True alors ne compte que les éléments sélectionnés et avec des données, si False alors compte tous les éléments du segment.
La fonction renvoie : suivant la valeur de l’argument « QueLesActifs », soit le nombre d’éléments sélectionnés et avec des données, soit le nombre total d’éléments du segment.
Exemple pour afficher le nombre d’éléments des segments « Prof » et « Salle » du tableau « TS_Eleves » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentCompterElements()
'------------------------------------------------------------------------------------------------------
MsgBox "Il y a " & SegmentCompterElements("TS_Eleves", "Prof", False) _
& " profs et " & SegmentCompterElements("TS_Eleves", "Salle", False) _
& " salles."
End SubII-2-C. SegmentListeElements▲
La fonction SegmentListeElements renvoie la liste, en base 1, des éléments d’un segment.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• QueLesActifs : si True alors ne traite que les éléments sélectionnés et avec des données, si False alors traite tous les éléments du segment.
La fonction renvoie une liste en base 1 : suivant la valeur de l’argument « QueLesActifs », soit les éléments sélectionnés et avec des données, soit tous les éléments du segment.
Exemple pour afficher en cellule « C6 » les éléments sélectionnés du segment « Prof » du tableau « TS_Eleves », séparés par une virgule :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentListeElements()
'------------------------------------------------------------------------------------------------------
Dim Prof
Prof = SegmentListeElements("TS_Eleves", "Prof", True)
Range("C6") = Join(Prof, ", ")
End SubII-2-D. SegmentActiverUnElement▲
La fonction SegmentActiverUnElement active (sélectionne) un élément d’un segment, sans désactiver les éventuels éléments déjà activés.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• Item : le nom de l’élément du segment à activer.
La fonction renvoie : True si tout s’est bien passé, False dans le cas contraire.
Exemple pour activer l’élément « DURAND » du segment « Prof » du tableau « TS_Eleves » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentActiverUnElement()
'------------------------------------------------------------------------------------------------------
Call SegmentActiverUnElement("TS_Eleves", "Prof", "DURAND")
End SubII-2-E. SegmentActiverListeElements▲
La fonction SegmentActiverListeElements active (sélectionne) un ou plusieurs éléments d’un segment, et désactive les autres éléments qui ne sont pas dans la liste.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• ListeItems : la liste des éléments à activer. S'il y en a plusieurs alors passer un Array. Si vide alors active tous les éléments.
La fonction renvoie : le nombre d'éléments sélectionnés, ou 0 si tout est sélectionné.
Exemple pour activer tous les éléments du segment « Prof », puis les éléments « Jaune » et « Verte » du segment « Salle », du tableau « TS_Eleves » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentActiverListeElements()
'------------------------------------------------------------------------------------------------------
Call SegmentActiverListeElements("TS_Eleves", "Prof", "")
Call SegmentActiverListeElements("TS_Eleves", "Salle", Array("Jaune", "Verte"))
End SubII-2-F. SegmentElementEstActif▲
La fonction SegmentElementEstActif renseigne si un élément d’un segment est actif (sélectionné) ou pas.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• Item : le nom de l'élément.
La fonction renvoie : True si l’élément est actif (même s’il n’a pas de donnée), False s’il ne l’est pas.
Exemple pour afficher un message indiquant si l'élément « DURAND » du segment « Prof » est activé ou non :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentElementEstActif()
'------------------------------------------------------------------------------------------------------
MsgBox "Le prof DURAND " _
& IIf(SegmentElementEstActif("TS_Eleves", "Prof", "DURAND") = True, "est", "n'est pas")
& " sélectionné."
End SubII-2-G. SegmentEstAvecDonnees▲
La fonction SegmentEstAvecDonnees renseigne si un élément d’un segment a des données ou pas.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• Item : le nom de l'élément.
La fonction renvoie : True si l’élément a des données, False s’il n'en a pas.
Exemple pour activer uniquement l'élément « Jaune » du segment « Salle », puis afficher un message indiquant si l'élément « DURAND » du segment « Prof » a des données ou non :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentEstAvecDonnees()
'------------------------------------------------------------------------------------------------------
Call SegmentActiverListeElements("TS_Eleves", "Salle", "Jaune")
MsgBox "Le prof DURAND " _
& IIf(SegmentEstAvecDonnees("TS_Eleves", "Prof", "DURAND") = True, "a des élèves.", "n'a pas d'élève") _
& " dans la salle jaune."
End SubII-2-H. SegmentDesactiverUnElement▲
La fonction SegmentDesactiverUnElement désactive un élément d’un segment, sans désactiver les autres éléments sélectionnés. Si plus aucun élément n'est sélectionné, alors tous sont sélectionnés.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment ;
• Item : le nom de l'élément.
La fonction renvoie : True si l’élément a été désactivé, False dans le cas contraire.
Exemple pour activer tous les éléments du segment « Salle » sauf l'élément « Jaune » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentDesactiverUnElement()
'------------------------------------------------------------------------------------------------------
Call SegmentActiverListeElements("TS_Eleves", "Salle", "")
Call SegmentDesactiverUnElement("TS_Eleves", "Salle", "Jaune")
End SubII-2-I. SegmentEffacerTousLesFiltres▲
La fonction SegmentEffacerTousLesFiltres efface les filtres, soit des segments du tableau passé en argument, soit de tous les segments du classeur actif si l’argument n’est pas renseigné.
Son argument (sensible à la casse) est :
• NomTableau : (facultatif) le nom du tableau qui contient les données.
La fonction renvoie : True si au moins un segment est traité, False dans le cas contraire.
Exemple pour effacer les filtres de tous les segments liés au tableau « TS_Eleves » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentEffacerTousLesFiltres()
'------------------------------------------------------------------------------------------------------
Call SegmentEffacerTousLesFiltres("TS_Eleves")
End SubII-2-J. SegmentSaisirSelection▲
La fonction SegmentSaisirSelection ouvre une boîte de saisie pour permettre à l'utilisateur de saisir un mot clé afin d'activer un ou plusieurs éléments dans un segment, qui correspondent à ce mot clé.
Vous pouvez utiliser les jokers :
* (étoile) pour remplacer plusieurs caractères ;
? (point d'interrogation) pour remplacer un caractère.
Si aucun élément n'est trouvé, la recherche sera relancée sans respecter la casse et avec le joker « étoile » au début et en fin du mot clé.
Si malgré cette seconde recherche aucun élément n’est trouvé, la sélection active n’est pas modifiée.
Ses arguments (sensibles à la casse) sont :
• NomTableau : le nom du tableau qui contient les données ;
• NomColonne : le nom de la colonne liée au segment.
La fonction renvoie : True si au moins un élément est activé suite à cette recherche, False dans le cas contraire.
Exemple pour inviter l’utilisateur à faire une sélection sur le segment « Salle » :
'------------------------------------------------------------------------------------------------------
Sub Démo_SegmentSaisirSelection()
'------------------------------------------------------------------------------------------------------
Call SegmentSaisirSelection("TS_Eleves", "Salle")
End Sub
S’il saisit « V* », les salles « Verte » et « Violette » sont sélectionnées.
S’il saisit « v », aucun élément ne répondant à la demande, la recherche est relancée sur « *v* » sans tenir compte de la casse, ce qui sélectionne les salles « Verte » et « Violette ».
De même, s’il saisit « vert », la salle « Verte » est sélectionnée, après transformation du mot clé en « *vert* ».
Vous pouvez lancer cette fonction depuis un bouton de commande placé en haut du segment, en lui associant une macro (qui contient par exemple Call SegmentSaisirSélection("TS_Eleves", "Salle") )
II-3. Exemples de gestion des éléments d’un segment▲
Vous pouvez ajouter un bouton de commande qui efface, via une macro, les filtres des deux segments « Prof » et « Salle » :
'------------------------------------------------------------------------------------------------------
Sub Démo_AfficherTout()
'------------------------------------------------------------------------------------------------------
Call SegmentEffacerTousLesFiltres("TS_Eleves")
End Sub
Ou bien, sélectionner les éléments qui commencent par « V » dans le segment « Salle » :
'------------------------------------------------------------------------------------------------------
Sub Démo_Afficher_Salle_V()
'------------------------------------------------------------------------------------------------------
Dim i As Integer, Salle
' Affiche toutes les données des deux segments:
Call SegmentEffacerTousLesFiltres("TS_Eleves")
' Mémorise les éléments du segment "Salle" :
Salle = SegmentListeElements("TS_Eleves", "Salle", False)
' Boucle pour désactiver les éléments qui ne commencent pas par V:
For i = 1 To UBound(Salle)
If Not Salle(i) Like "V*" Then Call SegmentDesactiverUnElement("TS_Eleves", "Salle", Salle(i))
Next i
End Sub
Dans les chapitres qui suivent, nous allons voir d’autres exemples de gestion des éléments d’un segment en VBA qui interceptent un changement et agissent en conséquence.
II-4. Capter un changement de sélection dans un segment lié à un tableau structuré▲
Les ingénieurs de Microsoft n’ont pas eu la bonne idée de mettre à la disposition des développeurs un évènement déclenché lorsqu’une sélection change dans un segment lié à un tableau structuré. Pour capter un tel changement, la solution de contournement est de mettre une formule sur la feuille, et d’appliquer un évènement lors de son calcul : Worksheet_Calculate().
L’évènement va alors lancer une fonction personnelle, placée dans un module classique, qui va gérer ce changement.
Dans notre exemple, une formule dans la colonne « Actif » du tableau structuré permet d’afficher « 1 » si la ligne est visible (donc sélectionnée par un élément du segment), et « 0 » lorsqu’elle n’est pas visible (donc non sélectionnée) : =SOUS.TOTAL(103; [@Salle])
Ce qui permet, d’une part, d’alimenter en temps réel le tableau de bord qui contient des formules pour traiter les données des lignes visibles uniquement (donc sélectionnées par les segments) :
Et d’autre part, d’afficher la liste des profs sélectionnés, grâce à la fonction Démo_MiseAJour_TableauDeBord, lancée quand la feuille est calculée suite à un changement de sélection des éléments.
La procédure évènementielle de la feuille :
'------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Calculate()
'------------------------------------------------------------------------------------------------------
Call Démo_MiseAJour_TableauDeBord
End Sub
La procédure personnelle dans un module de programmation :
'------------------------------------------------------------------------------------------------------
Sub Démo_MiseAJour_TableauDeBord()
'------------------------------------------------------------------------------------------------------
' Affiche en "C6" les éléments actifs du segment "Prof":
Dim Prof
Prof = SegmentListeElements("TS_Eleves", "Prof", True)
Range("C6") = Join(Prof, ", ")
End Sub
Complétons cette fonction pour changer le style du segment « Prof » lorsque tous les éléments sont sélectionnés :
'------------------------------------------------------------------------------------------------------
Sub Démo_MiseAJour_TableauDeBord()
'------------------------------------------------------------------------------------------------------
Dim Prof
' Affiche en "C6" les éléments actifs du segment "Prof":
Prof = SegmentListeElements("TS_Eleves", "Prof", True)
Range("C6") = Join(Prof, ", ")
' Couleur du segment "Prof" en vert foncé si tous les prof sont sélectionnés, sinon en bleu clair:
If SegmentEstFiltre("TS_Eleves", "Prof") = False Then
Call SegmentStyle("TS_Eleves", "Prof", SlicerStyleDark6)
Else
Call SegmentStyle("TS_Eleves", "Prof", SlicerStyleLight1)
End If
End Sub
Un segment peut avoir plusieurs éléments sélectionnés, mais il est possible de contourner cette possibilité de sélection multiple, si vous souhaitez restreindre la sélection à un seul élément.
Ce code, ajouté à la fonction Démo_MiseAJour_TableauDeBord ci-dessus, ne retient la sélection que d’un seul élément dans le segment « Salle », même si plusieurs ont été sélectionnés :
Dim Salle
Salle = SegmentListeElements("TS_Eleves", "Salle", True)
If UBound(Salle) > 1 Then
Application.EnableEvents = False
Call SegmentActiverListeElements("TS_Eleves", "Salle", Salle(1))
Application.EnableEvents = True
End If
L’exercice suivant consiste à ajouter un segment contenant une liste de colonnes du tableau structuré « TS_Eleves », que nous choisirons d’afficher ou non :
- Dans une nouvelle feuille, renseignez une plage contenant le titre « Colonnes à afficher », et les noms des colonnes souhaitées : « Prénom », « Commentaire », « Genre » ; sans oublier le choix « Aucune » (si l’on souhaite donner la possibilité de masquer les trois colonnes).
- Sélectionnez la plage pour la transformer en un tableau (structuré) que vous renommerez « TS_Afficher ».
- Ajoutez-y une ligne de total (pour générer un calcul en cas de sélection et ainsi déclencher un évènement qui sera capté) ; dans la liste déroulante choisissez « Nombre ».
- Insérez un segment sur la colonne « Colonnes à afficher » et le code suivant pour la procédure évènementielle de la feuille :
'------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Calculate()
'------------------------------------------------------------------------------------------------------
' Suivant les éléments sélectionnés de "TS_Afficher", affiche ou masque les colonnes de "TS_Eleves":
Call SegmentMasquerAfficherColonneTS("TS_Afficher", "TS_Eleves")
' Si plusieurs sélections sont faites alors l’élément "Aucune" ne doit pas être sélectionné:
If SegmentCompterElements("TS_Afficher", "", True) > 1 Then
Application.EnableEvents = False
Call SegmentDesactiverUnElement("TS_Afficher", "", "Aucune")
Application.EnableEvents = True
End If
End Sub- Enfin, déplacez le segment sur la feuille du tableau « TS_Eleves ».
Le tableau « TS_Afficher » a été généré sur une autre feuille qui sera masquée par la suite afin de ne pas perturber l’utilisateur, mais il aurait pu être généré directement sur la feuille du tableau « TS_Eleves ».
La fonction SegmentMasquerAfficherColonneTS a comme arguments (sensibles à la casse) :
• TSSegment : le nom du tableau structuré qui contient les données du segment ;
• NomTableau : le nom du tableau structuré dont il faut masquer ou afficher les colonnes.
La fonction renvoie : True si tout s’est bien passé, False dans le cas contraire.
La liste des éléments de notre segment « Colonne à afficher » peut être affichée dans un ordre personnalisé et non plus par ordre alphabétique :
Sélectionnez le tableau structuré « TS_Afficher » puis le menu « Données / Trier ». Dans le champ « Ordre » piochez « Liste personnalisée… ».
Dans la colonne « Entrées de la liste » saisissez les éléments dans l’ordre que vous souhaitez les voir dans le segment, cliquez sur « Ajouter » puis « OK ».
N’oubliez pas de modifiez les paramètres du segment pour activer l’option « Utiliser des listes personnalisées lors du tri ».
III. Utiliser une chronologie sur un tableau structuré▲
Comme évoqué en introduction, les chronologies ne s’appliquent pas aux tableaux structurés, mais uniquement aux tableaux et graphiques croisés dynamiques (et aux cubes).
Pour s’en servir sur un tableau structuré, nous allons utiliser une astuce que je trouve élégante et familière aux utilisateurs : en générant un tableau croisé dynamique (TCD) dont la source est la colonne de dates du tableau structuré, y insérer une chronologie, et capter les actions effectuées sur la chronologie pour filtrer le tableau structuré.
Par convention, nous n’utiliserons qu’une seule chronologie par TCD.
III-1. Fonctions pour gérer les chronologies▲
Dans un premier temps, commençons par présenter les fonctions que vous trouverez dans le fichier joint et en annexe, qui nous permettent de gérer les chronologies.
III-1-A. ChronologieExiste▲
La fonction ChronologieExiste renseigne si une chronologie existe.
Son argument (sensible à la casse) est :
• NomTCD : le nom du tableau croisé dynamique qui contient les données.
La fonction renvoie : True si la chronologie existe, False dans le cas contraire.
Exemple pour afficher un message si la chronologie du TCD nommé « TCD_Dates » n’existe pas :
'------------------------------------------------------------------------------------------------------
Sub Démo_ChronologieExiste()
'------------------------------------------------------------------------------------------------------
If ChronologieExiste("TCD_Dates") = False Then MsgBox "TCD_Dates et sa chronologie doivent être ajoutés"
End SubIII-1-B. ChronologieStyle▲
La fonction SegmeChronologieStylentCreer modifie le style (la couleur) d’une chronologie.
Ses arguments (sensibles à la casse) sont :
• NomTCD : le nom du tableau croisé dynamique qui contient les données ;
• NouveauStyle : l’une des 12 valeurs de l’énumération « SlicerCachesStyle », c’est-à-dire de « SlicerStyleLight1 » à « SlicerStyleLight6 » pour les 6 styles clairs, et de « SlicerStyleDark1 » à « SlicerStyleDark6 » pour les 6 styles foncés.
La fonction renvoie : True si tout s’est bien passé, False dans le cas contraire.
Exemple pour donner le style « SlicerStyleDark1 » à la chronologie du TCD nommé « TCD_Dates » :
'------------------------------------------------------------------------------------------------------
Sub Démo_ChronologieStyle()
'------------------------------------------------------------------------------------------------------
Call ChronologieStyle("TCD_Dates", SlicerStyleDark1)
End SubIII-1-C. ChronologieTaille▲
La fonction ChronologieTaille configure la taille d’une chronologie.
Ses arguments (sensibles à la casse) sont :
• NomTCD : le nom du tableau croisé dynamique qui contient les données ;
• Hauteur : (optionnel) si différent de zéro, la hauteur (en points) de la chronologie ;
• Largeur : (optionnel) si différent de zéro, la largeur (en points) de la chronologie.
La fonction renvoie : True si tout s’est bien passé, False dans le cas contraire.
Exemple pour modifier la taille de la chronologie du TCD nommé « TCD_Dates » :
'------------------------------------------------------------------------------------------------------
Sub Démo_ChronologieTaille()
'------------------------------------------------------------------------------------------------------
Call ChronologieTaille("TCD_Dates", 100, 260)
End SubIII-1-D. ChronologieInfos▲
La fonction ChronologieInfos renvoie des informations sur les filtres d’une chronologie.
Ses arguments (sensibles à la casse) sont :
• NomTCD : le nom du tableau croisé dynamique qui contient les données ;
• Début : (optionnel) date de début de la chronologie ;
• FiltreMini : (optionnel) date de début du filtre de la chronologie ;
• FiltreMaxi : (optionnel) date de fin du filtre de la chronologie ;
• Fin : (optionnel) date de fin de la chronologie.
La fonction renvoie : True si la chronologie est filtrée, False dans le cas contraire.
Elle renseigne les variables de dates passées en arguments.
Exemple pour afficher les dates de filtres de la chronologie du TCD nommé « TCD_Dates » :
'------------------------------------------------------------------------------------------------------
Sub Démo_ChronologieInfos()
'------------------------------------------------------------------------------------------------------
Dim Début, Mini, Maxi, Fin
If ChronologieInfos("TCD_Dates", Début, Mini, Maxi, Fin) = True Then
MsgBox "Filtres de la chronologie : " & Mini & " à " & Maxi
Else
MsgBox "La chronologie n’est pas filtrée. Plage = " & Début & " à " & Fin
End If
End Sub
Les dates de la chronologie sont en lecture seule. Vous ne pouvez donc pas modifier directement le filtre de la chronologie en modifiant ces dates. Pour cela, utilisez la fonction ChronologieFiltrer.
Lorsqu’il n’y a aucun filtre sur la chronologie une erreur est levée. Dans ce cas, la fonction ChronologieInfos renvoie False, et les dates sont renseignées en analysant le TCD source.
III-1-E. ChronologieFiltrer▲
La fonction ChronologieFiltrer pose les filtres d’une chronologie.
Ses arguments (sensibles à la casse) sont :
• NomTCD : le nom du tableau croisé dynamique qui contient les données ;
• FiltreMini : la date de début du filtre de la chronologie ;
• FiltreMaxi : la date de fin du filtre de la chronologie.
La fonction renvoie : True si le filtre de la chronologie est réalisé, False dans le cas contraire (par exemple si les dates ne sont pas cohérentes).
Exemple pour filtrer la chronologie liée au TCD nommé « TCD_Dates » entre aujourd’hui et le 31 décembre 2029 :
'------------------------------------------------------------------------------------------------------
Sub Démo_ChronologieFiltrer()
'------------------------------------------------------------------------------------------------------
Call ChronologieFiltrer("TCD_Dates", Date, "31/12/2029")
End SubIII-1-F. ChronologieEffacer▲
La fonction ChronologieEffacer efface les filtres, soit de la chronologie liée au nom du TCD passé en argument, soit de toutes les chronologies du classeur actif si l’argument n’est pas renseigné.
Son argument (sensible à la casse) est :
• NomTCD : (facultatif) le nom du tableau croisé dynamique qui contient les données.
La fonction renvoie : True si au moins une chronologie est traitée, False dans le cas contraire.
III-1-G. ChronologieActualiser▲
La fonction ChronologieActualiser actualise, soit la chronologie liée au nom du TCD passé en argument, soit toutes les chronologies du classeur actif si l’argument n’est pas renseigné.
Son argument (sensible à la casse) est :
• NomTCD : (facultatif) le nom du tableau croisé dynamique qui contient les données.
La fonction renvoie : True si au moins une chronologie (et son TCD) est actualisée, False dans le cas contraire.
III-2. Capter un changement de sélection dans une chronologie liée à un TCD▲
Contrairement aux tableaux structurés, les tableaux croisés dynamiques (TCD) disposent d’un évènement déclenché lors d’une modification de filtre d’un TCD :
L’évènement de la feuille Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable).
Et c’est bien utile, car la chronologie étant liée au TCD, une modification du filtre de la chronologie va modifier le filtre du TCD, et cette modification déclenche la procédure évènementielle.
On y placera la fonction ChronologieLiaisonsTCD qui se chargera de retrouver le tableau structuré lié au TCD et la colonne concernée, pour y poser les filtres équivalents aux filtres de la chronologie.
L’exercice consiste à ajouter une chronologie pour filtrer la colonne « Dates » du tableau structuré « TS_Eleves » :
- Dans une nouvelle feuille (nommée par exemple « TCD_Dates »), insérez un TCD lié à la colonne « Dates » du tableau « TS_Eleves », menu « Insertion » / « Tableau croisé dynamique » / « A partir d’un tableau ou d’une plage ».
- Renseignez la source et la destination :
- Renommez le TCD en « TCD_Dates ».
- Sélectionnez le champ « Dates » pour constituer le TCD :
- Dans les « Options du tableau croisé dynamique », dans l’onglet « Données », à la rubrique « Nombre d’éléments à retenir par champ », choisissez « Aucun » (ce qui permettra d’avoir une chronologie qui ne reprend que les données actives en cas de suppression de données).
- Insérez une chronologie depuis le menu « Analyse du tableau croisé dynamique » et le code suivant pour la procédure évènementielle de la feuille :
'------------------------------------------------------------------------------------------------------
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'------------------------------------------------------------------------------------------------------
Call ChronologieLiaisonsTCD(Target.Name)
End Sub- Enfin, déplacez la chronologie sur la feuille du tableau « TS_Eleves » par un copier-supprimer-coller.
Pour une meilleure ergonomie, je vous invite à ne pas afficher la barre de défilement de la chronologie :
Et à lui affecter la fonction ChronologieAnnulerSélection qui ne contient aucun code ! Ça permet juste d’éviter de sélectionner la chronologie lors d’un clic gauche dessus : clic droit sur la chronologie / « Affecter une macro… », sélectionnez la fonction ChronologieAnnulerSélection.
Nous avons vu qu’une chronologie et son TCD sont liés.
Dans notre exemple, le tableau structuré « TS_Eleves » n’est pas lié au tableau croisé dynamique « TCD_Dates », et la modification ou l’ajout de données dans « TS_Eleves » ne se répercute donc pas dans « TCD_Dates » : la chronologie n’est alors pas mise à jour.
Nous allons y remédier…
• Si vous disposez d’une version d’Excel qui possède l’option « Renouvellement automatique », pensez à l’activer pour mettre à jour le TCD lors d’une modification ou d’un ajout de ligne dans le tableau structuré source :
• Sinon, il faut ajouter une procédure évènementielle sur la feuille du tableau structuré, pour capter une saisie, et la fonction ChronologieLiaisonsTS comme dans cet exemple :
'------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'------------------------------------------------------------------------------------------------------
Call ChronologieLiaisonsTS("TS_Eleves")
End Sub
• Ou demander à l’utilisateur de cliquer sur le bouton « Actualiser tout » du menu « Données ».
IV. Démonstration▲
Dans les chapitres précédents, nous avons étudié les principes de base de la gestion en VBA des segments et des chronologies.
Le fichier joint présente une démonstration de leur usage qui, j’espère, vous convaincra de la puissance ces technologies.
La feuille « TS_Commandes » contient une liste de commandes identifiées par un numéro de commande, un fournisseur, un type de produit, et la quantité commandée. Le but est de suivre les facturations reçues, à imputer à ces commandes, et le reste à recevoir.
Les segments permettent de filtrer les commandes, pour des analyses rapides.
La feuille « TS_Saisie » est utilisée pour imputer aux commandes les factures reçues.
Les segments servent à identifier la commande concernée, d’après son numéro, le fournisseur, le produit (l’ordre de sélection est libre bien entendu) :
Quand l’identification de la commande est faite (sélection d’un seul numéro de commande, d’un seul fournisseur, d’un seul produit), le bouton « Prendre cette commande » alimente le tableau de gauche des informations et place le curseur dans la colonne « Quantité » qu’il reste à saisir :
La facture peut comporter plusieurs lignes. Le bouton « Valider la saisie » enregistre les données sur la feuille « TS_Factures », et efface le tableau pour permettre une nouvelle saisie.
Une notification générée par PoweShell s’affiche quelques secondes pour informer l’utilisateur du traitement de sa saisie.
La feuille « TS_Factures » contient également des segments et une chronologie pour filtrer les factures :
Les filtres effectués sur cette feuille se répercutent sur la feuille des commandes, y compris la chronologie :
V. Conclusion▲
Ce n’est pas par hasard si les utilisateurs apprécient les segments et les chronologies, car leur ergonomie simplifie grandement la sélection des données : il est bien plus facile, et rapide, de cliquer sur un élément d’un segment que de passer par les filtres d’un tableau (qui restent disponibles pour les requêtes plus complexes).
Idem pour la chronologie qui permet de sélectionner facilement une période et une échelle de temps de son choix.
Les fonctions en VBA étudiées dans cette documentation vous permettront d’intégrer ces technologies dans vos projets.
Vous en trouverez le code source dans le fichier joint et en annexe.
Les débutants en VBA acquerront le niveau nécessaire avec le tutoriel « Tome 1 - Des bases de la programmation à l'algorithme de classement rapide QuickRanking ».
Bonne programmation.
Laurent OTT.
2026.
VI. Remerciements▲
Je remercie f-leb pour sa correction orthographique.
Ainsi que toute l’équipe de Developpez.com qui participe à la maintenance du site.
VII. Le fichier joint▲
Vous trouverez le fichier Segments.xlsm qui contient les fonctions étudiées dans cette documentation regroupées dans le module « Segments » facilement exportable dans vos applications par un simple « glisser-copier » depuis l’explorateur de projet VBA.
Le module « TS » reprend les fonctions de cette documentation : Fonctions en VBA pour gérer les Tableaux Structurés d'Excel.
VIII. Annexe - Le code source▲
Pour ceux qui ne peuvent pas télécharger le fichier source, cliquez sur « Afficher le code » :
