........attendi qualche secondo.

#adessonews solo agevolazioni personali e aziendali.

per le ricerche su Google, inserisci prima delle parole di ricerca:

#adessonews

Siamo operativi in tutta Italia

 

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

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

Pierre Fauconnier y consacre un tutoriel très complet 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 le nom de sa colonne 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 (ces deux variables seront exploitées par le programmeur s’il souhaite afficher un message d’erreur ou gérer un fichier de suivi des événements) ;
  • 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 ».

Cette documentation a été réalisée avec Excel 2016 version 32 bits sous Windows 10.

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.

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

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”

Le code de la fonction :

Public Function TS_ConvertirPlageEnTS(TD As Range, _ Optional ByRef Nom As String = “”, _ Optional ByRef Style As String = “*”, _ Optional AvecEntete As XlYesNoGuess = xlYes) As Range On Error GoTo Gest_Err Err.Clear If TD.ListObject Is Nothing Then If TD.Count = 1 Then Set TD = TD.CurrentRegion If Nom > “” Then TD.Parent.ListObjects.Add(xlSrcRange, TD, , AvecEntete).Name = Nom Else TD.Parent.ListObjects.Add xlSrcRange, TD, , AvecEntete End If If Style <> “*” Then TD.Parent.ListObjects(TD.ListObject.Name).TableStyle = Style End If Nom = TD.ListObject.DisplayName Style = TD.Parent.ListObjects(TD.ListObject.Name).TableStyle Set TS_ConvertirPlageEnTS = TD Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

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.

Le code de la fonction :

Public Function TS_CréerUnTableau(Plage As Range, _ Titres As Variant, _ Optional ByRef Nom As String = “”, _ Optional ByRef Style As String = “*”) As Range On Error GoTo Gest_Err Err.Clear If Not Plage.ListObject Is Nothing Then Nom = Plage.ListObject.DisplayName Style = Plage.ListObject.TableStyle Set TS_CréerUnTableau = Range(Plage.ListObject) Else If IsArray(Titres) = True Then Plage.Resize(1, UBound(Titres) + 1).Value = Titres Else Plage.Value = Titres End If Set TS_CréerUnTableau = TS_ConvertirPlageEnTS(Plage, Nom, Style) End If Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

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)

Le code de la fonction :

Public Function TS_SupprimerLeTableau(TS As Range) As Boolean On Error GoTo Gest_Err Err.Clear TS.ListObject.Delete TS_SupprimerLeTableau = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

Le code de la fonction :

Public Function TS_IndexColonne(TS As Range, Colonne As Variant) As Long On Error GoTo Gest_Err Err.Clear If Colonne = “” Or Colonne = 0 Then Colonne = TS.ListObject.ListColumns.Count If TypeName(Colonne) = “String” Then Colonne = TS.ListObject.ListColumns(Colonne).Index If Colonne < 0 Or Colonne > TS.ListObject.ListColumns.Count Then TS_IndexColonne = -1 Else TS_IndexColonne = Colonne End If Gest_Err: If Err.Number <> 0 Or TS_IndexColonne = -1 Then TS_IndexColonne = -1 TS_Err_Description = “La colonne [” & Colonne & “] n’est pas incluse dans le tableau.” End If Err.Clear End Function

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

Le code de la fonction :

Public Function TS_IndexLigne(TS As Range, Ligne As Long) As Long On Error GoTo Gest_Err Err.Clear If Ligne = 0 Then Ligne = TS.ListObject.ListRows.Count If Ligne < 0 Or Ligne > TS.ListObject.ListRows.Count Then TS_IndexLigne = -1 Else TS_IndexLigne = Ligne End If Gest_Err: If Err.Number <> 0 Or TS_IndexLigne = -1 Then TS_IndexLigne = -1 TS_Err_Description = “La Ligne [” & Ligne & “] n’est pas incluse dans le tableau.” End If Err.Clear End Function

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

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

Remarque : pour simplifier vos traitements, nommez vos colonnes en évitant les caractères spéciaux « arobase, dièse, 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.

Le code de la fonction :

Public Function TS_ChangerLibellé(TS As Range, _ ByVal Colonne As Variant, _ Libellé As String) As Boolean On Error GoTo Gest_Err Err.Clear Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description TS.ListObject.ShowHeaders = True If Libellé <> “” Then TS.ListObject.HeaderRowRange(Colonne).NumberFormat = “0;””””;””””;””” & Libellé & “””” Else TS.ListObject.HeaderRowRange(Colonne).NumberFormat = “@” End If TS_ChangerLibellé = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Call TS_EffacerUneLigne(TS:=Tableau, Ligne:=0) End Sub

Ou :

Sub Exemple() Call TS_EffacerUneLigne(Range(“TS_Eleves”), 0) End Sub

Le code de la fonction :

Public Function TS_EffacerUneLigne(TS As Range, ByVal Ligne As Long) As Boolean On Error GoTo Gest_Err Err.Clear Ligne = TS_IndexLigne(TS, Ligne) If Ligne = -1 Then Err.Raise vbObjectError, , TS_Err_Description Select Case TS.ListObject.ListRows.Count Case Is > 1 TS.ListObject.DataBodyRange.Rows(Ligne).Clear Case 1 Dim i As Long For i = 1 To TS.ListObject.ListColumns.Count TS.ListObject.DataBodyRange(1, i).Clear Next i End Select TS_EffacerUneLigne = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Call TS_EffacerToutesLignes(Tableau) End Sub

Image non disponible

Le code de la fonction :

Public Function TS_EffacerToutesLignes(TS As Range) As Boolean On Error GoTo Gest_Err Err.Clear Select Case TS.ListObject.ListRows.Count Case Is > 1 TS.ListObject.DataBodyRange.Clear Case 1 Dim i As Long For i = 1 To TS.ListObject.ListColumns.Count TS.ListObject.DataBodyRange(1, i).Clear Next i End Select TS_EffacerToutesLignes = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

‘———————————————————————————————— 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.

Le code de la fonction :

Public Function TS_AjouterUneLigne(TS As Range, Optional ByVal Ligne As Long = 0) As Boolean Dim MesFiltres As Variant Dim Anc_ScreenUpdating As Boolean On Error GoTo Gest_Err Err.Clear If Ligne <> 0 Then Ligne = TS_IndexLigne(TS, Ligne) If Ligne = -1 Then Err.Raise vbObjectError, , TS_Err_Description End If Anc_ScreenUpdating = Application.ScreenUpdating Application.ScreenUpdating = False Call TS_Filtres_Mémoriser(TS, MesFiltres) Call TS_Filtres_Effacer(TS) Select Case Ligne Case 0 If TS.ListObject.ListRows.Count = 0 Then Set TS = TS.ListObject.ListRows.Add.Range Else TS.ListObject.ListRows.Add End If TS_AjouterUneLigne = True Case 1 To TS.ListObject.ListRows.Count TS.ListObject.ListRows.Add Ligne TS_AjouterUneLigne = True End Select Call TS_Filtres_Restaurer(TS, MesFiltres) Application.ScreenUpdating = Anc_ScreenUpdating Gest_Err : TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function ‘————————————————————————————————

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

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.

Le code de la fonction :

Public Function TS_SupprimerUneLigne(TS As Range, ByVal Ligne As Long) As Boolean Dim MesFiltres As Variant Dim Anc_ScreenUpdating As Boolean On Error GoTo Gest_Err Err.Clear Ligne = TS_IndexLigne(TS, Ligne) If Ligne = -1 Then Err.Raise vbObjectError, , TS_Err_Description Anc_ScreenUpdating = Application.ScreenUpdating Application.ScreenUpdating = False Call TS_Filtres_Mémoriser(TS, MesFiltres) Call TS_Filtres_Effacer(TS) TS.ListObject.ListRows(Ligne).Delete TS_SupprimerUneLigne = True Call TS_Filtres_Restaurer(TS, MesFiltres) Application.ScreenUpdating = Anc_ScreenUpdating Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

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.

Le code de la fonction :

Public Function TS_SupprimerPlusieursLignes(TS As Range, ByVal LigneDébut As Long, ByVal LigneFin As Long) As Boolean Dim MesFiltres As Variant Dim Anc_ScreenUpdating As Boolean Dim Ligne As Long On Error GoTo Gest_err Err.Clear LigneDébut = TS_IndexLigne(TS, LigneDébut) If LigneDébut = -1 Then Err.Raise vbObjectError, , TS_Err_Description LigneFin = TS_IndexLigne(TS, LigneFin) If LigneFin = -1 Then Err.Raise vbObjectError, , TS_Err_Description If LigneFin < LigneDébut Then Ligne = LigneDébut LigneDébut = LigneFin LigneFin = Ligne End If Anc_ScreenUpdating = Application.ScreenUpdating Application.ScreenUpdating = False Call TS_Filtres_Mémoriser(TS, MesFiltres) Call TS_Filtres_Effacer(TS) TS.ListObject.DataBodyRange.Rows(LigneDébut & ":" & LigneFin).Delete TS_SupprimerPlusieursLignes = True Call TS_Filtres_Restaurer(TS, MesFiltres) Application.ScreenUpdating = Anc_ScreenUpdating Gest_err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Call TS_SupprimerToutesLignes(Tableau) End Sub

Image non disponible

Le code de la fonction :

Public Function TS_SupprimerToutesLignes(TS As Range) As Boolean On Error GoTo Gest_Err Err.Clear Call TS_Filtres_Effacer(TS) TS.ListObject.DataBodyRange.Delete TS_SupprimerToutesLignes = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

Function TS_SupprimerLignesVisibles(TS As Range) As Long Dim NbAvant As Long, AncDisplayAlerts As Boolean On Error GoTo Gest_err Err.Clear NbAvant = TS_Nombre_Lignes(TS) AncDisplayAlerts = Application.DisplayAlerts Application.DisplayAlerts = False TS.ListObject.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete Application.DisplayAlerts = AncDisplayAlerts TS_SupprimerLignesVisibles = NbAvant – TS_Nombre_Lignes(TS) Gest_err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description If Err.Number <> 0 Then TS_SupprimerLignesVisibles = -1 Err.Clear End Function

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

Le code de la fonction :

Function TS_SupprimerLignesMasquées(TS As Range) As Long Dim NbAvant As Long, y As Long Dim MesFiltres As Variant Dim Anc_ScreenUpdating As Boolean Dim Lignes() As Long On Error GoTo Gest_err Err.Clear NbAvant = TS_Nombre_Lignes(TS) ReDim Lignes(0 To NbAvant) For y = 1 To TS_Nombre_Lignes(TS) Lignes(y) = TS.ListObject.DataBodyRange(y, 1).Height Next y Anc_ScreenUpdating = Application.ScreenUpdating Application.ScreenUpdating = False Call TS_Filtres_Mémoriser(TS, MesFiltres) Call TS_Filtres_Effacer(TS) For y = TS_Nombre_Lignes(TS) To 1 Step -1 If Lignes(y) = 0 Then TS.ListObject.ListRows(y).Delete Next y Call TS_Filtres_Restaurer(TS, MesFiltres) Application.ScreenUpdating = Anc_ScreenUpdating TS_SupprimerLignesMasquées = NbAvant – TS_Nombre_Lignes(TS) Gest_err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description If Err.Number <> 0 Then TS_SupprimerLignesMasquées = -1 Err.Clear End Function

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

Sub Exemple() Dim TS As Range Set TS = Range(“TS_Eleves”) Call TS_SupprimerDoublons(TS, “B”) Call TS_SupprimerDoublons(TS, Array(“B”, “E”)) Call TS_SupprimerDoublons(TS, 0) Call TS_SupprimerDoublons(TS, Array(“B”, 0)) End Sub Function TS_SupprimerDoublons(TS As Range, ListeColonnes As Variant) As Long Dim NbAvant As Long Dim Colonnes As String, i As Integer On Error GoTo Gest_err Err.Clear NbAvant = TS_Nombre_Lignes(TS) If IsArray(ListeColonnes) = True Then Colonnes = TS_IndexColonne(TS, ListeColonnes(0)) For i = 1 To UBound(ListeColonnes) Colonnes = Colonnes & “,” & TS_IndexColonne(TS, ListeColonnes(i)) Next i Else Colonnes = TS_IndexColonne(TS, ListeColonnes) End If TS.ListObject.Range.RemoveDuplicates Columns:=Array(Colonnes), Header:=xlYes TS_SupprimerDoublons = NbAvant – TS_Nombre_Lignes(TS) Gest_err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description If Err.Number <> 0 Then TS_SupprimerDoublons = -1 Err.Clear End Function

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

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

Le code de la fonction :

Public Function TS_AjouterUneColonne(TS As Range, _ ByVal Colonne As Variant, _ Nom As String) As Boolean Dim i As Integer On Error GoTo Gest_Err Err.Clear TS.ListObject.ShowHeaders = True For i = 1 To TS.ListObject.ListColumns.Count If TS.ListObject.ListColumns(i).Name = Nom Then _ Err.Raise vbObjectError, , “La colonne [” & Nom & “] existe déjà dans le tableau.” Next i If Colonne = “” Then Colonne = 0 If Colonne <> 0 Then Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description End If Select Case Colonne Case 0 TS.ListObject.ListColumns.Add.Name = Nom Nom = TS.ListObject.ListColumns(TS.ListObject.ListColumns.Count).Name TS.ListObject.HeaderRowRange(TS.ListObject.ListColumns.Count).NumberFormat = “@” TS_AjouterUneColonne = True Case Else TS.ListObject.ListColumns.Add(Colonne).Name = Nom Nom = TS.ListObject.ListColumns(TS.ListObject.ListColumns(Colonne)).Name TS.ListObject.HeaderRowRange(Colonne).NumberFormat = “@” TS_AjouterUneColonne = True End Select Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Call TS_SupprimerUneColonne(TS:=Tableau, Colonne:=”Note2″) End Sub

Image non disponible

Le code de la fonction :

Public Function TS_SupprimerUneColonne(TS As Range, ByVal Colonne As Variant) As Boolean On Error GoTo Gest_Err Err.Clear Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description TS.ListObject.ListColumns(Colonne).Delete TS_SupprimerUneColonne = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

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

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Call TS_OptionsStyle(Tableau, False, , True, , , , , “TableStyleMedium6”) End Sub

Image non disponible

Le code de la fonction :

Public Function TS_OptionsStyle(TS As Range, _ Optional Bouton_Filtre As Integer = 1, _ Optional Ligne_Entête As Integer = 1, _ Optional Ligne_Totaux As Integer = 1, _ Optional Ligne_Bandes As Integer = 1, _ Optional Colonne_Bandes As Integer = 1, _ Optional Première_Colonne As Integer = 1, _ Optional Dernière_Colonne As Integer = 1, _ Optional StyleTableau As String = “/”) As Boolean On Error GoTo Gest_Err Err.Clear Select Case Ligne_Entête Case True: TS.ListObject.ShowHeaders = True Case False: TS.ListObject.ShowHeaders = False TS.ListObject.ShowAutoFilterDropDown = False End Select Select Case Ligne_Totaux Case True: TS.ListObject.ShowTotals = True Case False: TS.ListObject.ShowTotals = False End Select Select Case Ligne_Bandes Case True: TS.ListObject.ShowTableStyleRowStripes = True Case False: TS.ListObject.ShowTableStyleRowStripes = False End Select Select Case Première_Colonne Case True: TS.ListObject.ShowTableStyleFirstColumn = True Case False: TS.ListObject.ShowTableStyleFirstColumn = False End Select Select Case Dernière_Colonne Case True: TS.ListObject.ShowTableStyleLastColumn = True Case False: TS.ListObject.ShowTableStyleLastColumn = False End Select Select Case Colonne_Bandes Case True: TS.ListObject.ShowTableStyleColumnStripes = True Case False: TS.ListObject.ShowTableStyleColumnStripes = False End Select Select Case Bouton_Filtre Case True: TS.ListObject.ShowHeaders = True TS.ListObject.ShowAutoFilterDropDown = True Case False: TS.ListObject.ShowAutoFilterDropDown = False End Select If StyleTableau <> “/” Then Sheets(TS.Parent.Name).ListObjects(TS.ListObject.DisplayName).TableStyle = StyleTableau End If Gest_Err: If Err.Number = 0 Then TS_OptionsStyle = True TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

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.

Le code de la fonction :

Public Function TS_DéfinirTotaux(TS As Range, _ ByVal Colonne As Variant, _ TypeCalcul As XlTotalsCalculation) As Boolean On Error GoTo Gest_Err Err.Clear Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description TS.ListObject.ShowTotals = True TS.ListObject.ListColumns(Colonne).TotalsCalculation = TypeCalcul TS_DéfinirTotaux = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

II-T. 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;
  • FormatNumérique : le format numérique à appliquer.

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

Sub Exemple() Call TS_FormatColonne(Range(“TS_Eleves”), 0, “dd/mm/yyyy”) End Sub

Image non disponible

Le code de la fonction :

Public Function TS_FormatColonne(TS As Range, _ ByVal Colonne As Variant, _ FormatNumérique As String) As Boolean On Error GoTo Gest_Err Err.Clear Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description TS.ListObject.ListColumns(Colonne).DataBodyRange.NumberFormat = FormatNumérique TS_FormatColonne = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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 :

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

Le code de la fonction :

Public Function TS_TrierUneColonne(TS As Range, _ ByVal Colonne As Variant, _ Optional Méthode As XlSortOn = xlSortOnValues, _ Optional Ordre As XlSortOrder = xlAscending, _ Optional EffacerAncienTri As Boolean = True) As Boolean On Error GoTo Gest_Err Err.Clear Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description Colonne = TS.ListObject.ListColumns(Colonne).Name TS.ListObject.ShowHeaders = True TS.ListObject.ShowAutoFilterDropDown = True If EffacerAncienTri = True Then Call TS_EffacerTri(TS, “”) Else Call TS_EffacerTri(TS, Colonne) End If TS.ListObject.Sort.SortFields.Add Range(TS.ListObject.DisplayName & “[” & Colonne & “]”), Méthode, Ordre TS.ListObject.Sort.Apply TS_TrierUneColonne = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description If TS_TrierUneColonne = False And (Colonne < 0 Or Colonne > TS.ListObject.ListColumns.Count) Then _ TS_Err_Description = “La colonne [” & Colonne & “] n’est pas incluse dans le tableau.” Err.Clear End Function

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 :

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

Le code de la fonction :

Public Function TS_EffacerTri(TS As Range, ByVal Colonne As Variant) As Boolean Dim i As Integer, x As Integer On Error GoTo Gest_Err Err.Clear If Colonne = “” Then TS.ListObject.Sort.SortFields.Clear Else Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description Colonne = TS.ListObject.ListColumns(Colonne).Name For i = 1 To TS.ListObject.Sort.SortFields.Count x = TS.ListObject.Sort.SortFields(i).Key.Column – TS.Column + 1 If UCase(TS.ListObject.ListColumns(x).Name) = UCase(Colonne) Then TS.ListObject.Sort.SortFields(i).Delete TS.ListObject.Sort.Apply Exit For End If Next i End If TS_EffacerTri = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

III-C. 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.
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 » :

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 :

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.

Le code de la fonction :

Public Function TS_Filtres_Poser(TS As Range, _ ByVal Colonne As Variant, _ ByVal Critère1 As Variant, _ Optional Opérateur As XlAutoFilterOperator, _ Optional Critère2 As String = “”) As Boolean On Error GoTo Gest_Err Err.Clear Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description If IsArray(Critère1) = True Then TS.ListObject.Range.AutoFilter Field:=Colonne, Criteria1:=Array(Critère1), Operator:=xlFilterValues Else If Critère1 = “” Then Critère1 = Null If Critère2 = “” Then TS.ListObject.Range.AutoFilter Field:=Colonne, Criteria1:=Critère1 Else TS.ListObject.Range.AutoFilter Field:=Colonne, Criteria1:=Critère1, Operator:=Opérateur, Criteria2:=Critère2 End If End If TS_Filtres_Poser = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Call TS_Filtres_Effacer(Tableau) End Sub

Image non disponible

Le code de la fonction :

Public Function TS_Filtres_Effacer(TS As Range, Optional Colonne As Variant) As Boolean On Error GoTo Gest_Err Err.Clear If IsMissing(Colonne) = True Then Colonne = “” If Colonne = “” Then TS.ListObject.AutoFilter.ShowAllData TS_Filtres_Effacer = True Else If TS_Filtres_Poser(TS, Colonne, “”) = False Then Err.Raise TS_Err_Number, , TS_Err_Description Else TS_Filtres_Effacer = True End If End If Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Dim MesFiltres As Variant Call TS_Filtres_Mémoriser(Tableau, MesFiltres) End Sub

Le code de la fonction :

Public Function TS_Filtres_Mémoriser(TS As Range, Mémoire As Variant) As Boolean Dim f As Integer On Error GoTo Gest_Err Err.Clear With TS.ListObject.AutoFilter.Filters ReDim Mémoire(1 To .Count, 1 To 3) For f = 1 To .Count With .Item(f) If .On Then TS_Filtres_Mémoriser = True Mémoire(f, 1) = .Criteria1 If .Operator Then Mémoire(f, 2) = .Operator Mémoire(f, 3) = .Criteria2 End If End If End With Next End With Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function  

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

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

Le code de la fonction :

Public Function TS_Filtres_Restaurer(TS As Range, Mémoire As Variant) As Boolean Dim f As Integer On Error GoTo Gest_Err Err.Clear For f = 1 To UBound(Mémoire, 1) If Not IsEmpty(Mémoire(f, 1)) Then If Mémoire(f, 2) Then TS.ListObject.Range.AutoFilter Field:=f, _ Criteria1:=Mémoire(f, 1), _ Operator:=Mémoire(f, 2), _ Criteria2:=Mémoire(f, 3) Else TS.ListObject.Range.AutoFilter Field:=f, Criteria1:=Mémoire(f, 1) End If End If Next Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description If Err.Number = 0 Then TS_Filtres_Restaurer = True Err.Clear End Function

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

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 :

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves_1”) Call TS_CouleurLigneChangeValeur(TS:=Tableau) End Sub

Le code de la fonction :

Public Function TS_CouleurLigneChangeValeur(TS As Range, Optional ByVal Colonne As Variant = -1, _ Optional ByVal CoulA As Long = xlNone, _ Optional ByVal CoulB As Long = xlNone) As Boolean Dim y As Long Dim AncValeur As Variant Dim Coul As Long Dim Anc_ScreenUpdating As Boolean Dim Anc_Cursor As Long On Error GoTo Gest_err Err.Clear Anc_Cursor = Application.Cursor Application.Cursor = xlDefault Anc_ScreenUpdating = Application.ScreenUpdating Application.ScreenUpdating = False If Colonne = -1 Then TS.ListObject.DataBodyRange.Interior.Color = xlNone GoTo Gest_err End If Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description If TS_Nombre_Lignes(TS) < 2 Then GoTo Gest_err For y = 1 To TS_Nombre_Lignes(TS) If TS.ListObject.DataBodyRange(y, 1).Height > 0 Then If TS.ListObject.DataBodyRange(y, Colonne).Value <> AncValeur Then If Coul = CoulA Then Coul = CoulB Else Coul = CoulA End If TS.ListObject.DataBodyRange.Rows(y).Interior.Color = Coul AncValeur = TS.ListObject.DataBodyRange(y, Colonne).Value End If Next y TS_CouleurLigneChangeValeur = True Gest_err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear Application.Cursor = Anc_Cursor Application.ScreenUpdating = Anc_ScreenUpdating End Function

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

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 :

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)

Le code de la fonction :

Public Function TS_Rechercher(TS As Range, _ RespecterCasse As Boolean, _ ParamArray ListeColonnesValeurs() As Variant) As Long Dim i As Integer, Ligne As Long, ik As Integer, Trouvé As Boolean Dim Arguments() As Variant, Colonne() As Variant, Valeur() As Variant On Error GoTo Gest_Err Err.Clear If UBound(ListeColonnesValeurs) = 0 Then ReDim Arguments(LBound(ListeColonnesValeurs(0)) To UBound(ListeColonnesValeurs(0))) For ik = LBound(Arguments) To UBound(Arguments) Arguments(ik) = ListeColonnesValeurs(0)(ik) Next ik End If If UBound(ListeColonnesValeurs) > 0 Then ReDim Arguments(LBound(ListeColonnesValeurs) To UBound(ListeColonnesValeurs)) For ik = LBound(Arguments) To UBound(Arguments) Arguments(ik) = ListeColonnesValeurs(ik) Next ik End If ik = LBound(Arguments) For i = LBound(Arguments) To UBound(Arguments) Step 2 ReDim Preserve Colonne(LBound(Arguments) To ik) Colonne(ik) = TS_IndexColonne(TS, Arguments(i)) If Colonne(ik) = -1 Then Err.Raise vbObjectError, , TS_Err_Description ik = ik + 1 Next i ik = LBound(Arguments) For i = LBound(Arguments) To UBound(Arguments) Step 2 ReDim Preserve Valeur(LBound(Arguments) To ik) Valeur(ik) = Arguments(i + 1) If IsNumeric(Valeur(ik)) = True Then Valeur(ik) = CDec(Valeur(ik)) ik = ik + 1 Next i For Ligne = 1 To TS.ListObject.ListRows.Count Trouvé = True Select Case RespecterCasse Case True For i = LBound(Colonne) To UBound(Colonne) If TS.ListObject.DataBodyRange(Ligne, Colonne(i)).Value <> Valeur(i) Then Trouvé = False Exit For End If Next i Case False For i = LBound(Colonne) To UBound(Colonne) If UCase(TS.ListObject.DataBodyRange(Ligne, Colonne(i)).Value) <> UCase(Valeur(i)) Then Trouvé = False Exit For End If Next i End Select If Trouvé = True Then TS_Rechercher = Ligne Exit For End If Next Ligne Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

V-B. 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 nom 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.

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 :

Dim C As Range Call TS_Sélectionner(Tableau, “Nom”, “”) For Each C In Selection Debug.Print C Next C Call TS_Sélectionner(Tableau, “Note”, “”) Selection.NumberFormat = “#0”

Le code de la fonction :

Public Function TS_Sélectionner(TS As Range, _ Optional ByVal Colonne As Variant = “”, _ Optional ByVal Ligne As Variant) As Boolean On Error GoTo Gest_Err Err.Clear If Colonne = “” And IsMissing(Ligne) = True Then TS.ListObject.DataBodyRange.Select TS_Sélectionner = True GoTo Gest_Err End If If IsMissing(Ligne) = True Then Ligne = “” If Ligne = “” Then Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description TS.ListObject.ListColumns(Colonne).DataBodyRange.Select TS_Sélectionner = True GoTo Gest_Err End If If Ligne = 0 Then Ligne = TS.ListObject.ListRows.Count If Colonne = “” Then If Ligne > 0 And Ligne <= TS.ListObject.ListRows.Count Then TS.ListObject.ListRows(Ligne).Range.Select TS_Sélectionner = True End If If Ligne < 0 Then TS.ListObject.ShowTotals = True TS.ListObject.TotalsRowRange.Select TS_Sélectionner = True End If GoTo Gest_Err End If Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description If Ligne < 0 Then TS.ListObject.ShowTotals = True TS.ListObject.Range(TS.ListObject.ListRows.Count + 2, Colonne).Select TS_Sélectionner = True Else Ligne = TS_IndexLigne(TS, CLng(Ligne)) If Ligne = -1 Then Err.Raise vbObjectError, , TS_Err_Description TS.ListObject.Range(Ligne + 1, Colonne).Select TS_Sélectionner = True End If Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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

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.

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 :

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

Le code de la fonction :

Public Function TS_ModifCellule(TS As Range, _ ByVal Colonne As Variant, _ ByVal Ligne As Long, _ Valeur As Variant, _ Optional TypeInfo As Enum_InfoTS = TS_Valeur, _ Optional LargeurCommentaire As Long = 0, _ Optional HauteurCommentaire As Long = 0) As Boolean On Error GoTo Gest_Err Err.Clear If TS.ListObject.ListRows.Count = 0 And (Ligne = 0 Or Ligne = 1) Then Call TS_AjouterUneLigne(TS, 0) End If Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description If Ligne < 0 Then Ligne = TS.ListObject.ListRows.Count + 1 TS.ListObject.ShowTotals = True Else Ligne = TS_IndexLigne(TS, Ligne) If Ligne = -1 Then Err.Raise vbObjectError, , TS_Err_Description End If With TS.ListObject.DataBodyRange(Ligne, Colonne) Select Case TypeInfo Case TS_Valeur .Value = Valeur Case TS_Ajouter .Value = .Value + Valeur Case TS_Soustraire .Value = .Value - Valeur Case TS_Multiplier .Value = .Value * Valeur Case TS_Diviser .Value = .Value / Valeur Case TS_Formule .Formula = Valeur Case TS_CouleurTexte .Font.Color = Valeur Case TS_CouleurFond If IsNumeric(Valeur) = True Then .Interior.Color = Valeur If Valeur = "" Then .Interior.Pattern = xlNone Case TS_Gras If Valeur = True Then .Font.Bold = True If Valeur = False Then .Font.Bold = False Case TS_Italique If Valeur = True Then .Font.Italic = True If Valeur = False Then .Font.Italic = False Case TS_Format .NumberFormat = Valeur Case TS_Commentaire .ClearComments If Valeur <> “” Then .AddComment .Comment.Text Text:=Valeur If HauteurCommentaire > 0 Then .Comment.Shape.Height = HauteurCommentaire If LargeurCommentaire > 0 Then .Comment.Shape.Width = LargeurCommentaire End If Case TS_ImageCommentaireJPG .ClearComments If Valeur <> “” Then .AddComment .Comment.Shape.Fill.UserPicture Valeur If HauteurCommentaire > 0 Then .Comment.Shape.Height = HauteurCommentaire If LargeurCommentaire > 0 Then .Comment.Shape.Width = LargeurCommentaire End If Case TS_LienHypertexte .Hyperlinks.Delete If Valeur <> “” Then .Hyperlinks.Add Anchor:=TS.ListObject.DataBodyRange(Ligne, Colonne), Address:=Valeur End If End Select End With Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description If Err.Number = 0 Then TS_ModifCellule = True Err.Clear End Function

V-D. 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 mettre la valeur “Ok” dans la colonne “Admis” pour les élèves dont la note est suppérieure ou égale à 10 :

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves_1”) Call TS_Filtres_Poser(Tableau, “Note”, “>=10″) Call TS_ForcerValeurColonne(TS:=Tableau, Colonne:=”Admis”, Valeur:=”OK”, VisibleUniquement:=True) Call TS_Filtres_Effacer(Tableau) End Sub

Image non disponible

Le code de la fonction :

Public Function TS_ForcerValeurColonne(TS As Range, ByVal Colonne As Variant, Valeur As Variant, VisibleUniquement As Boolean) As Boolean Dim y As Long Dim Anc_ScreenUpdating As Boolean On Error GoTo Gest_err Err.Clear Anc_ScreenUpdating = Application.ScreenUpdating Application.ScreenUpdating = False Colonne = TS_IndexColonne(TS, Colonne) If Colonne = -1 Then Err.Raise vbObjectError, , TS_Err_Description For y = 1 To TS_Nombre_Lignes(TS) If TS.ListObject.DataBodyRange(y, Colonne).Height > 0 _ Or VisibleUniquement = False Then TS.ListObject.DataBodyRange(y, Colonne).Formula = Valeur End If Next y TS_ForcerValeurColonne = True Gest_err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear Application.ScreenUpdating = Anc_ScreenUpdating End Function

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 :

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Dim TD As Range Set TD = Range(“Données”) 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

Le code de la fonction :

Public Function TS_ImporterDonnées(TS As Range, _ TD As Range, _ Méthode As Enum_ImportationTS, _ RespecterCasse As Boolean, _ ParamArray ListeColonnesClés() As Variant) As Boolean Dim x As Integer, i As Integer, y As Long, yy As Long, NomColonne As String Dim Ajout As Boolean Dim Arguments() As Variant Dim NbClé As Integer Dim MesFiltres As Variant On Error GoTo Gest_Err Err.Clear Call TS_Filtres_Mémoriser(TS, MesFiltres) Call TS_Filtres_Effacer(TS) If IsMissing(ListeColonnesClés) = False Then NbClé = (UBound(ListeColonnesClés) + 1) * 2 ReDim Arguments(1 To NbClé) x = 1 For i = 0 To UBound(ListeColonnesClés) Arguments(x) = ListeColonnesClés(i) If IsNumeric(Arguments(x)) = True Then _ Arguments(x) = TS.ListObject.ListColumns(Arguments(x)).Name x = x + 2 Next i End If For y = 1 To TD.ListObject.ListRows.Count Ajout = False yy = 0 If TD.ListObject.DataBodyRange(y, 1).Height = 0 Then GoTo Suite_Y If NbClé > 0 And Méthode <> TS_Ajout_Forcé Then For i = 2 To NbClé Step 2 Arguments(i) = TS_InfoCellule(TD, Arguments(i – 1), y, TS_Valeur) If TS_Err_Number <> 0 Then Err.Raise TS_Err_Number, , TS_Err_Description Next i yy = TS_Rechercher(TS, RespecterCasse, Arguments) If yy <> 0 Then Ajout = True If TS_Err_Number <> 0 Then Err.Raise TS_Err_Number, , TS_Err_Description If Méthode = TS_MAJ_Uniquement And yy = 0 Then GoTo Suite_Y If Méthode = TS_IgnorerSiExiste And yy <> 0 Then GoTo Suite_Y End If For x = 1 To TD.ListObject.ListColumns.Count NomColonne = TD.ListObject.HeaderRowRange(x).Value If Ajout = False Then Ajout = True TS.ListObject.ListRows.Add yy = 0 End If Call TS_ModifCellule(TS, NomColonne, yy, TD.ListObject.DataBodyRange(y, x).Value, TS_Valeur) Next x Suite_Y: Next y Call TS_Filtres_Restaurer(TS, MesFiltres) Gest_Err: If Err.Number = 0 Then TS_ImporterDonnées = True TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

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 :

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Dim TS_Destination As Range On Error Resume Next Set TS_Destination = Range(“Tableau_Noms”) Call TS_SupprimerLeTableau(TS_Destination) On Error GoTo Gest_Err Err.Clear Set TS_Destination = TS_CréerUnTableau(Plage:=ThisWorkbook.Sheets(“Feuil2”).Range(“A1″), _ Titres:=”Nom”, _ Nom:=”Tableau_Noms”, _ Style:=”*”) If TS_ImporterDonnées(TS_Destination, Tableau, TS_MAJ_Ou_Ajout, True, “Nom”) = False Then Err.Raise TS_Err_Number, , TS_Err_Description End If If TS_TrierUneColonne(TS:=TS_Destination, _ Colonne:=”Nom”, _ Méthode:=xlSortOnValues, _ Ordre:=xlAscending, _ EffacerAncienTri:=True) = False Then Err.Raise TS_Err_Number, , TS_Err_Description End If 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 :

Sub Exemple() If TS_ExporterEnFichier(Range(“TS_Eleves”), TS_PDF, “P:FichierTableau_Eleves.pdf”, True) = False Then MsgBox TS_Err_Number & ” : ” & TS_Err_Description, vbCritical, “Erreur de conversion en PDF” End If

Image non disponible

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

Le code de la fonction :

Public Function TS_ExporterEnFichier(TS As Range, _ FormatFichier As Enum_ExportationTS, _ ByVal FichierDest As String, _ OuvrirFichier As Boolean) As Boolean Dim Repertoire As String, Sh As Worksheet Dim Filtre As String Dim Tmp As String, FileNumber As Long, ol As Range, oC As Range Dim Anc_ScreenUpdating As Boolean Dim Anc_Visible As Integer Dim ObjPicture As Object Dim Rng As Range On Error GoTo Gest_Err Err.Clear Set Sh = ActiveSheet Anc_ScreenUpdating = Application.ScreenUpdating Application.ScreenUpdating = True Anc_Visible = Sheets(TS.Parent.Name).Visible Sheets(TS.Parent.Name).Visible = True Sheets(TS.Parent.Name).Activate Set Rng = TS.ListObject.Range If FichierDest = “” Then Err.Raise vbObjectError, , “Le fichier destination doit être renseigné.” Repertoire = Left(FichierDest, InStrRev(FichierDest, “”)) If CreateObject(“Scripting.FileSystemObject”).Folderexists(Repertoire) = False Then _ Err.Raise vbObjectError, , “Le dossier de destination ” & Repertoire & ” n’est pas présent.” Application.Cursor = xlWait Select Case FormatFichier Case TS_CSV If UCase(Right(FichierDest, 4)) <> “.CSV” Then FichierDest = FichierDest & “.csv” If Dir(FichierDest) <> “” Then Kill FichierDest TS.Cells.EntireColumn.AutoFit FileNumber = FreeFile Open FichierDest For Output As #FileNumber For Each ol In Rng.Rows Tmp = “” If ol.Height <> 0 Then For Each oC In ol.Cells Tmp = Tmp & CStr(oC.Text) & “;” Next Tmp = Left(Tmp, Len(Tmp) – 1) Print #FileNumber, Tmp End If Next Close #FileNumber Case TS_XLSX If UCase(Right(FichierDest, 5)) <> “.XLSX” Then FichierDest = FichierDest & “.xlsx” If Dir(FichierDest) <> “” Then Kill FichierDest TS.ListObject.Range.Copy Dim AppXl As Excel.Application Set AppXl = CreateObject(“excel.application”) AppXl.Visible = False AppXl.Workbooks.Add AppXl.ActiveSheet.Paste Call TS_ConvertirPlageEnTS(AppXl.ActiveSheet.Range(“A1”), TS.ListObject.DisplayName, TS.Parent.ListObjects(TS.ListObject.Name).TableStyle, xlYes) AppXl.ActiveSheet.Range(“A1”).Select AppXl.ActiveWorkbook.SaveAs FichierDest AppXl.ActiveWorkbook.Close Set AppXl = Nothing Case TS_PDF If UCase(Right(FichierDest, 4)) <> “.PDF” Then FichierDest = FichierDest & “.pdf” If Dir(FichierDest) <> “” Then Kill FichierDest Rng.ExportAsFixedFormat xlTypePDF, FichierDest Case TS_BMP, TS_JPG If FormatFichier = TS_BMP Then If UCase(Right(FichierDest, 4)) <> “.BMP” Then FichierDest = FichierDest & “.bmp” Filtre = “BMP” End If If FormatFichier = TS_JPG Then If UCase(Right(FichierDest, 4)) <> “.JPG” Then FichierDest = FichierDest & “.jpg” Filtre = “JPG” End If If Dir(FichierDest) <> “” Then Kill FichierDest Rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture Set ObjPicture = Rng.Parent.ChartObjects.Add(10, 10, 10, 10) DoEvents With ObjPicture .ShapeRange.Line.Visible = msoFalse DoEvents .Height = Rng.Height .Width = Rng.Width DoEvents .Chart.Paste DoEvents .Chart.Export Filename:=FichierDest, Filtername:=Filtre .Delete End With Case Else Err.Raise vbObjectError, , “Le type d’exportation demandé n’est pas géré.” End Select TS_ExporterEnFichier = True Gest_Err: Sh.Activate Application.ScreenUpdating = Anc_ScreenUpdating Sheets(TS.Parent.Name).Visible = Anc_Visible Application.Cursor = xlDefault TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear If TS_ExporterEnFichier = True And OuvrirFichier = True _ Then Call Shell(“Explorer.exe ” & FichierDest, vbMaximizedFocus) End Function

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) ;
  • Valeur : la valeur qu’il faut utiliser ;
  • 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). Laisser 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 :

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 :

Call TS_EnregistrerDansAccess(Range(“TS_Eleves_1”), “P:Test.accdb”, “Table_1”, “”, Array(“Nom”, “Nom”, “Prénom”, “Prénom”), False)

par :

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

Call TS_EnregistrerDansAccess(Range(“TS_Eleves”), “P:Test.accdb”, “Table_1”, “”, Array(“Nom”, “Nom”, “Prénom”, “Prénom”, “>Qui”, Application.UserName), False)

Le code de la fonction :

Public Function TS_EnregistrerDansAccess(TS As Range, _ BaseAccess As String, TableAccess As String, MotDePasse As String, _ ListeChampsColonnes As Variant, _ VisibleUniquement As Boolean) As Boolean Dim y As Long Dim i As Integer Dim x As Integer Dim Anc_Cursor As Long Dim Anc_StatusBar As Variant On Error GoTo Gest_err Err.Clear If IsArray(ListeChampsColonnes) = False Then i = TS_Nombre_Colonnes(TS) * 2 ReDim ListeChampsColonnes(0 To i – 1) For i = 0 To UBound(ListeChampsColonnes) Step 2 x = x + 1 ListeChampsColonnes(i) = TS.ListObject.HeaderRowRange(x).Value ListeChampsColonnes(i + 1) = TS.ListObject.HeaderRowRange(x).Value Next i End If Anc_Cursor = Application.Cursor Application.Cursor = xlDefault If TS_Cnn_Initialise(BaseAccess, MotDePasse) = False Then Err.Raise vbObjectError, “”, “Connexion à la base Access [” & BaseAccess & “] impossible. Veuillez contacter votre administrateur.” End If If Cnn_TS.State = 0 Then Cnn_TS.Open If Cnn_TS.State >= 2 Then While (Cnn_TS.State = 2): DoEvents: Wend End If Dim MonRs As Variant Set MonRs = CreateObject(“ADODB.Recordset”) MonRs.Open “SELECT * FROM [” & TableAccess & “]”, Cnn_TS, 1, 2, 1 For y = 1 To TS_Nombre_Lignes(TS) Application.StatusBar = “Traitement ligne ” & y & ” sur ” & TS_Nombre_Lignes(TS) DoEvents If (VisibleUniquement = True And TS.ListObject.DataBodyRange(y, 1).Height > 0) Or VisibleUniquement = False Then MonRs.AddNew For i = LBound(ListeChampsColonnes) To UBound(ListeChampsColonnes) Step 2 If Left(ListeChampsColonnes(i), 1) <> “>” Then MonRs.Fields(ListeChampsColonnes(i)).Value = TS_InfoCellule(TS, ListeChampsColonnes(i + 1), y, TS_valeur) Else MonRs.Fields(Mid(ListeChampsColonnes(i), 2)).Value = ListeChampsColonnes(i + 1) End If Next i MonRs.Update End If Next y MonRs.Close Cnn_TS.Close TS_EnregistrerDansAccess = True Gest_err: If MonRs Is Nothing = False Then Set MonRs = Nothing If Err.Number <> 0 Then MsgBox Err.Number & ” : ” & Err.Description, vbCritical + vbOKOnly, “Erreur de traitement” End If Err.Clear Set Cnn_TS = Nothing Application.StatusBar = Anc_StatusBar Application.Cursor = Anc_Cursor End Function Private Function TS_Cnn_Initialise(StrBaseSource As String, MotDePasse As String) As Boolean Err.Clear On Error GoTo Gest_Err If Dir(StrBaseSource) = “” Then Err.Raise vbObjectError, , “La base “”” & StrBaseSource & “”” n’a pas été trouvée.” End If Set Cnn_TS = CreateObject(“ADODB.Connection”) Cnn_TS.CommandTimeout = 30 Cnn_TS.CursorLocation = 2 Application.StatusBar = “Demande de connexion…”: DoEvents If MotDePasse = “” Then Cnn_TS.Open “Provider= ” & Provider_ACCDB & “;” _ & “Data Source=” & StrBaseSource & “;” _ , “Admin”, “”, 16 Else Cnn_TS.Open “Provider= ” & Provider_ACCDB & “;” _ & “Data Source=” & StrBaseSource & “;” _ & “Jet OLEDB:Database Password=” & MotDePasse & “;” End If While (Cnn_TS.State = 2): DoEvents: Wend On Error Resume Next If Cnn_TS.State = 1 Then TS_Cnn_Initialise = True End If Gest_Err: If Cnn_TS Is Nothing = False Then If Cnn_TS.State = 1 Then Cnn_TS.Close End If Application.StatusBar = “” Err.Clear End Function

VI-E. 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 ;
  • SQLWhere : la requête d’instruction en language 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 (a utiliser avant d’y copier de nouveaux enregistrements en remplacement des anciens, voir le chapitre précédent) :

Call TS_RequeteBaseAccess(“P:Eleves.accdb”), “”, “DELETE * FROM [T_Notes]”)

Remarque : si besoin vous trouverez en Annexe 1 plus d’informations sur le language SQL.

Le code de la fonction :

Public Function TS_RequeteBaseAccess(BaseAccess As String, MotDePasse As String, _ SQLWhere As String) As Boolean Dim Anc_Screen As Boolean Dim Anc_Cursor As Long Dim Anc_StatusBar As Variant On Error GoTo Gest_Err Err.Clear Anc_Cursor = Application.Cursor Application.Cursor = xlDefault Anc_StatusBar = Application.StatusBar Application.StatusBar = Left(“Connexion à la base Access [” & BaseAccess & “]…”, 99) Anc_Screen = Application.ScreenUpdating Application.ScreenUpdating = False If TS_Cnn_Initialise(BaseAccess, MotDePasse) = False Then Err.Raise vbObjectError, “”, “Connexion à la base Access [” & BaseAccess & “] impossible. Veuillez contacter votre administrateur.” End If If Cnn_TS.State = 0 Then Cnn_TS.Open If Cnn_TS.State >= 2 Then While (Cnn_TS.State = 2): DoEvents: Wend End If Application.StatusBar = Left(“Requête: ” & SQLWhere, 99) Cnn_TS.Execute SQLWhere Cnn_TS.Close TS_RequeteBaseAccess = True Gest_Err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear Set Cnn_TS = Nothing Application.StatusBar = Anc_StatusBar Application.Cursor = Anc_Cursor Application.ScreenUpdating = Anc_Screen End Function

VI-F. 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 premiere 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 :

Sub Exemple() Dim Tableau As Range Dim Destination As Range Set Tableau = Range(“TS_Eleves_1”) Set Destination = Range(“TS_Eleves_2”) Call TS_Filtres_Effacer(Tableau) Call TS_Filtres_Poser(Tableau, “Admis”, “Ok”) Call TS_EffacerToutesLignes(Destination) Call TS_CopierUneColonne(Tableau, “Nom”, Destination, “Nom”, 1, xlCellTypeVisible) Call TS_CopierUneColonne(Tableau, “Prénom”, Destination, “Prénom”, 1, xlCellTypeVisible) Call TS_Filtres_Poser(Destination, “Nom”, “>”””) Call TS_Sélectionner(Destination, 1, 1) End Sub

Image non disponible

Le code de la fonction :

Public Function TS_CopierUneColonne(TS_Source As Range, ByVal Colonne_Source As Variant, _ TS_Dest As Range, ByVal Colonne_Dest As Variant, ByVal Ligne_Dest As Long, _ Optional Méthode As XlCellType = xlCellTypeVisible) As Boolean On Error GoTo Gest_err Err.Clear Dim Anc_Visible As Long Dim Anc_Feuille As String Dim Anc_Screen As Boolean Anc_Visible = Sheets(TS_Dest.Parent.Name).Visible Anc_Feuille = ActiveSheet.Name Anc_Screen = Application.ScreenUpdating Colonne_Source = TS_IndexColonne(TS_Source, Colonne_Source) If Colonne_Source = -1 Then Err.Raise vbObjectError, , TS_Err_Description Colonne_Dest = TS_IndexColonne(TS_Dest, Colonne_Dest) If Colonne_Dest = -1 Then Err.Raise vbObjectError, , TS_Err_Description Ligne_Dest = TS_IndexLigne(TS_Dest, Ligne_Dest) If Ligne_Dest = -1 Then Err.Raise vbObjectError, , TS_Err_Description TS_Source.ListObject.ListColumns(Colonne_Source).DataBodyRange.SpecialCells(Méthode).Copy Application.ScreenUpdating = False Sheets(TS_Dest.Parent.Name).Visible = True Sheets(TS_Dest.Parent.Name).Activate Call TS_Sélectionner(TS_Dest, Colonne_Dest, Ligne_Dest) Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Gest_err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear Sheets(TS_Dest.Parent.Name).Visible = Anc_Visible Sheets(Anc_Feuille).Activate Application.ScreenUpdating = Anc_Screen End Function

VI-G. 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.

Ses arguments sont :

  • TS_Source : la plage (de type Range) du tableau structuré source ;
  • 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):

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

Le code de la fonction :

Public Function TS_CopierUnTableau(TS_Source As Variant, TS_Dest As Range, _ Méthode As Enum_CopierDonnées, _ Optional ValeursOuFormules As Enum_ValeursOuFormules = TS_Valeurs) As Long Dim Copie As Variant Dim NbOrigine As Long On Error GoTo Gest_err Err.Clear TS_CopierUnTableau = -1 If TypeName(TS_Source) = “Range” Then Copie = TS_MémoriseTableau(TS_Source, ValeursOuFormules) Else Copie = TS_Source End If ReDim Preserve Copie(1 To UBound(Copie), 1 To TS_Nombre_Colonnes(TS_Dest)) Select Case Méthode Case TS_RemplacerDonnées Call TS_EffacerToutesLignes(TS_Dest) NbOrigine = 0 TS_Dest.ListObject.Resize Range(Cells(TS_Dest.Row – 1, TS_Dest.Column), Cells(TS_Dest.Row + UBound(Copie) – 1, TS_Dest.Column + TS_Dest.ListObject.ListColumns.Count – 1)) Select Case ValeursOuFormules Case TS_Valeurs: TS_Dest.ListObject.DataBodyRange.Value = Copie Case TS_Formules: TS_Dest.ListObject.DataBodyRange.Formula = Copie End Select Case TS_AjouterDonnées NbOrigine = TS_Nombre_Lignes(TS_Dest) TS_Dest.Cells(TS_Dest.Rows.Count + 1, 1).Resize(UBound(Copie), TS_Dest.Columns.Count) = Copie End Select TS_CopierUnTableau = TS_Nombre_Lignes(TS_Dest) – NbOrigine Gest_err: TS_Err_Number = Err.Number TS_Err_Description = Err.Description Err.Clear End Function

VI-H. 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 :

Sub Exemple() Dim Tableau As Range Dim Données As Variant Set Tableau = Range(“TS_Eleves_1”) Données = TS_MémoriseTableau(Tableau, TS_Valeurs) End Sub

Image non disponible

Le code de la fonction :

Public Function TS_MémoriseTableau(TS As Variant, Optional ValeursOuFormules As Enum_ValeursOuFormules = TS_Valeurs) As Variant Dim Données As Variant On Error GoTo Gest_Err Err.Clear Select Case ValeursOuFormules Case TS_Valeurs: Données = TS.ListObject.DataBodyRange.Value Case TS_Formules: Données = TS.ListObject.DataBodyRange.Formula End Select Dim x As Long, y As Long For y = 1 To UBound(Données) For x = 1 To UBound(Données, 2) If IsError(Données(y, x)) = False Then If Données(y, x) <> “” Then If IsDate(Données(y, x)) = True Then Données(y, x) = CDec(Format(Données(y, x), “0.000000000000”)) End If End If Next x Next y TS_MémoriseTableau = Données Gest_Err: If Err.Number <> 0 Then TS_Err_Number = Err.Number TS_Err_Description = Err.Description End If Err.Clear End Function

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

Pour rechercher, exporter ou 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 : le tableau structuré doit faire moins de 256 colonnes sur 65536 lignes, les ingénieurs de Microsoft ont dû oublier qu’Excel depuis quelques années est monté en puissance.

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

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

X-B. TS_RequeteSQL

La fonction TS_RequeteSQL exécute une requête SQL sur un tableau structuré et renvoie un jeu d’enregistrements.

Ses arguments sont :

  • TS : la plage (de type Range) qui représente le tableau structuré ;
  • StrChamps : le nom des champs à sélectionner (ceux qui alimenteront le jeu d’enregistrements) ;
  • StrSQL : (facultatif) la requête SQL des critères de sélection.

La fonction renvoie : un jeu d’enregistrements, ou Nothing si la requête ne sélectionne rien.

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 » sur la feuille 2 :

Sub Exemple() Dim Tableau As Range Set Tableau = Range(“TS_Eleves”) Dim TS_Destination As Range Dim Enr As Variant On Error Resume Next Set TS_Destination = Range(“Tableau_Noms”) Call TS_SupprimerLeTableau(TS_Destination) On Error GoTo Gest_Err Err.Clear Set TS_Destination = TS_CréerUnTableau(Plage:=ThisWorkbook.Sheets(“Feuil2”).Range(“A1″), _ Titres:=”Nom”, _ Nom:=”Tableau_Noms”, _ Style:=”*”) Set Enr = TS_RequeteSQL(Tableau, “[Nom]”, “GROUP BY [Nom] ORDER BY [Nom]”) If Not Enr Is Nothing Then Range(TS_Destination.ListObject).CopyFromRecordset Enr End If Gest_Err: If Err.Number <> 0 Then MsgBox Err.Number & ” : ” & Err.Description, vbCritical, Err.Source End Sub

Remarques :

  • l’instruction SQL “GROUP BY [Nom] ORDER BY [Nom]” regroupe les noms sans doublon et les trie par ordre croissant ;
  • l’affichage dans le tableau structuré « TS_Destination » du jeu d’enregistrements « Enr » se fait avec l’instruction Range(TS_Destination.ListObject).CopyFromRecordset Enr.

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

… Set TS_Destination = TS_CréerUnTableau(Plage:=ThisWorkbook.Sheets(“Feuil2”).Range(“A1”), _ Titres:=Array(“Nom”, “Prénom”, “Note”), _ Nom:=”Tableau_3_Meilleurs”, _ Style:=”*”) Set Enr = TS_RequeteSQL(Tableau, “TOP 3 [Nom],[Prénom],[Note]”, “WHERE [Note] > 10 ORDER BY [Note] DESC”) If Not Enr Is Nothing Then Range(TS_Destination.ListObject).CopyFromRecordset Enr End If …

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

Image non disponible

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

… Set TS_Destination = TS_CréerUnTableau(Plage:=ThisWorkbook.Sheets(“Feuil4”).Range(“A1”), _ Titres:=Array(“Note”, “Quantité”), _ Nom:=”Tableau_X”, _ Style:=”*”) Set Enr = TS_RequeteSQL(Tableau, “[Note], Count([Note])”, “GROUP BY [Note] ORDER BY [Note] DESC”) If Not Enr Is Nothing Then Range(TS_Destination.ListObject).CopyFromRecordset Enr End If 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 :

… Set Enr = TS_RequeteSQL(Tableau, “[Note]”, “WHERE [Note] < 10") If Not Enr Is Nothing Then While Enr.EOF = False Enr.Fields(0).Value = Enr.Fields(0).Value + 1 Enr.Update Enr.MoveNext 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.

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 ( ;
  • le mot clé Like permet de sélectionner des chaînes de caractères en disposant des jokers
    WHERE Nom Like renvoie les noms commençants par « T ».

Le code de la fonction :

Public Function TS_RequeteSQL(TS As Range, StrChamps As String, _ Optional ByVal StrSQL As String = “”) As Variant On Error GoTo Gest_Err Err.Clear If TS.ListObject.ListColumns.Count > 255 _ Or TS.ListObject.ListRows.Count > 65535 Then _ Err.Raise vbObjectError, , “Taille du Tableau Structuré trop grande pour une requête SQL.” 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 StrSQL = “SELECT ” & IIf(StrChamps > “”, StrChamps, “*”) & ” FROM [” & TS.Parent.Name & “$” _ & TS.CurrentRegion.Address(False, False, xlA1) & “] ” & StrSQL Set TS_RequeteSQL = TS_ExecuterSQL(TS, StrSQL) TS.ListObject.ShowHeaders = Anc_Entete TS.ListObject.ShowTotals = Anc_Totaux Application.ScreenUpdating = Anc_ScreenUpdating Gest_Err: If Err.Number <> 0 Then TS_Err_Number = Err.Number TS_Err_Description = Err.Description Set TS_RequeteSQL = Nothing End If Err.Clear End Function

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.

Private Function TS_ExecuterSQL(TS As Range, StrSQL As String) As Variant Dim Cnn As Variant Dim Rs As Variant On Error GoTo Gest_Err Err.Clear Set TS_ExecuterSQL = Nothing Set Cnn = CreateObject(“ADODB.Connection”) Cnn.Open “Provider=Microsoft.ACE.OLEDB.12.0;” & _ “Data Source=” & TS.Worksheet.Parent.FullName & “;” & _ “Extended Properties=””Excel 12.0;HDR=Yes””;” Set Rs = CreateObject(“ADODB.Recordset”) Rs.Open StrSQL, Cnn, 1, 2, 1 If Rs.EOF = False Then Rs.MoveFirst Set TS_ExecuterSQL = Rs End If Gest_Err: Set Rs = Nothing Set Cnn = Nothing TS_Err_Number = Err.Number TS_Err_Description = Err.Description If Err.Number <> 0 Then Set TS_ExecuterSQL = Nothing Err.Clear End Function

Clicca qui per collegarti al sito e articolo dell’autore

“https://laurent-ott.developpez.com/tutoriels/Tableaux-Structures/”

Pubblichiamo solo i migliori articoli della rete. Clicca qui per visitare il sito di provenienza. SITE: the best of the best ⭐⭐⭐⭐⭐

Clicca qui per collegarti al sito e articolo dell’autore

La rete Adessonews è un aggregatore di news e replica gli articoli senza fini di lucro ma con finalità di critica, discussione od insegnamento, come previsto dall’art. 70 legge sul diritto d’autore e art. 41 della costituzione Italiana. Al termine di ciascun articolo è indicata la provenienza dell’articolo. Per richiedere la rimozione dell’articolo clicca qui

#adessonews

#adessonews

#adessonews

 

Richiedi info
1
Richiedi informazioni
Ciao Posso aiutarti?
Per qualsiasi informazione:
Inserisci il tuo nominativo e una descrizione sintetica dell'agevolazione o finanziamento richiesto.
Riceverai in tempi celeri una risposta.