IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

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

Des fonctions génériques en VBA pour gérer facilement 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.

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Excel, principalement depuis sa version 2010, propose une gestion des données sous forme de tableaux dits « tableaux structurés » (ou « Table », « Tableau », « Tableau de données »), une technologie bien plus puissante et ergonomique que les classiques « plages de données ».

Pierre Fauconnier y consacre un tutoriel 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 soit le nom de sa colonne soit son numéro, 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 description Err.Description et d'afficher ou non une boîte de dialogue (nous détaillerons cela avec l'étude de la fonction TS_SiErreur) ;
  • 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 ».

Les codes de cette documentation ont été réalisés avec Excel 2016 version 32 bits sous Windows 10. Ils sont compatibles avec la version 64 bits d'Excel 365.



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.
Mais avant tout nous allons étudier comment sont gérées les erreurs dans les différentes fonctions.

II-A. TS_SiErreur

Les différentes fonctions que nous allons étudier ci-après renvoient pratiquement toutes une valeur d'exécution permettant d'identifier si le traitement s'est déroulé correctement, généralement True si tout s’est bien passé ou False ou -1 dans le cas contraire.

Dans tous les cas, une erreur de traitement alimente les variables publiques TS_ErrNumber et TS_ErrDescription avec respectivement le code de l’erreur Err.Number et sa description Err.Description.

Le programmeur peut donc savoir si une erreur s'est produite en analysant la valeur renvoyée par la fonction appelée, et afficher s'il le souhaite une boîte de dialogue pour décrire cette erreur en utilisant les variables TS_ErrNumber et TS_ErrDescription.
Ce qui peut être lourd à gérer dans une application faisant de nombreux appels aux fonctions comme il m'a été fait remarqué dans la discussion liée à cette documentation.

J’ai donc 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 dans son argument :

  • 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, 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 (False ou -1) ;
  • TS_Ignorer_Erreur : l’erreur est « ignorée », seul le code d'erreur renvoyé (False ou -1) permet de savoir qu’une erreur s’est produite.

Remarque : la fonction TS_SiErreur peut être appelée n'importe où dans votre programme et autant de fois que vous le désirez.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez


Exemple avec un cas (théorique) 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" :

L’instruction Call TS_SiErreur(TS_Ignorer_Erreur) permet d’ignorer les erreurs du module « TS ». Ainsi la fonction TS_ImporterDepuisClasseur 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.

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

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

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------



II-B. 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) :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = TS_ConvertirPlageEnTS(TD:=Sheets("Feuil3").Range("A1"), Nom:="TS_Eleves", _
                                    Style:="TableStyleLight13", AvecEntete:=xlYes)
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible



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"



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-C. 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 forme String ;
  • 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 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------


Image non disponible

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.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-D. 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 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Call TS_SupprimerLeTableau(Range("TS_Eleves"))
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible

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 Range
Set Tableau = Range("TS_Eleves")
Call TS_SupprimerLeTableau(Tableau)

Certain vont préférer passer en argument la plage du tableau sous sa forme « simplifiée » (c'est-à-dire le nom du tableau mis entre crochets) ce qui donne :
Call TS_SupprimerLeTableau([TS_Eleves])

Chacun a ses habitudes de programmation et je vous laisse libre de faire comme bon vous semble.




Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-E. 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.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-F. 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.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-G. 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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------


Image non disponible


Pour simplifier vos traitements, nommez vos colonnes en évitant les caractères spéciaux « arobase, dièse (croisillons), 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 (simple quote).



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-H. 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 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Call TS_EffacerUneLigne(TS:=Tableau, Ligne:=0)
End Sub
'------------------------------------------------------------------------------------------------


Ou :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Call TS_EffacerUneLigne(Range("TS_Eleves"), 0)
End Sub
'------------------------------------------------------------------------------------------------




Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-I. 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 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Call TS_EffacerToutesLignes(Tableau)
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-J. 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 » :

 
Cacher/Afficher le codeSélectionnez
------------------------------------------------------------------------------------------------
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------------------------------------------------------------------------------------------------


Image non disponible

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.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-K. 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 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Call TS_SupprimerUneLigne(Tableau, 0)
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible

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.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-L. TS_SupprimerPlusieursLignes

La fonction TS_SupprimerPlusieursLignes supprime plusieurs lignes consécutives 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é ;
  • LigneDébut : le numéro de la première ligne à supprimer ;
  • LigneFin : le numéro de la dernière 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 de la huitième ligne jusqu'à la dernière ligne du tableau structuré nommé « TS_Eleves » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Call TS_SupprimerPlusieursLignes(Tableau, 8, 0)
End Sub
'------------------------------------------------------------------------------------------------

Remarque : avant de supprimer les lignes 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.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-M. 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 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Call TS_SupprimerToutesLignes(Tableau)
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-N. TS_SupprimerLignesVisibles

La fonction TS_SupprimerLignesVisibles supprime les lignes visibles dans un tableau structuré, lignes préalablement sélectionnées par un filtre.

Si toutes les lignes sont 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.

Son argument est :

  • TS : la plage (de type Range) qui représente le tableau structuré.

La fonction renvoie : le nombre de lignes supprimées ou -1 en car d'erreur.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-O. TS_SupprimerLignesMasquées

La fonction TS_SupprimerLignesMasquées supprime les lignes masquées dans un tableau structuré suite à l'utilisation d'un filtre.

Son argument est :

  • TS : la plage (de type Range) qui représente le tableau structuré.

La fonction renvoie : le nombre de lignes supprimées ou -1 en car d'erreur.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-P. TS_SupprimerDoublons

La fonction TS_SupprimerDoublons supprime les doublons dans un tableau structuré. Les lignes masquées par un filtre sont quand même prises en compte

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • ListeColonnes : la ou les colonnes concernées (dans ce cas passez un Array).

La fonction renvoie : le nombre de lignes supprimées ou -1 en car d'erreur.

Exemples d'appels sur le tableau « TS_Eleves » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim TS As Range
Set TS = Range("TS_Eleves")
Call TS_SupprimerDoublons(TS, "Nom") ' Supprime les doublons de la colonne nommée "Nom"
Call TS_SupprimerDoublons(TS, Array("Nom", "Prénom")) ' idem mais si doublons en colonnes "Nom" et "Prénom".
Call TS_SupprimerDoublons(TS, 0) ' Supprime les doublons de la dernière colonne.
Call TS_SupprimerDoublons(TS, Array("Nom", 0)) ' idem mais si doublons en colonnes "Nom" et dernière colonne.
End Sub
'------------------------------------------------------------------------------------------------


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-Q. 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 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-R. 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 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Call TS_SupprimerUneColonne(TS:=Tableau, Colonne:="Note2")
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-S. TS_DéplacerUneColonne

La fonction TS_DéplacerUneColonne déplace une colonne dans un tableau structuré.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • Colonne_Source : le numéro de la colonne ou le nom de la colonne à déplacer. Si vide ou 0, alors déplace la dernière colonne ;
  • Colonne_Dest : le numéro de la colonne destination où déplacer les données. Si vide ou 0, alors déplace les données la dernière colonne.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple pour s'assurer que les colonnes du tableau structuré « TS_Eleves » sont bien dans l'ordre désiré. Les colonnes sont déplacées en cas de besoin :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim i As Integer
Dim Ordre As Variant

' Définition des colonnes que l'on souhaite déplacer
Ordre = Array("Nom", "Prénom", "Note", "Date", "Prochain")

' Boucle sur les colonnes pour les déplacer dans cet ordre.
' Attention le tableau est en base 0 (de 0 à n-1) alors que les colonnes
' d'un tableau structuré vont de 1 à n et pas de 0 à n-1:
For i = LBound(Ordre) To UBound(Ordre)
    Call TS_DéplacerUneColonne(TS:=Range("TS_Eleves"), Colonne_Source:=Ordre(i), Colonne_Dest:=i + 1)
Next i

End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-T. TS_AfficherOuMasquerColonne

La fonction TS_AfficherOuMasquerColonne permet d'afficher ou de masquer 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 ou le nom de la colonne concernée. Si vide ou 0, alors prend la dernière colonne du tableau structuré ;
  • EstVisible : Si True alors la colonne est visible, si False alors elle est masquée.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-U. TS_LargeurColonne

La fonction TS_LargeurColonne définit la largeur (en points) d'une colonne d'un tableau structuré situé dans le classeur.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • Colonne : le numéro ou le nom de la colonne concernée. Si vide ou 0, alors prend la dernière colonne du tableau structuré ;
  • Largeur : la largeur de la colonne ou 0 pour un ajustement automatique.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-V. 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é :


Image non disponible

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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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) :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Call TS_OptionsStyle(Tableau, False, , True, , , , , "TableStyleMedium6")
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-W. 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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------


Image non disponible

Remarque : nous étudierons dans un autre chapitre la mise en forme des cellules.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-X. TS_FormatColonne

La fonction TS_FormatColonne définit le format numérique des cellules d'une colonne dans un tableau structuré. Les cellules masquées sont également affectées.

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 concernée, ou 0 pour traiter la dernière colonne ;
  • FormatColonne : le format numérique à appliquer ;
  • ForcerNumérique : (facultatif) si True alors remplace la virgule "," par un point "." pour qu'Excel reconnaisse les nombres comme des numériques et puisse les traiter correctement.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple pour mettre la dernière colonne en format date (jour/mois/année) du tableau structuré nommé « TS_Eleves » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Call TS_FormatColonne(Range("TS_Eleves"), 0, "dd/mm/yyyy")
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



II-Y. TS_Aligner

La fonction TS_Aligner définit l'alignement vertical et/ou horizontal des données et/ou de l'en-tête et/ou du total d'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 concernée, ou 0 pour traiter la dernière colonne ;
  • AlignementHorizontal : (facultatif) définit l'alignement horizontal suivant l’énumération XlHAlign
       - -1 : (par défaut) ne modifie pas l'alignement,
       - xlHAlignCenter = Centrer,
       - xlHAlignCenterAcrossSelection = Centrer dans la sélection,
       - xlHAlignDistributed = Distribuer,
       - xlHAlignFill = Remplir,
       - xlHAlignGeneral = Aligner en fonction du type de données,
       - xlHAlignJustify = Justifier,
       - xlHAlignLeft = Gauche,
       - xlHAlignRight = Droite ;
  • AlignementVertical : (facultatif) définit l'alignement vertical suivant l’énumération XlVAlign
       - -1 : (par défaut) ne modifie pas l'alignement,
       - xlVAlignBottom = Inférieure,
       - xlVAlignCenter = Centre,
       - xlVAlignDistributed = Distribué,
       - xlVAlignJustify = Justifier,
       - xlVAlignTop = Haut ;
  • Aligner : (facultatif) définit quelles sont les cellules qui doivent être alignées suivant l’énumération personnelle Enum_TS_Aligner
       - TS_Aligner_Données  = (par défaut) les données,
       - TS_Aligner_Entete = l'en-tête,
       - TS_Aligner_Total = la ligne du total.
    Vous pouvez cumuler ces valeurs : TS_Aligner_Données + TS_Aligner_Entete + TS_Aligner_Total.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple pour centrer l'en-tête et le total et aligner à gauche les données de la dernière colonne :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Call TS_Aligner(Range("TS_Liste_Noms"),0,xlHAlignCenter, ,TS_Aligner_Total+TS_Aligner_Entete)
Call TS_Aligner(range("TS_Liste_Noms"),0,xlHAlignLeft)
End Sub
'------------------------------------------------------------------------------------------------



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



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, si False 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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



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 :

 
Cacher/Afficher le codeSélectionnez
------------------------------------------------------------------------------------------------
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é.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



III-C. TS_Filtres_Existe

La fonction 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.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • Colonne : (facultatif, vide par défaut) le nom ou le numéro de la colonne qu'il faut analyser. Si cet argument vaut zéro alors la dernière colonne du tableau est traitée. S’il est vide alors tout le tableau est analysé.

La fonction renvoie True :
- si le tableau a au moins un filtre actif et que l'argument « Colonne » est vide,
- si la colonne indiquée est filtrée.
Ou False dans le cas contraire.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



III-D. 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.
Pour filtrer une date vous devrez convertir la date de référence au format "année/mois/jour". Et curieusement pour filtrer sur un même jour vous devrez utiliser deux opérateurs (inférieur ou égal et supérieur ou égal) car égal ne fonctionne pas. Voir l'exemple dans le code de la fonction.
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 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------


Image non disponible

Exemple pour supprimer tous ces filtres :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



III-E. 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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Call TS_Filtres_Effacer(Tableau)
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



III-F. 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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Dim MesFiltres As Variant
Call TS_Filtres_Mémoriser(Tableau, MesFiltres)
End Sub
'------------------------------------------------------------------------------------------------



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



 

III-G. 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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



III-H. TS_CouleurLigneChangeValeur

La fonction TS_CouleurLigneChangeValeur alterne la couleur de fond des lignes visibles d'un tableau structuré à chaque changement de valeur dans la colonne de votre choix.

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 le numéro est zéro, alors la dernière colonne du tableau est traitée. Si ce nom est vide ou -1 (valeur par défaut), alors efface toutes les couleurs des lignes visibles ;
  • CoulA : le numéro de la première couleur, ou -1 (valeur par défaut) pour ne pas appliquer de couleur ;
  • CoulB : le numéro de la seconde couleur, ou -1 (valeur par défaut) pour ne pas appliquer de couleur.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple pour alterner la couleur des lignes d'un tableau à chaque changement de Note :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves_1")
Call TS_CouleurLigneChangeValeur(TS:=Tableau, Colonne:="Note", CoulA:=14277081, CoulB:=14348258)

End Sub
'------------------------------------------------------------------------------------------------


Image non disponible

Pour effacer les couleurs personnelles (sur les lignes visibles) et restaurer les couleurs par défaut du tableau structuré, ne renseignez pas l'argument "Colonne" de la fonction :

 
Sélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves_1")
Call TS_CouleurLigneChangeValeur(TS:=Tableau)

End Sub
'------------------------------------------------------------------------------------------------




Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



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.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



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


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



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

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



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 : renvoie True si la cellule est en gras et False dans le cas contraire,
       - TS_Italique : renvoie True si la cellule est en italique et False dans le cas contraire,
       - TS_Visible : renvoie True si la cellule est visible et False 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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



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 ou False 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) :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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)


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



V-B. TS_RechercherVisible

La fonction TS_RechercherVisible recherche une valeur dans une colonne d'un tableau structuré uniquement pour les lignes visibles et renvoie la ligne trouvée.
En option vous pouvez indiquer si la recherche doit respecter ou non la casse et s'il faut ou non une correspondance exacte.
Par défaut la fonction renvoie le numéro de la première ligne trouvée ou zéro si rien n’est trouvé, mais vous pouvez aussi lui demander de renvoyer la valeur de la colonne de votre choix (équivalent à RechercheV), ou la valeur à renvoyer si la recherche est infructueuse.

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 concernée, ou 0 pour traiter la dernière colonne ;
  • ValeurCherchée : la valeur qu'il faut chercher ;
  • RespecterCasse : True pour respecter la casse ou False pour l’ignorer ;
  • ValeursOuFormules : énumération Enum_ValeursOuFormules pour indiquer si la recherche porte sur la valeur de la cellule ou sur sa formule :
       - TS_Valeurs : recherche dans les valeurs,
       - TS_Formules : recherche dans les formules.
  • Correspondance : (facultatif) indique le mode de recherche :
       - xlWhole : (par défaut) détecte une correspondance avec l'ensemble du texte recherché,
       - xlPart : détecte une correspondance avec une partie du texte recherché ;
  • ColonneRenvoyée : (facultatif) le nom ou le numéro de la colonne concernée. Si ce numéro est zéro, alors la dernière colonne du tableau structuré est traitée ;
  • ValeurSiNonTRouvé : (facultatif) la valeur à renvoyer si la recherche ne trouve rien.

La fonction renvoie :

  • - si ColonneRenvoyée <0 : Le numéro de la 1ère ligne trouvée ou 0 si rien n'est trouvé ;
  • - si ColonneRenvoyée >= 0 : La valeur contenue dans la colonne indiquée pour la ligne trouvée, ou la valeur de l'argument ValeurSiNonTRouvé si rien n'est trouvé.

Exemple pour rechercher la ligne où se trouve l’élève dont le nom est « FIL » dans un tableau nommé « TS_Eleves » en respectant la casse :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Dim Ligne As Long
Ligne = TS_RechercherVisible(Tableau, "Nom", "FIL", True, TS_Valeurs)
End Sub
'------------------------------------------------------------------------------------------------


Remarque : Cette fonction est plus rapide que la fonction TS_Rechercher car elle utilise la propriété Find au lieu d'une boucle sur les lignes du tableau.
Elle permet aussi de ne faire la recherche que sur les lignes visibles. En contrepartie la recherche ne porte que sur une colonne.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



V-C. TS_Remplacer

La fonction TS_Remplacer remplace une valeur dans une colonne d'un tableau structuré.
Attention :
- si le tableau est sur la feuille active les cellules masquées ne sont pas traitées ;
- si le tableau n'est pas sur la feuille active les cellules masquées sont traitées.

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 concernée, ou 0 pour traiter la dernière colonne ;
  • ValeurCherchée : la valeur qu'il faut remplacer ;
  • ValeurRemplacement : la valeur de remplacement ;
  • RespecterCasse : True pour respecter la casse ou False pour l’ignorer ;
  • Correspondance : (facultatif) Indique le mode de recherche :
    - xlPart : détecte une correspondance avec une partie du texte recherché,
    - xlWhole : (valeur par défaut) détecte une correspondance avec l'ensemble du texte recherché.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple pour remplacer toutes les notes 14 par 15 dans le champ « Note » un tableau nommé « TS_Eleves » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves")
Call TS_Remplacer(Tableau, "Note", 14, 15, True)
End Sub
'------------------------------------------------------------------------------------------------


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



V-D. 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 numéro 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.
 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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.			
'------------------------------------------------------------------------------------------------



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



V-E. TS_Range

La fonction TS_Range renvoie une plage de données d'un tableau structuré. La plage peut être une cellule, une ligne, une colonne. La feuille du tableau structuré n'a pas besoin d'être active.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • VisibleUniquement : Si True seules les données visibles sont traitées, si False les données masquées sont prises en compte également ;
  • Colonne : le numéro de la colonne, ou le nom de la colonne. Si 0 est renseigné alors prend la dernière colonne. Si la colonne n'est pas renseignée alors traite la ligne entière renseignée ;
  • Ligne : la ligne concernée. Si 0 est renseigné alors prend la dernière ligne. Si inférieur à 0 alors prend la ligne des totaux. Si la ligne est non renseignée alors traite la colonne entière renseignée.

Si la colonne et la ligne ne sont pas renseignées, alors sélectionne toutes les données du tableau structuré.

La fonction renvoie : un Range de la sélection faite ou Nothing.

Plusieurs exemples pour renvoyer un Range du tableau structuré des élèves, respectivement :

  • pour renvoyer les données de la colonne « Nom » (sans l'en-tête et le total) ;
  • pour renvoyer la cellule située 5e ligne de la colonne « Nom » ;
  • pour renvoyer la dernière ligne de la colonne « Nom » ;
  • pour renvoyer la cellule située sur la dernière ligne de la colonne « Nom » ;
  • pour renvoyer le total de la colonne « Nom » ;
  • pour renvoyer la 5e ligne ;
  • pour renvoyer la dernière ligne ;
  • pour renvoyer la ligne des totaux ;
  • pour ne renvoyer que les données visibles du Tableau Structuré.


 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim r as Range
Set r = TS_Range(TS, False, "Nom") ' pour renvoyer les données de la colonne "Nom" (sans l'en-tête et le total).
Set r = TS_Range(TS, False, "Nom", 5) ' pour renvoyer la cellule située 5ème ligne de la colonne "Nom"
Set r = TS_Range(TS, False, "Nom", 0) ' pour renvoyer la dernière ligne de la colonne "Nom".
Set r = TS_Range(TS, False, "Nom", -1) ' pour renvoyer le total de la colonne "Nom".
Set r = TS_Range(TS, False, , 5) ' pour renvoyer la 5ème ligne.
Set r = TS_Range(TS, False, , 0) ' pour renvoyer la dernière ligne.
Set r = TS_Range(TS, False, , -1) ' pour renvoyer la ligne des totaux.
Set r = TS_Range(TS, True) ' pour ne renvoyer que les données visibles du Tableau Structuré.

' Vous pouvez utilise r comme une plage ordinaire, exemples:
' r.Count/r.Columns.Count = nombre le lignes, r.Columns.Count = nombre de colonnes, 
' r.Cells(1, 1).Value = valeur cellule.
End Sub
'------------------------------------------------------------------------------------------------


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



V-F. 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 vaut True ou False,
       - TS_Italique : met la cellule en italique ou non selon que Valeur vaut True ou False,
       - 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).
  • Mémoire : (facultatif) la mémoire qui représente une image du tableau et où sont faites les modifications. Cela permet d'être plus rapide sur les traitements en boucle qui appellent de nombreuses fois TS_ModifCellule (même avec écran et calculs bloqués). Utilisez TypeInfo = TS_ViderMémoire pour actualiser le tableau avec cette image. Une fois vidée, la mémoire peut être réutilisée pour servir d'image à un tableau.

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.
 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



Pour accélérer les traitements de mise à jour des données il est conseillé d'utiliser Application.ScreenUpdating = False pour éviter la mise à jour de l'écran et Application.Calculation = xlCalculationManual pour bloquer les calculs.
Puis vous restaurez la situation avec Application.Calculation = xlCalculationAutomatic et Application.ScreenUpdating = True.

Il y a encore plus rapide, en utilisant une mémoire cache qui représente les données du tableau structuré.
Les données sont mises à jour dans cette mémoire et à la fin du traitement le tableau est alimenté par cette mémoire.
Les traitements sont alors environ cinq fois plus rapides qu'avec l'écran et les calculs bloqués, ce qui n'est pas négligeable, et les propriétés Application.Calculation et Application.ScreenUpdating n'ont même pas besoin d'être utilisées.

La fonction TS_ModifCellule gère cela en passant dans son argument Mémoire une variable préalablement déclarée de type Variant.
Utilisez la fonction TS_ModifCellule dans vos traitements comme d'habitude sans oublier de renseigner l'argument Mémoire.

Pour basculer la mémoire dans le tableau structuré faites un nouvel appel à la fonction TS_ModifCellule en utilisant TS_ViderMémoire comme valeur de l'argument TypeInfo.
Cette appel vide la mémoire qui peut être réutilisée pour servir d'image cache à un autre tableau.
Les arguments obligatoires Colonne, Ligne et Valeur n'ont pas d'utilité et peuvent être laissés à zéro.



 
Cacher/Afficher le codeSélectionnez
Dim M As Variant, i As Integer
For i=1 to 1000: Call TS_ModifCellule(Range("Tableau1"), 1, 1, 10, TS_Ajouter, , , M): Next i
Call TS_ModifCellule(Range("Tableau1"), 0, 0, "", TS_ViderMémoire, , , M)



V-G. TS_ForcerValeurColonne

La fonction TS_ForcerValeurColonne met une valeur unique dans 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 contient zéro, alors la dernière colonne du tableau est traitée ;
  • Valeur : la valeur qu'il faut utiliser ;
  • VisibleUniquement : si True alors ne traite que les lignes visibles, si False alors traite toutes les lignes même les masquées.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple pour mettre la valeur "Ok" dans la colonne "Admis" pour les élèves dont la note est supérieure ou égale à 10 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Set Tableau = Range("TS_Eleves_1")

' Filtre les notes >= 10:
Call TS_Filtres_Poser(Tableau, "Note", ">=10")

' Force la valeur OK dans la colonne "Admis" pour les lignes visibles:
Call TS_ForcerValeurColonne(TS:=Tableau, Colonne:="Admis", Valeur:="OK", VisibleUniquement:=True)

' Efface le filtre:
Call TS_Filtres_Effacer(Tableau)

End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI. Importer, exporter des données d’un tableau structuré

Ce chapitre est consacré à l’importation et l’exportation des données.

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 ou False 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) :


Image non disponible

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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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 :


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
Call TS_ImporterDonnées(TS_Destination, Tableau, TS_MAJ_Ou_Ajout, True, "Nom")

' Tri de la colonne :
Call TS_TrierUneColonne(TS:=TS_Destination, _
                        Colonne:="Nom", _
                        Méthode:=xlSortOnValues, _
                        Ordre:=xlAscending, _
                        EffacerAncienTri:=True) 

' Gestion des erreurs :
Gest_Err:
If Err.Number <> 0 Then MsgBox Err.Number & " : " & Err.Description, vbCritical, Err.Source

End Sub
'------------------------------------------------------------------------------------------------


Ce qui donne :


Image non disponible



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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------


Image non disponible


Vous trouverez en annexe 2 un code VBA pour fusionner deux fichiers PDF si vous disposez de l’application « Adobe Acrobat Pro ».



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-D. TS_EnregistrerDansAccess

La fonction TS_EnregistrerDansAccess enregistre un tableau structuré ou certaines de ses colonnes dans une base Access. Les éventuels enregistrements de la table préalablement créée ne sont pas effacés, les nouveaux enregistrements issus du tableau structuré y sont ajoutés. La fonction ne prend pas en charge les règles de gestion de la table Access, il conviendra donc de s'assurer que les données du tableau structuré sont cohérentes avec la table de destination pour ne pas générer une erreur d'importation dans Access.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • BaseAccess : le nom complet de la base Access (y compris son dossier) ;
  • TableAccess : le nom de la table dans la base ;
  • MotDePasse : éventuellement le mot de passe qui protège la base Access ;
  • ListeChampsColonnes : la liste des couples sous forme d'un Array (c'est-à-dire entre guillemets et séparés par une virgule) des champs de la table et des colonnes du tableau structuré (voir exemple). S'il faut prendre une valeur fixe pour un champ et non pas la valeur de la colonne alors faire précéder le nom du champ par ">" (supérieur). Laissez vide pour prendre toutes les colonnes à l'identique des champs ;
  • VisibleUniquement : si True alors ne traite que les lignes visibles, si False alors traite toutes les lignes même les masquées.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple d'utilisation :

Soit une base Access "P:\Test.accdb" avec une table "Table_1" qui contient les champs "A", "B" qu'il faut alimenter par les colonnes "Nom", "Prénom" du tableau structuré "TS_Eleves_1" pour toutes les lignes, même masquées :

 
Sélectionnez
Call TS_EnregistrerDansAccess(Range("TS_Eleves_1"), "P:\Test.accdb", "Table_1", "", Array("A", "Nom", "B", "Prénom"), False)

Si les champs de la table Access ont le même nom que les en-têtes du tableau structuré, il est possible de remplacer par :

 
Sélectionnez
Call TS_EnregistrerDansAccess(Range("TS_Eleves_1"), "P:\Test.accdb", "Table_1", "", "", False)

c'est-à-dire sans renseigner l'argument "ListeChampsColonnes".

Pour forcer une valeur fixe pour un champ et non pas la valeur de la cellule de la colonne, faire précéder le nom du champ par ">" (supérieur).
Exemple pour ajouter l'utilisateur en cours (obtenu par Application.UserName) dans le champ "Qui" :

 
Sélectionnez
Call TS_EnregistrerDansAccess(Range("TS_Eleves"), "P:\Test.accdb", "Table_1", "", Array("Nom", "Nom", "Prénom", "Prénom", ">Qui", Application.UserName), False)




Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-E. TS_ImporterDepuisAccess

La fonction TS_ImporterDepuisAccess recopie les enregistrements d'une table ou d'une requête d'une base Access dans un tableau structuré.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré (s'il n'y pas assez de colonnes dans le tableau pour recevoir les données importées elles seront ajoutées automatiquement) ;
  • BaseAccess : le nom complet de la base Access (y compris son dossier) ;
  • MotDePasse : éventuellement le mot de passe qui protège la base Access ;
  • ListeDesChamps : la liste des champs à récupérer, séparés par une virgule. Mettre les champs entre des crochets ouverts "[" et fermés "]" s'ils contiennent des espaces. Laissez à vide pour reprendre tous les champs ;
  • SQLWhere : la requête d'instruction en langage SQL permettant d'identifier la sélection (sans le mot clé WHERE). Si SQLWhere vaut "" alors toute la table est sélectionnée ;
  • Méthode : indique si les données importées doivent remplacer celles existantes dans le tableau structuré ou y être ajoutées, suivant l'énumération personnelle Enum_CopierDonnées :
       - TS_RemplacerDonnées : remplace les données existantes par les nouvelles,
       - TS_AjouterDonnées : ajoute les nouvelles données à la suite des données existantes.

La fonction renvoie : le nombre de lignes importées ou -1 en cas d'erreur.

Exemple d'utilisation pour importer dans le tableau structuré « TS_Eleves » tous les champs de la table des élèves « Eleves » de la base Access « Ecole.accdb » dont la note est supérieure à 10 (et effacer les éventuelles données antérieures) :

 
Sélectionnez
Call TS_ImporterDepuisAccess(Range("TS_Eleves"), "C:\Access\Ecole.accdb", "Eleves", "", "", "Note>10", TS_RemplacerDonnées)


Si besoin vous trouverez en Annexe 1 plus d'informations sur le langage SQL.





Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-F. TS_RequeteBaseAccess

La fonction TS_RequeteBaseAccess permet de faire des requêtes sur les enregistrements d'une base Access.

Ses arguments sont :

  • BaseAccess : le nom complet de la base Access (y compris son dossier) ;
  • MotDePasse : éventuellement le mot de passe qui protège la base Access ;
  • StrSQL : la requête d'instruction en langage SQL.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple d'utilisation pour supprimer tous les enregistrements de table "T_Notes" dans la base Eleves (à utiliser avant d'y copier de nouveaux enregistrements en remplacement des anciens, voir le chapitre précédent) :

 
Sélectionnez
Call TS_RequeteBaseAccess("P:\Eleves.accdb"), "", "DELETE * FROM [T_Notes]")


Si besoin vous trouverez en Annexe 1 plus d'informations sur le langage SQL.





Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-G. TS_CopierUneColonne

La fonction TS_CopierUneColonne copie une colonne d'un tableau structuré dans un autre tableau structuré.

Ses arguments sont :

  • TS_Source : la plage (de type Range) du tableau structuré source où se trouve la colonne à copier ;
  • Colonne_Source : le nom ou le numéro de la colonne à copier. Si ce nom est vide ou contient zéro, alors la dernière colonne du tableau est traitée ;
  • TS_Dest : la plage (de type Range) du tableau structuré destination où il faut copier la colonne (la feuille doit être active) ;
  • Colonne_Dest : le nom ou le numéro de la colonne où copier les données. Si ce nom est vide ou contient zéro, alors la dernière colonne du tableau est traitée ;
  • Ligne_Dest : ligne où commencer la copie, par exemple 1 pour copier à la première ligne du tableau, ou 0 pour la dernière ligne du tableau ;
  • Méthode : énumération XlCellType par défaut xlCellTypeVisible pour les cellules visibles uniquement.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Remarque : si vous utilisez la méthode xlCellTypeVisible (par défaut) pensez à effacer les filtres du tableau structuré source si vous voulez copier toutes les données de la colonne et pas uniquement les cellules visibles.


Exemple pour sélectionner les élèves admis du tableau « TS_Eleves_1 » et recopier les colonnes « Nom » et « Prénom » dans le tableau « TS_Eleves_2 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Dim Destination As Range

Set Tableau = Range("TS_Eleves_1")
Set Destination = Range("TS_Eleves_2")

' Filtre les Admis = OK du tableau source:
Call TS_Filtres_Effacer(Tableau)
Call TS_Filtres_Poser(Tableau, "Admis", "Ok")

' Effacer les lignes du tableau destination:
Call TS_EffacerToutesLignes(Destination)
Call TS_SupprimerPlusieursLignes(Destination, 2, 0)

' Recopie les élèves dans le tableau destination:
Call TS_CopierUneColonne(Tableau, "Nom", Destination, "Nom", 1, xlCellTypeVisible)
Call TS_CopierUneColonne(Tableau, "Prénom", Destination, "Prénom", 1, xlCellTypeVisible)

' Efface le filtre sur Admis:
Call TS_Filtres_Effacer(Tableau)

' Se place sur le premier nom du tableau destination:
Call TS_Sélectionner(Destination, 1, 1)

End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-H. TS_CopierValeurColonne

La fonction 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.
Les tableaux n'ont pas besoin d'être sur la feuille active ni sur la même feuille.

Ses arguments sont :

  • TS_Source : la plage (de type Range) du tableau structuré source où se trouve la colonne à copier ;
  • Colonne_Source : le nom ou le numéro de la colonne à copier. Si ce nom est vide ou contient zéro, alors la dernière colonne du tableau est traitée ;
  • TS_Dest : la plage (de type Range) du tableau structuré destination où il faut copier la colonne ;
  • Colonne_Dest : le nom ou le numéro de la colonne où copier les données. Si ce nom est vide ou contient zéro, alors la dernière colonne du tableau est traitée ;
  • Méthode : indique si les données à copier doivent remplacer celles existantes dans le tableau structuré ou y être ajoutées, suivant l'énumération personnelle Enum_CopierDonnées :
       - TS_RemplacerDonnées : remplace les données existantes par les nouvelles,
       - TS_AjouterDonnées : ajoute les nouvelles données à la suite des données existantes ;
  • VisibleUniquement : Si True seules les données visibles sont traitées, si False les données masquées sont prises en compte également.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Remarque : contrairement à la fonction TS_CopierUneColonne, la fonction TS_CopierValeurColonne ne copie que les valeurs, mais elle se révèle beaucoup plus rapide, c'est pourquoi je la préfère.


Exemple pour sélectionner les élèves admis du tableau « TS_Eleves_1 » et recopier les colonnes « Nom » et « Prénom » dans le tableau « TS_Eleves_2 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Dim Destination As Range

Set Tableau = Range("TS_Eleves_1")
Set Destination = Range("TS_Eleves_2")

' Filtre les Admis = OK du tableau source:
Call TS_Filtres_Effacer(Tableau)
Call TS_Filtres_Poser(Tableau, "Admis", "Ok")

' Effacer les lignes du tableau destination:
Call TS_EffacerToutesLignes(Destination)
Call TS_SupprimerPlusieursLignes(Destination, 2, 0)

' Recopie les élèves dans le tableau destination:
Call TS_CopierValeurColonne(Tableau, "Nom", Destination, "Nom", TS_RemplacerDonnées, True)
Call TS_CopierValeurColonne(Tableau, "Prénom", Destination, "Prénom", TS_RemplacerDonnées, True)

' Efface le filtre sur Admis:
Call TS_Filtres_Effacer(Tableau)

End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-I. TS_CopierUneLigne

La fonction TS_CopierUneLigne copie une ligne (en valeur ou en formule) d'un tableau structuré dans un autre tableau structuré, ou dans le même tableau.
Les tableaux n'ont pas besoin d'être sur la feuille active ni sur la même feuille.

Ses arguments sont :

  • TS_Source : la plage (de type Range) du tableau structuré source où se trouve la ligne à copier ;
  • Ligne_Source : le numéro de la ligne à copier. Si zéro alors la dernière ligne du tableau est traitée ;
  • TS_Dest : la plage (de type Range) du tableau structuré destination où il faut copier la ligne ;
  • Ligne_Dest : le nom de la ligne où copier les données. Si zéro alors la dernière ligne du tableau est traitée ;
  • ValeursOuFormules : énumération Enum_ValeursOuFormules pour indiquer s'il faut copier les valeurs ou les formules :
       - TS_Valeurs : copie des valeurs,
       - TS_Formules : copie des formules.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Remarque: Si le tableau destination à moins de colonnes que la source, la copie se limite aux colonnes existantes. S'il en a plus, les colonnes supplémentaires sont effacées.



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-J. TS_CopierUnTableau

La fonction TS_CopierUnTableau copie 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.
La fonction gère aussi une source issue d'une plage mémorisée dans une variable (par la fonction TS_MémoriseTableau) ou d'un jeu d'enregistrements pour la copier en valeur dans un tableau structuré.

Ses arguments sont :

  • TS_Source : la plage (de type Range) du tableau structuré source, ou des données mémorisées dans une variable ou un jeu d'enregistrements ;
  • TS_Dest : la plage (de type Range) du tableau structuré destination ;
  • Méthode : énumération Enum_CopierDonnées :
       - TS_RemplacerDonnées : remplace les données existantes par les nouvelles,
       - TS_AjouterDonnées : ajoute les nouvelles données à la suite des données existantes ;
  • ValeursOuFormules : énumération Enum_ValeursOuFormules :
       - TS_Valeurs : en valeurs (par défaut),
       - TS_Formules : en formules (les en-têtes doivent être les mêmes dans les deux tableaux).

La fonction renvoie : le nombre de lignes ajoutées ou -1 si erreur.

Remarques :
- Pour copier les formules avec TS_Formules les en-têtes doivent être les mêmes dans les deux tableaux et/ou les formules doivent être cohérentes avec le tableau de destination.
- Si le tableau source à plus de colonnes que le tableau destination : les colonnes en surplus sont ignorées.
- Si le tableau source à moins de colonnes que le tableau destination : les colonnes manquantes sont vides.


Exemple pour copier en valeur les données du tableau « TS_Eleves_1 » dans le tableau « TS_Eleves_2 » (qui volontairement ne contient que trois colonnes):

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Dim Destination As Range

Set Tableau = Range("TS_Eleves_1")
Set Destination = Range("TS_Eleves_2")

Call TS_CopierUnTableau(Tableau, Destination, TS_RemplacerDonnées, TS_Valeurs)

End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-K. TS_MémoriseTableau

La fonction TS_MémoriseTableau mémorise 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.

Ses arguments sont :

  • TS : la plage (de type Range) du tableau structuré ;
  • ValeursOuFormules : énumération Enum_ValeursOuFormules :
       - TS_Valeurs : en valeurs (par défaut),
       - TS_Formules : en formules.

La fonction renvoie : un Variant à plusieurs dimensions.

Remarque : la mémoire renvoyée sera utilisée pour analyser les données ou pour les recopier dans un autre tableau structuré comme le fait la fonction TS_CopierUnTableau.


Exemple pour mémoriser les données du tableau « TS_Eleves_1 » :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Tableau As Range
Dim Données As Variant

' Mémorise les valeurs du tableau TS_Eleves_1:
Set Tableau = Range("TS_Eleves_1")
Données = TS_MémoriseTableau(Tableau, TS_Valeurs)

End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-L. TS_ImporterDepuisClasseur

Comme son nom l’indique la fonction TS_ImporterDepuisClasseur importe, dans un tableau structuré existant, les données d’un autre classeur. Les données sources peuvent être contenues soit dans une plage Excel classique, soit dans un tableau structuré.
L’importation peut porter sur l’intégralité des colonnes source ou sur certaines seulement.
Le fichier source peut être fermé ou déjà ouvert, mais normalement vous utiliserez cette fonction sur des classeurs fermés car pour les classeurs ouverts d’autres fonctions sont déjà proposées dans le module « TS ».

Pour éviter que les macros se lancent à l'ouverture du classeur avec l'événement Workbook_Open() nous les désactiverons en forçant le mode de sécurité par l'instruction Application.AutomationSecurity = msoAutomationSecurityForceDisable, puis nous les restaurerons avec le mode en cours préalablement mémorisé.


Ses arguments sont :

  • Fichier_Source : le classeur Excel qui contient les sources (chemin complet + nom avec l'extension) ;
  • MotDePasse : éventuellement le mot de passe pour ouvrir le fichier (vide si non nécessaire) ;
  • Nom_Tableau_Source : soit le nom du tableau structuré qui contient les données source (pas un Range, voir exemple), soit le nom de la feuille entre crochets ouvert « [ » et fermé « ] » suivi d'un point d'exclamation « ! » (si la plage à un en-tête ou un dollar « $ » si la plage n’en a pas) et de la plage ou de la première cellule haut-gauche de la plage (voir exemple) ;
  • TS_Dest : le tableau structuré destination (de type Range) situé dans le classeur contenant cette fonction ;
  • Méthode : indique si les données importées doivent remplacer celles existantes dans le tableau structuré ou y être ajoutées, suivant l'énumération personnelle Enum_CopierDonnées :
       - TS_RemplacerDonnées : remplace les données existantes par les nouvelles,
       - TS_AjouterDonnées : ajoute les nouvelles données à la suite des données existantes.
  • ListeColonnes : (facultatif) le couple des noms des colonnes sources, noms (ou numéro) des colonnes destination, séparé par une virgule. Si vide alors les colonnes du tableau source sont copiées dans la destination sans ajout de colonnes supplémentaires si la destination n'a pas assez de colonnes.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.


Remarques sur la source Nom_Tableau_Source :

  • si la source est un tableau structuré alors passez son nom, par exemple "Tableau1" et non pas un objet Range comme Range("Tableau1") ;
  • si la source est une plage classique, passez le nom de la feuille et la plage, comme par exemple "[Feuil1]!A1" pour que la fonction étende automatiquement la plage, ou "[Feuil1]!A1:C99" pour prendre une plage déterminé ;
  • la plage doit avoir un en-tête, si ce n'est pas le cas remplacez « ! » par « $ » ;
  • si le tableau source à plus de colonnes que le tableau destination alors les colonnes en surplus sont ignorées ;
  • si le tableau source à moins de colonnes que le tableau destination alors les colonnes manquantes sont vides.


Exemples d'appels de la fonction TS_ImporterDepuisClasseur pour importer des données du fichier « C:\Sources\Monfichier.xls » (qui n'a pas de mot de passe) et les coller en valeur dans le tableau structuré « TS_1 ».
Si vous utilisez Dim TS As Range : Set TS=Range("TS_1") alors remplacez Range("TS_1") par TS dans les exemples :

La source est une plage qui commence en « A1 » de la feuille « Feuil1 » et il faut remplacer les données existantes dans le tableau structuré destination « TS_1 » :
TS_ImporterDepuisClasseur("C:\Sources\Monfichier.xls", "", "[Feuil1]!A1", Range("TS_1"), TS_RemplacerDonnées)

Idem mais cette fois en ajoutant les données importées à celles existantes :
TS_ImporterDepuisClasseur("C:\Sources\Monfichier.xls", "", "[Feuil1]!A1", Range("TS_1"), TS_AjouterDonnées)

La source est aussi un tableau structuré nommé « TS_X » et il faut remplacer les données existantes :
TS_ImporterDepuisClasseur("C:\Sources\Monfichier.xls", "", "TS_X", Range("TS_1"), TS_RemplacerDonnées)

Idem mais ici il ne faut pas prendre toutes les colonnes mais seulement dans la source les colonnes « MATRICULE », « NOM », « PRENOM » que l'on place dans la destination en colonnes nommées « A », « B » et à la dernière colonne :
TS_ImporterDepuisClasseur("C:\Sources\Monfichier.xls", "", "TS_X", _ Range("TS_1"), TS_RemplacerDonnées, "MATRICULE", "A", "NOM", "B", "PRENOM", 0)


Le code de la fonction :

Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-M. TS_ImporterDepuisClasseurAvecSQL

La fonction TS_ImporterDepuisClasseurAvecSQL importe, dans un tableau structuré existant, les données d’un classeur qui est fermé et effectuant une requête SQL pour la sélection des données à importer.
Les données sources peuvent être contenues soit dans une plage Excel classique soit dans un tableau structuré.
Le fichier source peut être déjà ouvert, mais normalement vous utiliserez cette fonction sur des classeurs fermés car pour les classeurs ouverts d’autres fonctions sont déjà proposées dans le module « TS ».
Le tableau source doit obligatoirement avoir un en-tête avec des noms de champs uniques pour pouvoir exécuter une requête.
Le classeur n'est ouvert que s'il est protégé par mot de passe (pas d'autre façon de faire) ou si un tableau structuré est passé dans le nom du tableau source (afin de retrouver la feuille concernée), mais comme pour la fonction TS_ImporterDepuisClasseur les macros sont désactivées pour ouvrir le fichier sans lancer l'événement Workbook_Open.


Ses arguments sont :

  • Fichier_Source : le classeur Excel qui contient les sources (chemin complet + nom avec l'extension) ;
  • MotDePasseOuverture : éventuellement le mot de passe pour ouvrir le fichier (vide si non nécessaire) ;
  • MotDePasseEcriture : éventuellement le mot de passe pour permettre les modifications dans le fichier (vide si non nécessaire) ;
  • Nom_Tableau_Source : soit le nom du tableau structuré qui contient les données source (pas un Range, voir exemple), soit le nom de la feuille entre crochets ouvert « [ » et fermé « ] » (voir exemple) ;
  • TS_Dest : le tableau structuré destination (de type Range) situé dans le classeur contenant cette fonction ;
  • Méthode : indique si les données importées doivent remplacer celles existantes dans le tableau structuré ou y être ajoutées, suivant l'énumération personnelle Enum_CopierDonnées :
       - TS_RemplacerDonnées : remplace les données existantes par les nouvelles,
       - TS_AjouterDonnées : ajoute les nouvelles données à la suite des données existantes.
  • StrChamps : (facultatif) le nom des champs de la requête ou vide pour tout prendre ;
  • StrSQL : (facultatif) la requête SQL.

La fonction renvoie : le nombre de lignes importées ou -1 si erreur.


Remarques sur la source Nom_Tableau_Source :

  • si la source est un tableau structuré alors passez son nom, par exemple "Tableau1" et non pas un objet Range comme Range("Tableau1") ;
  • si la source est une plage classique, passez le nom de la feuille et la plage, comme par exemple "[Feuil1]" pour un traitement sur la feuille nommée « Feuil1 » ;
  • si le tableau source à plus de colonnes que le tableau destination alors les colonnes en surplus sont ignorées ;
  • si le tableau source à moins de colonnes que le tableau destination alors les colonnes manquantes sont vides.


Exemples d'appels de la fonction TS_ImporterDepuisClasseurAvecSQL pour importer des données du fichier « C:\Sources\Monfichier.xls » (qui n'a pas de mot de passe) et les coller en valeur dans le tableau structuré « TS_1 ».
Si vous utilisez Dim TS As Range : Set TS=Range("TS_1") alors remplacez Range("TS_1") par TS dans les exemples :

La source est une plage de la feuille « Feuil1 » et il faut remplacer les données existantes dans le tableau structuré destination « TS_1 » par une requête SQL qui regroupe les données sources de la colonne « Référence » et les classe par ordre croissant :
Call TS_ImporterDepuisClasseurAvecSQL("C:\_Formation_VBA\Test.xlsm", "Mgx", "x", _ "[Feuil1]", Range("TS_1"), TS_RemplacerDonnées, _ "[Référence]", "GROUP BY [Référence] ORDER BY [Référence]")

Idem mais cette fois les données sources sont dans un tableau structuré nommé « Tableau1 » et il faut compter le nombre de fois que les groupes de « Référence » sont utilisés, et trier des plus utilisés aux moins utilisés :
Call TS_ImporterDepuisClasseurAvecSQL("C:\_Formation_VBA\Test.xlsm", "Mgx", "x", _ "Tableau1", Range("TS_1"), TS_RemplacerDonnées, _ "[Référence], Count([Référence])", "GROUP BY [Référence] ORDER BY count([Référence]) DESC")

Ou tout simplement importer toutes les données où « Montant » est supérieur à 100 :
Call TS_ImporterDepuisClasseurAvecSQL("C:\_Formation_VBA\Test.xlsm", "Mgx", "x", _ "Tableau1", Range("TS_1"), TS_RemplacerDonnées, _ "", "WHERE [Montant] > 100")


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-N. TS_ImporterFichierTexteAvecSQL

La fonction TS_ImporterFichierTexteAvecSQL importe, dans un tableau structuré existant, les données d'un fichier texte dont les champs sont délimités par un caractère particulier, généralement point-virgule, suivant éventuellement une requête SQL.
Les données sources doivent impérativement avoir un en-tête avec des noms de champs uniques pour pouvoir exécuter une requête.


Ses arguments sont :

  • Fichier_Source : le fichier texte qui contient les sources (chemin complet + nom avec l'extension) ;
  • Délimiteur : le caractère délimiteur des champs ;
  • AvecEntête : Indique si le fichier source à un en-tête (True) ou non (False) ;
  • ListeColonnes : le format des colonnes à analyser (ou vide pour tout reprendre au format général) séparés par une virgule. Si la liste ne reprend pas toutes les colonnes, les colonnes non renseignées ne sont pas traitées.
    Liste des formats disponibles (vous pouvez en créer d'autres) :
        1 = Général (xlGeneralFormat) (les numériques de plus de 15 caractères sont tronqués et ceux avec une virgule sont considérés comme du texte),
        2 = Force le texte en le préfixant par " ' ",
        3 = Format de date M/J/A (xlMDYFormat),
        4 = Format de date J/M/A (xlDMYFormat),
        5 = Format de date A/M/J (xlYMDFormat),
        6 = Format de date M/A/J (xlMYDFormat),
        7 = Format de date J/A/M (xlDYMFormat),
        8 = Format de date A/J/M (xlYDMFormat),
        9 = La colonne n'est pas analysée (xlSkipColumn),
        10 = Numérique,
        11 = Numérique arrondis à 2 chiffres après la virgule,
        12 = Texte sans les espaces de gauche,
        13 = Texte sans les espaces de droite,
        14 = Texte sans les espaces de gauche et de droite,
        15 = Format JJMMAAAA transformé en date,
        16 = Format AAAAMMJJ transformé en date ;
  • TS_Dest : le tableau structuré destination (de type Range) situé dans le classeur contenant cette fonction ;
  • Méthode : indique si les données importées doivent remplacer celles existantes dans le tableau structuré ou y être ajoutées, suivant l'énumération personnelle Enum_CopierDonnées :
       - TS_RemplacerDonnées : remplace les données existantes par les nouvelles,
       - TS_AjouterDonnées : ajoute les nouvelles données à la suite des données existantes ;
  • StrSQL : (facultatif) la requête SQL, basée sur le nom des colonnes du tableau structuré destination et non pas sur les noms des champs du fichier texte.

La fonction renvoie : le nombre de lignes importées ou -1 si erreur.


Remarques :

  • si le fichier texte a plus de colonnes que le tableau destination : les colonnes en surplus sont ignorées ;
  • si le fichier texte a moins de colonnes que le tableau destination : les colonnes manquantes sont vides.


Exemples pour importer dans le tableau structuré destination nommé « TS_1 » ce fichier texte nommé « Eleves.csv » qui a comme délimiteur un point-virgule :

Image non disponible


Pour importer tous les champs avec le format par défaut (format général) :

 
Sélectionnez
Call TS_ImporterFichierTexteAvecSQL("C:\_Formation_VBA\Eleves.csv", ";", True, _
                                    "", _
                                    Range("TS_1"), TS_RemplacerDonnées)


Ce qui donne ce résultat (les notes avec une virgule sont considérées comme étant du texte, le numéro de dossier est tronqué) :

Image non disponible


Pour importer tous les champs avec format numérique pour les notes, format date pour les dates, format texte pour les dossiers :

 
Sélectionnez
Call TS_ImporterFichierTexteAvecSQL("C:\_Formation_VBA\Eleves.csv", ";", True, _
                                    "1, 1, 10, 4, 1, 1, 1, 1, 2", _
                                    Range("TS_1"), TS_RemplacerDonnées)
                                    
Call TS_FormatColonne(Range("TS_1"), "Note", "0.0")
Call TS_FormatColonne(Range("TS_1"), "Date", "dd/mm/yyyy")


Ce qui donne ce résultat :

Image non disponible




Pour importer uniquement des champs Nom, Prénom, Note, Souhaits, pour les notes supérieures à 10 et les trier par nom :

 
Sélectionnez
Call TS_ImporterFichierTexteAvecSQL("C:\_Formation_VBA\Eleves.csv", ";", True, _
                                    "1, 1, 10, 9, 9, 9, 1", _
                                    Range("TS_1"), TS_RemplacerDonnées, _
                                    "WHERE [Note] > 10 ORDER BY [Nom]")


Ce qui donne ce résultat :

Image non disponible



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VI-O. TS_ImporterDepuisFichierHTML

La fonction TS_ImporterDepuisFichierHTML importe dans un tableau structuré existant les données d'un fichier au format HTML qui contient un tableau.
Si le tableau structuré n'a pas assez de colonnes, celles nécessaires seront créées automatiquement.
Les données importées sont soit ajoutées aux données existantes du tableau structuré soit les remplacent.
Le code est inspiré de cette discussion.

Ses arguments sont :

  • TS : la plage (de type Range) du tableau structuré ;
  • Fichier : le fichier HTML qui contient un tableau (chemin complet + nom avec l'extension) ;
  • Méthode : indique si les données importées doivent remplacer celles existantes dans le tableau structuré ou y être ajoutées, suivant l'énumération personnelle Enum_CopierDonnées :
       - TS_RemplacerDonnées : remplace les données existantes par les nouvelles,
       - TS_AjouterDonnées : ajoute les nouvelles données à la suite des données existantes.

La fonction renvoie : le nombre de lignes importées ou -1 en cas d'erreur.



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII. Vos contributions

Ce chapitre reprend les fonctions qui m'ont été proposées ou que certaines discussions m'ont inspiré.

VII-A. TS_CelluleActive

La fonction TS_CelluleActive indique si la cellule active est dans le tableau structuré passé en argument. Si c'est le cas alors elle renseigne les variables « Colonne » et « Ligne » des coordonnées de la cellule dans le tableau et renvoie la cellule active.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • Colonne : si la cellule active est dans le tableau, cette variable contiendra le numéro de la colonne ;
  • Ligne : si la cellule active est dans le tableau, cette variable contiendra le numéro de la ligne.

La fonction renvoie : la cellule active (la première si c'est une plage de plusieurs cellules, ou Nothing si la cellule active n'est pas dans le tableau structuré (ou si une erreur s'est produite).

Exemple d'utilisation pour savoir si la cellule active est à l'intérieur du tableau structuré « TS_Eleves » et si c'est le cas pour afficher dans la fenêtre d'exécution sa position et sa valeur :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim r As Range, Colonne As Long, Ligne As Long
Set r = TS_CelluleActive(Range("TS_Eleves"), Colonne, Ligne)
If Not r Is Nothing Then Debug.Print Colonne, Ligne, r.Value
End Sub
'------------------------------------------------------------------------------------------------



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-B. TS_ObjetInserer

La fonction TS_ObjetInserer insère un objet dans une cellule du tableau structuré passé en argument. Un objet peut être une image (cas classique) mais aussi tout autre type de fichier supporté par votre version d'Excel, tels que les fichiers au format PDF, les fichiers son, les fichiers texte, les classeurs Excel...
Un double-clic (ou un clic droit) sur l'objet l'ouvre (testé sur les versions 2016 et 365).

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 0, alors la dernière colonne du tableau est traitée ;
  • Ligne : la ligne concernée. Si 0 est renseigné alors prend la dernière ligne ;
  • Fichier : le nom complet du fichier (avec son chemin) ;
  • Nom : le nom à donner à l'objet (laissez à vide pour garder le nom attribué automatiquement par Excel), plusieurs objets peuvent avoir le même nom ;
  • LargeurObjet : la largeur de l'objet, laissez à 0 pour prendre la largeur par défaut (le ratio est conservé) ;
  • HauteurObjet : la hauteur de l'objet, laissez à 0 pour prendre la hauteur par défaut (le ratio est conservé).

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

remarques :

  • si la feuille n'est pas protégée l'utilisateur peut déplacer l'objet (voire le supprimer) et les objets ne sont pas masqués sur les cellules filtrées ;
  • l'incorporation des objets fait gonfler la taille du classeur.

Cette technologie est donc à utiliser avec modération.
Suivant votre situation, pensez à l'alternative qui consiste à insérer un lien hypertexte dans la cellule ou une image dans le commentaire, voir la fonction TS_ModifCellule.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-C. TS_ObjetSupprimer

La fonction TS_ObjetSupprimer supprime un objet du tableau structuré passé en argument.
En principe l'objet a été inséré par la fonction TS_ObjetInserer.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • Nom : le nom de l'objet (laissez à vide pour supprimer tous les objets de la feuille), plusieurs objets peuvent avoir le même nom.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-D. TS_ObjetNom

La fonction TS_ObjetNom revoie le nom de l'objet contenu dans une cellule du tableau structuré passé en argument.
En principe l'objet a été inséré par la fonction TS_ObjetInserer.
L'objet ne doit pas avoir été déplacé par l'utilisateur pour pouvoir être retrouvé par cette fonction.

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. Si 0 est renseigné alors prend la dernière colonne ;
  • Ligne : la ligne concernée. Si 0 est renseigné alors prend la dernière ligne.

La fonction renvoie : le nom de l'objet de la cellule s'il est trouvé ou vide.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-E. TS_ObjetRenommer

La fonction TS_ObjetRenommer renomme un objet du tableau structuré passé en argument.
En principe l'objet a été inséré par la fonction TS_ObjetInserer.
Si plusieurs objets ont le même nom tous sont renommés.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • AncienNom : le nom de l'objet qu'il faut renommer ;
  • NouveauNom : le nouveau nom de l'objet.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-F. TS_ModifierListeValidation

La fonction TS_ModifierListeValidation met à jour une liste de validation dont la source est une colonne d'un tableau structuré.
Une alternative est de renseigner =INDIRECT("Tableau[Colonne]" dans la source des données de validation où "Tableau" est le nom du tableau structuré et "Colonne" le nom de la colonne. Ce qui met à jour automatiquement la liste de validation en cas de modification de la source.
Cette fonction permet de référencer différentes sources à une même liste de validation.

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 concernée, ou 0 pour traiter la dernière colonne ;
  • Cellule : la cellule (de type Range) qui contient la liste de validation.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-G. TS_ListeSansDoubon

La fonction TS_ListeSansDoubon renvoie une liste sans doublon (valeur unique) en base 0 de la colonne d'un tableau structuré, classée par ordre croissant.
La fonction utilise une requête SQL mais nous verrons avec TS_ValeurUnique une autre façon de faire, plus rapide en temps d'exécution mais qui ne prend pas bien en charge les cellules contenant des erreurs.

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 concernée, ou 0 pour traiter la dernière colonne.

La fonction renvoie : une mémoire contenant les valeurs uniques où la première valeur est en index 0. Une cellule non renseignée (vide) vaut Null (et pas Empty).


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-H. TS_ValeurUnique

La fonction TS_ValeurUnique renvoie une liste sans doublon (valeur unique) en base 0 de la colonne d'un tableau structuré, classée par ordre croissant.
La fonction utilise un l'algorithme de classement rapide QuickSort (un peu moins rapide que QuickRanking mais plus simple à écrire et qui convient ici) pour trier les données. Il suffit ensuite de parcourir cette liste et de ne retenir que les éléments différents du suivant pour avoir une liste de valeurs uniques. Ce qui est très rapide en temps d'exécution en VBA.

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 concernée, ou 0 pour traiter la dernière colonne.

La fonction renvoie : une mémoire contenant les valeurs uniques où la première valeur est en index 0.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-I. TS_ComparerDeuxTableaux

La fonction TS_ComparerDeuxTableaux compare les données de deux colonnes de deux tableaux structurés et renvoie les données identiques ou différentes (suivant la méthode de comparaison désirée) soit dans une variable soit dans un tableau.

La fonction utilise TS_ValeurUnique pour obtenir des listes de données uniques et triées puis fait une recherche dichotomique sur les éléments, ce qui est bien plus rapide qu'un traitement par la force brute ou l'utilisation de Application.Match (Find est beaucoup plus lent mais permet de respecter la casse si besoin).

Ses arguments sont :

  • TS_1 : la plage (de type Range) qui représente le premier tableau structuré ;
  • Colonne_1 : le numéro de la colonne ou le nom de la colonne concernée dans le premier tableau structuré, ou 0 pour traiter la dernière colonne ;
  • TS_2 : la plage (de type Range) qui représente le deuxième tableau structuré ;
  • Colonne_2 : le numéro de la colonne ou le nom de la colonne concernée dans le deuxième tableau structuré, ou 0 pour traiter la dernière colonne ;
  • Méthode : la méthode de comparaison
        0 = renvoie les données que l'on retrouve dans TS_1 et dans TS_2,
       -1 = renvoie les données de TS_1 que l'on ne retrouve pas dans TS_2,
        1 = renvoie les données de TS_2 que l'on ne retrouve pas dans TS_1 ;
  • TS_Dest : (facultatif) la plage (de type Range) qui représente le tableau structuré destination où afficher les données ;
  • RemplacerOuAjouter : (facultatif) indique si les données doivent remplacer celles existantes dans le tableau structuré destination ou y être ajoutées, suivant l'énumération personnelle Enum_CopierDonnées :
       - TS_RemplacerDonnées : remplace les données existantes par les nouvelles,
       - TS_AjouterDonnées : ajoute les nouvelles données à la suite des données existantes.

La fonction renvoie :

  • Si TS_Dest n'est pas renseigné : une mémoire des valeurs uniques où la première valeur est en index 0, ou Null si aucune valeur trouvée.
  • Si TS_Dest est renseigné : le nombre de lignes ajoutées au tableau destination ou -1 si erreur.

Exemples pour comparer les données de deux tableaux nommés "TS_1" et "TS_2" et alimenter trois tableaux "TS_Dest1", "TS_Dest2" et "TS_Dest3" respectivement, des données uniques communes aux deux tableaux, de celles qui ne sont présentes que dans le premier, de celles qui ne sont présentes que dans le second (les en-têtes de colonne sont identiques aux noms des tableaux structurés) :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Call TS_ComparerDeuxTableaux(Range("TS_1"), 1, Range("TS_2"), 1, 0, Range("TS_Dest1"), TS_RemplacerDonnées)
Call TS_ComparerDeuxTableaux(Range("TS_1"), 1, Range("TS_2"), 1, -1, Range("TS_Dest2"), TS_RemplacerDonnées)
Call TS_ComparerDeuxTableaux(Range("TS_1"), 1, Range("TS_2"), 1, 1, Range("TS_Dest3"), TS_RemplacerDonnées)
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez


Comme indiqué au début de ce chapitre, la recherche dichotomique utilisée par cette fonction permet de rendre le traitement très rapide sur de grandes listes de données.
J'ai testé différentes méthodes sur deux listes de 50 000 lignes comprenant des nombres aléatoires compris entre 1 et 999 999.
Avec Application.Match le traitement prend 2 minutes, avec une requête SQL il passe à 10 secondes, et avec cette fonction : à moins d'une seconde.
Vous trouverez dans le fichier joint le détail de ces codes.



VII-J. TS_AlimenterColonne

La fonction TS_AlimenterColonne alimente une colonne d'un tableau structuré en effectuant l'équivalent d'un RechercheV sur l'ensemble des lignes d'une colonne. Mais contrairement à RechercheV elle permet d'indiquer plusieurs colonnes de référence dans les sources et non pas une seule.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré qu'il faudra alimenter ;
  • Colonne : le numéro de la colonne ou le nom de la colonne qu'il faut alimenter, ou 0 pour traiter la dernière colonne ;
  • TS_Recherche : la plage (de type Range) qui représente le tableau structuré où il faut rechercher ;
  • ColonneRenvoyée : le numéro de la colonne ou le nom de la colonne dans le deuxième tableau structuré "TS_Recherche" dont il faut renvoyer les données, ou 0 pour traiter la dernière colonne ;
  • RespecterCasse : True pour respecter la casse ou False pour l'ignorer ;
  • EffacerSiNonTrouvé : indique s'il faut ou non effacer la cellule si la recherche est infructueuse (si True alors la cellule est effacée, si False elle est conservée) ;
  • VisibleUniquement : si True ne traite que les lignes visibles, si False alors traite toutes les lignes ;
  • ListeColonnes : la liste des couples colonnes de "TS" et de "TS_Recherche" de type ParamArray (tableau de paramètres) qui sont les clés (colonnes de référence) pour la recherche à effectuer.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple pour alimenter la colonne "Note 2" du tableau "TS_Eleves" qui contient les colonnes "Nom Famille" et "Prénom" pour identifier les élèves, en récupérant les notes dans le tableau "TS_Notes" qui contient les colonnes "NOM" et "PRENOM" pour identifier les élèves et la colonne "NOTE" pour la note. Si l'élève n'est pas trouvé dans la "TS_Notes" alors la note dans "Note 2" est conservée :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Call TS_AlimenterColonne(Range("TS_Eleves"), "Note 2", Range("TS_Notes"), "NOTE", True, False, False, _
                        "Nom Famille", "NOM", "Prénom", "PRENOM")
End Sub
'------------------------------------------------------------------------------------------------


Image non disponible


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-K. TS_ComboBox

La fonction TS_ComboBox alimente une zone de liste déroulante (ComboBox) de type ActiveX sur une feuille du classeur d'après les données source issues d'un tableau structuré.
Elle déclenche une macro personnelle suite à la validation d’un élément de la liste.
La sélection des données source se fait par une requête SQL ce qui permet une grande souplesse.
Une saisie est possible dans la zone de texte pour faciliter la sélection.
La sélection doit correspondre à un élément de la liste (CB.ListIndex <> -1) et être validée par la touche "[Entrée]" (KeyCode = 13) pour déclencher la macro personnelle liée, voir l'exemple ci-dessous.
La variable "TS_ChoixComboBox()" sera alimentée des valeurs des colonnes de la ligne sélectionnée et renvoyée comme argument de la macro liée (1er indice = 1) de type Sub MaMacro(X As Variant).
Vous pouvez associer (manuellement) une cellule liée à la ComboBox comme dans un cas classique et renseigner BoundColumn pour spécifier la valeur du contrôle pour cette cellule liée.

Ses arguments sont :

  • CB : la zone de liste déroulante (ComboBox) ActiveX qu'il faut alimenter ;
  • MacroLiée : le nom de la macro à exécuter quand un élément de la liste est validé ;
  • TS : la plage (de type Range) qui contient les données source ;
  • StrChamps : (facultatif) la liste des champs de la requête (ou tous les champs si vide ou non renseigné) ;
  • StrSQL : (facultatif) la requête SQL ;
  • NombreLignes : (facultatif) le nombre de lignes à afficher dans la liste déroulante ;
  • ColonneClé : (facultatif) la colonne de référence qui alimentera la zone de saisie (voir ici) ;
  • LargeurColonnes : (facultatif) la largeur des colonnes en pouces (0 pour masquer une colonne), chaîne de caractères où les valeurs sont séparées par un point-virgule (si vide alors les colonnes sont de même taille et leur cumul est égal à la taille de la ComboBox, voir ici).

Après sélection d'un élément de la liste :
CB.Value contient la valeur de la colonne indiquée dans BoundColumn (1 par défaut, 0 pour affecter ListIndex).
CB.Text contient la valeur de la colonne de référence (le texte affiché dans la zone de saisie = TextColumn).
CB.ListIndex représente le numéro de la ligne dans la liste déroulante (1er indice = 0).
CB.List(CB.ListIndex, x) contient la valeur de la xième colonne (1er indice = 0).

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.
La fonction renseigne : l'argument de la macro liée d'une variable à plusieurs indices (1er indice = 1) des valeurs des colonnes de la ligne sélectionnée.

Remarques :
- La liste déroulante est normalement initialisée à chaque ouverture (réception du focus) par l'événement Private Sub ComboBox1_GotFocus() afin d'avoir des données toujours actualisées. Toutefois sur des listes volumineuses et qui n'évoluent pas (ou rarement), il est préférable pour limiter les temps de traitement d'initialiser la liste par un événement à l'ouverture du classeur et de l'actualiser par un événement en cas de changement des données sources.
- La touche [Suppr] efface la saisie faite.
- La touche [Inser] restaure la dernière saisie validée.

Exemple d'utilisation pour sélectionner un élément dans une liste déroulante ActiveX nommée "ComboBox1" et lancer la fonction "MaMacro" quand un élément est sélectionné puis validé par la touche [Entrée].
L'utilisateur peut saisir un prénom (colonne 2 de la liste) pour présélectionner un élément.

Image non disponible
La liste déroulante est initialisée d’après une requête SQL sur le tableau structuré de gauche

Placez dans le code de la feuille où est la ComboBox ces deux événements (le 1er pour initialiser la zone de liste à la réception du focus, le second pour contrôler la sélection faite à chaque saisie et déclencher la macro liée s'il y a une validation).
La requête SQL sélectionne les noms, prénoms et numéro Id du tableau "TS_Eleves" et les trie pas ordre croissant. La 3e colonne ne sera pas visible dans la liste déroulante :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Private Sub ComboBox1_GotFocus()
'------------------------------------------------------------------------------------------------
' Alimente la liste déroulante d'après trois colonnes du tableau "TS_Eleves" (Id ne sera pas visible):
Call TS_ComboBox(CB:=ComboBox1, _
                 MacroLiée:="MaMacro", _
                 TS:=Range("TS_Eleves"), _
                 StrChamps:="[Nom Famille], [Prénom], [Id]", _
                 StrSQL:="ORDER BY [Nom Famille]", _
                 ColonneClé:=2, _
                 LargeurColonnes:="60;60;0")
End Sub

'------------------------------------------------------------------------------------------------
Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'------------------------------------------------------------------------------------------------
Call TS_ComboBox(ComboBox1, Int(KeyCode))
End Sub
'------------------------------------------------------------------------------------------------


Placez la macro "MaMacro" dans un module classique.
Elle fera la mise à jour du tableau structuré (par une requête SQL) d'après la valeur "Id" (3e colonne) de la sélection faite, en mettant la valeur "Bleu" dans la colonne "Couleur". L'argument "Choix" de cette fonction contient les valeurs des colonnes (1er indice = 1) :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub MaMacro(Choix As Variant)
'------------------------------------------------------------------------------------------------
Call TS_ModifierSQL(TS:=Range("TS_Eleves"), _
                    Colonne:="Couleur", _
                    StrSQL:="WHERE [id]=" & Choix(3), _
                    Valeur:="Bleu", _
                    Méthode:=TS_Update_Valeur)
End Sub
'------------------------------------------------------------------------------------------------


Astuce : vous pouvez faire un événement sur clic dans la liste pour forcer la validation sans avoir à valider par [Entrée] :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Private Sub ComboBox1_Click()
'------------------------------------------------------------------------------------------------
Call TS_ComboBox(ComboBox1, Int(13))
End Sub
'------------------------------------------------------------------------------------------------


Dans cet exemple, une première ComboBox affiche la liste des communes sans doublon. Après validation la fonction liée "SelectionnerVille_1" compte le nombre de commune portant ce nom.
S’il y en a plusieurs une seconde ComboBox affiche la liste des communes portant ce nom pour permettre à l’utilisateur d’affiner son choix.
La ville sélectionnée est mise en surbrillance dans le tableau source.

Image non disponible
En cas de doublon sur le nom d’une commune une seconde ComboBox est renseignée


Si l’initialisation de la première ComboBox se fait par un événement sur réception du focus :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Private Sub ComboBox1_GotFocus()
'------------------------------------------------------------------------------------------------------
' Efface la ComboBox2:
ActiveSheet.ComboBox2.Text = ""
ActiveSheet.ComboBox2.Clear

' Initialise la liste des villes sans doublon:
Call TS_ComboBox(CB:=ComboBox1, _
                 MacroLiée:="SelectionnerVille_1", _
                 TS:=Range("TS_CP"), _
                 StrChamps:="[Commune]", _
                 StrSQL:="GROUP BY [Commune] ORDER BY [Commune]", _
                 ColonneClé:=1, _
                 LargeurColonnes:="150")
                 
End Sub
'------------------------------------------------------------------------------------------------


La seconde sera faite à l’intérieur de la macro "SelectionnerVille_1" :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub SelectionnerVille_1(Choix)
'------------------------------------------------------------------------------------------------------
Dim i As Long
Dim Tableau As Range
Set Tableau = Range("TS_CP")

' Si le nom de la commune est unique dans la liste:
If Application.Evaluate("=COUNTIF(TS_CP[Commune],""" & Choix(1) & """)") = 1 Then

    ' Alors recherche sa position dans le tableau et sélectionne toute la ligne:
    Call TS_Filtres_Effacer(Tableau)
    i = TS_RechercherVisible(Tableau, "Commune", Choix(1), False, TS_Valeurs, xlWhole)
    ' Met la ligne en jaune:
    Tableau.ListObject.DataBodyRange.Interior.Color = xlNone
    Tableau.ListObject.DataBodyRange.Rows(i).Interior.Color = 65535
    Application.Goto Tableau.ListObject.DataBodyRange.Rows(i), True
    [A1].Select
    
Else
    
    ' Initialise la liste des communes portant le nom sélectionnné, contenu dans Choix(1):
    ActiveSheet.ComboBox2.Activate
    Call TS_ComboBox(CB:=ActiveSheet.ComboBox2, _
                     MacroLiée:="SelectionnerVille_2", _
                     TS:=Range("TS_CP"), _
                     StrChamps:="[Commune], [Code postal], [Département], [Insee]", _
                     StrSQL:="WHERE [Commune] = " & TS_FormatSQL(Choix(1), TS_Chaine), _
                     NombreLignes:=15, _
                     ColonneClé:=3, _
                     LargeurColonnes:="110;50;150;0")
                     
End If

End Sub
'------------------------------------------------------------------------------------------------




Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez


Vous retrouverez l’intégralité des codes dans le fichier joint sur la feuille « Villes » et dans le module « Module_Test_ComboBox ».



VII-L. TS_RangeDansClasseur

La fonction TS_RangeDansClasseur renvoie l'objet Range correspondant à un tableau structuré situé dans un classeur autre que celui qui est actif sans avoir à connaitre le nom de la feuille où il se trouve. C'est cet objet qui sera utilisé dans nos fonctions pour gérer les tableaux structurés.

Souvenez-vous que lorsque l'on manipule un tableau du classeur actif il n'est pas nécessaire d'indiquer le nom de la feuille où il se situe, mais lorsqu'il est dans un autre classeur il faut renseigner la feuille concernée. Qui n'est pas toujours connue ou qui peut avoir été changée.

Nous verrons un exemple d'utilisation dans le chapitre suivant.

Ses arguments sont :

  • Classeur : le classeur (de type Workbook) qui contient le tableau structuré ;
  • TS_Nom : le nom du tableau structuré.

La fonction renvoie : un objet Range si tout s’est bien passé ou Nothing dans le cas contraire.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



VII-M. TS_OuvrirClasseur et TS_FermerClasseur

La fonction TS_OuvrirClasseur ouvre un classeur externe en le rendant visible ou non. Le rendre non visible permet de faire des manipulations sans que l'utilisateur soit perturbé par les affichages à l'écran.

Un fichier en lecture seule peut être modifié car la fonction faut sauter cet attribut temporairement puis le remet à la fermeture par la fonction TS_FermerClasseur.

La fonction TS_FermerClasseur fermera ce classeur en l'enregistrant ou non, suivant votre choix.


Les arguments de la fonction TS_OuvrirClasseur sont :

  • Fichier : le classeur Excel qu'il faut ouvrir (chemin complet + nom avec l'extension) ;
  • Visible : True pour afficher le classeur ou False pour le masquer ;
  • MotDePasseOuverture : éventuellement le mot de passe pour ouvrir le fichier (vide si non nécessaire) ;
  • MotDePasseEcriture : éventuellement le mot de passe pour modifier le fichier (vide si non nécessaire) ;
  • MAJ_Liens : mettre Vrai s'il faut faire une mise à jour des liens à l'ouverture du classeur ;
  • DésactiveMacros : mettre Vrai pour désactiver les macros et les événements y compris Workbook_Open.

La fonction renvoie : l'objet Workbook du classeur si tout s’est bien passé ou Nothing dans le cas contraire.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez


Les arguments de la fonction TS_FermerClasseur sont :

  • Classeur : l'objet Workbook à fermer ;
  • Enregistrer : mettre True pour enregistrer le classeur, ou False pour le fermer sans l'enregistrer.

La fonction renvoie : l'objet True si tout s’est bien passé ou False dans le cas contraire.


Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez


Exemple d'utilisation pour remplacer les données du tableau « TS_Eleves » contenues dans le classeur « C:\Users\ott_l\Downloads\Test_TS.xlsm » sur la feuille dont nous ne connaissons pas le nom (ou qui pourrait éventuellement changer) par les données du tableau « TS_Eleves » du classeur « C:\Users\ott_l\Downloads\Classeur_Elèves.xlsx ».
Notez ici l'usage de la fonction TS_RangeDansClasseur qui permet de retrouver le tableau sans avoir à indiquer le nom de la feuille concernée.
Dans notre exemple les macros n'ont pas été désactivées car nous allons lancer celle contenue dans le classeur « Test_TS.xlsm » qui met en forme le tableau.

Le code  :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
On Error GoTo Gest_Err
Err.Clear

Dim Wk_S As Workbook
Set Wk_S = TS_OuvrirClasseur("C:\Users\ott_l\Downloads\Classeur_Elèves.xlsx", False)

Dim Wk_D As Workbook
Set Wk_D = TS_OuvrirClasseur(Fichier:="C:\Users\ott_l\Downloads\Test_TS.xlsm", Visible:=False, DésactiveMacros:=False)

Dim TS As Range: Set TS = TS_RangeDansClasseur(Wk_S, "TS_Eleves")
Dim TD As Range: Set TD = TS_RangeDansClasseur(Wk_D, "TS_Eleves")

Call TS_CopierUnTableau(TS, TD, TS_RemplacerDonnées, TS_Valeurs)

' Active le classeur qui contient la macro à exécuter:
Wk_D.Activate
Application.Run "Test_TS.xlsm!'MiseEnForme'"

Call TS_FermerClasseur(Wk_S, False)
Call TS_FermerClasseur(Wk_D, True)

MsgBox "fin du traitement"

Gest_Err:

If Err.Number <> 0 Then
    MsgBox Err.Number & " : " & Err.Description, vbExclamation
    Call TS_FermerClasseur(Wk_S, False)
    Call TS_FermerClasseur(Wk_D, True)
End If
Err.Clear

End Sub
'------------------------------------------------------------------------------------------------



VIII. 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 1 des exemples qui utilisent les requêtes SQL et devraient vous convaincre plus que de longs discours.

Mais pour faire des traitements corrects encore faut-il que les données soient correctes : l'annexe 3 vous donnera une astuce pour contrôler la validation des saisies.

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.



IX. Remerciements

Je remercie 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…



Note : de nombreux ajouts ont été réalisés après la première publication de cette documentation sans avoir été soumis à une relecture technique et orthographique, si vous constatez des erreurs merci de les signaler dans cette discussion ou par messagerie privée.

Le but ici étant de répondre aux besoins des développeurs, n'hésitez pas à proposer de nouvelles fonctions.



X. 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 :


Image non disponible

Pour utiliser les codes présentés dans cette documentation vous devrez installer les références suivantes dans votre projet :
- Microsoft Office 16.0 Object Library
- Microsoft Forms 2.0 Object Library



XI. Exemples d'utilisation de fonctions

Vous trouverez ci-dessous des exemples d'utilisation de quelques fonctions TS que nous avons étudiées dans cette documentation pour réaliser deux exercices à partir d'une liste d'élèves enregistrés dans le tableau structuré nommé « TS_Eleves ».
Le premier exercice consiste à générer une liste sans doublon des noms des élèves pour renseigner le tableau structuré « TS_Liste_Noms ».
Dans le second exercice nous ferons l'inverse pour alimenter le tableau structuré « TS_Fratries » des fratries, c'est-à-dire des noms en doublons, pour renseigner le nom de famille, le nombre d'enfants, la note moyenne, minimum et maximum de la fratrie.

Cette image représente le tableau source « TS_Eleves », le tableau des valeurs uniques « TS_Liste_Noms » et le tableau des fratries « TS_Fratries ».

Image non disponible

XI-A. Alimenter un tableau d'une liste sans doublon

Il y a plusieurs façons de faire pour alimenter un tableau d'une liste sans doublon.
Nous allons en présenter cinq.
Pour vous faciliter la lecture du code j'ai nommé les arguments de certaines fonctions.

1) Alimenter un tableau d'une liste sans doublon en utilisant les boucles.
Principe : On boucle sur la source pour alimenter la destination de l'ensemble des noms, puis on supprime les doublons et l'on trie les noms restants par ordre croissant.

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'---------------------------------------------------------------------------------------
Dim i As Long, Nom As String

' Supprime les lignes de la destination pour avoir un tableau de destination vierge:
Call TS_SupprimerPlusieursLignes(Range("TS_Liste_Noms"), 1, 0)

' Boucle sur les lignes de la source:
For i = 1 To TS_Nombre_Lignes(Range("TS_Eleves"))
    ' Mémorise le nom de famille:
    Nom = TS_InfoCellule(Range("TS_Eleves"), "Nom Famille", i)
    ' Ajoute une ligne à la destination:
    Call TS_AjouterUneLigne(Range("TS_Liste_Noms"))
    ' Place dans cette ligne le nom mémorisé
    Call TS_ModifCellule(Range("TS_Liste_Noms"), 1, i, Nom)
Next i

' Supprime les doublons:
Call TS_SupprimerDoublons(Range("TS_Liste_Noms"), 1)

' Trie dans l'ordre croissant le résultat:
Call TS_TrierUneColonne(Range("TS_Liste_Noms"), 1)

End Sub
'-----------------------------------------------------------------------------------------------



2) Alimenter un tableau d'une liste sans doublon en utilisant la fonction TS_ValeurUnique.
Principe : On mémorise dans une variable la liste des noms sans doublon et classés par ordre croissant obtenue par la fonction TS_ValeurUnique, puis on alimente le tableau destination des données.

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'---------------------------------------------------------------------------------------
Dim i As Long, Noms As Variant

' Supprime les lignes de la destination pour avoir un tableau de destination vierge:
Call TS_SupprimerPlusieursLignes(Range("TS_Liste_Noms"), 1, 0)

' Renvoie la liste des noms sans doublon et classés par ordre croissant:
Noms = TS_ValeurUnique(Range("TS_Eleves"), "Nom Famille")

' Alimente le tableau de la liste obtenue:
For i = 0 To UBound(Noms)
    Call TS_AjouterUneLigne(Range("TS_Liste_Noms"))
    Call TS_ModifCellule(Range("TS_Liste_Noms"), 1, 0, Noms(i))
Next i

End Sub
'-----------------------------------------------------------------------------------------------



3) Alimenter un tableau d'une liste sans doublon en utilisant la fonction TS_CopierUneColonne.
Principe : On copie la colonne des noms de famille dans le tableau destination par la fonction TS_CopierUneColonne, puis on supprime les doublons et l'on trie les noms restants par ordre croissant.

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'---------------------------------------------------------------------------------------
' Il faut effacer les filtres de la source car TS_CopierUneColonne ne copie que les lignes visibles:
Call TS_Filtres_Effacer(Range("TS_Eleves"))

' Efface les données de la destination et supprime les lignes sauf la première pour
' avoir un tableau de destination vide:
Call TS_EffacerToutesLignes(Range("TS_Liste_Noms"))
Call TS_SupprimerPlusieursLignes(Range("TS_Liste_Noms"), 2, 0)

' Copie les lignes visibles de la colonne source et les colle dans la destination:
Call TS_CopierUneColonne(TS_Source:=Range("TS_Eleves"), Colonne_Source:="Nom Famille", _
                         TS_Dest:=Range("TS_Liste_Noms"), Colonne_Dest:=1, Ligne_Dest:=1, _
                         Méthode:=xlCellTypeVisible)

' Sélectionne la première ligne car TS_CopierUneColonne a sélectionné toutes les lignes:
Call TS_Sélectionner(Range("TS_Liste_Noms"), 1, 1)

' Supprime les doublons:
Call TS_SupprimerDoublons(Range("TS_Liste_Noms"), 1)

' Trie dans l'ordre croissant le résultat:
Call TS_TrierUneColonne(Range("TS_Liste_Noms"), 1)

End Sub
'-----------------------------------------------------------------------------------------------



4) Alimenter un tableau d'une liste sans doublon en utilisant les fonctions TS_Range et TS_CopierUnTableau.
Principe : normalement la fonction TS_CopierUnTableau copie l'intégralité d'un tableau source dans un tableau destination, dans la limite des colonnes disponibles. Dans notre cas la première colonne serait copiée et l'on retrouverait les noms de famille dans le tableau « TS_Liste_Noms ». Sauf si l'utilisateur a modifié l'ordre de présentation des colonnes. Donc par sécurité nous mémorisons la colonne des noms de famille dans une variable par la fonction TS_Range et nous utilisons cette variable à la place d'un tableau. Puis on supprime les doublons et l'on trie les noms restants par ordre croissant. Vous remarquerez que le tableau destination n'a pas besoin d'être préalablement vidé car l'option TS_RemplacerDonnées s'en charge.

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'---------------------------------------------------------------------------------------
Dim V As Variant

' Mémorise dans une variable de type variant les données même masquées de la colonne du tableau source:
V = TS_Range(TS:=Range("TS_Eleves"), VisibleUniquement:=False, Colonne:="Nom Famille", Ligne:="")

' Colle la source, donc la variable V, dans la destination:
Call TS_CopierUnTableau(TS_Source:=V, TS_Dest:=Range("TS_Liste_Noms"), _
                        Méthode:=TS_RemplacerDonnées, _
                        ValeursOuFormules:=TS_Valeurs)
' Supprime les doublons:
Call TS_SupprimerDoublons(Range("TS_Liste_Noms"), 1)

' Trie dans l'ordre croissant le résultat:
Call TS_TrierUneColonne(Range("TS_Liste_Noms"), 1)

End Sub
'-----------------------------------------------------------------------------------------------



5) Alimenter un tableau d'une liste sans doublon en utilisant la fonction TS_RequeteSQL.
Principe : On utilise une requête SQL pour sélectionner les données source, les grouper en valeur unique, les trier par ordre croissant et alimenter le tableau destination.

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'---------------------------------------------------------------------------------------
Call TS_RequeteSQL(TS:=Range("TS_Eleves"), _
                   StrChamps:="[Nom Famille]", _
                   StrSQL:="GROUP BY [Nom Famille] ORDER BY [Nom Famille]", _
                   TS_Dest:=Range("TS_Liste_Noms"), _
                   Méthode:=TS_RemplacerDonnées)
End Sub
'-----------------------------------------------------------------------------------------------



XI-B. Alimenter le tableau structuré « TS_Fratries » des fratries

Rappel de l'exercice : alimenter le tableau structuré « TS_Fratries » pour renseigner le nom de famille, le nombre d'enfants, la note moyenne, minimale et maximale de chaque fratrie.

J'avoue ne pas avoir cherché à faire cela avec des boucles, des comparaisons et autres complications en tout genre car une requête SQL fait le travail tout naturellement (mais pour vous amuser vous pouvez faire plus compliqué).

Quelques explications sur cette requête :

  • TS : Range("TS_Eleves") est le tableau structuré source ;
  • StrChamps : est la sélection des données source. En plus du nom de famille nous avons Count pour compter les noms de famille, AVG pour la moyenne des notes, MIN pour la note minimale, MAX pour la note maximale ;
  • StrSQL : la requête regroupe les noms de famille pour avoir des valeurs uniques GROUP BY [Nom Famille], compte les noms de famille et ne retient que ceux qui sont supérieurs à un HAVING Count([Nom Famille]) > 1 et les trie ORDER BY [Nom Famille] ;
  • TS_Dest : Range("TS_Fratries") est le tableau structuré destination ;
  • Méthode : TS_RemplacerDonnées indique qu'il faut vider la destination avant d'y poser les données.

La requête renvoie - 1 s'il y a une erreur, ou s'il n'y a aucun enregistrement sélectionné, c'est-à-dire aucune fratrie dans les données sources. Un message l'affiche.
Une boucle sur les colonnes permet de mettre en forme les données pour centrer horizontalement les en-têtes et la colonne nombre.


 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'---------------------------------------------------------------------------------------
If TS_RequeteSQL(TS:=Range("TS_Eleves"), _
                 StrChamps:="[Nom Famille], Count([Nom Famille]), AVG([Note]), MIN([Note]), MAX([Note])", _
                 StrSQL:="GROUP BY [Nom Famille] HAVING Count([Nom Famille]) > 1 ORDER BY [Nom Famille]", _
                 TS_Dest:=Range("TS_Fratries"), _
                 Méthode:=TS_RemplacerDonnées) = -1 Then
    ' Si rien n'est trouvé:
    MsgBox "Aucune fratrie trouvée."
End If

' Pour chaque colonne: Centre les titres, met les données au format standard sauf la colonne Nombre à centrer:
Dim i As Integer
For i = 1 To TS_Nombre_Colonnes(Range("TS_Fratries"))
    Call TS_Aligner(Range("TS_Fratries"), i, xlHAlignCenter, xlVAlignCenter, TS_Aligner_Entete)
    Call TS_Aligner(Range("TS_Fratries"), i, xlHAlignGeneral, xlVAlignCenter, TS_Aligner_Données)
Next i
Call TS_Aligner(Range("TS_Fratries"), "Nombre", xlHAlignCenter)

End Sub
'-----------------------------------------------------------------------------------------------



XII. Annexe 1 – Les requêtes SQL en VBA sur les tableaux structurés

XII-A. Présentation des requêtes SQL

Pour rechercher, synthétiser, regrouper, trier, 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 : pour faire des mises à jour avec la commande UPDATE le classeur ne doit pas être en lecture seule.

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 » (voir plus bas pour le cas des tableaux structurés de plus de 65 535 lignes ou de plus de 255 colonnes).

- 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. Si la donnée comporte un simple quote il convient de le doubler. Par exemple :
WHERE [Département] = 'Val d''Oise'
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.



XII-B. TS_RequeteSQL

La fonction TS_RequeteSQL exécute une requête SQL sur un tableau structuré
Par défaut elle renvoie un jeu d’enregistrements mais si un tableau destination est renseigné dans les arguments alors elle alimente ce tableau et renvoie le nombre de lignes ajoutées.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré (s'il fait plus de 65 535 lignes ou 255 colonnes alors le tableau devra être unique sur la feuille sans quoi il y a un risque que la requête ne sélectionne pas les bonnes données) ;
  • StrChamps : (facultatif) le nom des champs à sélectionner (ceux qui alimenteront le jeu d’enregistrements) ou vide pour tout prendre ;
  • StrSQL : (facultatif) la requête SQL (qui sera automatiquement préfixée SELECT) avec ses conditions (si "!" alors la requête SQL passée dans l'argument StrSQL est exécutée) ;
  • TS_Dest : (facultatif) le tableau structuré destination où afficher les données (en valeur) de la requête ;
  • Méthode : (facultatif) indique si les données importées doivent remplacer celles existantes dans le tableau structuré de destination ou y être ajoutées, suivant l'énumération personnelle Enum_CopierDonnées :
       - TS_RemplacerDonnées : remplace les données existantes par les nouvelles (valeur par défaut),
       - TS_AjouterDonnées : ajoute les nouvelles données à la suite des données existantes.

Si TS_Dest n'est pas renseigné la fonction renvoie un jeu d’enregistrements, ou Nothing si la requête ne sélectionne rien.
Si TS_Dest est renseigné la fonction renvoie le nombre de lignes ajoutées au tableau destination ou -1 si erreur.

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 » existant déjà sur la feuille 2 mais dont l'étendue est limitée à la feuille (il faut donc indiquer la feuille concernée) :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim TS_Destination As Range

' Gestion des erreurs :
On Error GoTo Gest_Err
Err.Clear

' Initialisation du tableau structuré :
Set TS_Destination = ThisWorkbook.Sheets("Feuil2").Range("Tableau_Noms")
                           
' Requête pour obtenir la liste des noms sans doublon et triée et les afficher en remplacement des données :
Call TS_RequeteSQL(Range("TS_Eleves"), "[Nom]", "GROUP BY [Nom] ORDER BY [Nom]", TS_Destination, TS_RemplacerDonnées)

' Gestion des erreurs :
Gest_Err:
If Err.Number <> 0 Then MsgBox Err.Number & " : " & Err.Description, vbCritical, Err.Source

End Sub
'------------------------------------------------------------------------------------------------

Remarque : l’instruction SQL "GROUP BY [Nom] ORDER BY [Nom]" regroupe les noms sans doublon et les trie par ordre croissant.

Exemple pour obtenir la liste des trois meilleures notes et le nom et prénom des élèves concernés :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
...
   
' Requête pour obtenir les élèves qui ont eu les 3 meilleures notes, classées par notes :
Call TS_RequeteSQL(Tableau, "TOP 3 [Nom],[Prénom],[Note]", "WHERE [Note] > 10 ORDER BY [Note] DESC", TS_Destination, TS_RemplacerDonnées)

...
'------------------------------------------------------------------------------------------------


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 (et les égalités) 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 ;
  • dans cet exemple les trois meilleures notes sont 18, 15 et 14 et deux élèves ont eu 14, donc ils sont repris tous les deux.


Image non disponible

Exemple pour regrouper les notes par ordre décroissant et indiquer le nombre d’élèves concernés :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
...

' Requête pour obtenir la liste des notes et la quantité d'élèves ayant eu cette note :
Call TS_RequeteSQL(Tableau, "[Note], Count([Note])", "GROUP BY [Note] ORDER BY [Note] DESC", TS_Destination, TS_RemplacerDonnées))

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


Image non disponible

Exemple pour ajouter 1 aux notes inférieures à 10 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
...
   
' 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) en Fields(1) et ainsi de suite ;
  • n’oubliez pas l’instruction Update pour valider la modification faite à un enregistrement.

Attention : pour exécuter une mise à jour des données avec l’instruction Update le classeur ne doit pas être en lecture seule.


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 ».
    WHERE Nom Like '%TOTO%' renvoie les noms qui contiennent « TOTO ».



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez


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.

Cliquez sur « Afficher le code » pour voir le code de la fonction privée TS_ExecuterSQL
Cacher/Afficher le codeSélectionnez



XII-C. TS_ModifierSQL

La fonction TS_ModifierSQL exécute une requête SQL sur un tableau structuré pour modifier les données d'une colonne (même si elles sont masquées.

Attention : pour exécuter une mise à jour des données la fonction TS_ModifierSQL le classeur ne doit pas être en lecture seule.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré (s'il fait plus de 65 535 lignes ou 255 colonnes alors le tableau devra être unique sur la feuille sans quoi il y a un risque que la requête ne sélectionne pas les bonnes données) ;
  • 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 ;
  • StrSQL : la requête SQL des critères de sélection des enregistrements concernés ;
  • Valeur : la valeur de référence ;
  • Méthode : (facultatif) indique le mode de mise à jour des données, suivant l'énumération personnelle Enum_ModifierSQL :
       - TS_Update_Valeur : (par défaut) modifie la valeur qui prend la valeur passée dans Valeur,
       - TS_Update_Ajouter : ajoute à la valeur existante la valeur passée dans Valeur,
       - TS_Update_Soustraire : soustrait à la valeur existante la valeur passée dans Valeur,
       - TS_Update_Multiplier : multiplie la valeur existante par la valeur passée dans Valeur,
       - TS_Update_Diviser : divise la valeur existante par la valeur passée dans Valeur.

La fonction renvoie : True si tout s’est bien passé ou False dans le cas contraire.

Exemple pour ajouter 2 points à toutes les notes (pas de critère de sélection dans la requête SQL) puis limiter les notes à 20 (cette fois il faut sélectionner les notes supérieures à 20 dans la requête SQL) :

Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Public Function TS_ModifierSQL(TS As Range, Colonne As Variant, _
                               ByVal StrSQL As String, _
                               ByVal Valeur As Variant, _
                               Optional Méthode As Enum_ModifierSQL = TS_Update_valeur) As Boolean
'------------------------------------------------------------------------------------------------
' Modifie les données d'une colonne d'un tableau structuré (même sur les lignes masquées).
' Les données sont sélectionnées par une requête SQL.
' ATTENTION : le classeur ne doit pas être en lecture seule.
'------------------------------------------------------------------------------------------------
' TS : La plage du Tableau Structuré (si > 65535 lignes ou > 255 colonnes alors les champs doivent être
' uniques sur la feuille sinon il y a un risque de se tromper de tableau).
' 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.
' StrSQL : La requête SQL avec ses conditions d'identification des enregistrements concernés.
' Valeur : La valeur de référence.
' Méthode : (facultatif) énumération Enum_ModifierSQL  =
'    TS_Update_Valeur : (par défaut) Modifie la valeur qui prend la valeur passée dans Valeur.
'    TS_Update_Ajouter : Ajoute à la valeur existante la valeur passée dans Valeur.
'    TS_Update_Soustraire : Soustrait à la valeur existante la valeur passée dans Valeur.
'    TS_Update_Multiplier : Multiplie la valeur existante par la valeur passée dans Valeur.
'    TS_Update_Diviser : Divise la valeur existante par la valeur passée dans Valeur.
'------------------------------------------------------------------------------------------------
' Renvoie : VRAI si tout s'est bien passé.
'------------------------------------------------------------------------------------------------
' Exemple pour ajouter 2 points aux notes puis limiter les notes à 20 :
'Call TS_ModifierSQL(Range("TS_Eleves"), "Note", "", 2, TS_Update_Ajouter)
'Call TS_ModifierSQL(Range("TS_Eleves"), "Note", "WHERE [Note] > 20", 20, TS_Update_Valeur)
'------------------------------------------------------------------------------------------------
' Attention : Les nombres doivent avoir comme séparateur décimal le point, les dates doivent être au
' format anglais et entre dièses (croisillons), les chaîne entre apostrophes (simple quote).
' l'apostrophe est un mot réservé par le SQL. Si la chaîne de caractère en contient la doubler.
' Exemple : "WHERE [Département] = 'Val d''oise' "
' Vous pouvez utiliser la fonction TS_FormatSQL pour faire cela (surtout lorsque la donnée est une variable).
' Exemple : "WHERE [Département] = " & TS_FormatSQL("Val d'oise")
'------------------------------------------------------------------------------------------------
Dim Enr As Variant
Dim i As Long
Dim StrChamp As String

' Gestion des erreurs:
On Error GoTo Gest_Err
Err.Clear

' 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

' 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 son nom pour l'utiliser dans la requête SQL:
StrChamp = TS.ListObject.ListColumns(Colonne).Name
 
' Requête sur le tableau passé en argument:
If TS.ListObject.ListRows.Count > 65535 Or TS.ListObject.ListColumns.Count > 255 Then
    StrSQL = "SELECT [" & StrChamp & "] FROM [" & TS.Parent.Name & "$] " & StrSQL
Else
    StrSQL = "SELECT [" & StrChamp & "] FROM [" & TS.Parent.Name & "$" _
         & Left(TS.ListObject.HeaderRowRange.Address(False, False, xlA1), InStr(1, TS.ListObject.HeaderRowRange.Address(False, False, xlA1), ":") - 1) _
         & Mid(TS.ListObject.DataBodyRange.Address(False, False, xlA1), InStr(1, TS.ListObject.DataBodyRange.Address(False, False, xlA1), ":")) _
         & "] " & StrSQL
End If

' S'il y a des enregistrements concernés alors les traiter:
Set Enr = TS_ExecuterSQL(TS, StrSQL)
If Not Enr Is Nothing Then
    For i = 1 To Enr.RecordCount
        Select Case Méthode
            Case TS_Update_Valeur: Enr.Fields(0).Value = Valeur
            Case TS_Update_Ajouter: Enr.Fields(0).Value = Enr.Fields(0).Value + Valeur
            Case TS_Update_Soustraire: Enr.Fields(0).Value = Enr.Fields(0).Value - Valeur
            Case TS_Update_Multiplier: Enr.Fields(0).Value = Enr.Fields(0).Value * Valeur
            Case TS_Update_Diviser: Enr.Fields(0).Value = Enr.Fields(0).Value / Valeur
        End Select
        ' Enregistre la mise à jour et passe à l'enregistrement suivant:
        Enr.Update
        Enr.MoveNext
    Next i
End If

' Renvoie VRAI:
TS_ModifierSQL = True

' Fin du traitement:
Gest_Err:
 
' Restaure l'affichage:
TS.ListObject.ShowHeaders = Anc_Entete
TS.ListObject.ShowTotals = Anc_Totaux
Application.ScreenUpdating = Anc_ScreenUpdating

' Gestion des erreurs:
TS_Err_Number = Err.Number
TS_Err_Description = Err.Description
If Err.Number <> 0 Then
    If TS_Méthode_Err = TS_Générer_Erreur Then Err.Raise TS_Err_Number, "TS_ModifierSQL", TS_Err_Description
    If TS_Méthode_Err = TS_MsgBox_Erreur Then MsgBox TS_Err_Number & " : " & TS_Err_Description, vbInformation, "TS_ModifierSQL"
End If
Err.Clear

End Function
'------------------------------------------------------------------------------------------------



XII-D. TS_FormatSQL

La fonction TS_FormatSQL permet de mettre au format SQL une valeur passée dans un requête.
Par exemple une chaîne de caractère comportant une apostrophe (ou simple quote), caractère réservé au SQL, génèrera une erreur de conversion. Dans ce cas il faut la doubler pour que le SQL l'interprète correctement. De même les dates doivent être passées au format "année/mois/jour" et les nombres doivent avoir comme séparateur décimal un point et non pas une virgule.
Cette fonction vous simplifiera la vie en faisant ces corrections et en renvoyant une valeur admise par le SQL.

Ses arguments sont :

  • Valeur : la valeur à traiter ;
  • FormatSQL : (facultatif) Le format désiré, suivant l'énumération personnelle Enum_FormatSQL :
       - TS_Standard : (par défaut) identifie le format de l'argument Valeur et applique le même,
       - TS_Chaine : force le format chaîne de caractères,
       - TS_Date : force le format date (une chaîne au format "AAAAMMJJ" est convertie en date),
       - TS_Numérique : force le format numérique.



Cliquez sur « Afficher le code » pour voir le code de la fonction
Cacher/Afficher le codeSélectionnez



XIII. 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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
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
'------------------------------------------------------------------------------------------------



XIV. Annexe 3 – Contrôler la validation des données

Comme indiqué en introduction de cette documentation, utiliser les tableaux structurés apporte de nombreux avantages pour l’utilisateur mais surtout pour le programmeur, d’autant plus qu’il trouvera ici un large éventail de fonctions pour lui simplifier la vie.

Mais encore faut-il, pour un travail correct, que les données soient fiables. C’est-à-dire qu’elles respectent le format attendu.
Par exemple un texte dans une cellule qui attend une date va certainement générer des erreurs lors des traitements.

Pour contrôler la cohérence des saisies faites par l’utilisateur, Excel met à notre disposition dans le menu « Données / Validation des données » un outil permettant de restreindre une saisie à certaines conditions.
Ça s’applique très bien pour une date, pour une liste de choix, pour un entier ou un décimal, pour un texte dont on veut limiter la longueur…

Mais il n’est pas facile de restreindre la saisie à un format spécifique. Par exemple une référence de produit devant contenir deux lettres majuscules suivies d’un trait et de deux chiffres.
De même certains utilisateurs vont saisir un accent dans un nom de famille, d’autres pas, d’où un problème lorsqu’il faudra analyser les données.

La solution de contournement est de programmer une procédure événementielle à chaque changement de valeur saisie
Private Sub Worksheet_Change(ByVal Target As Range) et de vérifier le contenu de la saisie pour la corriger si besoin.
Soit du sur-mesure. Ce qui demande du temps de programmation et ne simplifie pas la maintenance de l’application.

La méthode employée dans cette discussion apporte, à mon avis, une bonne réponse pour restreindre la saisie à un modèle.
Elle utilise la « Validation des données » mais en la complétant par la technologie des « expressions régulières ».
Voir ici pour plus d’informations si vous ne savez pas comment utiliser les modèles de formats des expressions régulières.

La principe m’a inspiré pour aller encore plus loin, c’est-à-dire pour forcer le format de restitution de la saisie.
Par exemple pour mettre un nom en majuscules même si l’utilisateur l’a saisi en minuscules.
Du coup il n’est plus nécessaire de repasser après chaque saisie pour la corriger puisque tout est pris en compte directement lors de la saisie, ce qui simplifie leur gestion.

Nous utiliserons l’onglet « Message de saisie » pour renseigner dans le titre le format de restitution souhaité et dans le corps du message le modèle attendu de la saisie.

J’ai prévu quatre formats de restitution pour les chaînes de caractères, à renseigner dans le titre (évidement si rien n’est renseigné aucune transformation de la saisie ne sera réalisée) et la possibilité d'accepter une cellule vide :

  • > : met tout en majuscules ;
  • >> : met chaque première lettre d'une chaîne en majuscule et le reste en minuscule (Proper) ;
  • < : met tout en minuscules ;
  • >< : met la première lettre d'une chaîne en majuscule et le reste en minuscule ;
  • ! : une cellule vide est acceptée.

Le modèle de l’expression régulière sera renseigné dans le corps du message, mais j’ai un peu triché en ajoutant des modèles personnalisés :

  • Mail : pour la saisie d'une adresse électronique, soit le modèle de l’expression régulière « ^[\w\.\-]+@[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+\.[A-Za-z]{2,24}$ » ;
  • Password : pour la saisie d'un mot de passe de 8 caractères minimum dont une lettre, un chiffre, un caractère spécial ;
  • « Name= » suivi du nom de la cellule : pour faire référence à une cellule nommée et reprendre le modèle contenu dans cette cellule ;
  • « Like= » suivi de la règle : pour utiliser l'opérateur LIKE beaucoup moins puissant que les expressions régulières mais plus facile à comprendre et peut être utilisé sur des règles simples de longueur fixe. Exemple : « Like=[A-Z][A-Z]-[0-9][0-9] » ;
  • pour rendre la saisie unique et donc interdire la saisie de doublons (pour une chaîne), ajoutez à la suite du modèle :
    - Unique=Tableau[Colonne] : où "Tableau" est le nom d'un tableau structuré et "Colonne" le nom de la colonne de référence. Exemple : « Unique=TS_Eleves[Nom] »,
    - Unique=Feuille!Plage : où "Feuille" est le nom d'une feuille de calcul et "Plage" la plage de référence. Exemple : « Unique=Feuil_Source!D:D »,
    - Unique=Plage : où "Plage" est la plage de référence sur la feuille active. Exemple : "Unique=B3:B9",
    - Unique=? : pour faire référence à la colonne de la cellule dans le tableau structuré ;
  • pour traiter le résultat d'une formule ajoutez à la suite du modèle « Formule= » suivi de l'adresse de la cellule contenant la formule. La formule doit renvoyer Vrai ou Faux afin de déterminer si la saisie est, respectivement, correcte ou non.
    - Formule=Tableau[@Colonne] : où "Tableau" est le nom du tableau structuré de la cellule et "Colonne" le nom de la colonne où se trouve la formule. Le signe « @ » signifie qu'il faut prendre la ligne de la cellule active. Exemple : « Formule=TS_Eleves[@[Sans Doublon]] » avec comme formule dans la colonne "Sans Doublon" pour compter le nombre de fois que le couple "Nom Famille" et "Prénom" est présent dans le tableau afin d'interdire les doublons sur ce couple de données :
    « =NB.SI.ENS([Nom Famille];[@[Nom Famille]];[Prénom];[@Prénom])<=1 ».
    La formule « Formule=TS_Eleves[@[Sans Doublon]] » sera à placer dans les conditions de validation des cellules des colonnes "Nom Famille" et "Prénom",
    - Formule=Feuille!Plage : où "Feuille" est le nom d'une feuille de calcul et "Plage" la plage de référence. Exemple : « Formule=Feuil_Source!A1 » si la formule est sur une autre feuille ou tout simplement « Formule=A1 » si elle est sur la feuille active ;
  • - « Formule= » suivi d'une formule mise au format anglais et qui renvoie Vrai ou Faux peut aussi être utilisé, si vous maitrisez bien les formules en anglais. Attention les points-virgules de nos formules sont à remplacer par des virgules.
    Pour reprendre l'exemple d'une validation sans doublon sur le couple "Nom Famille" et "Prénom" en utilisant directement une formule et non plus l'adresse d'une cellule qui contient la formule, cela donne :
    « Formule=COUNTIFS(TS_Eleves[Nom Famille],TS_Eleves[@[Nom Famille]],TS_Eleves[Prénom],TS_Eleves[@Prénom])<=1 ».
    Formule à placer dans les cellules des colonnes "Nom Famille" et "Prénom".

Vous pouvez compléter par d'autres modèles...

Avant cela il faut inclure dans votre projet le module « Validation_Données » (que vous trouverez dans le fichier joint à cette documentation) qui contient les fonctions EstValide et RegExpMatch que nous détaillerons plus loin.

Puis dans le gestionnaire de noms, créer un nom nommé arbitrairement : « Controle_Validation ».
Qui fait référence à : « =EstValide() ».
Vous pouvez maintenant paramétrer les conditions de validation désirées via l’outil « Validation des données ».

Par exemple pour une saisie d’un nom de famille de 25 caractères au plus, sans accent ni caractères spéciaux et devant être mis en majuscules, placez-vous dans la cellule concernée puis configurez ainsi :
- Onglet « Options »
o Autoriser : « Personnalisé »
o Case « Ignorer si vide » : non cochée
o Formule : « =Controle_Validation »

- Onglet « Message de saisie »
o Titre : « > »
o Message de saisie : « ^[A-Za-z ]{1,25}$ »

- Onglet « Alerte d’erreur » : libre à vous de mettre ce que vous souhaitez.

Puis décochez la case « Quand la cellule est sélectionnée ».

La fonction EstValide est appelée chaque fois qu’une saisie est réalisée. Elle va prendre comme références les informations enregistrées dans le « message du titre » pour le modèle de saisie admis et le « titre » comme modèle de format à appliquer.


Cliquez sur « Afficher le code » pour voir le code de la fonction EstValide
Cacher/Afficher le codeSélectionnez



Si cette méthode de validation des données assure l’intégrité des saisies réalisées, un copier/coller échappe à ce contrôle et permet malheureusement de placer des données au mauvais format. Pour s’en prémunir nous allons empêcher le copier/coller sur les cellules sensibles avec l'instruction Application.CutCopyMode = False activée à chaque sélection d'une cellule.



Le code de l'événement à placer sur la feuille contenant des cellules avec validation de données :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'------------------------------------------------------------------------------------------------
' Empêche de coller sur une cellule avec une validation de données (ou si plusieurs cellules sélectionnées):
On Error Resume Next
Dim F As String
F = Target.Validation.Formula1
If F <> "" Or Target.Count > 1 Then Application.CutCopyMode = False
On Error GoTo 0
End Sub
'------------------------------------------------------------------------------------------------



Mais ce n'est pas suffisant. Car si l'utilisateur sélectionne la cellule puis bascule dans une autre application pour copier des données et les colle dans la cellule active, l'événement sur changement de sélection n'est pas exécuté et le copier/coller n'est pas annulé.
Idem si l'utilisateur modifie ou supprime la règle de validation d'une cellule.
Avant de sauvegarder le classeur nous allons donc contrôler la conformité des règles de validation des données de nos tableaux structurés.
En cas d'erreur la cellule concernée est sélectionnée et l'enregistrement est annulé.



Le code de l'événement à placer sur l'objet ThisWorkbook, ici le tableau structuré "TS_Eleves" est contrôlé mais vous adapterez le code à votre situation.
Vous pouvez définir le niveau d'alerte :

  • xlValidAlertStop : bloque les validations non conformes avec l'icône d'arrêt mais ne bloque pas les avertissements et les informations ;
  • xlValidAlertWarning : bloque les validations non conformes avec l'icône d'arrêt ou avertissements mais ne bloque pas les informations ;
  • xlValidAlertInformation : bloque toutes les validations non conformes.
 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'------------------------------------------------------------------------------------------------
' Refuse la sauvegarde du classeur s'il y a des alertes stop non valides (laisse passer les alertes
' avertissement et informations):
If TS_EstValide(Range("TS_Eleves"), xlValidAlertStop) = False Then Cancel = True
End Sub
'------------------------------------------------------------------------------------------------



Le code de la fonction TS_EstValide, que vous trouverez dans le module Validation_Données du fichier joint :

Cliquez sur « Afficher le code » pour voir le code de la fonction TS_EstValide
Cacher/Afficher le codeSélectionnez



XV. Annexe 4 – 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 :

 
Cacher/Afficher le codeSélectionnez
'------------------------------------------------------------------------------------------------
Public TS_Err_Number As Long
Public TS_Err_Description As String

Private TS_Méthode_Err As Integer

Private Cnn_TS As Variant ' => ADODB.Connection en liaison anticipée avec Microsoft ActiveX Data Objects 6.1 Library
Private Const Provider_ACCDB As String = "Microsoft.ACE.OLEDB.12.0"
 
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
    TS_ViderMémoire
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
 
Public Enum Enum_CopierDonnées
    TS_RemplacerDonnées
    TS_AjouterDonnées
End Enum

Public Enum Enum_ValeursOuFormules
    TS_Valeurs
    TS_Formules
End Enum

Public Enum Enum_ModifierSQL
    TS_Update_Valeur
    TS_Update_Ajouter
    TS_Update_Soustraire
    TS_Update_Multiplier
    TS_Update_Diviser
End Enum

Public Enum Enum_FormatSQL
    TS_Standard
    TS_Chaine
    TS_Date
    TS_Numérique
End Enum

Public Enum Enum_Gestion_Err
    TS_Générer_Erreur
    TS_MsgBox_Erreur
    TS_Ignorer_Erreur
End Enum

Public Enum Enum_Renvoi_Recherche
    TS_Recherche_Premiere_Valeur
    TS_Recherche_Premiere_Ligne
    TS_Recherche_Toutes_Valeurs
    TS_Recherche_Toutes_Lignes
End Enum

Public Enum Enum_TS_Aligner
    TS_Aligner_Données = 2
    TS_Aligner_Entete = 4
    TS_Aligner_Total = 8
End Enum
'------------------------------------------------------------------------------------------------



Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   



Licence Creative Commons
Le contenu de cet article est rédigé par Laurent Ott et est mis à disposition selon les termes de la Licence Creative Commons Attribution - Pas d'Utilisation Commerciale 3.0 non transposé.
Les logos Developpez.com, en-tête, pied de page, css, et look & feel de l'article sont Copyright © 2022 Developpez.com.