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.