[Tutoriel] Fonctions en VBA pour gérer les Tableaux Structurés d’Excel

Version imprimable

Voir 40 message(s) de cette discussion en une page

14/02/2022, 20h07
laurent_ott

[Tutoriel] Fonctions en VBA pour gérer les Tableaux Structurés d’Excel

Bonjour.
J'ai le plaisir de vous présenter une nouvelle documentation :

Fonctions en VBA pour gérer les Tableaux Structurés d’Excel

Dans cet article vous allez découvrir des fonctions en VBA pour gérer les tableaux structurés d’Excel.
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.

Vous pouvez déposer vos commentaires dans cette discussion.
Bonne lecture.
15/02/2022, 09h58
Malick

Bonjour Laurent,

Excellente contribution qui montre encore la puissance des tableaux structurés qui malheureusement ne sont pas systématiquement utilisés :ccool:

Je le mets dans ma liste des tutos de référence à l’instar de ceux rédigés par Pierre Fauconnier:coucou:

:merci: beaucoup
17/02/2022, 23h37
Invité

Bonsoir,

Merci pour ce lien c'est intéressant.
02/03/2022, 08h47
Keuf95

Petit oubli

Bonjour et merci pour ce tuto sur le VBA et les tableaux.

Juste un petit oubli il manque un "End Function" à la fin de la première Function : TS_ConvertirPlageEnTS
Il faut bien qu'il reste quelque chose à faire pour nous faire réfléchir un simple copier/coller de code serait trop facile :D

Mais lorsqu'il n'y a plus que ça à faire on devrait s'en sortir.
Encore une fois merci pour le travail.

Bonne continuation.
02/03/2022, 09h03
laurent_ott

Bonjour,
Merci pour cette remarque.
Je viens de corriger cette coquille.

Bonne programmation.
12/05/2022, 21h31
spookyz

Merci, c'est super!

SUPER ! Super boulot, super pratique, super pédagogie, super merci.:D

Fonction ts importer données

Bonjour et félicitations pour votre travail.
Je me permets de vous signaler un problème rencontré à l'utilisation
En ce qui concerne la fonction d'importation des données, en méthode ajout modif, à ce niveau dans la procédure
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 ' 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
il se trouve que si le tableau qui reçoit les données est vide (tableau structuré vide contenant une ligne vide), la variable Ajout = False et une nouvelle ligne est créée alors qu'elle existe déjà.
Sinon sur le bouclage des données à importer, ne faudrait-il pas d'abord boucler sur les lignes puis sur les colonnes ?

Pour ma part j'ai modifié en testant le nombre de lignes pour en ajouter une ou pas.
Puis je boucle sur les colonnes de la table d'importation pour remplir la ligne qui reçoit les valeurs.

Je ne sais pas si cela est correct (je ne suis pas du métier) mais cela fonctionne maintenant pour ma part.

Cdt et encore merci pour vos travaux 👌👍👍👍👍👍

Merci AlainD49 pour votre retour d'expérience.

Je profite de l'occasion pour ajouter une fonction qui supprime plusieurs lignes (c'est plus rapide que de le faire une à une) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
'------------------------------------------------------------------------------------------------
Public Function TS_SupprimerPlusieursLignes(TS As Range, ByVal LigneDébut As Long, ByVal LigneFin As Long) As Boolean
'------------------------------------------------------------------------------------------------
' Supprime plusieurs lignes dans un Tableau Structuré, même si elles sont masquées.
'------------------------------------------------------------------------------------------------
' TS : La plage du Tableau Structuré.
' LigneDébut : La position de la première ligne à supprimer dans le tableau.
' LigneFin   : La position de la dernière ligne à supprimer dans le tableau
'              Si 0 alors supprime la dernière ligne du tableau.
'------------------------------------------------------------------------------------------------
' Renvoie : Vrai si une ligne a été supprimée.
'------------------------------------------------------------------------------------------------
' Remarque : Une fois toutes les lignes supprimées, il ne reste qu'une ligne vide sous l'en-tête.
'------------------------------------------------------------------------------------------------
Dim MesFiltres As Variant
Dim Anc_ScreenUpdating As Boolean
Dim Ligne As Long
 
' Gestion des erreurs:
On Error GoTo Gest_err
Err.Clear
 
' Contrôle la cohérence des lignes passées en argument:
LigneDébut = TS_IndexLigne(TS, LigneDébut)
If LigneDébut = -1 Then Err.Raise vbObjectError, , TS_Err_Description
LigneFin = TS_IndexLigne(TS, LigneFin)
If LigneFin = -1 Then Err.Raise vbObjectError, , TS_Err_Description
 
' S'il faut inverser le début et la fin:
If LigneFin < LigneDébut Then
    Ligne = LigneDébut
    LigneDébut = LigneFin
    LigneFin = Ligne
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)
 
' Supprime les lignes:
TS.ListObject.DataBodyRange.Rows(LigneDébut & ":" & LigneFin).Delete
TS_SupprimerPlusieursLignes = 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

Bonne programmation.

15/07/2022, 16h40
laurent_ott

Bonjour,
Je viens de mettre à jour la documentation et y ajouter deux autres fonctions pour supprimer les doublons, et pour supprimer les lignes visibles (préalablement sélectionnées par un filtre).
Bonne programmation.
30/09/2022, 14h48
laurent_ott

Bonjour,

Je viens de rajouter les fonctions suivantes à la documentation :
- TS_SupprimerLignesMasquées : pour supprimer les lignes masquées.
- TS_CouleurLigneChangeValeur : pour alterne la couleur de fond des lignes visibles d'un tableau structuré à chaque changement de valeur dans la colonne de votre choix.
- TS_ForcerValeurColonne : pour met une valeur unique dans une colonne d'un tableau structuré.
- TS_Enregistrer_Dans_Access : pour enregistrer un tableau structuré ou certaines de ses colonnes dans une base Access.
- TS_CopierUneColonne : pour copier une colonne d'un tableau structuré dans un autre tableau structuré.
- TS_CopierUnTableau : pour copier l'intégralité d'un tableau structuré (y compris les colonnes masquées) dans un autre tableau structuré. Les données copiées sont soit les valeurs soit les formules.
- TS_MémoriseTableau : pour mémoriser les données d'un tableau structuré dans un variant à plusieurs dimensions. Les données mémorisées sont soit les valeurs soit les formules.

Bonne programmation.
13/10/2022, 18h23
fvalera

Bonjour,

Déjà superbe travail.

Concernant la fonction copier une colonne, existe il la même fonction pour plusieurs colonnes ?
Je souhaiterais copier plusieurs colonnes non contiguës d'un tableau A filtré d'un classeur A à la suite d'un tableau B d'un classeur B.
Le nombre de lignes à copier est variable.

Merci d'avance

Citation:

Envoyé par fvalera

Je souhaiterais copier plusieurs colonnes non contiguës d'un tableau A filtré d'un classeur A à la suite d'un tableau B d'un classeur B.

Bonjour,
Voici les étapes à suivre :
Identifier le tableau source et le tableau destination.
Ajouter une ligne au tableau destination, c'est à cette ligne que seront importées les données.
Mémoriser le nombre de lignes dans le tableau destination = numéro de la nouvelle ligne.
Copier la colonne désirée du tableau source dans la colonne désirée du tableau destination sur cette nouvelle ligne.
Copier la colonne désirée du tableau source dans la colonne désirée du tableau destination sur cette nouvelle ligne.

Exemple pour copier la première et la dernière colonne du tableau source filtré dans les deux premières colonnes du tableau destination.
Note : pour définir un tableau structuré d'un autre classeur que le classeur actif (le classeur actif est celui qui contient le tableau structuré destination) il faut indiquer le nom de classeur, et aussi le nom de la feuille :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Dim TS_Source As Range Dim TS_Dest As Range Dim Dern_Ligne As Long Set TS_Source = Workbooks("Classeur_Source.xlsm").Sheets("Feuille_Source").Range("Tableau_Source") Set TS_Dest = Range("Tableau_Destination") Call TS_AjouterUneLigne(TS_Dest, 0) Dern_Ligne = TS_Nombre_Lignes(TS_Dest) Call TS_CopierUneColonne(TS_Source, 1, TS_Dest, 1, Dern_Ligne, xlCellTypeVisible) Call TS_CopierUneColonne(TS_Source, 0, TS_Dest, 2, Dern_Ligne, xlCellTypeVisible)
J'en profite pour signaler que je viens de mettre à jour la documentation en ajoutant deux nouvelles fonctions :
- TS_FormatColonne : pour définir le format numérique d'une colonne.
- TS_RequeteBaseAccess : pour faire des requêtes en SQL dans une base Access.

N'hésitez pas à partager vos fonctions que j'intégrerai éventuellement dans la documentation.
Bonne programmation.

Hello Laurent,
Suite à cette discussion

Citation:

Dans un tableau structuré, je n'arrive pas à trouver les propriétés permettant de renvoyer la colonne et/ou la ligne d'une cellule sélectionnée.
N'y-a-t'il pas d'autre choix que de passer par Activecell.Column et Activecell.Row de la feuille Excel?

J'ai proposé une solution mais j'utilise l'ActiveCell, n'y-a-t-il vraiment pas d'autres types de solution ?
Fonction proposée :
Function CellActiveTS(NomTableau) As Variant
La fonction teste si la cellule active fait partie du tableau, si cela est vrai elle renvoie un tableau de 3 éléments :
le premier élément est un booléen qui indique si le test a réussi ou non.
le deuxième élément indique le nom de la colonne.
le troisième élément indique le numéro de ligne de données dans le tableau.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Function CellActiveTS(NomTableau) As Variant Dim TempArray(1 To 3) TempArray(1) = False TempArray(2) = "" TempArray(3) = 0 Dim r As Range, lo As ListObject Set r = ActiveCell Set lo = r.ListObject If Not lo Is Nothing Then If lo.Name = NomTableau Then TempArray(1) = True TempArray(2) = Intersect(lo.HeaderRowRange, r.EntireColumn).Value TempArray(3) = r.Row - lo.Range.Row End If End If CellActiveTS = TempArray End Function
exemple d'utilisation :
Code:

1 2 3 4 5 6 7 8 9 10 Sub TestCellActiveTS() Dim ActCellTS ActCellTS = CellActiveTS("sales") If ActCellTS(1) Then Debug.Print "Colonne : " & ActCellTS(2) Debug.Print "N° Ligne de données : " & ActCellTS(3) Else Debug.Print "La cellule active ne fait pas partie du tableau" End If End Sub
Ami calmant, J.P

14/10/2022, 22h09
laurent_ott

Citation:

Envoyé par jurassic pork

J'ai proposé une solution mais j'utilise l'ActiveCell, n'y-a-t-il vraiment pas d'autres types de solution ?

Bonjour jurassic pork

Il existe peut-être une autre solution pour répondre à cette demande que d'utiliser "ActiveCell" mais je ne la connais pas.
En tout cas cela ne me choque pas.
J'ai testé ton code et il est parfais (comme d'habitude), bravo.
Si tu me l'autorises j'aimerais bien le rajouter à la documentation, dans un chapitre "vos contributions".

Amicalement, Laurent.
15/10/2022, 08h42
jurassic pork

Hello Laurent,

Citation:

Envoyé par laurent_ott

J'ai testé ton code et il est parfait (comme d'habitude), bravo.
Si tu me l'autorises j'aimerais bien le rajouter à la documentation, dans un chapitre "vos contributions".

Pas de problème, tu peux faire ce que tu veux du code.
Ami calmant, J.P
15/10/2022, 12h12
Philippe Tulliez

Bonjour J.P,

Citation:

La fonction teste si la cellule active fait partie du tableau

La propriété Active de l'objet ListObject permet de savoir si la cellule active (ActiveCell) est dans un tableau nommé

Exemple : MsgBox Range("t_List").ListObject.Active
15/10/2022, 12h40
jurassic pork
Hello Philippe,
Merci du renseignement. On peut alors dans ma fonction remplacer les lignes :
Code:

1 2 If Not lo Is Nothing Then If lo.Name = NomTable Then
par :

Code:

If Range(NomTable).ListObject.Active Then

Ami calmant, J.P
18/10/2022, 17h33
deedolith

Je n'ai pas encore tout lu en détail, mais deux gros point noir me sautent aux yeux:

1) Tu déroutes le système de gestion des erreurs, ce qui a des conséquences non négligeables voir très lourdes:
Les fonctions ne plantent pas, mais exigent de vérifier après chaque appel, à minima la valeur de la variable TS_Err_Number (voir TS_Err_Description) pour s'assurer que ces dernières ont fait leur job correctement (comme en VB Script).
Ce qui, à mon sens, va à l'encontre de toute programmation moderne. Il sera plus sage de laisser les exceptions remonter s'il y a lieux, ou lever une exception personnalisée.

2) Les variables TS_Err_Number et TS_Err_Description sont globales, et apportent tous les inconvénients qui vont avec (la liste est déjà suffisamment longue, je n'en rajouterai pas).
21/10/2022, 12h54
gstn_lgff

Merci pour vos contributions

Merci à tous pour vos contributions.

Elles me sont très utiles et je vais les intégrer dans mon code.

Cordialement,

JM
25/11/2022, 18h04
laurent_ott

Citation:

Envoyé par deedolith

Je n'ai pas encore tout lu en détail, mais deux gros point noir me sautent aux yeux:
1) Tu déroutes le système de gestion des erreurs, ce qui a des conséquences non négligeables voir très lourdes:
Les fonctions ne plantent pas, mais exigent de vérifier après chaque appel, à minima la valeur de la variable TS_Err_Number (voir TS_Err_Description) pour s'assurer que ces dernières ont fait leur job correctement (comme en VB Script).
Ce qui, à mon sens, va à l'encontre de toute programmation moderne. Il sera plus sage de laisser les exceptions remonter s'il y a lieux, ou lever une exception personnalisée.

Bonjour,
J'ai pris en compte cette remarque et j'ai modifié la gestion des erreurs. Désormais il est possible grâce à la nouvelle fonction "TS_SiErreur" de déclencher ou non la boîte de message directement dans la fonction où se produit l'erreur.
J'ai ajouté d'autres fonctions :
- TS_RechercherVisible : pour faire des recherches sur les cellules visibles uniquement, plus rapide que TS_Rechercher mais moins complète.
- TS_Remplacer : pour remplacer une valeur par une autre dans une colonne.
- TS_Range : pour obtenir une mémoire du tableau structuré.
- TS_ForcerValeurColonne : pour forcer une valeur dans toute une colonne.
- TS_ImporterDepuisAccess : pour importer des données d'Access et les coller dans un tableau structuré.
- TS_CelluleActive : en m'inspirant des dernière messages de cette discussion cette fonction renseigne si la cellule active est dans le tableau structuré désiré.

Bonne programmation.

Voir 40 message(s) de cette discussion en une page