I. Introduction▲
Excel, principalement depuis sa version 2010, propose une gestion des données sous forme de tableaux dits « tableaux structurés », une technologie bien plus puissante et ergonomique que les classiques « plages de données ».
Pierre Fauconnier y consacre un tutoriel très complet destiné aux utilisateurs « Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages » que je vous recommande de lire si vous n’utilisez pas encore les tableaux structurés.
En résumant les avantages que les tableaux structurés procurent aux utilisateurs nous pouvons citer, 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 ;
- la possibilité de déplacer le tableau sur la feuille de calcul d’un simple glisser/déposer ou de déplacer les colonnes à l’intérieur du tableau pour adapter la présentation à ses besoins.
Cette nouvelle technologie connaît donc logiquement un engouement auprès des utilisateurs, mais aussi auprès des développeurs, que ce soit par choix ou par nécessité.
Par choix, car comme nous le verrons dans cette documentation, un tableau structuré se gère un peu comme une base de données avec des noms de colonnes uniques et une plage déterminée. Ainsi, dans de nombreux domaines, gérer les données dans un tableau structuré est plus simple que dans une plage de données classique. Par exemple, nous accéderons à une donnée en utilisant le nom de sa colonne et le numéro de sa ligne sans avoir à nous soucier de savoir où elle est positionnée physiquement sur la feuille.
Par nécessité, car les programmeurs se retrouvent de plus en plus souvent face à des tableaux structurés que les utilisateurs, à juste titre, plébiscitent.
En complément au tutoriel précité, celui-ci s’adresse aux programmeurs débutants ou confirmés en proposant des fonctions génériques pour gérer les tableaux structurés en VBA.
Ces fonctions couvrent l’essentiel des besoins du programmeur et lui simplifient la vie, car il n’est pas nécessaire de connaître les subtilités de l’objet « ListObject » pour les utiliser.
Les fonctions sont regroupées en cinq thèmes :
- concevoir un tableau structuré ;
- trier, filtrer les données ;
- obtenir des informations sur les données ;
- rechercher, sélectionner, modifier les données ;
- importer, exporter des données.
Toutes les fonctions présentées ont en commun :
- de renvoyer une valeur permettant d’identifier si le traitement demandé s’est déroulé correctement ou non ;
- en cas d’erreur de traitement, d’alimenter les variables publiques TS_ErrNumber et TS_ErrDescription, déclarées en en-tête du module « TS » du fichier joint, avec respectivement le code de l’erreur
Err
.Number
et sa descriptionErr
.Description
(ces deux variables seront exploitées par le programmeur s’il souhaite afficher un message d’erreur ou gérer un fichier de suivi des événements) ; - d’être préfixées « TS_ » pour mieux les identifier.
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 ».
Cette documentation a été réalisée avec Excel 2016 version 32 bits sous Windows 10.
II. Concevoir un tableau structuré▲
Les différentes fonctions de ce chapitre sont consacrées à la conception d’un tableau structuré.
Cela va de sa création à la gestion de ses options, en passant par l’effacement, l’ajout ou la suppression de lignes et colonnes.
II-A. TS_ConvertirPlageEnTS▲
La fonction TS_ConvertirPlageEnTS convertit une plage de données classique en un tableau structuré.
Ses arguments sont :
- TD : la plage (de type Range) qui représente la plage de données à convertir ou tout simplement la première cellule haut/gauche de cette plage, car la plage sera étendue automatiquement ;
- Nom : (facultatif) le nom à donner au tableau structuré généré. Si l’argument n’est pas renseigné, le tableau prendra le nom attribué automatiquement par Excel ;
- Style : (facultatif) le nom du style du tableau structuré. Si l’argument n’est pas renseigné, le style par défaut sera appliqué. Si l’argument est vide, alors le tableau sera sans style ;
- AvecEntete : (facultatif) une valeur de l’énumération XlTotalsCalculation qui indique si la première ligne contient des en-têtes, soit l'une des valeurs suivantes :
- xlYes : (valeur par défaut) la plage contient des en-têtes,
- xlNo : la plage ne contient pas d'en-tête et Excel les rajoute,
- xlGuess : Excel détecte automatiquement si la plage contient ou non des en-têtes.
La fonction renseigne :
- Nom : le nom donné au tableau structuré, ce qui peut être utile si l’argument n’avait pas été renseigné pour connaître le nom attribué par Excel ;
- Style : le nom du style du tableau structuré, ce qui peut également être utile si l’argument n’avait pas été renseigné.
La fonction renvoie : un Range qui représente la plage du tableau structuré généré.
Exemple pour convertir la plage de données située en « A1 » de la feuille « Feuil3 » en un tableau structuré qui sera nommé « TS_Eleves » et de style « clair 13 » (ici la plage de type Range renvoyée n’est pas utilisée, mais nous étudierons des exemples où elle le sera, c’est pourquoi j’ai souhaité la représenter) :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
TS_ConvertirPlageEnTS
(
TD:=
Sheets
(
"Feuil3"
).Range
(
"A1"
), Nom:=
"TS_Eleves"
, _
Style:=
"TableStyleLight13"
, AvecEntete:=
xlYes)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : dans le code de cet exemple (et dans plusieurs autres de cette documentation), les arguments sont nommés pour vous faciliter la lecture, un appel plus court reste évidemment possible : Set
Tableau =
TS_ConvertirPlageEnTS
(
Sheets
(
"Feuil3"
).Range
(
"A1"
), "TS_Eleves"
, "TableStyleLight13"
)
Ou puisque le renvoi de la fonction n’est pas utilisé :Call
TS_ConvertirPlageEnTS
(
Sheets
(
"Feuil3"
).Range
(
"A1"
), "TS_Eleves"
, "TableStyleLight13"
)
Ou encore (sans l’instruction Call
) :
TS_ConvertirPlageEnTS Sheets
(
"Feuil3"
).Range
(
"A1"
), "TS_Eleves"
, "TableStyleLight13"
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_ConvertirPlageEnTS
(
TD As
Range, _
Optional
ByRef
Nom As
String
=
""
, _
Optional
ByRef
Style As
String
=
"*"
, _
Optional
AvecEntete As
XlYesNoGuess =
xlYes) As
Range
'------------------------------------------------------------------------------------------------
' Transforme une plage classique en un Tableau Structuré.
'------------------------------------------------------------------------------------------------
' TD : la plage concernée (ou la cellule haut gauche) du tableau de données.
' Nom : le nom à donner au tableau ou vide pour prendre le nom attribué par EXCEL.
' Style : le style ou * pour le style par défaut ou vide pour aucun style.
' AvecEntete : indique si la première ligne contient des en-têtes :
' xlYes : (valeur par défaut) la plage contient des en-têtes;
' xlNo : la plage ne contient pas d'en-tête et Excel les rajoute;
' xlGuess : Excel détecte automatiquement si la plage contient ou non des en-têtes.
'------------------------------------------------------------------------------------------------
' Renvoie : la plage Range qui représente la plage du Tableau Structuré créé.
'------------------------------------------------------------------------------------------------
On
Error
GoTo
Gest_Err
Err
.Clear
' Si le TD n'existe pas déjà alors le créer:
If
TD.ListObject
Is
Nothing
Then
' Si TD ne représente qu'une seule cellule alors étend la plage:
If
TD.Count
=
1
Then
Set
TD =
TD.CurrentRegion
' Création du Tableau Structuré en attribuant le nom passé ou en prenant celui attribué par EXCEL:
If
Nom >
""
Then
TD.Parent.ListObjects.Add
(
xlSrcRange, TD, , AvecEntete).Name
=
Nom
Else
TD.Parent.ListObjects.Add
xlSrcRange, TD, , AvecEntete
End
If
' Modifie le style s'il ne faut pas prendre celui par défaut, ou pas de style si vide:
If
Style <>
"*"
Then
TD.Parent.ListObjects
(
TD.ListObject.Name
).TableStyle
=
Style
End
If
' Renseigne le nom du Tableau Structuré et son style:
Nom =
TD.ListObject.DisplayName
Style =
TD.Parent.ListObjects
(
TD.ListObject.Name
).TableStyle
' Renvoie la plage du Tableau Structuré:
Set
TS_ConvertirPlageEnTS =
TD
' Fin du traitement:
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-B. TS_CréerUnTableau▲
La fonction TS_CréerUnTableau crée un tableau structuré en utilisant les informations passées en arguments.
Ses arguments sont :
- Plage : la plage (de type Range) qui représente la première cellule haut/gauche où sera placé le tableau structuré ;
- Titres : les noms pour l'en-tête des colonnes, de format
Array
(
) c'est-à-dire entre guillemets et séparés par une virgule (voir l’exemple). Si le tableau n’a qu’une colonne, le titre peut être passé sous la formeString
; - Nom : (facultatif) le nom à donner au tableau structuré créé. Si l’argument n’est pas renseigné, le tableau prendra le nom attribué automatiquement par Excel ;
- Style : (facultatif) le nom du style du tableau structuré. Si l’argument n’est pas renseigné, le style par défaut sera appliqué. Si l’argument est vide, alors le tableau sera sans style.
La fonction renseigne les arguments :
- Nom : le nom donné au tableau structuré (utile si l’argument n’avait pas été renseigné) ;
- Style : le nom du style du tableau structuré (utile si l’argument n’avait pas été renseigné).
La fonction renvoie : un Range qui représente la plage du tableau structuré créé.
Si un tableau existait déjà à l’emplacement demandé, alors la fonction renvoie la plage de ce tableau.
Exemple pour créer un tableau structuré en « A1 » sur la feuille « Feuil3 » qui sera nommé « TS_Eleves » et de style « clair 13 » :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
TS_CréerUnTableau
(
Plage:=
Sheets
(
"Feuil3"
).Range
(
"A1"
), _
Titres:=
Array
(
"Nom"
, "Prénom"
, "Note"
), _
Nom:=
"TS_Eleves"
, _
Style:=
"TableStyleLight13"
)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : le tableau est créé avec une ligne qui s'affiche dessous, mais reste vierge, la saisie peut commencer. En VBA, le tableau sera initialisé lors de l’ajout d’une première ligne, voir la fonction TS_AjouterUneLigne.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_CréerUnTableau
(
Plage As
Range, _
Titres As
Variant
, _
Optional
ByRef
Nom As
String
=
""
, _
Optional
ByRef
Style As
String
=
"*"
) As
Range
'------------------------------------------------------------------------------------------------
' Création d'un Tableau Structuré.
'------------------------------------------------------------------------------------------------
' Plage : la plage du tableau (ou juste la cellule Haut Gauche du tableau).
' Titres : les titres pour l'en-tête, de format Array() entre guillemets et séparés par une virgule.
' S'il n'y a qu'une colonne à créer on peut la passer de la forme String.
' Nom : le nom à donner au tableau ou vide pour prendre le nom attribué par EXCEL.
' Style : le nom EXCEL du style ou * pour le style par défaut ou vide pour aucun style.
'------------------------------------------------------------------------------------------------
' Renvoie : la plage de type Range qui représente le Tableau Structuré créé.
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Si le tableau existe déjà alors le renvoyer :
If
Not
Plage.ListObject
Is
Nothing
Then
Nom =
Plage.ListObject.DisplayName
Style =
Plage.ListObject.TableStyle
Set
TS_CréerUnTableau =
Range
(
Plage.ListObject
)
Else
' Affiche les titres:
If
IsArray
(
Titres) =
True
Then
Plage.Resize
(
1
, UBound
(
Titres) +
1
).Value
=
Titres
Else
Plage.Value
=
Titres
End
If
' Conversion de la plage en un Tableau Structuré :
Set
TS_CréerUnTableau =
TS_ConvertirPlageEnTS
(
Plage, Nom, Style)
End
If
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-C. TS_SupprimerLeTableau▲
La fonction TS_SupprimerLeTableau supprime le tableau structuré passé en argument.
Son argument est :
- TS : la plage (de type Range) qui représente le tableau structuré à supprimer.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour supprimer le tableau structuré nommé « TS_Eleves » :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Call
TS_SupprimerLeTableau
(
Range
(
"TS_Eleves"
))
End
Sub
'------------------------------------------------------------------------------------------------
Le nom d'un tableau structuré est unique dans un classeur. Il n'est donc pas nécessaire d'indiquer la feuille où il se trouve dans l'argument passé à la fonction car Excel sait l'identifier, sauf (la nuance est importante) s'il n'est pas dans le classeur actif. Dans ce cas il faut effectivement indiquer la feuille où il se trouve.
Pour cette documentation tous les tableaux structurés sont dans le classeur actif.
En pratique, pour simplifier l'emploi des fonctions, privilégiez l'usage d'une variable qui fait référence au tableau structuré.
Cela permet de ne déclarer la plage du tableau qu'une seule fois et donc de faciliter la maintenance du code.Dim
Tableau As
RangeSet
Tableau =
Range
(
"TS_Eleves"
)Call
TS_SupprimerLeTableau
(
Tableau)
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_SupprimerLeTableau
(
TS As
Range) As
Boolean
'------------------------------------------------------------------------------------------------
' Suppression d'un Tableau Structuré.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
'------------------------------------------------------------------------------------------------
' Renvoie : Vrai si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
On
Error
GoTo
Gest_Err
Err
.Clear
' Supprime le tableau :
TS.ListObject.Delete
TS_SupprimerLeTableau =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-D. TS_IndexColonne▲
La fonction TS_IndexColonne renvoie le numéro de la colonne passée en argument, qu’elle soit passée d’après son nom ou sa position. Cette fonction sera très utilisée par la suite, car nous accéderons à une colonne d’un tableau structuré principalement d’après son nom.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si ce nom est vide ou contient zéro, alors la dernière colonne du tableau est traitée.
La fonction renvoie : le numéro de la colonne concernée ou -1 en cas d’erreur.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_IndexColonne
(
TS As
Range, Colonne As
Variant
) As
Long
'------------------------------------------------------------------------------------------------
' Recherche le numéro de la colonne d'un tableau structuré.
'------------------------------------------------------------------------------------------------
' TS : La plage du Tableau Structuré.
' Colonne : le numéro de la colonne ou le nom de la colonne.
' Si vide ou 0 alors renvoie le numéro de la dernière colonne du tableau.
'------------------------------------------------------------------------------------------------
' Renvoie : Le numéro de la colonne dans le tableau, ou -1 si erreur.
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' S'il faut traiter la dernière colonne :
If
Colonne =
""
Or
Colonne =
0
Then
Colonne =
TS.ListObject.ListColumns.Count
' Retrouve le numéro de la colonne si c'est le nom qui est passé en argument,
' une erreur des déclanchée si le nom n'existe pas:
If
TypeName
(
Colonne) =
"String"
Then
Colonne =
TS.ListObject.ListColumns
(
Colonne).Index
' Contrôle la cohérence de la colonne passée en argument :
If
Colonne <
0
Or
Colonne >
TS.ListObject.ListColumns.Count
Then
TS_IndexColonne =
-
1
Else
TS_IndexColonne =
Colonne
End
If
' Fin du traitement :
Gest_Err
:
If
Err
.Number
<>
0
Or
TS_IndexColonne =
-
1
Then
TS_IndexColonne =
-
1
TS_Err_Description =
"La colonne ["
&
Colonne &
"] n'est pas incluse dans le tableau."
End
If
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-E. TS_IndexLigne▲
La fonction TS_IndexLigne contrôle la cohérence de la ligne passée en argument.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Ligne : le numéro de la ligne concernée. Si ce nombre est zéro, alors la dernière ligne du tableau structuré est traitée.
La fonction renvoie : le numéro de la ligne concernée ou -1 en cas d’erreur.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_IndexLigne
(
TS As
Range, Ligne As
Long
) As
Long
'------------------------------------------------------------------------------------------------
' Contrôle la cohérence du numéro de la ligne passée en argument dans un tableau structuré.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Ligne : le numéro de la ligne.
' Si vide ou 0 alors traite la dernière ligne du tableau.
'------------------------------------------------------------------------------------------------
' Renvoie : le numéro de la ligne dans le tableau ou -1 si erreur.
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' S'il faut traiter la dernière Ligne :
If
Ligne =
0
Then
Ligne =
TS.ListObject.ListRows.Count
' Contrôle la cohérence de la Ligne passée en argument :
If
Ligne <
0
Or
Ligne >
TS.ListObject.ListRows.Count
Then
TS_IndexLigne =
-
1
Else
TS_IndexLigne =
Ligne
End
If
' Fin du traitement :
Gest_Err
:
If
Err
.Number
<>
0
Or
TS_IndexLigne =
-
1
Then
TS_IndexLigne =
-
1
TS_Err_Description =
"La Ligne ["
&
Ligne &
"] n'est pas incluse dans le tableau."
End
If
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-F. TS_ChangerLibellé▲
La fonction TS_ChangerLibellé affiche un libellé personnalisé à la place du nom d’une colonne d’un tableau structuré, sans modifier le nom de cette colonne qui restera celui exploité dans les traitements.
Cette notion est très utile, car elle permet :
- de conserver un nom court pour désigner la colonne qui sera utilisée dans les traitements et les formules tout en affichant un texte plus explicite pour les utilisateurs ;
- d’éviter les caractères spéciaux dans le nom des colonnes (voir la remarque ci-dessous) ;
- d’adapter le libellé affiché au souhait de l’utilisateur sans avoir à modifier le code déjà écrit ;
- de faciliter le portage d’une application dans une autre langue.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si ce nom est vide ou contient zéro, alors la dernière colonne du tableau est traitée ;
- Libellé : le libellé à afficher à la place du nom ou vide pour restaurer le nom d'origine.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour modifier l’affichage des colonnes « Nom », « Prénom » et « Note » du tableau structuré nommé « TS_Eleves », en forçant un retour à la ligne par vbCrLf
:
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_ChangerLibellé
(
Tableau, "Nom"
, "Nom de"
&
vbCrLf
&
"l'élève"
)
Call
TS_ChangerLibellé
(
Tableau, "Prénom"
, "Prénom de"
&
vbCrLf
&
"l'élève"
)
Call
TS_ChangerLibellé
(
Tableau, 0
, "1ère"
&
vbCrLf
&
"Note"
)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : pour simplifier vos traitements, nommez vos colonnes en évitant les caractères spéciaux « arobase, dièse, tabulation, saut de ligne, virgule, point, crochets, apostrophe, ... », qui nécessitent dans les formules l’usage de crochets supplémentaires ou d'être précédés d'une apostrophe.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_ChangerLibellé
(
TS As
Range, _
ByVal
Colonne As
Variant
, _
Libellé As
String
) As
Boolean
'------------------------------------------------------------------------------------------------
' Change l'affichage de l'en-tête dans un Tableau Structuré, mais pas son nom.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne
' Si vide ou 0 alors prend la dernière colonne du Tableau Structuré.
' Libellé : le libellé à afficher à la place du nom du champ, ou vide pour le nom d'origine.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur) :
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Affiche l'en-tête et change le libellé :
TS.ListObject.ShowHeaders
=
True
If
Libellé <>
""
Then
TS.ListObject.HeaderRowRange
(
Colonne).NumberFormat
=
"0;"""";"""";"""
&
Libellé &
""""
Else
TS.ListObject.HeaderRowRange
(
Colonne).NumberFormat
=
"@"
End
If
TS_ChangerLibellé =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-G. TS_EffacerUneLigne▲
La fonction TS_EffacerUneLigne efface le contenu d’une ligne dans un tableau structuré, mais ne supprime pas la ligne.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Ligne : le numéro de la ligne concernée. Si ce nombre est zéro, alors la dernière ligne du tableau structuré est effacée.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour effacer la dernière ligne du tableau structuré nommé « TS_Eleves » :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_EffacerUneLigne
(
TS:=
Tableau, Ligne:=
0
)
End
Sub
'------------------------------------------------------------------------------------------------
Ou :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Call
TS_EffacerUneLigne
(
Range
(
"TS_Eleves"
), 0
)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_EffacerUneLigne
(
TS As
Range, ByVal
Ligne As
Long
) As
Boolean
'------------------------------------------------------------------------------------------------
' Efface le contenu d'une ligne dans un Tableau Structuré, mais ne la supprime pas, même si elle est masquée.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Ligne : la position de la ligne à effacer dans le tableau.
' Si 0 alors efface la dernière ligne du tableau.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si l'effacement a été réalisé.
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Contrôle la cohérence de la ligne passée en argument :
Ligne =
TS_IndexLigne
(
TS, Ligne)
If
Ligne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Efface la ligne :
Select
Case
TS.ListObject.ListRows.Count
Case
Is
>
1
TS.ListObject.DataBodyRange.Rows
(
Ligne).Clear
Case
1
Dim
i As
Long
For
i =
1
To
TS.ListObject.ListColumns.Count
TS.ListObject.DataBodyRange
(
1
, i).Clear
Next
i
End
Select
TS_EffacerUneLigne =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-H. TS_EffacerToutesLignes▲
La fonction TS_EffacerToutesLignes efface le contenu d’un tableau structuré, mais ne le supprime pas.
Son argument est :
- TS : la plage (de type Range) qui représente le tableau structuré.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour effacer le contenu du tableau structuré nommé « TS_Eleves » :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_EffacerToutesLignes
(
Tableau)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_EffacerToutesLignes
(
TS As
Range) As
Boolean
'------------------------------------------------------------------------------------------------
' Efface le contenu de toutes les lignes dans un Tableau Structuré, mais ne les supprime pas,
' même si elles sont masquées.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Efface toutes les lignes du tableau :
Select
Case
TS.ListObject.ListRows.Count
Case
Is
>
1
TS.ListObject.DataBodyRange.Clear
Case
1
Dim
i As
Long
For
i =
1
To
TS.ListObject.ListColumns.Count
TS.ListObject.DataBodyRange
(
1
, i).Clear
Next
i
End
Select
TS_EffacerToutesLignes =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-I. TS_AjouterUneLigne▲
La fonction TS_AjouterUneLigne ajoute une ligne dans un tableau structuré.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Ligne : (facultatif) la position où ajouter une ligne. Si ce nombre est zéro (valeur par défaut), alors une ligne est ajoutée à la suite du tableau structuré.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour ajouter une ligne en deuxième position puis à la fin du tableau structuré nommé « TS_Eleves » :
‘------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
‘------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_AjouterUneLigne
(
TS:=
Tableau, Ligne:=
2
)
Call
TS_AjouterUneLigne
(
TS:=
Tableau, Ligne:=
0
)
End
Sub
‘------------------------------------------------------------------------------------------------
Remarque : avant d’ajouter une ligne au tableau, il convient de mémoriser les éventuels filtres existants et de les supprimer, puis de les restaurer à la fin du traitement. Nous verrons cela en détail dans un autre chapitre.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_AjouterUneLigne
(
TS As
Range, Optional
ByVal
Ligne As
Long
=
0
) As
Boolean
'------------------------------------------------------------------------------------------------
' Ajoute une ligne dans un Tableau Structuré (même si elle est masquée).
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Ligne : la position où ajouter la ligne dans le tableau.
' Si 0 alors ajoute la ligne à la suite du tableau.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si une ligne a été ajoutée.
'------------------------------------------------------------------------------------------------
Dim
MesFiltres As
Variant
Dim
Anc_ScreenUpdating As
Boolean
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Contrôle la cohérence de la ligne passée en argument :
If
Ligne <>
0
Then
Ligne =
TS_IndexLigne
(
TS, Ligne)
If
Ligne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
End
If
' Bloque la mise à jour de l'écran :
Anc_ScreenUpdating =
Application.ScreenUpdating
Application.ScreenUpdating
=
False
' Mémorise les éventuels filtres et les retire :
Call
TS_Filtres_Mémoriser
(
TS, MesFiltres)
Call
TS_Filtres_Effacer
(
TS)
Select
Case
Ligne
Case
0
' Si le tableau est vierge alors il faut l'initialiser :
If
TS.ListObject.ListRows.Count
=
0
Then
Set
TS =
TS.ListObject.ListRows.Add.Range
Else
' Ajoute une ligne en bas du tableau:
TS.ListObject.ListRows.Add
End
If
TS_AjouterUneLigne =
True
Case
1
To
TS.ListObject.ListRows.Count
' Ajoute dans le tableau
TS.ListObject.ListRows.Add
Ligne
TS_AjouterUneLigne =
True
End
Select
' Restaure les filtres et l'affichage :
Call
TS_Filtres_Restaurer
(
TS, MesFiltres)
Application.ScreenUpdating
=
Anc_ScreenUpdating
' Fin du traitement :
Gest_Err :
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
‘------------------------------------------------------------------------------------------------
II-J. TS_SupprimerUneLigne▲
La fonction TS_SupprimerUneLigne supprime une ligne dans un tableau structuré.
Une fois toutes les lignes supprimées, il ne reste qu’une ligne vide sous l’en-tête. En VBA, le tableau sera initialisé lors de l’ajout d’une première ligne, voir la fonction TS_AjouterUneLigne.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Ligne : le numéro de la ligne à supprimer. Si ce nombre est zéro, alors la dernière ligne du tableau structuré est supprimée.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour supprimer la dernière ligne du tableau structuré nommé « TS_Eleves » :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_SupprimerUneLigne
(
Tableau, 0
)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : avant de supprimer une ligne au tableau, il convient de mémoriser les éventuels filtres existants et de les supprimer, puis de les restaurer à la fin du traitement. Nous verrons cela en détail dans un autre chapitre.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_SupprimerUneLigne
(
TS As
Range, ByVal
Ligne As
Long
) As
Boolean
'------------------------------------------------------------------------------------------------
' Supprime une ligne dans un Tableau Structuré, même si elle est masquée.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Ligne : la position de la ligne à supprimer dans le tableau.
' Si 0 alors supprime la dernière ligne du tableau.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si une ligne a été supprimée.
'------------------------------------------------------------------------------------------------
Dim
MesFiltres As
Variant
Dim
Anc_ScreenUpdating As
Boolean
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Contrôle la cohérence de la ligne passée en argument :
Ligne =
TS_IndexLigne
(
TS, Ligne)
If
Ligne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Bloque la mise à jour de l'écran :
Anc_ScreenUpdating =
Application.ScreenUpdating
Application.ScreenUpdating
=
False
' Mémorise les éventuels filtres et les retire :
Call
TS_Filtres_Mémoriser
(
TS, MesFiltres)
Call
TS_Filtres_Effacer
(
TS)
' Supprime la ligne:
TS.ListObject.ListRows
(
Ligne).Delete
TS_SupprimerUneLigne =
True
' Restaure les filtres et l'affichage :
Call
TS_Filtres_Restaurer
(
TS, MesFiltres)
Application.ScreenUpdating
=
Anc_ScreenUpdating
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-K. TS_SupprimerToutesLignes▲
La fonction TS_SupprimerToutesLignes supprime toutes les lignes dans un tableau structuré, mais ne le supprime pas (pour cela utilisez la fonction TS_SupprimerLeTableau), il ne reste qu'une ligne vide sous l'en-tête. En VBA, le tableau sera initialisé lors de l’ajout d’une première ligne, voir la fonction TS_AjouterUneLigne.
Son argument est :
- TS : la plage (de type Range) qui représente le tableau structuré.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour supprimer toutes les lignes du tableau structuré nommé « TS_Eleves » :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_SupprimerToutesLignes
(
Tableau)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_SupprimerToutesLignes
(
TS As
Range) As
Boolean
'------------------------------------------------------------------------------------------------
' Supprime toutes les lignes d'un Tableau Structuré.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Efface les éventuels filtres :
Call
TS_Filtres_Effacer
(
TS)
' Supprime toutes les lignes :
TS.ListObject.DataBodyRange.Delete
TS_SupprimerToutesLignes =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-L. TS_AjouterUneColonne▲
La fonction TS_AjouterUneColonne ajoute une colonne dans un tableau structuré.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le numéro de la colonne ou le nom de la colonne après lequel insérer une nouvelle colonne. Si vide ou 0, alors ajoute une colonne à la fin du tableau structuré ;
- Nom : le nom de la nouvelle colonne. Si vide, alors Excel attribuera un nom d'office et « Nom » sera renseigné.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour ajouter une colonne nommée « Note2 » à la fin du tableau structuré nommé « TS_Eleves », puis lui changer son libellé en « 2e Note » :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_AjouterUneColonne
(
TS:=
Tableau, Colonne:=
0
, Nom:=
"Note2"
)
Call
TS_ChangerLibellé
(
TS:=
Tableau, Colonne:=
0
, Libellé:=
"2ème"
&
vbCrLf
&
"Note"
)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_AjouterUneColonne
(
TS As
Range, _
ByVal
Colonne As
Variant
, _
Nom As
String
) As
Boolean
'------------------------------------------------------------------------------------------------
' Ajoute une colonne dans un tableau Structuré.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne où inserer une nouvelle colonne.
' Si vide ou 0 alors ajoute une colonne à la fin du tableau.
' Nom = Le nom de la nouvelle colonne.
' Si vide alors Excel attribuera un nom d'office et Nom sera alimenté.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si une colonne a été ajoutée.
'------------------------------------------------------------------------------------------------
Dim
i As
Integer
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
TS.ListObject.ShowHeaders
=
True
' Si le nom existe déjà alors il n'est pas possible de créer la nouvelle colonne :
For
i =
1
To
TS.ListObject.ListColumns.Count
If
TS.ListObject.ListColumns
(
i).Name
=
Nom Then
_
Err
.Raise
vbObjectError
, , "La colonne ["
&
Nom &
"] existe déjà dans le tableau."
Next
i
' S'il faut traiter la dernière colonne :
If
Colonne =
""
Then
Colonne =
0
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
If
Colonne <>
0
Then
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
End
If
Select
Case
Colonne
Case
0
' Ajoute une colonne à la suite du tableau :
TS.ListObject.ListColumns.Add.Name
=
Nom
Nom =
TS.ListObject.ListColumns
(
TS.ListObject.ListColumns.Count
).Name
TS.ListObject.HeaderRowRange
(
TS.ListObject.ListColumns.Count
).NumberFormat
=
"@"
TS_AjouterUneColonne =
True
Case
Else
' Ajoute une colonne dans le tableau :
TS.ListObject.ListColumns.Add
(
Colonne).Name
=
Nom
Nom =
TS.ListObject.ListColumns
(
TS.ListObject.ListColumns
(
Colonne)).Name
TS.ListObject.HeaderRowRange
(
Colonne).NumberFormat
=
"@"
TS_AjouterUneColonne =
True
End
Select
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-M. TS_SupprimerUneColonne▲
La fonction TS_SupprimerUneColonne supprime une colonne dans un tableau structuré.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le numéro de la colonne ou le nom de la colonne à supprimer. Si vide ou 0, alors supprime la dernière colonne du tableau structuré.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Remarque : supprimer toutes les colonnes d’un tableau structuré équivaut à le supprimer, voir la fonction TS_SupprimerLeTableau.
Exemple pour supprimer la colonne « Note2 » du tableau structuré nommé « TS_Eleves » :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_SupprimerUneColonne
(
TS:=
Tableau, Colonne:=
"Note2"
)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_SupprimerUneColonne
(
TS As
Range, ByVal
Colonne As
Variant
) As
Boolean
'------------------------------------------------------------------------------------------------
' Supprime une colonne dans un tableau Structuré.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne à supprimer.
' Si vide ou 0 alors supprime la dernière colonne du Tableau Structuré.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si une colonne a été supprimée.
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur) :
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Supprime une colonne dans le tableau :
TS.ListObject.ListColumns
(
Colonne).Delete
TS_SupprimerUneColonne =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-N. TS_OptionsStyle▲
La fonction TS_OptionsStyle définit les options de style du tableau structuré à afficher ou masquer.
Soit l’équivalant de l’onglet « Création » du ruban lorsque le tableau est sélectionné :
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Bouton_Filtre : (facultatif)
True
pour activer l’option,False
pour désactiver l’option ; - Ligne_Entête : (facultatif)
True
pour activer l’option,False
pour désactiver l’option ; - Ligne_Totaux : (facultatif)
True
pour activer l’option,False
pour désactiver l’option ; - Ligne_Bandes : (facultatif)
True
pour activer l’option,False
pour désactiver l’option ; - Colonne_Bandes : (facultatif)
True
pour activer l’option,False
pour désactiver l’option ; - Première_Colonne : (facultatif)
True
pour activer l’option,False
pour désactiver l’option ; - Dernière_Colonne : (facultatif)
True
pour activer l’option,False
pour désactiver l’option ; - StyleTableau : (facultatif) le nom du style du tableau structuré. Si l’argument est vide, alors le tableau sera sans style, s’il n’est pas renseigné le style ne sera pas modifié.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour masquer le bouton de filtre du tableau structuré nommé « TS_Eleves », afficher la ligne des totaux et le passer en style « moyen 6 » sans modifier les autres options :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_OptionsStyle
(
TS:=
Tableau, Bouton_Filtre:=
False
, Ligne_Totaux:=
True
, StyleTableau:=
"TableStyleMedium6"
)
End
Sub
'------------------------------------------------------------------------------------------------
Ou sans nommer les arguments (instruction plus courte, mais moins intuitive à relire) :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_OptionsStyle
(
Tableau, False
, , True
, , , , , "TableStyleMedium6"
)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_OptionsStyle
(
TS As
Range, _
Optional
Bouton_Filtre As
Integer
=
1
, _
Optional
Ligne_Entête As
Integer
=
1
, _
Optional
Ligne_Totaux As
Integer
=
1
, _
Optional
Ligne_Bandes As
Integer
=
1
, _
Optional
Colonne_Bandes As
Integer
=
1
, _
Optional
Première_Colonne As
Integer
=
1
, _
Optional
Dernière_Colonne As
Integer
=
1
, _
Optional
StyleTableau As
String
=
"/"
) As
Boolean
'------------------------------------------------------------------------------------------------
' Paramétrage des options de style du Tableau Structuré.
'------------------------------------------------------------------------------------------------
' TS : Le Tableau Structuré.
' Options : Si ignorée = l'option est laissée dans son état.
' Si True = l'option est passée à True.
' Si False = l'option est passée à False.
' StyleTableau : "" pour effacer ou le nom existant.
'------------------------------------------------------------------------------------------------
' Renvoie : Vrai si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
On
Error
GoTo
Gest_Err
Err
.Clear
Select
Case
Ligne_Entête
Case
True
: TS.ListObject.ShowHeaders
=
True
Case
False
: TS.ListObject.ShowHeaders
=
False
TS.ListObject.ShowAutoFilterDropDown
=
False
End
Select
Select
Case
Ligne_Totaux
Case
True
: TS.ListObject.ShowTotals
=
True
Case
False
: TS.ListObject.ShowTotals
=
False
End
Select
Select
Case
Ligne_Bandes
Case
True
: TS.ListObject.ShowTableStyleRowStripes
=
True
Case
False
: TS.ListObject.ShowTableStyleRowStripes
=
False
End
Select
Select
Case
Première_Colonne
Case
True
: TS.ListObject.ShowTableStyleFirstColumn
=
True
Case
False
: TS.ListObject.ShowTableStyleFirstColumn
=
False
End
Select
Select
Case
Dernière_Colonne
Case
True
: TS.ListObject.ShowTableStyleLastColumn
=
True
Case
False
: TS.ListObject.ShowTableStyleLastColumn
=
False
End
Select
Select
Case
Colonne_Bandes
Case
True
: TS.ListObject.ShowTableStyleColumnStripes
=
True
Case
False
: TS.ListObject.ShowTableStyleColumnStripes
=
False
End
Select
Select
Case
Bouton_Filtre
Case
True
: TS.ListObject.ShowHeaders
=
True
TS.ListObject.ShowAutoFilterDropDown
=
True
Case
False
: TS.ListObject.ShowAutoFilterDropDown
=
False
End
Select
If
StyleTableau <>
"/"
Then
Sheets
(
TS.Parent.Name
).ListObjects
(
TS.ListObject.DisplayName
).TableStyle
=
StyleTableau
End
If
' Fin du traitement:
Gest_Err
:
If
Err
.Number
=
0
Then
TS_OptionsStyle =
True
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
II-O. TS_DéfinirTotaux▲
La fonction TS_DéfinirTotaux définit le calcul pour la ligne des totaux d'une colonne d’un tableau structuré.
Si l’option d’affichage de la ligne des totaux n’était pas active, elle l’est automatiquement par l’appel à cette fonction.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si ce nom est vide ou contient zéro, alors la dernière colonne du tableau est traitée ;
- TypeCalcul : une valeur de l’énumération XlTotalsCalculation
- xlTotalsCalculationAverage : moyenne,
- xlTotalsCalculationCount : décompte des cellules non vides,
- xlTotalsCalculationCountNums : décompte des cellules contenant des valeurs numériques,
- xlTotalsCalculationMax : valeur maximale dans la liste,
- xlTotalsCalculationMin : valeur minimale dans la liste,
- xlTotalsCalculationNone : aucun calcul,
- xlTotalsCalculationStdDev : valeur écart-type,
- xlTotalsCalculationSum : somme de toutes les valeurs de la colonne de liste.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour compter le nombre d’élèves et la note moyenne :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_DéfinirTotaux
(
Tableau, "Nom"
, xlTotalsCalculationCount)
Call
TS_DéfinirTotaux
(
Tableau, "Note"
, xlTotalsCalculationAverage)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : nous étudierons dans un autre chapitre la mise en forme des cellules.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_DéfinirTotaux
(
TS As
Range, _
ByVal
Colonne As
Variant
, _
TypeCalcul As
XlTotalsCalculation) As
Boolean
'------------------------------------------------------------------------------------------------
' Définit le type de calcul pour la ligne des totaux d'une colonne.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne.
' Si vide ou 0 alors prend la dernière colonne du Tableau Structuré.
' TypeCalcul : Type de calcul à appliquer suivant l'énumération.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
On
Error
GoTo
Gest_Err
Err
.Clear
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Affiche les totaux et pose le calcul :
TS.ListObject.ShowTotals
=
True
TS.ListObject.ListColumns
(
Colonne).TotalsCalculation
=
TypeCalcul
TS_DéfinirTotaux =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
III. Trier, filtrer les données d’un tableau structuré▲
Les différentes fonctions de ce chapitre permettent de trier et filtrer les données d’un tableau structuré.
III-A. TS_TrierUneColonne▲
La fonction TS_TrierUneColonne trie la colonne passée en argument, en tenant compte de la casse.
Les lignes masquées sont ignorées dans le tri.
Si l’option d’affichage de la ligne bouton de filtre n’était pas active elle le devient automatiquement par l’appel à cette fonction.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si ce nom est vide ou zéro, alors la dernière colonne du tableau est traitée ;
- Méthode : (facultatif) le paramètre de tri des données de l’énumération XlSortOn
- xlSortOnValues : (par défaut) trie suivant les valeurs,
- xlSortOnCellColor : trie suivant la couleur des cellules,
- xlSortOnFontColor : trie suivant la couleur de police ; - Ordre : (facultatif) l’ordre de tri de l’énumération XlSortOrder
- xlAscending : (par défaut) ordre croissant,
- xlDescending : ordre décroissant ; - EffacerAncienTri : (facultatif) si
True
(par défaut) alors efface l'ancien tri, siFalse
alors ajoute le tri à celui existant.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour trier les élèves par nom et prénom, dans l’ordre croissant :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_TrierUneColonne
(
TS:=
Tableau, Colonne:=
"Nom"
, Méthode:=
xlSortOnValues, Ordre:=
xlAscending, _
EffacerAncienTri:=
True
)
Call
TS_TrierUneColonne
(
TS:=
Tableau, Colonne:=
"Prénom"
, Méthode:=
xlSortOnValues, Ordre:=
xlAscending, _
EffacerAncienTri:=
False
)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_TrierUneColonne
(
TS As
Range, _
ByVal
Colonne As
Variant
, _
Optional
Méthode As
XlSortOn =
xlSortOnValues, _
Optional
Ordre As
XlSortOrder =
xlAscending, _
Optional
EffacerAncienTri As
Boolean
=
True
) As
Boolean
'------------------------------------------------------------------------------------------------
' Trie une colonne d'un Tableau Structuré. La casse est prise en compte.
' Attention, les lignes masquées sont ignorées dans le tri.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne à trier.
' Si 0 ou vide alors trie la dernière colonne du Tableau Structuré.
' Paramètre de tri des données:
' xlSortOnValues = les valeurs.
' xlSortOnCellColor = couleur de cellule.
' xlSortOnFontColor = couleur de police.
' Ordre du tri :
' xlAscending = Croissant.
' xlDescending = Décroissant.
' EffacerAncienTri : Si Vrai alors efface l'ancien tri, sinon ajoute le tri à celui existant.
'-----------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'----------------------------------------------------------------------------------------
' Exemple d'utilisation pour trier par ordre croissant un tableau par nom et prénom (en deux étapes):
' TS_TrierUneColonne(Range("TS_x"), "Nom")
' TS_TrierUneColonne(Range("TS_x"),"Prénom",,, False)
'----------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Retrouve le nom de la colonne :
Colonne =
TS.ListObject.ListColumns
(
Colonne).Name
' Force l'affichage de l'option Bouton de filtre :
TS.ListObject.ShowHeaders
=
True
TS.ListObject.ShowAutoFilterDropDown
=
True
' S'il faut effacer tous les anciens tris ou juste
' le tri sur la colonne concerné pour pouvoir en mettre un nouveau :
If
EffacerAncienTri =
True
Then
Call
TS_EffacerTri
(
TS, ""
)
Else
Call
TS_EffacerTri
(
TS, Colonne)
End
If
' Pose le tri sur la colonne :
TS.ListObject.Sort.SortFields.Add
Range
(
TS.ListObject.DisplayName
&
"["
&
Colonne &
"]"
), Méthode, Ordre
TS.ListObject.Sort.Apply
TS_TrierUneColonne =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
If
TS_TrierUneColonne =
False
And
(
Colonne <
0
Or
Colonne >
TS.ListObject.ListColumns.Count
) Then
_
TS_Err_Description =
"La colonne ["
&
Colonne &
"] n'est pas incluse dans le tableau."
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
III-B. TS_EffacerTri▲
La fonction TS_EffacerTri efface le tri de la colonne passée en argument ou de toutes les colonnes.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si cet argument vaut zéro alors la dernière colonne du tableau est traitée. S’il est vide alors tous les tris sont effacés.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour effacer tous les tris du tableau des élèves :
‘------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
‘------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_EffacerTri
(
TS:=
Tableau, Colonne:=
""
)
End
Sub
‘------------------------------------------------------------------------------------------------
Remarque : le fait d’effacer les tris n’a pas d’incidence sur l’ordre d’affichage du tableau structuré.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_EffacerTri
(
TS As
Range, ByVal
Colonne As
Variant
) As
Boolean
'------------------------------------------------------------------------------------------------
' Efface le tri d'une colonne (ou de toutes les colonnes) d'un Tableau Structuré.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne à effacer le tri.
' Si 0 alors efface le tri de la dernière colonne du Tableau Structuré.
' Si vide alors efface tous les tris.
'-----------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'----------------------------------------------------------------------------------------
Dim
i As
Integer
, x As
Integer
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' S'il faut effacer tous les tris :
If
Colonne =
""
Then
TS.ListObject.Sort.SortFields.Clear
Else
' S'il faut effacer le tri d'une colonne :
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Retrouve le nom de la colonne:
Colonne =
TS.ListObject.ListColumns
(
Colonne).Name
' boucle sur les tris existants:
For
i =
1
To
TS.ListObject.Sort.SortFields.Count
' Récupère le numéro de la colonne concernée :
x =
TS.ListObject.Sort.SortFields
(
i).Key.Column
-
TS.Column
+
1
' Si c'est la colonne à traiter alors supprime le tri :
If
UCase
(
TS.ListObject.ListColumns
(
x).Name
) =
UCase
(
Colonne) Then
TS.ListObject.Sort.SortFields
(
i).Delete
TS.ListObject.Sort.Apply
Exit
For
End
If
Next
i
End
If
TS_EffacerTri =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
III-C. TS_Filtres_Poser▲
La fonction TS_Filtres_Poser pose un filtre sur une colonne du tableau structuré.
Le filtre peut contenir un ou deux critères.
Les critères ne sont pas sensibles à la casse.
Si l’option d’affichage de l’option bouton de filtre n’était pas active, elle le devient automatiquement par l’appel à cette fonction.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si ce nom est vide ou zéro, alors la dernière colonne du tableau est traitée ;
- Critère1 : le premier critère, à laisser vide pour effacer le filtre ;
- Opérateur : (facultatif) l'opérateur logique s’il y a deux critères de l’énumération XlAutoFilterOperator(1)
- xlAnd : opérateur logique « Et »,
- XlOr : opérateur logique « Ou » ; - Critère2 : (facultatif) le second critère.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour filtrer dans le tableau des élèves les notes supérieures à 10, les noms commençant par les lettres « T » ou « C », les prénoms « Alex » ou « Sarah » ou « Toto » :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_Filtres_Poser
(
Tableau, "Note"
, ">10"
)
Call
TS_Filtres_Poser
(
Tableau, "Nom"
, "=t*"
, xlOr, "=c*"
)
Call
TS_Filtres_Poser
(
Tableau, "Prénom"
, Array
(
"alex"
, "sarah"
, "toto"
))
End
Sub
'------------------------------------------------------------------------------------------------
Exemple pour supprimer tous ces filtres :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_Filtres_Poser
(
Tableau, "Nom"
, ""
)
Call
TS_Filtres_Poser
(
Tableau, "prénom"
, ""
)
Call
TS_Filtres_Poser
(
Tableau, "note"
, ""
)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : pour supprimer les filtres d’une colonne ou du tableau structuré, vous pouvez aussi utiliser la fonction TS_Filtres_Effacer, voir ci-après.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_Filtres_Poser
(
TS As
Range, _
ByVal
Colonne As
Variant
, _
ByVal
Critère1 As
Variant
, _
Optional
Opérateur As
XlAutoFilterOperator, _
Optional
Critère2 As
String
=
""
) As
Boolean
'------------------------------------------------------------------------------------------------
' Pose un filtre sur une colonne d'un Tableau Structuré.
' Pour plus d'informations :
' https://docs.microsoft.com/fr-fr/office/vba/api/excel.range.autofilter
' https://excel.developpez.com/faq/?page=Filtre
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne.
' Si vide ou 0 alors prend la dernière colonne du Tableau Structuré.
' Critère1 : le premier critère. Laissez à vide pour effacer le filtre
' Opérateur : l'opérateur logique.
' Critère2 : le second critère.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
On
Error
GoTo
Gest_Err
Err
.Clear
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Si c'est un Array qui est passé en Critère 1 :
If
IsArray
(
Critère1) =
True
Then
TS.ListObject.Range.AutoFilter
Field:=
Colonne, Criteria1:=
Array
(
Critère1), Operator:=
xlFilterValues
' Si les critères sont des chaînes de caractères:
Else
' S'il faut effacer un filtre :
If
Critère1 =
""
Then
Critère1 =
Null
' S'il n'y a qu'un seul critère :
If
Critère2 =
""
Then
TS.ListObject.Range.AutoFilter
Field:=
Colonne, Criteria1:=
Critère1
' S'il y a deux critères :
Else
TS.ListObject.Range.AutoFilter
Field:=
Colonne, Criteria1:=
Critère1, Operator:=
Opérateur, Criteria2:=
Critère2
End
If
End
If
TS_Filtres_Poser =
True
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
III-D. TS_Filtres_Effacer▲
La fonction TS_Filtres_Effacer efface le filtre d’une colonne ou de toutes les colonnes d’un tableau structuré.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si cet argument vaut zéro, alors la dernière colonne du tableau est traitée. S’il est non renseigné ou vide, alors tous les filtres sont effacés.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour effacer tous les filtres du tableau des élèves :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_Filtres_Effacer
(
Tableau)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_Filtres_Effacer
(
TS As
Range, Optional
Colonne As
Variant
) As
Boolean
'------------------------------------------------------------------------------------------------
' Efface le filtre d'une colonne ou de toutes les colonnes d'un Tableau Structuré.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne à effacer le filtre.
' Si 0 alors efface le filtre de la dernière colonne du Tableau Structuré.
' Si vide ou non renseigné alors efface tous les filtres.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
On
Error
GoTo
Gest_Err
Err
.Clear
' S'il faut effacer tous les filtres :
If
IsMissing
(
Colonne) =
True
Then
Colonne =
""
If
Colonne =
""
Then
TS.ListObject.AutoFilter.ShowAllData
TS_Filtres_Effacer =
True
Else
' S'il faut effacer le filtre d'une colonne:
If
TS_Filtres_Poser
(
TS, Colonne, ""
) =
False
Then
Err
.Raise
TS_Err_Number, , TS_Err_Description
Else
TS_Filtres_Effacer =
True
End
If
End
If
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
III-E. TS_Filtres_Mémoriser▲
La fonction TS_Filtres_Mémoriser mémorise dans une variable les filtres d’un tableau structuré.
Ce traitement permettra de les restituer ultérieurement, voir la fonction TS_Filtres_Restaurer.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Mémoire : la mémoire à utiliser.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour mémoriser les filtres du tableau des élèves :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Dim
MesFiltres As
Variant
Call
TS_Filtres_Mémoriser
(
Tableau, MesFiltres)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_Filtres_Mémoriser
(
TS As
Range, Mémoire As
Variant
) As
Boolean
'-----------------------------------------------------------------------------------------------
' Mémorise les filtres d'un Tableau Structuré.
'-----------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Mémoire : la mémoire à utiliser.
'-----------------------------------------------------------------------------------------------
' Renvoie : vrai si le tableau a au moins un filtre actif.
'-----------------------------------------------------------------------------------------------
Dim
f As
Integer
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Boucle sur les filtres du tableau :
With
TS.ListObject.AutoFilter.Filters
ReDim
Mémoire
(
1
To
.Count
, 1
To
3
)
For
f =
1
To
.Count
With
.Item
(
f)
If
.On
Then
TS_Filtres_Mémoriser =
True
Mémoire
(
f, 1
) =
.Criteria1
If
.Operator
Then
Mémoire
(
f, 2
) =
.Operator
Mémoire
(
f, 3
) =
.Criteria2
End
If
End
If
End
With
Next
End
With
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
III-F. TS_Filtres_Restaurer▲
La fonction TS_Filtres_Restaurer restaure les filtres préalablement mémorisés dans une variable par la fonction TS_Filtres_Mémoriser.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Mémoire : la mémoire à utiliser.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour mémoriser les filtres du tableau des élèves, les effacer pour poser un nouveau filtre sur les notes supérieures à 10, afficher le nombre de ces notes, puis restaurer l’ancienne situation :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range, MesFiltres As
Variant
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_Filtres_Mémoriser
(
Tableau, MesFiltres)
Call
TS_Filtres_Effacer
(
Tableau)
Call
TS_Filtres_Poser
(
Tableau, "Note"
, ">10"
)
Call
TS_DéfinirTotaux
(
Tableau, "note"
, xlTotalsCalculationCountNums)
MsgBox
"Nombre de notes > 10"
Call
TS_Filtres_Restaurer
(
Tableau, MesFiltres)
Call
TS_OptionsStyle
(
Tableau, Ligne_Totaux:=
False
)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_Filtres_Restaurer
(
TS As
Range, Mémoire As
Variant
) As
Boolean
'-----------------------------------------------------------------------------------------------
' Restaure les filtres d'un Tableau Structuré préalablement mémorisés avec TS_Filtres_Mémoriser.
'-----------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Mémoire : la mémoire à utiliser.
'-----------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'-----------------------------------------------------------------------------------------------
Dim
f As
Integer
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
For
f =
1
To
UBound
(
Mémoire, 1
)
If
Not
IsEmpty
(
Mémoire
(
f, 1
)) Then
If
Mémoire
(
f, 2
) Then
TS.ListObject.Range.AutoFilter
Field:=
f, _
Criteria1:=
Mémoire
(
f, 1
), _
Operator:=
Mémoire
(
f, 2
), _
Criteria2:=
Mémoire
(
f, 3
)
Else
TS.ListObject.Range.AutoFilter
Field:=
f, Criteria1:=
Mémoire
(
f, 1
)
End
If
End
If
Next
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
If
Err
.Number
=
0
Then
TS_Filtres_Restaurer =
True
Err
.Clear
End
Function
'-----------------------------------------------------------------------------------------------
IV. Obtenir des informations sur les données d’un tableau structuré▲
Les deux premières fonctions de ce chapitre permettent d’obtenir des informations sur les dimensions d’un tableau structuré. Les deux suivantes renvoient des informations sur ses données.
IV-A. TS_Nombre_Lignes▲
La fonction TS_Nombre_Lignes renvoie le nombre de lignes du tableau structuré passé en argument.
Son argument est :
- TS : la plage (de type Range) qui représente le tableau structuré.
La fonction renvoie : le nombre de lignes du tableau structuré, donc zéro s’il est vide.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_Nombre_Lignes
(
TS As
Range) As
Long
'------------------------------------------------------------------------------------------------
TS_Nombre_Lignes =
TS.ListObject.ListRows.Count
End
Function
'-----------------------------------------------------------------------------------------------
IV-B. TS_Nombre_Colonnes▲
La fonction TS_Nombre_Colonnes renvoie le nombre de colonnes du tableau structuré passé en argument.
Son argument est :
- TS : la plage (de type Range) qui représente le tableau structuré.
La fonction renvoie : le nombre de colonnes du tableau structuré.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_Nombre_Colonnes
(
TS As
Range) As
Integer
'------------------------------------------------------------------------------------------------
TS_Nombre_Colonnes =
TS.ListObject.ListColumns.Count
End
Function
'-----------------------------------------------------------------------------------------------
IV-C. TS_ValeurColonne▲
La fonction TS_ValeurColonne renvoie la valeur du calcul pour la ligne des totaux d'une colonne d’un tableau structuré.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si ce nom est vide ou zéro alors la dernière colonne du tableau est traitée ;
- TypeCalcul : une valeur de l’énumération XlTotalsCalculation
- xlTotalsCalculationAverage : moyenne,
- xlTotalsCalculationCount : décompte des cellules non vides,
- xlTotalsCalculationCountNums : décompte des cellules contenant des valeurs numériques,
- xlTotalsCalculationMax : valeur maximale dans la liste,
- xlTotalsCalculationMin : valeur minimale dans la liste,
- xlTotalsCalculationStdDev : calcul l’écart-type standard,
- xlTotalsCalculationSum : somme,
- xlTotalsCalculationVar = (personalisé) nombre de cellules non numériques,
- xlTotalsCalculationNone = (personnalisé) nombre de cellules vides ; - CellulesVisiblesUniquement :
True
pour ne pour ne traiter que les cellules visibles,False
pour traiter toutes les cellules (y compris les filtrées et masquées).
La fonction renvoie : la valeur du calcul demandé ou Null
si erreur.
Exemple pour obtenir la note moyenne des élèves (y compris les éléments filtrés) :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Dim
Moyenne As
Double
Moyenne =
TS_ValeurColonne
(
Tableau, "Note"
, xlTotalsCalculationAverage, False
)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : vous pouvez aussi utiliser les fonctions d'Excel, par exemple :
Application.Evaluate
(
"=COUNTIF(Tableau[Note],"">10"")"
) : nombre de notes supérieures à 10.
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_ValeurColonne
(
TS As
Range, _
ByVal
Colonne As
Variant
, _
TypeCalcul As
XlTotalsCalculation, _
CellulesVisiblesUniquement As
Boolean
) As
Variant
'------------------------------------------------------------------------------------------------
' Renvoie la valeur d'une colonne.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne.
' Si vide ou 0 alors prend la dernière colonne du Tableau Structuré.
' TypeCalcul : Type de calcul à appliquer suivant l'énumération XlTotalsCalculation.
' xlTotalsCalculationAverage = Moyenne.
' xlTotalsCalculationCount = Nombre de cellules non vides.
' xlTotalsCalculationCountNums = Nombre de cellules avec des valeurs numériques.
' xlTotalsCalculationMax = Valeur numérique maximale.
' xlTotalsCalculationMin = Valeur numérique minimale.
' xlTotalsCalculationStdDev = Écart type standard.
' xlTotalsCalculationSum = Somme.
' xlTotalsCalculationVar = (Personalisé) Nombre de cellules non numériques.
' xlTotalsCalculationNone = (personnalisé) Nombre de cellules vides.
' CellulesVisiblesUniquement : VRAI pour ne calculer que les cellules visibles.
'------------------------------------------------------------------------------------------------
' Renvoie : la valeur concernée en prenant en compte les lignes filtrées ou non, au choix,
' ou Null si erreur (valeur non numérique).
'------------------------------------------------------------------------------------------------
Dim
CalculationNone As
Long
Dim
CalculationVar As
Long
Dim
PlageCalcul As
Range, Fx As
Integer
Dim
Ligne As
Long
On
Error
GoTo
Gest_Err
Err
.Clear
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Par défaut renvoyer Null :
TS_ValeurColonne =
Null
' S'il faut calculer les cellules visibles uniqement (non filtrées) pour les 7 opérateurs classiques,
' il est possible d'utiliser Application.Subtotal:
If
CellulesVisiblesUniquement =
True
Then
' Définit la plage de calcul :
Set
PlageCalcul =
TS.ListObject.ListColumns
(
Colonne).DataBodyRange
Select
Case
TypeCalcul
Case
xlTotalsCalculationAverage: Fx =
1
Case
xlTotalsCalculationCount: Fx =
3
Case
xlTotalsCalculationCountNums: Fx =
2
Case
xlTotalsCalculationMax: Fx =
4
Case
xlTotalsCalculationMin: Fx =
5
Case
xlTotalsCalculationStdDev: Fx =
7
Case
xlTotalsCalculationSum: Fx =
9
End
Select
If
Fx <>
0
Then
TS_ValeurColonne =
Application.Subtotal
(
Fx, PlageCalcul)
End
If
' S'il faut calculer toutes les cellules (y compris les filtrées) pour les 7 opérateurs
' classiques, et xlTotalsCalculationNone, il est possible d'utiliser les fonctions "Application":
If
CellulesVisiblesUniquement =
False
Then
' Définit la plage de calcul :
Set
PlageCalcul =
TS.ListObject.ListColumns
(
Colonne).DataBodyRange
Select
Case
TypeCalcul
Case
xlTotalsCalculationAverage: TS_ValeurColonne =
Application.Average
(
PlageCalcul)
Case
xlTotalsCalculationCount: TS_ValeurColonne =
Application.CountA
(
PlageCalcul)
Case
xlTotalsCalculationCountNums: TS_ValeurColonne =
Application.Count
(
PlageCalcul)
Case
xlTotalsCalculationMax: TS_ValeurColonne =
Application.Max
(
PlageCalcul)
Case
xlTotalsCalculationMin: TS_ValeurColonne =
Application.Min
(
PlageCalcul)
Case
xlTotalsCalculationStdDev: TS_ValeurColonne =
Application.StDev
(
PlageCalcul)
Case
xlTotalsCalculationSum: TS_ValeurColonne =
Application.Sum
(
PlageCalcul)
Case
xlTotalsCalculationNone: TS_ValeurColonne =
Application.CountBlank
(
PlageCalcul)
End
Select
End
If
' Pour les cas personnalisés :
' xlTotalsCalculationVar = Nombre de cellules non numériques.
' xlTotalsCalculationNone = Nombre de cellules vides.
If
IsNull
(
TS_ValeurColonne) Then
' Boucle sur les données de la colonne :
For
Ligne =
1
To
TS.ListObject.ListRows.Count
' Traitement de la cellule de la colonne :
With
TS.ListObject.DataBodyRange
(
Ligne, Colonne)
' Si la ligne est visible ou non (sa hauteur vaut 0) et suivant le cas demandé :
If
(
.Height
<>
(
CellulesVisiblesUniquement Xor
-
1
)) Then
' Si la ligne est non vide :
If
.Value
<>
""
Then
' Si la cellule n'est pas numérique :
If
IsNumeric
(
.Value
) =
False
Then
CalculationVar =
CalculationVar +
1
Else
' Si la cellule est vide:
CalculationNone =
CalculationNone +
1
End
If
End
If
End
With
Next
Ligne
' Renvoie le résultat suivant le calcul demandé:
Select
Case
TypeCalcul
Case
xlTotalsCalculationNone
TS_ValeurColonne =
CalculationNone
Case
xlTotalsCalculationVar
TS_ValeurColonne =
CalculationVar
End
Select
End
If
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'-----------------------------------------------------------------------------------------------
IV-D. TS_InfoCellule▲
La fonction TS_InfoCellule renvoie des informations sur une cellule d’un tableau structuré, même si elle est masquée.
La cellule concernée est identifiée par sa position dans le tableau structuré. La première colonne vaut 1 (il est conseillé d’utiliser le nom de la colonne au lieu de son numéro), la première ligne vaut 1.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si ce nom est vide ou zéro alors la dernière colonne du tableau est traitée ;
- Ligne : la ligne concernée. Si cette valeur est zéro alors prend la dernière ligne du tableau structuré. Si elle est inférieure à zéro alors prend la ligne des totaux ;
- TypeInfo : (facultatif) le type d’information désirée défini par une valeur de l’énumération personnelle Enum_InfoTS déclarée en en-tête du module
- TS_Valeur : (par défaut) renvoie la valeur de la cellule,
- TS_Formule : renvoie la formule de la cellule,
- TS_CouleurTexte : renvoie la couleur de texte de la cellule,
- TS_CouleurFond : renvoie la couleur de fond de la cellule,
- TS_Gras : renvoieTrue
si la cellule est en gras etFalse
dans le cas contraire,
- TS_Italique : renvoieTrue
si la cellule est en italique etFalse
dans le cas contraire,
- TS_Visible : renvoieTrue
si la cellule est visible etFalse
dans le cas contraire,
- TS_Format : renvoie le format de la cellule,
- TS_Commentaire : renvoie le commentaire de la cellule,
- TS_LienHypertexte : renvoie le lien hypertexte de la cellule.
La fonction renvoie l’information sur la cellule suivant l’option demandée, même si elle est masquée.
Exemple pour boucler sur les lignes du tableau structuré « TS_Eleves » et afficher dans le débogueur leur note :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Dim
Ligne As
Long
For
Ligne =
1
To
TS_Nombre_Lignes
(
Tableau)
Debug.Print
TS_InfoCellule
(
Tableau, "Note"
, Ligne, TS_Valeur)
Next
Ligne
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_InfoCellule
(
TS As
Range, _
ByVal
Colonne As
Variant
, _
ByVal
Ligne As
Long
, _
Optional
TypeInfo As
Enum_InfoTS =
TS_Valeur) As
Variant
'------------------------------------------------------------------------------------------------
' Renvoie une information sur la cellule d'une ligne d'une colonne (même si elle est masquée).
' L'information peut être la valeur ou la formule ou la couleur du texte...
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne.
' Si vide ou 0 alors prend la dernière colonne du Tableau Structuré.
' Ligne : la ligne concernée.
' Si 0 alors prend la dernière ligne du Tableau Structuré.
' Si <0 alors prend la ligne des totaux.
' TypeInfo : type d'information à renvoyer, voir l'énumération Enum_InfoTS.
' TS_Valeur : renvoie la valeur de la cellule.
' TS_Formule : renvoie la formule de la cellule.
' TS_CouleurTexte : renvoie la couleur de texte de la cellule.
' TS_CouleurFond : renvoie la couleur de fond de la cellule.
' TS_Gras : renvoie Vrai si la cellule est en gras.
' TS_Italique : renvoie Vrai si la cellule est en italique.
' TS_Visible : renvoie Vrai si la cellule est visible.
' TS_Format : renvoie le format de la cellule.
' TS_Commentaire : renvoie le commentaire de la cellule.
' TS_LienHypertexte : renvoie le lien hypertexte de la cellule.
'------------------------------------------------------------------------------------------------
' Renvoie l'information désirée sur la cellule.
'------------------------------------------------------------------------------------------------
Dim
Anc_ShowTotals As
Boolean
Dim
Anc_ScreenUpdating As
Boolean
On
Error
GoTo
Gest_Err
Err
.Clear
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Contrôle la cohérence de la ligne passée en argument :
If
Ligne >=
0
Then
Ligne =
TS_IndexLigne
(
TS, Ligne)
If
Ligne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
End
If
' S'il faut traiter les totaux alors force l'affichage des totaux :
If
Ligne <
0
Then
Anc_ShowTotals =
TS.ListObject.ShowTotals
Anc_ScreenUpdating =
Application.ScreenUpdating
Ligne =
TS.ListObject.ListRows.Count
+
1
Application.ScreenUpdating
=
False
TS.ListObject.ShowTotals
=
True
End
If
' Traitement:
With
TS.ListObject.DataBodyRange
(
Ligne, Colonne)
Select
Case
TypeInfo
Case
TS_Valeur
TS_InfoCellule =
.Value
Case
TS_Formule
TS_InfoCellule =
.Formula
Case
TS_CouleurTexte
TS_InfoCellule =
.Font.Color
Case
TS_CouleurFond
TS_InfoCellule =
.Interior.Color
Case
TS_Gras
TS_InfoCellule =
.Font.Bold
Case
TS_Italique
TS_InfoCellule =
.Font.Italic
Case
TS_Visible
If
.Height
>
0
Then
TS_InfoCellule =
True
Else
TS_InfoCellule =
False
Case
TS_Format
TS_InfoCellule =
.NumberFormat
Case
TS_Commentaire
If
Not
.Comment
Is
Nothing
Then
TS_InfoCellule =
.Comment.Text
End
If
Case
TS_LienHypertexte
If
.Hyperlinks.Count
>
0
Then
TS_InfoCellule =
.Hyperlinks
(
1
).Name
End
If
End
Select
End
With
' Restaure l'état de l'affichage des totaux :
If
Ligne >
TS.ListObject.ListRows.Count
Then
TS.ListObject.ShowTotals
=
Anc_ShowTotals
Application.ScreenUpdating
=
Anc_ScreenUpdating
End
If
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
V. Rechercher, sélectionner, modifier les données d’un tableau structuré▲
Les différentes fonctions de ce chapitre permettent de rechercher, sélectionner ou modifier les données d’un tableau structuré.
V-A. TS_Rechercher▲
La fonction TS_Rechercher recherche une donnée et renvoie le numéro de la ligne où elle se trouve.
La recherche peut comporter jusqu’à 16 colonnes dans ses critères.
Les lignes masquées sont incluses dans la recherche.
Les critères peuvent être passés « en dur » ou par un tableau.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- RespecterCasse :
True
pour respecter la casse ouFalse
pour l’ignorer ; - ListeColonnesValeurs : la liste des colonnes et valeurs de type ParamArray (tableau de paramètres), séparées par une virgule.
La fonction renvoie : le numéro de la ligne qui correspond aux critères de recherche ou zéro si rien n’est trouvé.
Exemple pour rechercher la ligne où se trouve l’élève dont le nom est « TINE » et le prénom « Clément » (en respectant la casse) :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Dim
Ligne As
Long
Ligne =
TS_Rechercher
(
Tableau, True
, "Nom"
, "TINE"
, "Prénom"
, "Clément"
)
End
Sub
'------------------------------------------------------------------------------------------------
Exemple pour rechercher la même chose mais les critères sont passés en utilisant un tableau :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Dim
Ligne As
Long
Dim
T
(
1
To
4
) As
Variant
T
(
1
) =
"Nom"
T
(
2
) =
"TINE"
T
(
3
) =
"Prénom"
T
(
4
) =
"Clément"
Ligne =
TS_Rechercher
(
Tableau, True
, T)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : pour une recherche de date, pensez à convertir la date en valeur par l’instruction DateValue
(
MaDate)
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_Rechercher
(
TS As
Range, _
RespecterCasse As
Boolean
, _
ParamArray ListeColonnesValeurs
(
) As
Variant
) As
Long
'-----------------------------------------------------------------------------------------------
' Recherche une valeur dans un Tableau Structuré.
'-----------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' RespecterCasse As Boolean : True pour respecter la casse ou False pour l'ignorer.
' ListeColonnesValeurs : La liste des colonnes et valeurs de type ParamArray (tableau de paramètres).
' Un ParamArray est limité à 32 arguments (séparés par une virgule), donc 16 couples.
'-----------------------------------------------------------------------------------------------
' Renvoie : la 1ère ligne trouvée ou 0 si rien n'est trouvé.
'------------------------------------------------------------------------------------------------
Dim
i As
Integer
, Ligne As
Long
, ik As
Integer
, Trouvé As
Boolean
Dim
Arguments
(
) As
Variant
, Colonne
(
) As
Variant
, Valeur
(
) As
Variant
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Si ListeColonnesValeurs est passé en programmation :
If
UBound
(
ListeColonnesValeurs) =
0
Then
ReDim
Arguments
(
LBound
(
ListeColonnesValeurs
(
0
)) To
UBound
(
ListeColonnesValeurs
(
0
)))
For
ik =
LBound
(
Arguments) To
UBound
(
Arguments)
Arguments
(
ik) =
ListeColonnesValeurs
(
0
)(
ik)
Next
ik
End
If
' Si ListeColonnesValeurs est passé en dur :
If
UBound
(
ListeColonnesValeurs) >
0
Then
ReDim
Arguments
(
LBound
(
ListeColonnesValeurs) To
UBound
(
ListeColonnesValeurs))
For
ik =
LBound
(
Arguments) To
UBound
(
Arguments)
Arguments
(
ik) =
ListeColonnesValeurs
(
ik)
Next
ik
End
If
' Chargement des colonnes :
ik =
LBound
(
Arguments)
For
i =
LBound
(
Arguments) To
UBound
(
Arguments) Step
2
ReDim
Preserve
Colonne
(
LBound
(
Arguments) To
ik)
Colonne
(
ik) =
TS_IndexColonne
(
TS, Arguments
(
i))
If
Colonne
(
ik) =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
ik =
ik +
1
Next
i
' Chargement des valeurs :
ik =
LBound
(
Arguments)
For
i =
LBound
(
Arguments) To
UBound
(
Arguments) Step
2
ReDim
Preserve
Valeur
(
LBound
(
Arguments) To
ik)
Valeur
(
ik) =
Arguments
(
i +
1
)
If
IsNumeric
(
Valeur
(
ik)) =
True
Then
Valeur
(
ik) =
CDec
(
Valeur
(
ik))
ik =
ik +
1
Next
i
' Recherche dans toutes les lignes du Tableau Structuré les couples Colonne/valeur :
For
Ligne =
1
To
TS.ListObject.ListRows.Count
Trouvé =
True
Select
Case
RespecterCasse
Case
True
For
i =
LBound
(
Colonne) To
UBound
(
Colonne)
If
TS.ListObject.DataBodyRange
(
Ligne, Colonne
(
i)).Value
<>
Valeur
(
i) Then
Trouvé =
False
Exit
For
End
If
Next
i
Case
False
For
i =
LBound
(
Colonne) To
UBound
(
Colonne)
If
UCase
(
TS.ListObject.DataBodyRange
(
Ligne, Colonne
(
i)).Value
) <>
UCase
(
Valeur
(
i)) Then
Trouvé =
False
Exit
For
End
If
Next
i
End
Select
If
Trouvé =
True
Then
TS_Rechercher =
Ligne
Exit
For
End
If
Next
Ligne
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
V-B. TS_Sélectionner▲
La fonction TS_Sélectionner sélectionne une plage dans un tableau Structuré.
La plage peut être une cellule, une ligne entière, une colonne entière ou l’ensemble des données du tableau structuré.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : (facultatif) le nom ou le numéro de la colonne concernée. Si ce nom est zéro, alors la dernière colonne du tableau structuré est traitée. Si cet argument n’est pas renseigné ou est vide, alors la ligne renseignée sera entièrement sélectionnée ;
- Ligne : (facultatif) la ligne concernée. Si cette valeur est zéro, alors la dernière ligne du tableau structuré est traitée. Si elle est inférieure à zéro, alors la ligne des totaux est traitée. Si cet argument n’est pas renseigné ou est vide, alors la colonne renseignée sera entièrement sélectionnée (juste les cellules visibles).
Si la colonne et la ligne ne sont pas renseignées, alors la sélection porte sur toutes les données du tableau structuré (juste les cellules visibles).
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Plusieurs exemples pour faire des sélections du tableau structuré des élèves, respectivement :
- l’ensemble des données visibles du tableau structuré ;
- la colonne « Nom » (juste les cellules visibles) ;
- la cellule située sur la 5e ligne de la colonne « Nom » ;
- la cellule située sur la dernière ligne de la colonne « Nom » ;
- le total de la colonne « Nom » ;
- la 5e ligne ;
- la dernière ligne ;
- la ligne des totaux.
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_Sélectionner
(
Tableau)
Call
TS_Sélectionner
(
Tableau, "Nom"
)
Call
TS_Sélectionner
(
Tableau, "Nom"
, 5
)
Call
TS_Sélectionner
(
Tableau, "Nom"
, 0
)
Call
TS_Sélectionner
(
Tableau, "Nom"
, -
1
)
Call
TS_Sélectionner
(
Tableau, ""
, 5
)
Call
TS_Sélectionner
(
Tableau, ""
, 0
)
Call
TS_Sélectionner
(
Tableau, ""
, -
1
)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : une fois les cellules sélectionnées, le programmeur peut les parcourir pour les analyser, les modifier ou leur appliquer un format, comme dans ces exemples :
'------------------------------------------------------------------------------------------------
Dim
C As
Range
Call
TS_Sélectionner
(
Tableau, "Nom"
, ""
) ' Sélectionne la colonne "Nom".
For
Each
C In
Selection
Debug.Print
C ' Affiche les noms.
Next
C
'------------------------------------------------------------------------------------------------
'------------------------------------------------------------------------------------------------
Call
TS_Sélectionner
(
Tableau, "Note"
, ""
) ' Sélectionne la colonne "Note".
Selection.NumberFormat
=
"#0"
' Modifie le format numérique.
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_Sélectionner
(
TS As
Range, _
Optional
ByVal
Colonne As
Variant
=
""
, _
Optional
ByVal
Ligne As
Variant
) As
Boolean
'------------------------------------------------------------------------------------------------
' Sélectionne une plage dans un tableau Structuré.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne
' Si 0 alors prend la dernière colonne du Tableau Structuré.
' Si non renseigné alors sélectionne la ligne entière renseignée.
' Ligne : la ligne concernée.
' Si 0 alors prend la dernière ligne du Tableau Structuré.
' Si <0 alors prend la ligne des totaux.
' Si non renseigné alors sélectionne la colonne entière renseignée.
' Si Colonne et Ligne ne sont pas renseignée alors sélectionne toutes les données.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si une sélection a été faite.
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Si la colonne et la ligne ne sont pas renseignées alors sélectionne toutes les données:
If
Colonne =
""
And
IsMissing
(
Ligne) =
True
Then
TS.ListObject.DataBodyRange.Select
TS_Sélectionner =
True
GoTo
Gest_Err
End
If
' Si la ligne n'est pas renseignée :
If
IsMissing
(
Ligne) =
True
Then
Ligne =
""
If
Ligne =
""
Then
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Sélectionner toutes les données de la colonne :
TS.ListObject.ListColumns
(
Colonne).DataBodyRange.Select
TS_Sélectionner =
True
' Quitte le traitement :
GoTo
Gest_Err
End
If
' Si la ligne est 0 alors prend la dernière ligne :
If
Ligne =
0
Then
Ligne =
TS.ListObject.ListRows.Count
' Si colonne est vide alors sélectionne toute la ligne:
If
Colonne =
""
Then
' La ligne du tableau :
If
Ligne >
0
And
Ligne <=
TS.ListObject.ListRows.Count
Then
TS.ListObject.ListRows
(
Ligne).Range.Select
TS_Sélectionner =
True
End
If
' La ligne des totaux :
If
Ligne <
0
Then
TS.ListObject.ShowTotals
=
True
TS.ListObject.TotalsRowRange.Select
TS_Sélectionner =
True
End
If
' Quitte le traitement :
GoTo
Gest_Err
End
If
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Deux cas de sélection possibles :
If
Ligne <
0
Then
' Sélectionne juste le total.
TS.ListObject.ShowTotals
=
True
TS.ListObject.Range
(
TS.ListObject.ListRows.Count
+
2
, Colonne).Select
TS_Sélectionner =
True
Else
' Sélectionne une cellule :
Ligne =
TS_IndexLigne
(
TS, CLng
(
Ligne))
If
Ligne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
TS.ListObject.Range
(
Ligne +
1
, Colonne).Select
TS_Sélectionner =
True
End
If
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
V-C. TS_ModifCellule▲
La fonction TS_ModifCellule modifie les informations sur une cellule d’un tableau structuré, même si elle est masquée.
La cellule concernée est identifiée par sa position dans le tableau structuré. La première colonne vaut 1 (il est conseillé d’utiliser le nom de la colonne au lieu de son numéro), la première ligne vaut 1.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- Colonne : le nom ou le numéro de la colonne concernée. Si ce nom est vide ou zéro, alors la dernière colonne du tableau est traitée ;
- Ligne : la ligne concernée. Si cette valeur est zéro, alors prend la dernière ligne du tableau structuré. Si elle est inférieure à zéro, alors prend la ligne des totaux ;
- Valeur : la valeur de référence qui sera utilisée pour modifier la cellule ;
- TypeInfo : (facultatif) le type d’information désiré défini par une valeur de l’énumération personnelle Enum_InfoTS déclarée en en-tête du module
- TS_Valeur : (par défaut) modifie la valeur de la cellule,
- TS_Ajouter : ajoute à la valeur existante la valeur passée dans Valeur,
- TS_Soustraire : soustrait à la valeur existante la valeur passée dans Valeur,
- TS_Multiplier : multiplie la valeur existante par la valeur passée dans Valeur,
- TS_Diviser : divise la valeur existante par la valeur passée dans Valeur,
- TS_Formule : applique à la cellule la formule passée dans Valeur,
- TS_CouleurTexte : applique à la cellule la couleur de texte passée dans Valeur,
- TS_CouleurFond : applique à la cellule la couleur de fond passée dans Valeur (si Valeur est vide alors efface la couleur de fond),
- TS_Gras : met la cellule en gras ou non selon que Valeur vautTrue
ouFalse
,
- TS_Italique : met la cellule en italique ou non selon que Valeur vautTrue
ouFalse
,
- TS_Format : applique à la cellule le format passé dans Valeur,
- TS_Commentaire : applique à la cellule le commentaire passé dans Valeur ou l’efface si Valeur est vide,
- TS_ImageCommentaireJPG : applique au commentaire l'image "jpg" passée dans Valeur ou l’efface si Valeur est vide,
- TS_LienHypertexte : applique à la cellule le lien hypertexte passé dans Valeur ou l’efface si Valeur est vide ; - LargeurCommentaire : (facultatif) la largeur du commentaire (ou 0 pour la taille par défaut) ;
- HauteurCommentaire: (facultatif) la hauteur du commentaire (ou 0 pour la taille par défaut).
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Plusieurs exemples pour modifier la dernière ligne de la colonne « Note » du tableau structuré des élèves, respectivement :
- pour passer la note à 15 ;
- pour ajouter 2 à la note existante ;
- pour la mettre en gras ;
- pour la mettre en rouge ;
- pour y ajouter un commentaire qui sera affiché dans une infobulle de 120 sur 20.
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_ModifCellule
(
Tableau, "Note"
, 0
, 15
)
Call
TS_ModifCellule
(
Tableau, "Note"
, 0
, 2
, TS_Ajouter)
Call
TS_ModifCellule
(
Tableau, "Note"
, 0
, True
, TS_Gras)
Call
TS_ModifCellule
(
Tableau, "Note"
, 0
, 255
, TS_CouleurTexte)
Call
TS_ModifCellule
(
Tableau, "Note"
, 0
, "De gros efforts réalisés."
, TS_Commentaire, 120
, 20
)
End
Sub
'------------------------------------------------------------------------------------------------
Remarque : cette fonction permet aussi de modifier les formules de la ligne des totaux (pour y mettre des formules personnalisées) par exemple pour avoir la moyenne des notes y compris quand des lignes sont masquées :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Call
TS_ModifCellule
(
Tableau, "Note"
, -
1
, "=SUM(TS_Eleves[Note])/COUNT(TS_Eleves[Note])"
, TS_Formule)
End
Sub
'------------------------------------------------------------------------------------------------
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_ModifCellule
(
TS As
Range, _
ByVal
Colonne As
Variant
, _
ByVal
Ligne As
Long
, _
Valeur As
Variant
, _
Optional
TypeInfo As
Enum_InfoTS =
TS_Valeur, _
Optional
LargeurCommentaire As
Long
=
0
, _
Optional
HauteurCommentaire As
Long
=
0
) As
Boolean
'------------------------------------------------------------------------------------------------
' Modifie une information sur la cellule même si elle est masquée.
' L'information peut être la valeur ou la formule ou la couleur du texte...
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' Colonne : le numéro de la colonne, ou le nom de la colonne.
' Si vide ou 0 alors prend la dernière colonne du Tableau Structuré.
' Ligne : la ligne concernée.
' Si 0 alors prend la dernière ligne du Tableau Structuré.
' Si <0 alors prend la ligne des totaux.
' Valeur : la valeur à appliquer.
' TypeInfo : Type d'information à modifier, voir l'énumération Enum_InfoTS
' TS_Valeur : Modifie la valeur qui prend la valeur passée dans Valeur.
' TS_Ajouter : Ajoute à la valeur existante la valeur passée dans Valeur.
' TS_Soustraire : Soustrait à la valeur existante la valeur passée dans Valeur.
' TS_Multiplier : Multiplie la valeur existante par la valeur passée dans Valeur.
' TS_Diviser : Divise la valeur existante par la valeur passée dans Valeur.
' TS_Formule : Modifie la formule qui devient la formule passée dans Valeur.
' TS_CouleurTexte : Modifie la couleur de texte qui prend la valeur passée dans Valeur.
' TS_CouleurFond : Modifie la couleur de fond qui prend la valeur passée dans Valeur.
' Si valeur est vide alors efface la couleur de fond.
' TS_Gras : Si Valeur vaut Vrai alors met la cellule est en gras.
' TS_Italique : Si Valeur vaut Vrai alors met la cellule est en italique.
' TS_Format : Modifie le format de la cellule.
' TS_Commentaire : Modifie le commentaire (vide pour l'effacer).
' TS_ImageCommentaireJPG : Modifie l'image "jpg" d'un commentaire (vide pour l'effacer).
' TS_LienHypertexte : Modifie le lien Hypertexte de la cellule (vide pour l'effacer).
' LargeurCommentaire : Largeur du commentaire, ou 0 pour la taille par défaut.
' HauteurCommentaire : Hauteur du commentaire, ou 0 pour la taille par défaut.
'------------------------------------------------------------------------------------------------
' Renvoie : VRAI si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
On
Error
GoTo
Gest_Err
Err
.Clear
' Si le tableau est vierge alors il faut l'initialiser (doit être dans le classeur actif):
If
TS.ListObject.ListRows.Count
=
0
And
(
Ligne =
0
Or
Ligne =
1
) Then
Call
TS_AjouterUneLigne
(
TS, 0
)
End
If
' Retrouve le numéro de la colonne et vérifie sa cohérence (ou -1 si erreur):
Colonne =
TS_IndexColonne
(
TS, Colonne)
If
Colonne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
' Deux cas de modification possibles :
If
Ligne <
0
Then
' Ligne des totaux :
Ligne =
TS.ListObject.ListRows.Count
+
1
TS.ListObject.ShowTotals
=
True
Else
' Ligne du taleau :
Ligne =
TS_IndexLigne
(
TS, Ligne)
If
Ligne =
-
1
Then
Err
.Raise
vbObjectError
, , TS_Err_Description
End
If
' Modifie la cellule suivant le type demandé :
With
TS.ListObject.DataBodyRange
(
Ligne, Colonne)
Select
Case
TypeInfo
Case
TS_Valeur
.Value
=
Valeur
Case
TS_Ajouter
.Value
=
.Value
+
Valeur
Case
TS_Soustraire
.Value
=
.Value
-
Valeur
Case
TS_Multiplier
.Value
=
.Value
*
Valeur
Case
TS_Diviser
.Value
=
.Value
/
Valeur
Case
TS_Formule
.Formula
=
Valeur
Case
TS_CouleurTexte
.Font.Color
=
Valeur
Case
TS_CouleurFond
If
IsNumeric
(
Valeur) =
True
Then
.Interior.Color
=
Valeur
If
Valeur =
""
Then
.Interior.Pattern
=
xlNone
Case
TS_Gras
If
Valeur =
True
Then
.Font.Bold
=
True
If
Valeur =
False
Then
.Font.Bold
=
False
Case
TS_Italique
If
Valeur =
True
Then
.Font.Italic
=
True
If
Valeur =
False
Then
.Font.Italic
=
False
Case
TS_Format
.NumberFormat
=
Valeur
Case
TS_Commentaire
.ClearComments
If
Valeur <>
""
Then
.AddComment
.Comment.Text
Text:=
Valeur
If
HauteurCommentaire >
0
Then
.Comment.Shape.Height
=
HauteurCommentaire
If
LargeurCommentaire >
0
Then
.Comment.Shape.Width
=
LargeurCommentaire
End
If
Case
TS_ImageCommentaireJPG
.ClearComments
If
Valeur <>
""
Then
.AddComment
.Comment.Shape.Fill.UserPicture
Valeur
If
HauteurCommentaire >
0
Then
.Comment.Shape.Height
=
HauteurCommentaire
If
LargeurCommentaire >
0
Then
.Comment.Shape.Width
=
LargeurCommentaire
End
If
Case
TS_LienHypertexte
.Hyperlinks.Delete
If
Valeur <>
""
Then
.Hyperlinks.Add
Anchor:=
TS.ListObject.DataBodyRange
(
Ligne, Colonne), Address:=
Valeur
End
If
End
Select
End
With
' Fin du traitement :
Gest_Err
:
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
If
Err
.Number
=
0
Then
TS_ModifCellule =
True
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
VI. Importer, exporter des données d’un tableau structuré▲
VI-A. TS_ImporterDonnées (version importation)▲
La fonction TS_ImporterDonnées permet d’importer des données depuis un autre tableau structuré en sélectionnant les critères d’importation.
Toutes les colonnes du tableau de destination qui ont une correspondance dans le tableau qui contient les données à importer sont traitées, les autres colonnes sont ignorées.
ATTENTION : Les lignes masquées du tableau structuré qui contient les données à importer ne sont pas importées. Ce qui permet (éventuellement) de faire en amont une règle de gestion des importations.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré où seront importées les données (c’est-à-dire le tableau destination) ;
- TD : la plage (de type Range) qui représente le tableau structuré source d’où proviennent les données ;
- Méthode : la méthode d’importation désirée définie par une valeur de l’énumération personnelle Enum_ImportationTS déclarée en en-tête du module
- TS_Ajout_Forcé : ajoute les lignes au tableau d'origine même si elles existent déjà (dans ce cas laissez ListeColonnesClés à vide),
- TS_MAJ_Uniquement : ne fait que des mises à jour et refuse les ajouts,
- TS_MAJ_Ou_Ajout : fait une mise à jour si possible ou un ajout si la donnée est nouvelle,
- TS_IgnorerSiExiste : ne tient pas compte de la donnée si elle existe déjà ; - RespecterCasse :
True
pour respecter la casse ouFalse
pour l’ignorer ; - ListeColonnesClés : la liste des colonnes (en-tête) qui servent de clés de référence dans la comparaison des mises à jour, de type ParamArray (tableau de paramètres), séparées par une virgule.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Remarque : l’importation nécessite que les deux tableaux aient en commun les champs utilisés pour la clé.
Exemple d’importation de données pour mettre à jour les notes des élèves du tableau structuré d’origine « TS_Eleves » (tableau de gauche) et y ajouter les nouveaux élèves, avec les données du tableau structuré « Données » (tableau de droite) :
Les clés seront « Nom » et « Prénom », champs présents dans les deux tableaux (l’ordre n’a pas d’importance).
La colonne « Note », présente dans les deux tableaux sera traitée et mettra à jour le tableau d’origine, inversement, la colonne « Date » sera ignorée puisque qu’elle n’a pas de correspondance dans le tableau d’origine (une importation serait possible si l’on ajoutait ce champ au tableau d’origine, il n’y a pas de limite dans le nombre de colonnes qui peuvent être mises à jour).
La méthode d’importation sera « TS_MAJ_Ou_Ajout » pour mettre à jour la note des élèves existants et ajouter les nouveaux élèves.
La casse sera ignorée car les données à importer ont parfois un format différent.
Les éventuels filtres du tableau structuré « Données » seront effacés pour importer toutes les données.
En cas d’erreur de traitement, un message le signale :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Dim
TD As
Range
Set
TD =
Range
(
"Données"
)
' Efface les éventuels filtres sur le tableau des données à importer :
Call
TS_Filtres_Effacer
(
TD)
‘ Importe les données suivant les clés Nom et Prénom, renvoie False
en cas d’erreur et l’affiche :
If
TS_ImporterDonnées
(
Tableau, TD, TS_MAJ_Ou_Ajout, False
, "Nom"
, "prénom"
) =
False
Then
MsgBox
TS_Err_Number &
" : "
&
TS_Err_Description, vbCritical
, "Erreur de traitement"
End
If
End
Sub
'------------------------------------------------------------------------------------------------
Ce qui donne :
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_ImporterDonnées
(
TS As
Range, _
TD As
Range, _
Méthode As
Enum_ImportationTS, _
RespecterCasse As
Boolean
, _
ParamArray ListeColonnesClés
(
) As
Variant
) As
Boolean
'------------------------------------------------------------------------------------------------
' Permet l'importation de données depuis un Tableau Structuré pour alimenter
' le Tableau Structuré d'origine.
' Toutes les colonnes du tableau d'origine qui ont une correspondance dans le tableau qui contient
' les données à importer sont traitées.
'------------------------------------------------------------------------------------------------
' ATTENTION : les lignes masquées du Tableau Structuré qui contient les données à importer
' ~~~~~~~~~~ ne sont pas importées. Ce qui permet (éventuellement) de faire en amont une règle
' de gestion des importations.
'------------------------------------------------------------------------------------------------
' TS : le Tableau Structuré d'origine.
' TD : le Tableau Structuré qui contient les données à importer.
' Méthode : la méthode d'importation
' TS_Ajout_Forcé : Ajoute les lignes au tableau d'origine même si elles existent déjà, dans
' ce cas mettre "" pour ListeColonnesClés.
' TS_MAJ_Uniquement : Ne fait que des mises à jour et refuse les ajouts.
' TS_MAJ_Ou_Ajout : Fait une mise à jour si possible ou un ajout si la donnée est nouvelle.
' TS_IgnorerSiExiste : Ne tient pas compte de la donnée si elle existe déjà.
' RespecterCasse As Boolean : True pour respecter la casse ou False pour l'ignorer.
' ListeColonnesClés : la liste des colonnes (en-tête) qui sert de clé de référence dans la comparaison
' des mises à jour, de type ParamArray (tableau de paramètres).
' Passez en argument soit le nom soit le numéro de la colonne.
' Un ParamArray est limité à 32 arguments, séparés par une virgule.
'------------------------------------------------------------------------------------------------
' Renvoie : vrai si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
Dim
x As
Integer
, i As
Integer
, y As
Long
, yy As
Long
, NomColonne As
String
Dim
Ajout As
Boolean
Dim
Arguments
(
) As
Variant
Dim
NbClé As
Integer
Dim
MesFiltres As
Variant
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Mémorise les éventuels filtres et les retire :
Call
TS_Filtres_Mémoriser
(
TS, MesFiltres)
Call
TS_Filtres_Effacer
(
TS)
' S'il y a des colonnes renseignées pour former une clé :
If
IsMissing
(
ListeColonnesClés) =
False
Then
NbClé =
(
UBound
(
ListeColonnesClés) +
1
) *
2
ReDim
Arguments
(
1
To
NbClé)
x =
1
For
i =
0
To
UBound
(
ListeColonnesClés)
Arguments
(
x) =
ListeColonnesClés
(
i)
If
IsNumeric
(
Arguments
(
x)) =
True
Then
_
Arguments
(
x) =
TS.ListObject.ListColumns
(
Arguments
(
x)).Name
x =
x +
2
Next
i
End
If
' Boucle sur les lignes du Tableau Structuré à importer :
For
y =
1
To
TD.ListObject.ListRows.Count
Ajout =
False
yy =
0
' Si la ligne est masquée alors passer à la suivante :
If
TD.ListObject.DataBodyRange
(
y, 1
).Height
=
0
Then
GoTo
Suite_Y
' Si une recherche sur les titres :
If
NbClé >
0
And
Méthode <>
TS_Ajout_Forcé Then
For
i =
2
To
NbClé Step
2
' Renvoie la valeur de la colonne clé :
Arguments
(
i) =
TS_InfoCellule
(
TD, Arguments
(
i -
1
), y, TS_Valeur)
' Si la clé n'exite pas:
If
TS_Err_Number <>
0
Then
Err
.Raise
TS_Err_Number, , TS_Err_Description
Next
i
' Si la clé est trouvée dans le tableau destination alors ne pas
' ajouter une ligne mais travailler sur la ligne trouvée = yy.
' La recherche tient compte de la casse ou non :
yy =
TS_Rechercher
(
TS, RespecterCasse, Arguments)
If
yy <>
0
Then
Ajout =
True
If
TS_Err_Number <>
0
Then
Err
.Raise
TS_Err_Number, , TS_Err_Description
' Si l'on ne doit faire que des mises à jour alors passer à la suite
' si la donnée n'est pas trouvée dans le tableau destination :
If
Méthode =
TS_MAJ_Uniquement And
yy =
0
Then
GoTo
Suite_Y
' Si l'on doit ignorer les données qui existe déjà alors passer
' à la suite si la donnée est trouvée dans le tableau destination :
If
Méthode =
TS_IgnorerSiExiste And
yy <>
0
Then
GoTo
Suite_Y
End
If
' Boucle sur les colonnes :
For
x =
1
To
TD.ListObject.ListColumns.Count
' Récupère le nom de la colonne :
NomColonne =
TD.ListObject.HeaderRowRange
(
x).Value
' S'il faut ajouter une ligne :
If
Ajout =
False
Then
Ajout =
True
TS.ListObject.ListRows.Add
yy =
0
End
If
' Ajoute la donnée au tableau destination :
Call
TS_ModifCellule
(
TS, NomColonne, yy, TD.ListObject.DataBodyRange
(
y, x).Value
, TS_Valeur)
Next
x
Suite_Y
:
Next
y
' Restaure les filtres et l'affichage :
Call
TS_Filtres_Restaurer
(
TS, MesFiltres)
' Fin du traitement :
Gest_Err
:
If
Err
.Number
=
0
Then
TS_ImporterDonnées =
True
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
VI-B. TS_ImporterDonnées (version exportation)▲
La fonction TS_ImporterDonnées permet aussi d’exporter des données vers un autre tableau structuré.
Puisque ce qui est une importation pour l’un est une exportation pour l’autre.
ATTENTION : Les lignes masquées du tableau structuré qui contient les données à exporter ne sont pas exportées. Ce qui permet (éventuellement) de faire en amont une règle de gestion des importations.
Exemple d’exportation des données du tableau des élèves pour avoir une liste sans doublon des noms dans un nouveau tableau structuré sur la feuille « Feuil2 » qui sera nommé « Tableau_Noms » :
La clé sera le « Nom ».
La méthode sera « TS_MAJ_Ou_Ajout » pour éviter justement les doublons.
Un tableau structuré sera généré pour recevoir les données qui seront classées par ordre croissant :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Dim
TS_Destination As
Range
' Suppression de l'éventuel ancien tableau (erreur générée si Range("Tableau_Noms") n’existe pas):
On
Error
Resume
Next
Set
TS_Destination =
Range
(
"Tableau_Noms"
)
Call
TS_SupprimerLeTableau
(
TS_Destination)
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Création du tableau sans doublon :
Set
TS_Destination =
TS_CréerUnTableau
(
Plage:=
ThisWorkbook.Sheets
(
"Feuil2"
).Range
(
"A1"
), _
Titres:=
"Nom"
, _
Nom:=
"Tableau_Noms"
, _
Style:=
"*"
)
' Importation des données sans doublons
If
TS_ImporterDonnées
(
TS_Destination, Tableau, TS_MAJ_Ou_Ajout, True
, "Nom"
) =
False
Then
Err
.Raise
TS_Err_Number, , TS_Err_Description
End
If
' Tri de la colonnne :
If
TS_TrierUneColonne
(
TS:=
TS_Destination, _
Colonne:=
"Nom"
, _
Méthode:=
xlSortOnValues, _
Ordre:=
xlAscending, _
EffacerAncienTri:=
True
) =
False
Then
Err
.Raise
TS_Err_Number, , TS_Err_Description
End
If
' Gestion des erreurs :
Gest_Err
:
If
Err
.Number
<>
0
Then
MsgBox
Err
.Number
&
" : "
&
Err
.Description
, vbCritical
, Err
.Source
End
Sub
'------------------------------------------------------------------------------------------------
Ce qui donne :
VI-C. TS_ExporterEnFichier▲
La fonction TS_ExporterEnFichier exporte un tableau structuré du classeur actif en fichier image JPG ou BMP, en fichier au format « Portable Document Format » PDF, en fichier texte au format CSV, ou en fichier Excel.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- FormatFichier : le format de fichier à générer suivant l’énumération Enum_ExportationTS
- TS_XLSX : fichier Excel,
- TS_CSV : fichier texte au format CSV avec le séparateur point-virgule (les lignes masquées ne sont pas reprises),
- TS_BMP : fichier image au format BMP,
- TS_JPG : fichier image au format JPG,
- TS_PDF : fichier au format PDF ; - OuvrirFichier : si
True
alors ouvre le fichier généré.
La fonction renvoie : True
si tout s’est bien passé ou False
dans le cas contraire.
Exemple pour exporter le tableau structuré nommé « TS_Eleves » en fichier PDF et l’ouvrir :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
If
TS_ExporterEnFichier
(
Range
(
"TS_Eleves"
), TS_PDF, "P:\Fichier\Tableau_Eleves.pdf"
, True
) =
False
Then
MsgBox
TS_Err_Number &
" : "
&
TS_Err_Description, vbCritical
, "Erreur de conversion en PDF"
End
If
'------------------------------------------------------------------------------------------------
Remarque : vous trouverez en annexe un code VBA pour fusionner deux fichiers PDF si vous disposez de l’application « Adobe Acrobat Pro ».
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_ExporterEnFichier
(
TS As
Range, _
FormatFichier As
Enum_ExportationTS, _
ByVal
FichierDest As
String
, _
OuvrirFichier As
Boolean
) As
Boolean
'------------------------------------------------------------------------------------------------
' Exporte un Tableau Structuré du classeur actif en fichier au format XLSX, CSV, BMP, JPG, PDF.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' FormatFichier : le format de fichier à générer, voir l'énumération Enum_ExportationTS
' TS_XLSX : fichier Excel.
' TS_CSV : fichier texte au format CSV, séparateur point-virgule (ne prend pas les lignes masquées).
' TS_BMP : fichier image au format BMP.
' TS_JPG : fichier image au format JPG.
' TS_PDF : fichier au format "Portable Document Format" PDF.
' FichierDest : le chemin destination (qui doit exister) et le nom du fichier à créer,
' si un fichier existait déjà il est remplacé.
' OuvrirFichier : Si VRAI alors ouvre le fichier généré.
'------------------------------------------------------------------------------------------------
' Renvoie : VRAI si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
Dim
Repertoire As
String
, Sh As
Worksheet
Dim
Filtre As
String
Dim
Tmp As
String
, FileNumber As
Long
, ol As
Range, oC As
Range
Dim
Anc_ScreenUpdating As
Boolean
Dim
Anc_Visible As
Integer
Dim
ObjPicture As
Object
Dim
Rng As
Range
On
Error
GoTo
Gest_Err
Err
.Clear
' Mémorise la feuille active:
Set
Sh =
ActiveSheet
' Force la mise à jour de l'écran:
Anc_ScreenUpdating =
Application.ScreenUpdating
Application.ScreenUpdating
=
True
' Sélection de la feuille et de la plage:
Anc_Visible =
Sheets
(
TS.Parent.Name
).Visible
Sheets
(
TS.Parent.Name
).Visible
=
True
Sheets
(
TS.Parent.Name
).Activate
Set
Rng =
TS.ListObject.Range
' Le fichier destination doit être renseigné:
If
FichierDest =
""
Then
Err
.Raise
vbObjectError
, , "Le fichier destination doit être renseigné."
' Le répertoire doit exister:
Repertoire =
Left
(
FichierDest, InStrRev
(
FichierDest, "\"
))
If
CreateObject
(
"Scripting.FileSystemObject"
).Folderexists
(
Repertoire) =
False
Then
_
Err
.Raise
vbObjectError
, , "Le dossier de destination "
&
Repertoire &
" n'est pas présent."
' Traitements suivant le type de fichier à générer:
Application.Cursor
=
xlWait
Select
Case
FormatFichier
Case
TS_CSV
' Contrôle la présence de l'extension et supprime le fichier existant:
If
UCase
(
Right
(
FichierDest, 4
)) <>
".CSV"
Then
FichierDest =
FichierDest &
".csv"
If
Dir
(
FichierDest) <>
""
Then
Kill FichierDest
' Ajustement des colonnes pour ne pas avoir de # quand elles sont trop petites:
TS.Cells.EntireColumn.AutoFit
' Création d'un fichier:
FileNumber =
FreeFile
Open FichierDest For
Output As
#FileNumber
' Boucle sur les lignes:
For
Each
ol In
Rng.Rows
' Boucle sur les cellules de la ligne:
Tmp =
""
' Si la ligne n'est pas masquée:
If
ol.Height
<>
0
Then
' Boucle sur les colonnes:
For
Each
oC In
ol.Cells
Tmp =
Tmp &
CStr
(
oC.Text
) &
";"
Next
' Ne pas prendre le dernier séparateur:
Tmp =
Left
(
Tmp, Len
(
Tmp) -
1
)
' Ecriture dans le fichier:
Print #FileNumber, Tmp
End
If
Next
Close #FileNumber
Case
TS_XLSX
' Contrôle la présence de l'extension et supprime le fichier existant:
If
UCase
(
Right
(
FichierDest, 5
)) <>
".XLSX"
Then
FichierDest =
FichierDest &
".xlsx"
If
Dir
(
FichierDest) <>
""
Then
Kill FichierDest
' Copie le Tableau Structuré:
TS.ListObject.Range.Copy
' Ouvre une instance Excel et un classeur:
Dim
AppXl As
Excel.Application
Set
AppXl =
CreateObject
(
"excel.application"
)
AppXl.Visible
=
False
AppXl.Workbooks.Add
' Y copie le Tableau Structuré en plage:
AppXl.ActiveSheet.Paste
' Converti la plage en Tableau Structuré:
Call
TS_ConvertirPlageEnTS
(
AppXl.ActiveSheet.Range
(
"A1"
), TS.ListObject.DisplayName
, TS.Parent.ListObjects
(
TS.ListObject.Name
).TableStyle
, xlYes)
AppXl.ActiveSheet.Range
(
"A1"
).Select
' Sauvegarde le fichier et ferme l'instance:
AppXl.ActiveWorkbook.SaveAs
FichierDest
AppXl.ActiveWorkbook.Close
Set
AppXl =
Nothing
Case
TS_PDF
' Contrôle la présence de l'extension et supprime le fichier existant:
If
UCase
(
Right
(
FichierDest, 4
)) <>
".PDF"
Then
FichierDest =
FichierDest &
".pdf"
If
Dir
(
FichierDest) <>
""
Then
Kill FichierDest
' Création d'un fichier au format PDF:
Rng.ExportAsFixedFormat
xlTypePDF, FichierDest
Case
TS_BMP, TS_JPG
' Contrôle la présence de l'extension et supprime le fichier existant:
If
FormatFichier =
TS_BMP Then
If
UCase
(
Right
(
FichierDest, 4
)) <>
".BMP"
Then
FichierDest =
FichierDest &
".bmp"
Filtre =
"BMP"
End
If
If
FormatFichier =
TS_JPG Then
If
UCase
(
Right
(
FichierDest, 4
)) <>
".JPG"
Then
FichierDest =
FichierDest &
".jpg"
Filtre =
"JPG"
End
If
If
Dir
(
FichierDest) <>
""
Then
Kill FichierDest
' Copie les cellules dans le Presse-papiers en tant qu'image:
Rng.CopyPicture
Appearance:=
xlScreen, Format:=
xlPicture
' Création d'un objet image (de petite taille mais elle sera ajustée):
Set
ObjPicture =
Rng.Parent.ChartObjects.Add
(
10
, 10
, 10
, 10
)
DoEvents
With
ObjPicture
.ShapeRange.Line.Visible
=
msoFalse ' Masque l'objet.
DoEvents
.Height
=
Rng.Height
' Ajuste la hauteur.
.Width
=
Rng.Width
' Ajuste la largeur.
DoEvents
.Chart.Paste
' Colle le presse-papiers
DoEvents
.Chart.Export
Filename:=
FichierDest, Filtername:=
Filtre ' Exporte l'image
.Delete
' Supprime l'objet.
End
With
Case
Else
Err
.Raise
vbObjectError
, , "Le type d'exportation demandé n'est pas géré."
End
Select
' Renvoie Vrai:
TS_ExporterEnFichier =
True
' Fin du traitement:
Gest_Err
:
Sh.Activate
Application.ScreenUpdating
=
Anc_ScreenUpdating
Sheets
(
TS.Parent.Name
).Visible
=
Anc_Visible
Application.Cursor
=
xlDefault
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
Err
.Clear
' S'il faut ouvrir le fichier:
If
TS_ExporterEnFichier =
True
And
OuvrirFichier =
True
_
Then
Call
Shell
(
"Explorer.exe "
&
FichierDest, vbMaximizedFocus)
End
Function
'------------------------------------------------------------------------------------------------
VII. Conclusion▲
Sur le forum d’entraide Excel de developpez.com de plus en plus souvent les intervenants suggèrent d’utiliser un tableau structuré pour simplifier la gestion des données : ainsi le problème récurrent de recherche de la fin d’une plage… n’en est plus un.
Les tableaux structurés se démocratisent, et c’est tant mieux.
Pierre Fauconnier y a contribué avec son tutoriel cité en introduction destiné aux utilisateurs.
Je souhaitais y contribuer également avec cette documentation destinée aux développeurs en leur apportant des fonctions prêtes à l’emploi afin qu’ils puissent affronter les tableaux structurés en toute simplicité.
Et aussi en profiter pour démocratiser l’utilisation des requêtes SQL en VBA pour les tableaux structurés qui permettent de répondre rapidement à des besoins parfois complexes.
Je laisse les plus curieux d’entre vous découvrir en annexe des exemples qui utilisent les requêtes SQL et devraient vous convaincre plus que de longs discours.
Vous retrouverez toutes les fonctions étudiées dans le fichier joint « Tableau-Structure.xlsm » regroupées dans le module « TS ».
Bonne programmation.
Laurent OTT.
2022.
VIII. Remerciements▲
Je remercie Pierre Fauconnier pour sa relecture technique, ses remarques pertinentes et la correction de mes erreurs et escartefigue pour la correction orthographique.
Ainsi que toute l’équipe de Developpez.com qui participe à la maintenance du site.
Je dédicace cette documentation à Claude Leloup.
Je le remercie pour ses nombreuses contributions en tant que correcteur orthographique sur le site de developpez.com et aussi pour m’avoir donné des ailes…
IX. Le fichier joint▲
Vous trouverez le fichier Tableaux-Structures.xlsm qui contient les fonctions étudiées dans cette documentation regroupées dans le module « TS » facilement exportable dans vos applications par un simple « glisser/copier » depuis l’explorateur de projet VBA.
Pour simplifier la saisie de votre code, pensez à utiliser la notation pointée « TS. » qui affiche l’ensemble des fonctions du module :
X. Annexe 1 – Les requêtes SQL en VBA sur les tableaux structurés▲
X-A. Présentation des requêtes SQL▲
Pour rechercher, exporter ou mettre à jour des données dans un tableau structuré, j'utilise les requêtes SQL : vous allez constater dans les lignes qui suivent que c'est très simple et très pratique…
Seule restriction : le tableau structuré doit faire moins de 256 colonnes sur 65536 lignes, les ingénieurs de Microsoft ont dû oublier qu’Excel depuis quelques années est monté en puissance.
Si vous programmez sous ACCESS les requêtes SQL vous sont familières, pour ceux qui ne connaissent pas voici un bref rappel :
SQL (Structured Query Language) est un langage de programmation qui permet de manipuler une base de données : sélections d'enregistrements, modifications, ajouts, suppressions.
Dans notre cas nous n'utiliserons le SQL que pour faire des sélections, avec « SELECT », soit la syntaxe :
SELECT « nom des colonnes » FROM « nom de la table » WHERE « conditions » ORDER BY « nom des colonnes »
- SELECT « nom des colonnes » : indiquez les colonnes à sélectionner (entre crochets si leur nom contient un espace), en les séparant par une virgule (l'ordre est libre). Un exemple :
SELECT Nom, Prénom, Note, [Date épreuve] FROM…
Ou plus simplement, une étoile permet de sélectionner directement toutes les colonnes :
SELECT * FROM… (attention, le SELECT * n’est pas recommandé, car sensible aux modifications de la table et contre performant)
Pour simplifier cela, notre fonction générique se contentera des noms des colonnes passés en argument.
- FROM « nom de la table » : pour Excel, le nom de la table s'écrit au format [NomFeuille$PlageCellules], où « PlageCellules » n'est pas en référence absolue et inclut l'en-tête des champs. Pour simplifier cela, notre fonction générique se contentera du tableau structuré passé en argument et déterminera automatiquement le « nom de la table ».
- WHERE « conditions » : facultatif, pour restreindre la sélection vous pouvez ajouter une condition simple ou combinée avec les opérateurs AND et OR comme en VBA. Les données alphanumériques sont à mettre entre simples quotes. Les dates sont à mettre entre deux croisillons et au format anglo-saxon (année/mois/jour). Par exemple pour sélectionner les personnes nées à Noël 1971 :
WHERE [Date Naissance] = #1971/12/25#
- ORDER BY « nom des colonnes » : facultatif, permet de trier le résultat de la requête, par ordre ascendant (par défaut) avec la clause ASC ou descendant avec la clause DESC. Il est possible d'ordonner sur plusieurs colonnes en respectant la syntaxe :
ORDER BY « nom colonne 1 » [ASC, DESC], « nom colonne 2 » [ASC, DESC].
Vous trouverez sur Internet d'autres informations sur les requêtes SQL.
J'aime bien cette adresse : http://www.1keydata.com/fr/sql/syntaxe-sql.php.
Attention, toutes les commandes SQL ne sont pas compatibles avec EXCEL.
X-B. TS_RequeteSQL▲
La fonction TS_RequeteSQL exécute une requête SQL sur un tableau structuré et renvoie un jeu d’enregistrements.
Ses arguments sont :
- TS : la plage (de type Range) qui représente le tableau structuré ;
- StrChamps : le nom des champs à sélectionner (ceux qui alimenteront le jeu d’enregistrements) ;
- StrSQL : (facultatif) la requête SQL des critères de sélection.
La fonction renvoie : un jeu d’enregistrements, ou Nothing
si la requête ne sélectionne rien.
Reprenons l’exemple d’exportation pour obtenir la liste sans doublon des noms de famille des élèves du tableau structuré « TS_Eleves ».
La liste classée par ordre alphabétique sera restituée dans un tableau structuré nommé « Tableau_Noms » sur la feuille 2 :
'------------------------------------------------------------------------------------------------
Sub
Exemple
(
)
'------------------------------------------------------------------------------------------------
Dim
Tableau As
Range
Set
Tableau =
Range
(
"TS_Eleves"
)
Dim
TS_Destination As
Range
Dim
Enr As
Variant
' Suppression de l'éventuel ancien tableau :
On
Error
Resume
Next
Set
TS_Destination =
Range
(
"Tableau_Noms"
)
Call
TS_SupprimerLeTableau
(
TS_Destination)
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Création du tableau structuré :
Set
TS_Destination =
TS_CréerUnTableau
(
Plage:=
ThisWorkbook.Sheets
(
"Feuil2"
).Range
(
"A1"
), _
Titres:=
"Nom"
, _
Nom:=
"Tableau_Noms"
, _
Style:=
"*"
)
' Requête pour obtenir la liste des noms sans doublon et triée :
Set
Enr =
TS_RequeteSQL
(
Tableau, "[Nom]"
, "GROUP BY [Nom] ORDER BY [Nom]"
)
' Affiche la requête dans le tableau structuré :
If
Not
Enr Is
Nothing
Then
Range
(
TS_Destination.ListObject
).CopyFromRecordset
Enr
End
If
' Gestion des erreurs :
Gest_Err
:
If
Err
.Number
<>
0
Then
MsgBox
Err
.Number
&
" : "
&
Err
.Description
, vbCritical
, Err
.Source
End
Sub
'------------------------------------------------------------------------------------------------
Remarques :
- l’instruction SQL
"GROUP BY [Nom] ORDER BY [Nom]"
regroupe les noms sans doublon et les trie par ordre croissant ; - l’affichage dans le tableau structuré « TS_Destination » du jeu d’enregistrements « Enr » se fait avec l’instruction
Range
(
TS_Destination.ListObject
).CopyFromRecordset
Enr.
Exemple pour obtenir la liste des trois meilleures notes et le nom et prénom des élèves concernés :
'------------------------------------------------------------------------------------------------
...
' Création du tableau destination :
Set
TS_Destination =
TS_CréerUnTableau
(
Plage:=
ThisWorkbook.Sheets
(
"Feuil2"
).Range
(
"A1"
), _
Titres:=
Array
(
"Nom"
, "Prénom"
, "Note"
), _
Nom:=
"Tableau_3_Meilleurs"
, _
Style:=
"*"
)
' Requête pour obtenir les élèves qui ont eu les 3 meilleures notes, classées par notes :
Set
Enr =
TS_RequeteSQL
(
Tableau, "TOP 3 [Nom],[Prénom],[Note]"
, "WHERE [Note] > 10 ORDER BY [Note] DESC"
)
' Affiche la requête dans le tableau structuré :
If
Not
Enr Is
Nothing
Then
Range
(
TS_Destination.ListObject
).CopyFromRecordset
Enr
End
If
...
'------------------------------------------------------------------------------------------------
Remarques :
- l’instruction SQL
"TOP 3 [Nom],[Prénom],[Note]"
sélectionne les champs désirés et ne retient que les trois premiers enregistrements du critère de sélection ; - l’instruction SQL
"WHERE [Note]>10 ORDER BY [Note] DESC"
ne retient que les notes supérieures à 10 et les classe par ordre décroissant.
Exemple pour regrouper les notes par ordre décroissant et indiquer le nombre d’élèves concernés :
'------------------------------------------------------------------------------------------------
...
' Création du tableau destination :
Set
TS_Destination =
TS_CréerUnTableau
(
Plage:=
ThisWorkbook.Sheets
(
"Feuil4"
).Range
(
"A1"
), _
Titres:=
Array
(
"Note"
, "Quantité"
), _
Nom:=
"Tableau_X"
, _
Style:=
"*"
)
' Requête pour obtenir la liste des notes et la quantité d'élèves ayant eu cette note :
Set
Enr =
TS_RequeteSQL
(
Tableau, "[Note], Count([Note])"
, "GROUP BY [Note] ORDER BY [Note] DESC"
)
' Affiche la requête dans le tableau structuré :
If
Not
Enr Is
Nothing
Then
Range
(
TS_Destination.ListObject
).CopyFromRecordset
Enr
End
If
' Place des totaux :
Call
TS_ModifCellule
(
TS_Destination, "Note"
, -
1
, "Nb élèves :"
, TS_Valeur)
Call
TS_DéfinirTotaux
(
TS_Destination, "Quantité"
, xlTotalsCalculationSum)
...
'------------------------------------------------------------------------------------------------
Remarques :
- l’instruction SQL
"[Note], Count([Note])"
sélectionne les notes et leur nombre ;
- AVG : calcule la moyenne,
- SUM : calcule la somme,
- COUNT : compte le nombre d'éléments,
- MIM : renvoie la valeur la plus petite,
- MAX : renvoie la valeur la plus grande ; - l’instruction SQL
"GROUP BY [Note] ORDER BY [Note] DESC"
regroupe les notes et les trie par ordre décroissant.
Exemple pour ajouter 1 aux notes inférieures à 10 :
'------------------------------------------------------------------------------------------------
...
' Requête pour obtenir les notes inférieures à 10 :
Set
Enr =
TS_RequeteSQL
(
Tableau, "[Note]"
, "WHERE [Note] < 10"
)
' Boucle sur le jeu d’enregistrements pour ajouter 1 à la note :
If
Not
Enr Is
Nothing
Then
While
Enr.EOF
=
False
Enr.Fields
(
0
).Value
=
Enr.Fields
(
0
).Value
+
1
' Ajoute 1 à la valeur.
Enr.Update
' Valide la modification.
Enr.MoveNext
' Passe à l'enregistrement suivant.
Wend
End
If
...
'------------------------------------------------------------------------------------------------
Remarques :
- le premier champ de la sélection est contenu dans
Fields
(
0
), le suivant (s’il y en a un dans la requête) enFields
(
1
) et ainsi de suite ; - n’oubliez pas l’instruction Update pour valider la modification faite à un enregistrement.
Ne sont présentés ici que quelques exemples des capacités qu’offrent les requêtes SQL et qui évitent bien souvent de laborieuses lignes de code en VBA.
Vous imaginez que le SQL dispose de critères de sélections plus complets, par exemple :
- le mot clé
IN
permet de sélectionner des éléments parmi une liste de valeurs, soit la syntaxe suivante WHERE « nom de colonne »IN
(
'valeur1', 'valeur2', 'valeur3'…)
; - le mot clé Like permet de sélectionner des chaînes de caractères en disposant des jokers
WHERE Nom Like'T*'
renvoie les noms commençants par « T ».
Le code de la fonction :
'------------------------------------------------------------------------------------------------
Public
Function
TS_RequeteSQL
(
TS As
Range, StrChamps As
String
, _
Optional
ByVal
StrSQL As
String
=
""
) As
Variant
'------------------------------------------------------------------------------------------------
' Sélectionne les données d'un tableau de données EXCEL (ou une plage avec en-tête)
' et alimente TS_RequeteSQL des enregistrements correspondants à la requête.
' TS : le tableau Structuré (ou la plage avec en-tête). Maxi = 65535 lignes et 255 colonnes. A1:IU65535
' StrChamps : Liste des champs (ou * ou vide pour tous).
' StrSQL : Requête SQL avec ses conditions.
'------------------------------------------------------------------------------------------------
' Renvoie : le jeu d'enregistrements sélectionné,
' ou Nothing si rien n'est sélectionné (ou erreur).
'------------------------------------------------------------------------------------------------
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
' Contrôle la taille du tableau :
If
TS.ListObject.ListColumns.Count
>
255
_
Or
TS.ListObject.ListRows.Count
>
65535
Then
_
Err
.Raise
vbObjectError
, , "Taille du Tableau Structuré trop grande pour une requête SQL."
' Pour faire une sélection dans un Tableau Structuré il faut la ligne d'en-tête,
' et supprimer les totaux pour ne pas les renvoyer :
Dim
Anc_Entete As
Boolean
Dim
Anc_Totaux As
Boolean
Dim
Anc_ScreenUpdating As
Boolean
Anc_Entete =
TS.ListObject.ShowHeaders
Anc_Totaux =
TS.ListObject.ShowTotals
Anc_ScreenUpdating =
Application.ScreenUpdating
Application.ScreenUpdating
=
False
TS.ListObject.ShowHeaders
=
True
TS.ListObject.ShowTotals
=
False
' Requête sur le tableau de données passé en argument (ou la plage avec en-tête)
StrSQL =
"SELECT "
&
IIf
(
StrChamps >
""
, StrChamps, "*"
) &
" FROM ["
&
TS.Parent.Name
&
"$"
_
&
TS.CurrentRegion.Address
(
False
, False
, xlA1) &
"] "
&
StrSQL
' S'il y a des enregistrements concernés alors les renvoie :
Set
TS_RequeteSQL =
TS_ExecuterSQL
(
TS, StrSQL)
' Restaure l'affichage :
TS.ListObject.ShowHeaders
=
Anc_Entete
TS.ListObject.ShowTotals
=
Anc_Totaux
Application.ScreenUpdating
=
Anc_ScreenUpdating
' Fin du traitement :
Gest_Err
:
If
Err
.Number
<>
0
Then
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
TS_RequeteSQL =
Nothing
End
If
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
La fonction privée suivante utilise des liaisons tardives, vous n’avez donc pas besoin d’installer la bibliothèque « Microsoft ActiveX Data Objects 6.0 Library » dans votre projet.
'------------------------------------------------------------------------------------------------
Private
Function
TS_ExecuterSQL
(
TS As
Range, StrSQL As
String
) As
Variant
' => ADODB.Recordset
'------------------------------------------------------------------------------------------------
' Exécute une requête au format SQL.
'------------------------------------------------------------------------------------------------
' TS : la plage du Tableau Structuré.
' StrSQL : la requête.
'------------------------------------------------------------------------------------------------
' La liaison anticipée nécessite d'installer la référence : Microsoft ActiveX Data Objects 6.0 Library
' La liaison tardive permet de ne pas référencer la bibliothèque ADO.
' L’instanciation d’objets à l’aide de la liaison tardive est généralement plus lente
' que l’utilisation de la liaison anticipée.
' Ici la liaison tardive a été adoptée uniquement pour vous éviter d'installer manuellement une
' référence et donc simplifier la portabilité du code.
' Les remarques ci-dessous indiquent comment adapter le code pour une liaison anticipée.
'------------------------------------------------------------------------------------------------
' Provider et Extended à utiliser :
' Soit Microsoft.Jet.OLEDB.4.0 et Excel 8.0
' Soit Microsoft.ACE.OLEDB.12.0 et Excel 12.0 (voire Excel 14.0)
'------------------------------------------------------------------------------------------------
Dim
Cnn As
Variant
' Liaision anticipée => As ADODB.Connection
Dim
Rs As
Variant
' Liaision anticipée => As ADODB.Recordset
' Gestion des erreurs :
On
Error
GoTo
Gest_Err
Err
.Clear
Set
TS_ExecuterSQL =
Nothing
' Création d'une connexion :
Set
Cnn =
CreateObject
(
"ADODB.Connection"
) ' Liaision anticipée => Set Cnn = New ADODB.Connection
Cnn.Open
"Provider=Microsoft.ACE.OLEDB.12.0;"
&
_
"Data Source="
&
TS.Worksheet.Parent.FullName
&
";"
&
_
"Extended Properties=""Excel 12.0;HDR=Yes"";"
' Exécute une requête SQL sur un jeu d'enregistrements :
Set
Rs =
CreateObject
(
"ADODB.Recordset"
) ' Liaision anticipée => Set Rs = New ADODB.Recordset
Rs.Open
StrSQL, Cnn, 1
, 2
, 1
' Liaision anticipée => adOpenKeyset, adLockPessimistic, adCmdText
' S'il y a des enregistrements concernés :
If
Rs.EOF
=
False
Then
Rs.MoveFirst
' Replace le pointeur au début du jeu d'enregistrements (facultatif).
Set
TS_ExecuterSQL =
Rs ' Renvoie les enregistrements.
End
If
' Fin du traitement, libération des mémoires :
Gest_Err
:
Set
Rs =
Nothing
Set
Cnn =
Nothing
TS_Err_Number =
Err
.Number
TS_Err_Description =
Err
.Description
If
Err
.Number
<>
0
Then
Set
TS_ExecuterSQL =
Nothing
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
XI. Annexe 2 – Fusionner deux fichiers PDF en VBA▲
Si vous disposez de l’application « Adobe Acrobat Pro » vous pouvez fusionner deux fichiers PDF en utilisant le code VBA suivant :
'------------------------------------------------------------------------------------------------
Public
Function
FusionAcrobatPro
(
FichierSource As
String
, _
FichierAJoindre As
String
, _
FichierDestination As
String
, _
Ouvrir As
Boolean
) As
Boolean
'------------------------------------------------------------------------------------------------------
' Fait la fusion de deux fichiers PDF pour en générer un troisième.
' Documentation Officielle:
' https://opensource.adobe.com/dc-acrobat-sdk-docs/acrobatsdk/html2015/index.html#t=Acro12_MasterBook%2FIAC_API_OLE_Objects%2FAcroExch_PDDoc.htm
'------------------------------------------------------------------------------------------------------
' FichierSource : le fichier d'origine (qui contient les premières pages).
' FichierAJoindre : le fichier à joindre au fichier d'origine, a la suite du fichier d'origine.
' FichierDestination : le fichier qui sera générer par cette fusion.
' Ouvrir : VRAI s'il faut ouvrir le fichier fusionné.
'------------------------------------------------------------------------------------------------------
' Renvoie : VRAI si tout s'est bien passé.
'------------------------------------------------------------------------------------------------------
' Exemple:
' Call FusionAcrobatPro("C:\Users\ott_l\Downloads\FichierA.pdf", _
' "C:\Users\ott_l\Downloads\FichierB.pdf"_
' "C:\Users\ott_l\Downloads\Fusion.pdf", True)
'------------------------------------------------------------------------------------------------------
' Cas de la liaison tardive:
Const
PDSaveFull =
1
' Gestion des erreurs:
Err
.Clear
On
Error
GoTo
Gest_Err
Dim
oPdfDoc1 As
Object
Dim
oPdfDoc2 As
Object
Set
oPdfDoc1 =
CreateObject
(
"AcroExch.PDDoc"
)
Set
oPdfDoc2 =
CreateObject
(
"AcroExch.PDDoc"
)
If
oPdfDoc1.Open
(
FichierSource) =
False
Then
_
Err
.Raise
vbObjectError
, "FusionAcrobatPro"
, "Le fichier ["
&
FichierSource &
"] n'a pas été trouvée."
If
oPdfDoc1.GetNumPages
(
) <
1
Then
_
Err
.Raise
vbObjectError
, "FusionAcrobatPro"
, "Impossible de lire les pages du fichier ["
&
FichierSource &
"]."
If
oPdfDoc2.Open
(
FichierAJoindre) =
False
Then
_
Err
.Raise
vbObjectError
, "FusionAcrobatPro"
, "Le fichier ["
&
FichierAJoindre &
"] n'a pas été trouvée."
If
oPdfDoc2.GetNumPages
(
) <
1
Then
_
Err
.Raise
vbObjectError
, "FusionAcrobatPro"
, "Impossible de lire les pages du fichier ["
&
FichierAJoindre &
"]."
If
oPdfDoc1.InsertPages
(
0
, oPdfDoc2, 0
, oPdfDoc2.GetNumPages
(
), 0
) =
False
Then
_
Err
.Raise
vbObjectError
, "FusionAcrobatPro"
, "Impossible de fusionné les fichiers ["
&
FichierSource &
"] + ["
&
FichierAJoindre &
"]."
If
oPdfDoc1.Save
(
PDSaveFull, FichierDestination) =
False
Then
_
Err
.Raise
vbObjectError
, "FusionAcrobatPro"
, "Impossible de sauvegarder la fusion ["
&
FichierDestination &
"]."
If
Ouvrir =
True
Then
Call
Shell
(
"Explorer.exe "
&
FichierDestination, vbMaximizedFocus)
' Fin du traitement:
Gest_Err
:
If
Err
.Number
<>
0
Then
MsgBox
"Erreur : "
&
Err
.Number
&
vbCrLf
&
vbCrLf
_
&
"Description : "
&
Err
.Description
&
vbCrLf
&
vbCrLf
_
&
"Source : "
&
Err
.Source
&
vbCrLf
&
vbCrLf
_
, vbCritical
, "L'application rencontre une erreur de traitement"
Else
FusionAcrobatPro =
True
End
If
On
Error
Resume
Next
oPdfDoc1.Close
oPdfDoc2.Close
Set
oPdfDoc1 =
Nothing
Set
oPdfDoc2 =
Nothing
Err
.Clear
End
Function
'------------------------------------------------------------------------------------------------
XII. Annexe 3 – les variables publiques et les énumérations du module▲
Pour compléter les codes présentés, voici les variables publiques et les énumérations utilisées, déclarées en en-tête du module :
'------------------------------------------------------------------------------------------------
Public
TS_Err_Number As
Long
Public
TS_Err_Description As
String
Public
Enum Enum_InfoTS
TS_Valeur
TS_Formule
TS_CouleurTexte
TS_CouleurFond
TS_Gras
TS_Italique
TS_Visible
TS_Commentaire
TS_Format
TS_LienHypertexte
TS_ImageCommentaireJPG
TS_Ajouter
TS_Soustraire
TS_Multiplier
TS_Diviser
End
Enum
Public
Enum Enum_ImportationTS
TS_Ajout_Forcé
TS_MAJ_Uniquement
TS_MAJ_Ou_Ajout
TS_IgnorerSiExiste
End
Enum
Public
Enum Enum_ExportationTS
TS_XLSX
TS_CSV
TS_BMP
TS_JPG
TS_PDF
End
Enum
'------------------------------------------------------------------------------------------------