Discussion :

[laurent_ott]

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.

[Malick]

Bonjour Laurent,

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

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

beaucoup

[Invité]

Bonsoir,

Merci pour ce lien c'est intéressant.

[Keuf95]

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

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

Bonne continuation.

[laurent_ott]

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

Bonne programmation.

[spookyz]

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

[AlainD49]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 👌👍👍👍👍👍

[laurent_ott]

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 VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[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.

[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.

[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

[laurent_ott]

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 VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[jurassic pork]

Hello Laurent,
Suite à cette discussion

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[laurent_ott]

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.

[jurassic pork]

Hello Laurent,

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

[Philippe Tulliez]

Bonjour J.P,

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

[jurassic pork]

Hello Philippe,
Merci du renseignement. On peut alors dans ma fonction remplacer les lignes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

    If Range(NomTable).ListObject.Active Then

Ami calmant, J.P

[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).

[gstn_lgff]

Merci à tous pour vos contributions.

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

Cordialement,

JM

[laurent_ott]

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.