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

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

Bonsoir,

Merci pour ce lien c'est intéressant.

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.

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

Bonne programmation.

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

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.

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.

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.

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

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.

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

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

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

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

Merci à tous pour vos contributions.

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

Cordialement,

JM

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.

Désolé, c'est toujours aussi moche.

VBA est un langage à exceptions, renvoyer un indicateur de bonne exécution est une pratique qui date du crétacé et n'a aucun sens aujourd'hui et a pour effet (catastrophique) d'inculquer de mauvaises pratiques à tes lecteurs. Revient à une gestion d'erreurs classique, tout le monde ne s'en porteras que mieux. Ca vireras également tes globales inutiles, et te permettras de renvoyer des valeurs utiles au profit des arguments d'entrée/sortie archaïques.

N'inclue une gestion d'erreur que lorsque c'est strictement nécessaire, et non pas partout (un autre archaïsme).

De plus, il n'est pas de la responsabilité de tes fonctions d'afficher un message d'erreur, ce n'est tout simplement pas leur job. Plus d'infos ici: https://en.wikipedia.org/wiki/Single...lity_principle

Les accents dans les noms de variable / fonction sont à bannir (ce n'est pas portable).

Je ne suis pas fan de la notation hongroise.

65535 lignes ? Excel sais faire mieux que cela de nos jours.

Etudie la possibilité d'un développement orienté objet.

Bonjour,
Pour information, j'ai ajouté les fonctions suivante à la documentation :

TS_Filtres_Existe : renseigne si le tableau structuré passé en argument est filtré ou non, ou si une colonne particulière du tableau est filtrée.
TS_CopierValeurColonne : copie en valeur une colonne d'un tableau structuré dans un autre tableau structuré, ou dans le même tableau, voire dans la même colonne ce qui équivaut dans ce cas à un copier/coller en valeur.
TS_DéplacerUneColonne : déplace une colonne dans un tableau structuré.
TS_CopierUneLigne : copie un ligne (en valeur ou en formule) d'un tableau structuré dans un autre tableau structuré, ou dans le même tableau.

Bonne programmation.

Bonjour Laurent et merci beaucoup pour tout ce travail qui m'aide énormément !!

Je me suis servi de ton code notamment pour ajouter des lignes dans un tableau structuré filtré dans une page protégée, via un bouton que j'ai ajouté sur ma feuille.
Le résultat fonctionne, merci !

Pour autant, j'ai tout de même une remarque !!
Lorsque j'ai un filtre actif (certaines valeurs sélectionnées et "vide" non coché), la ligne ajoutée est bien ajoutée, mais non visible.
En effet, la ligne ajoutée est vide de texte dans les différentes colonnes et comme j'ai un filtre qui n'affiche pas les vides, elle reste masquée.

Or j'aimerai avoir le comportement "standard" dans Excel quand j'insère une ligne en effectuant un clic droit sur mon tableau structuré filtré non protégé : c'est à dire que

• les filtres restent
• La ligne est ajoutée à l'endroit ou j'ai cliqué
• Elle est visible, même si les filtres ne sont du coup plus "respectés"

Je ne sais pas si je suis clair mais j'espère être assez compréhensible...
Existe-t-il une solution en VBA ?
Je ne trouve pas le "principe" de base qui pourrait faire ça.... donc je ne sais vraiment pas ou chercher.

Alors si tu as une idée, je suis preneur !
Et encore merci,
Guillaume

Bonjour,
Tout d'abord: n'hésitez pas à consulter de temps en temps cette documentation car j'y ajoute souvent de nouvelles fonctionnalités sans forcément les signaler dans cette discussion.

Pour votre problème je vous propose de renseigner une valeur non vide dans la colonne filtrée (un espace par exemple), si c'est possible.
Bonne programmation.

Merci Laurent pour cette proposition :)

Pour mon besoin, ce n'est pas possible car d'une part je ne sais pas quelle colonne pourra être filtrée ni avec quelles valeurs. Chaque utilisateur fera son propre filtre selon ses souhaits.
Et j'ai des colonnes avec des formules qui s'incrémentent en auto, donc je ne voudrai pas les supprimer par erreur.... (c'est l'avantage du tableau structuré !)

Par ailleurs, j'ai remarqué que la fonction de mémorisation des filtres ne fonctionnait pas dès lors que l'on avait plusieurs choix dans un filtre (j'ai essayé avec 4 et cela n'a pas marché).
==> ça vient peut être de moi, je suis très loin d'être un bon en VBA !

J'ai donc mis à jour ton code pour mes besoins et voici ce qui fonctionne chez moi :
Dans ma page; voici ce que j'ai :

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

Private Sub CommandButton1_Click() 'je déprotège la feuille Call déverrouillage_feuille   ' Je bloque la mise à jour de l'écran Anc_ScreenUpdating = Application.ScreenUpdating Application.ScreenUpdating = False   'test sur les filtres Dim Tableau As Range, MesFiltres As Variant Set Tableau = Range("Liste_preliminaire") If TS_Filtres_Existe(Tableau) = True Then   ' Enregistrer les filtres Call TS_Filtres_Memoriser(Tableau, MesFiltres)   ' Ajouter xx lignes (sélection) avec un numéro incrémental pour chaque ligne Call TS_ajout_ligne_LP   'Remettre les filtres mémorisés Call TS_Filtres_Restaurer(Tableau, MesFiltres)   Else ' Ajouter xx lignes (sélection) avec un numéro incrémental pour chaque ligne Call TS_ajout_ligne_LP   End If   'je remets l'affichage Application.ScreenUpdating = Anc_ScreenUpdating   'je protège la feuille Call verrouillage_feuille   End Sub

---

Et au niveau du module, voici le code mis, fortement inspiré de tes sources ! :

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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90

Option Explicit   Public Function TS_Filtres_Existe(TS As Range) As Boolean ' Indique si un Tableau Structuré est filtré ou non ' TS : La plage du Tableau Structuré.   Dim f As Integer   ' Boucle sur les filtres du tableau pour savoir s'ils sont actifs ou non: With TS.ListObject.AutoFilter.Filters For f = 1 To .Count If .Item(f).On Then TS_Filtres_Existe = True Exit For End If Next f End With   End Function   Public Function TS_Filtres_Memoriser(TS As Range, Memoire As Variant) As Boolean ' Mémorise les filtres d'un Tableau Structuré puis les supprime. ' TS : la plage du Tableau Structuré. ' Mémoire : la mémoire à utiliser.   Dim f As Integer Dim MultiSelect As String Dim iItem As Long Const delim As String = "</:\>"   ' Boucle sur les filtres du tableau: With TS.ListObject.AutoFilter.Filters ReDim Memoire(1 To .Count, 1 To 4) For f = 1 To .Count With .Item(f) Memoire(f, 1) = .On If .On Then Memoire(f, 2) = .Operator If .Operator = 7 Then MultiSelect = "" For iItem = LBound(.Criteria1) To UBound(.Criteria1) MultiSelect = MultiSelect & IIf(MultiSelect = "", "", delim) & .Criteria1(iItem) Next iItem Memoire(f, 3) = MultiSelect Else Memoire(f, 3) = .Criteria1 If .Operator = 1 Or .Operator = 2 Then Memoire(f, 4) = .Criteria2 End If End If End If End With Next f End With   ' je supprime les filtres TS.ListObject.AutoFilter.ShowAllData   End Function   Public Function TS_Filtres_Restaurer(TS As Range, Memoire 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. Dim f As Integer Dim Monfiltre As Variant Dim Monfiltre2 As Variant Const delim As String = "</:\>"   For f = 1 To UBound(Memoire, 1) If Memoire(f, 1) = True Then 'Il y a un filtre. Est-ce un multi-selection? If Memoire(f, 2) = 7 Then Monfiltre = Split(Memoire(f, 3), delim) TS.ListObject.Range.AutoFilter Field:=f, Criteria1:=Monfiltre, Operator:=xlFilterValues Else 'le filtre est "classique" Monfiltre = Memoire(f, 3) If Memoire(f, 2) = 1 Or Memoire(f, 2) = 2 Then Monfiltre2 = Memoire(f, 4) TS.ListObject.Range.AutoFilter Field:=f, Criteria1:=Monfiltre, Operator:=Memoire(f, 2), Criteria2:=Monfiltre2 Else TS.ListObject.Range.AutoFilter Field:=f, Criteria1:=Monfiltre End If End If End If Next f   End Function

---

Enfin, dans un autre module, j'ai le code pour supprimer la ligne :

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

' Ajouter xxx lignes (sélection) dans le tableau liste préliminaire Public Function TS_ajout_ligne_LP() Dim LastIndex As Integer Dim LastRow As Long Dim CurrentRow As Long Dim counter As Long counter = 0 Dim I As Integer Dim selectionLength As Long ' variable nombre de lignes selectionnées Dim selectedRange As Excel.Range Set selectedRange = Selection selectionLength = selectedRange.Rows.Count ' compte les lignes selectionnées   With ActiveSheet LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'Retrouve le numéro de la dernière ligne LastIndex = Application.WorksheetFunction.Max(Range("'Liste préliminaire'!A7:A" & LastRow)) 'Calcul l'index maximal enregistré End With   If Not Intersect(ActiveCell, Range("Liste_preliminaire")) Is Nothing Then If Not Intersect(ActiveCell, Range("A7:DZZ7")) Is Nothing Then MsgBox "Il n’est pas possible d’ajouter de ligne au dessus la ligne 7" Else   For I = 1 To selectionLength Range("A" & Selection.Rows(I).Row).Insert Range("A" & Selection.Rows(I).Row).Value = LastIndex + I 'Donne la valeur de l'index suivant Next End If   Else   MsgBox "Vous ne pouvez pas insérer une ligne ici ! Merci de sélectionner une ligne du tableau"   End If End Function

---

Pour finir, j'ai l'impression qu'il pourrait être possible de m'appuyer sur la cellule active (activecell) et trouver une solution pour l'afficher sans perdre les filtres....
Je suis preneur de toute idée sur laquelle je pourrai m'appuyer !

Mais j'atteins mes limites.... et ne sais pas si c'est possible....

Si tu vois une amélioration du code posté ou des erreurs, n'hésites pas, je prend très bien toutes les remarques ;-)
Bonne journée,

Bonjour, soyez béni ainsi que toute votre famille sur plusieurs générations pour tout ce travail en open source, merci.

Bonjour,

Pour information, j'ai apporté des nouveautés dans le module "TS" :

- TS_ObjetInserer : pour insérer un objet dans une cellule, l'objet peut être une image, un fichier texte, un classeur Excel.
et les fonctions qui vont avec :
TS_ObjetSupprimer : pour supprimer un objet.
TS_ObjetNom : pour obtenir le nom d'un objet.
TS_ObjetRenommer : pour renommer un objet.

- Mais surtout : "TS_ImporterDepuisClasseur" qui copie en valeur les données d'un tableau tableau, structuré ou non structuré, issues d'un classeur Excel (théoriquement) fermé dans un tableau structuré (existant) du classeur contenant le code. On peut importer toutes les colonnes du classeur ou seulement certaines, pour remplacer les données existantes ou les ajouter.

- J'ai corrigé une erreur de jeunesse : en ajoutant "LookAt:=xlWhole" dans la fonction de recherche "TS_RechercherVisible" et la fonction de remplacement "TS_Remplacer".

Bonne Programmation.

Bonjour,

Voici les nouveautés disponibles dès maintenant :

- TS_ImporterDepuisClasseurAvecSQL : Importe en valeur les données d'un tableau, structuré ou non structuré, issues d'un autre classeur avec une requête SQL pour sélectionner les données à importer. Les données importées peuvent remplacer les données existantes ou s'y ajouter.

- TS_ImporterFichierTexteAvecSQL : Importe les données d'un fichier texte dont les champs sont délimités, généralement par un point-virgule, suivant une requête SQL. Les données importées peuvent remplacer les données existantes ou s'y ajouter.

- TS_RequeteSQL : Modifications apportées pour gérer les requêtes sur plus de 255 colonnes ou 65 535 lignes.

Bonne programmation.
Laurent Ott.

Salut,

Tu risques de me trouver lourdingue, mais ta gestion des erreurs n'est toujours pas bonne.
Un simple exemple pour s'en convaincre:
Public Function TS_IndexColonne(TS As Range, Colonne As Variant) As Long

Code:

---

1 2	Dim index As Long index = TS_IndexColonne(Sheet1.Range("A1:F10"), "Un nom qui n'existe pas")

---

Ca devrait planter.
Problème: Ca ne plante pas !!!

Tes fonctions exigent de tester en permanence les code de retour, ce qui résulte en un code perclu d'instruction If ... Then.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

If(TS_... = -1) Then Exit Sub End If If(TS_... = -1) Then Exit Sub End If If(TS_... = -1) Then Exit Sub End If If(TS_... = -1) Then Exit Sub End If

---

De nos jours, personne n'a envie d'écrire un code comme ça.

Citation:

---

Envoyé par deedolith

Tu risques de me trouver lourdingue, mais ta gestion des erreurs n'est toujours pas bonne.

---

Effectivement, nous n’avons pas tous les mêmes habitudes de programmation.

J’ai modifié la gestion des erreurs du module « TS » en conséquence et désormais trois possibilités sont offertes par la fonction « TS_SiErreur » pour paramétrer la façon dont vous souhaitez gérer les erreurs, en sélectionnant l’une des options suivantes :
- TS_Générer_Erreur : (par défaut) La fonction renvoie l'erreur à la procédure appelante. Si celle-ci possède un gestionnaire d'erreurs validé, il est activé afin de gérer l'erreur à l’étiquette indiquée (voir exemple ci-dessous), sinon le traitement est interrompu.
- TS_MsgBox_Erreur : L’erreur n’est pas renvoyée à la fonction appelante mais une boîte de message signale l’erreur, et la fonction revoie un code d’erreur (suivant la fonction : False ou -1).
-TS_Ignorer_Erreur : L’erreur est « ignorée », seul le code d'erreur renvoyé (suivant la fonction : False ou -1) permet de savoir qu’une erreur s’est produite.

Dans les trois cas :
- la fonction renvoie un code d'erreur, généralement False ou -1 ;
- les variables publiques « TS_Err_Number » et « TS_Err_Description » sont alimentées et peuvent être consultées par la fonction appelante.
- les fonctions « TS_IndexColonne » et « TS_IndexLigne » font exception et renvoient -1 en cas d'erreur.

Exemple avec un cas (théorique, c'est juste pour mettre en pratique ces trois options) où l’utilisateur sélectionne un fichier contenant un tableau structuré nommé « Tableau1 » à importer dans le classeur actif. Puis les données sont mises en forme et triées sur la colonne « Prenom » :

Ligne 15 : L’instruction « Call TS_SiErreur(TS_Ignorer_Erreur) » permet d’ignorer les erreurs du module « TS ». Ainsi la fonction « TS_ImporterDepuisClasseur » (ligne 21) renvoie False en cas d’erreur sans bloquer la suite du traitement : l’utilisateur s’est simplement trompé de fichier, on lui demande s’il veut en sélectionner un autre.
On pourrait aussi utiliser « On Error Resume Next » avant l’appel de la fonction puis restaurer le gestionnaire d’erreur « On Error GoTo Gest_Err » après. C’est à vous de choisir ce que vous préférez.

Ligne 30 : L’instruction « Call TS_SiErreur(TS_MsgBox_Erreur) » affiche un message en cas d’erreur. Ici rien n’est bloquant, l’utilisateur a peut-être renommé la colonne « Date » en « Dates », peu importe car elle ne sera pas utilisée dans la suite du traitement, un message signale simplement cette anomalie (qu’il conviendra de corriger).

Ligne 38 : L’instruction « Call TS_SiErreur(TS_Générer_Erreur) » branchera la procédure à l’étiquette « Gest_Err » en cas d’erreur (étiquette déclarée par « On Error GoTo Gest_Err »).
Si l’utilisateur a renommé la colonne « Prenom » en « Prénom » l’erreur est critique car cette colonne est utilisée par diverses opérations, donc la procédure est terminée et un message affiche l’erreur.

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

'------------------------------------------------------------------------------------------------------ Sub Exemple() '------------------------------------------------------------------------------------------------------ Dim Tableau As Range Dim Fichier As String Dim Moyenne As Double Set Tableau = Range("Tableau1")   ' Gestion des erreurs: On Error GoTo Gest_Err Err.Clear   ' Ne bloque pas le traitement en cas d'erreur, c'est jusque que l'utilisateur ' a sélectionné le mauvais fichier: Call TS_SiErreur(TS_Ignorer_Erreur)   Do ' Sélectionne un fichier Excel: Fichier = Application.GetOpenFilename("Fichiers Excel,*.xls*") ' Importe les données dans un tableau structuré: If TS_ImporterDepuisClasseur(Fichier, "", "Tableau1", Tableau, TS_RemplacerDonnées, "") = False Then ' Si ce n'est pas le bon fichier alors le signaler: If MsgBox("Voulez-vous sélectionner un autre fichier ?", vbQuestion + vbYesNo, _ "Ce n'est pas le bon fichier") = vbNo Then Exit Sub Fichier = "" End If Loop While Fichier = ""   ' Génère un message en cas d'erreur mais continue la traitement: Call TS_SiErreur(TS_MsgBox_Erreur)   ' Format Numérique et format date: Call TS_FormatColonne(Tableau, "Note", "0.0", True) Call TS_FormatColonne(Tableau, "Date", "dd/mm/yyyy")   ' Branchement à l'étiquette "Gest_Err" si une erreur se produit dans les traitements ' du tableau: Call TS_SiErreur(TS_Générer_Erreur)   ' Trie la colonne "Prenom": Call TS_TrierUneColonne(Tableau, "Prenom", xlSortOnValues, xlAscending, True)   ' Compte la moyenne des notes: Moyenne = TS_ValeurColonne(Tableau, "Note", xlTotalsCalculationAverage, False)   ' Autres traitements:   ' ...   ' Gestion des erreurs: Gest_Err: If Err.Number <> 0 Then MsgBox "Erreur dans la fonction : " & Err.Source & vbCrLf & vbCrLf _ & Err.Number & " : " & Err.Description, _ vbCritical, "Exemple" Err.Clear End Sub '------------------------------------------------------------------------------------------------------

---

En espérant que cette façon de faire convienne au plus grand nombre, je n’impose rien à personne et vous laisse adapter les fonctions du module « TS » et la gestion des erreurs à votre convenance.
Mais j’imagine qu’une « loi » explique que « la bonne pratique » est de s’assurer en amont que les données à traiter soient correctes afin que l’erreur reste l’exception et non pas un problème que l’on gère comme on peut.

Bonne programmation.

Beau boulot laurent, merci beaucoup :bravo:

Bonjour.

Merci bcp pour le tuto sur les tableaux structurés excel.

En repartant du fichier Excel mis à disposition (Tableaux-Structures.xlsm) et plus précisemment en utilisant l'onglet "Villes" j'ai essayé d'utiliser la fonction TS_Rechercher. J'ai constaté un problème avec par exemple l'appel suivant :

Code:

---

1 2 3 4 5

Dim oRange As Range Set oRange = Range("TS_CP") Dim numLig As Long numLig = TS_Rechercher(oRange, True, "Code postal", 1400, "Code département", "01").

---

Dans le tableau TS_CP de l'onglet "Villes" la donnée "Code postal" est stockée comme un numérique et la donnée "Code département" comme un String.
Dans la function TS_Rechercher l'élément recherché "01" devient en fait "1".

En mettant quelques lignes de Debug dans la function TS_Rechercher je constate que l'Array "Valeur" est un Array avec 2 données (OK) dont les valeurs sont 1400 et 1 (et non 01).

La function retourne donc "logiquement" 0 et non 1.

Comment faut il faire pour utiliser la fonction dans ce cas de figure.

Cdt
PS : la recherche avec des strings fonctionne sans pb. numLig = TS_Rechercher(oRange, True, "Commune", "L ABERGEMENT CLEMENCIAT") est par exemple ok

Citation:

---

Envoyé par fbagent

Dans le tableau TS_CP de l'onglet "Villes" la donnée "Code postal" est stockée comme un numérique et la donnée "Code département" comme un String.
Dans la function TS_Rechercher l'élément recherché "01" devient en fait "1".
En mettant quelques lignes de Debug dans la function TS_Rechercher je constate que l'Array "Valeur" est un Array avec 2 données (OK) dont les valeurs sont 1400 et 1 (et non 01).
La function retourne donc "logiquement" 0 et non 1.
Comment faut il faire pour utiliser la fonction dans ce cas de figure.

---

Bonjour,
Il faut corriger le code source de la fonction "TS_Rechercher":

Remplacer :

Code:

---

If IsNumeric(Valeur(ik)) = True Then Valeur(ik) = CDec(Valeur(ik))

---

par :

Code:

---

If TypeName(Arguments(i + 1)) <> "String" Then Valeur(ik) = CDec(Valeur(ik))

---

Merci de m'avoir signaler cette coquille, je corrigerai dans la prochaine mise à jour de la documentation, prévue pour le mois prochain.
Bonne programmation.

Bonsoir,

Pardon de vous embêter, mais je n'arrive pas à déterminer l'origine de l'erreur :

Lorsque j'utilise pour m'entraîner le fichier "Tableaux-Structures.xlsm", ça ne pose aucun problème, en revanche dès lors que je copie/colle le module TS dans un de mes projets (en général en xlsb pour assurer une rapidité d'exécution) j'ai la fonction "Function TS_ComboBox" qui déclenche systématiquement la même erreur : "Erreur de compilation, type défini par l’utilisateur non défini."

Voici la partie surlignée :

Citation:

---

Function TS_ComboBox(CB As ComboBox, MacroLiée As String, _
Optional TS As Range, _
Optional ByVal StrChamps As String = "", _
Optional ByVal StrSQL As String = "", _
Optional ByVal NombreLignes As Integer = 8, _
Optional ByVal ColonneClé As Integer = 1, _
Optional ByVal LargeurColonnes As String = "") As Boolean

---

C'est un problème de définition de variables ? Je n'arrive pas à comprendre, surtout que sur l'autre fichier le module fonctionne.

Dans tous les cas, je profite de ce message pour vous remercier pour tous les tutos, ce sont des aides précieuses en tant que novice.

Bonjour

Citation:

---

"Erreur de compilation, type défini par l’utilisateur non défini."

---

Il s'agit probablement du type ComboBox (dans CB As ComboBox, ) qui est dans la bibliothèque MS Forms.
Pour la déclarer dans le projet tu passes par Outils, Références (dans l'explorateur VBA) et tu vas cocher dans la liste Microsoft Forms 2.0 Object Library

Bonjour Tête de chat,

Merci pour ta réponse, cependant je ne trouve pas Microsoft Forms 2.0 Object Library.

Pièce jointe 647023



Ce qui est embêtant c'est que le fichier d'exemple fonctionne et le simple copier/Coller ne devrait occasionner aucun désagrément. Et même en ayant les deux fichiers d'ouverts, dans mon projet ça plante aussitôt :roll:

Re,

la référence peut être en début de liste :

Pièce jointe 647139

ça sera le cas si tu l'as déjà utilisée dans un autre classeur...

... comme - au hasard - le fichier d'exemples :

Pièce jointe 647139

Re,

la référence peut être en début de liste :

Pièce jointe 647139

ça sera le cas si tu l'as déjà utilisée dans un autre classeur...

... comme - au hasard - le fichier d'exemples :

Pièce jointe 647140

Bonjour et bravo/félicitations.
Je rencontre un souci avec la fonction TS_CouleurLigneChangeValeur. Je n'arrive pas à la modifier pour que le changement de couleur se fasse en fonction de la valeur de deux colonnes.
Context:
Je récupère un export de données en provenance de SAP (mais sans avoir accès au scripting => par macro car l'action est récurrente). Les données représente dans l'ensemble des routes d'approvisionnement. Parmi les colonnes il y en a une ([Numéro Supply route]) qui comporte un chiffre représentant le n° de la route d'approvisionnement (1: pour la première; 2: lorsque l'on a deux routes d'appro; ...) et une autre colonne ([Article]) qui contient le code de l'article SAP.
Le but recherché:
Apporter une coloration des lignes du tableau en fonction du numéro d'Article: que toutes les lignes ayant le même numéro d'article soient de la même couleur mais que dans le cas de plusieurs routes d'approvisionnement différentes alors la route d'appro n°1 (et toutes les lignes de cet article, propre à la route n°1) ait une couleur différente des lignes de la route d'appro n°2.
Un exemple:

Division	Numéro Supply route	Séquence de tri	Article	Désignation	exemple de changement de couleur
6040	1	1	120	Article 0	bleu
6040	1	2	120	Article 0	bleu
6040	1	1	123	Article 1	incolore ou vert
6040	1	2	123	Article 1	incolore ou vert
6040	1	3	123	Article 1	incolore ou vert
6040	2	1	123	Article 1	bleu
6040	2	2	123	Article 1	bleu
6040	2	3	123	Article 1	bleu
6040	2	4	123	Article 1	bleu
6040	1	1	124	Article 2	incolore ou vert
6040	1	2	124	Article 2	incolore ou vert

Et là je bloque, enfin je ne fais rien de bien à l'exception de passer en revue la variété de messages d'erreur pas super explicites pour moi de VBA.

S'il vous plait pouvez-vous m'apporter une aide?
Merci.

Bonjour,
Je propose d'ajouter une colonne au tableau, qui servira de clé en concatenant les colonnes Division + Route + Article. Trier sur cette clé. Faire un changement de couleur à chaque changement de clé. Supprimer la colonne (si elle n'est plus utilisée).
Bonne programmation.