Suite à une demande en MP et faisant également écho à des demandes sur le forum, je propose ici une fonction qui permet de permuter les colonnes d'un tableau structuré Excel. La demande en MP s'étendait à une copie avec permutation, mais dans les faits, cela revient à copier tout le tableau puis à le permuter sur place.
Je ne soulignerai jamais assez que les tableaux structurés facilitent grandement la manipulation des données au sein d'un classeur, mais également les modifications structurelles des plages de travail… (exemple ici avec avec la modification/mise à jour de données dans un tableau structuré en VBA).
Il faut noter que les fonctions sont génériques et ne dépendent pas des options de compilation telles que Option Base qui définit le premier indice d'un tableau et Option Compare qui définit la façon de comparer les chaines de caractères. C'est pourquoi j'ai utilisé strComp pour comparer les chaînes et pas l'opérateur d'égalité qui dépend, lui, de Option Compare.
La procédure en elle-même est très simple et s'appuie sur la méthode Excel, à savoir couper la colonne que l'on veut déplacer puis la coller à un endroit précis par insertion avec déplacement vers la droite. Au départ, c'est juste une simple boucle sur un tableau des noms de colonnes à réorganiser. La fonction est agrémentée d'un test d'existence des colonnes renseignées, et permet de garder ou de supprimer les colonnes présentes dans le tableau structuré qui ne seraient pas renseignées dans la liste des colonnes à trier. La valeur renvoyée par la fonction permet de connaître le résultat de l'opération et, en cas de non-exécution, le code d'erreur rencontrée.
Cette procédure a comme avantages de conserver les formules, les formats et les mises en forme conditionnelles. De plus, dans la mesure où elle n'effectue que des couper-coller, elle permet de conserver les liaisons avec d'autres cellules du classeur qui pointeraient vers le tableau structuré.
Elle s'appuie sur une fonction qui teste l'existence d'une colonne dans un tableau structuré Excel, dans laquelle j'ai évité le Exit For (Je n'aime pas les Exit).
| Code VB : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | ''' 'Author Pierre Fauconnier 'Date 31/07/2018 'Comment Checks if a column exists in a structured table (listobject) 'Returns Boolean True if column exists 'Param Table ListObject structured table to test 'Param ColumnName String Name of column to check ''' Function ColumnExists(Table As ListObject, ByVal ColumnName As String) As Boolean Dim Found As Boolean Dim Index As Long Index = 1 Do While Index <= Table.ListColumns.Count And Not Found If StrComp(Table.ListColumns(Index).Name, ColumnName, vbTextCompare) = 0 Then Found = True Index = Index + 1 Loop ColumnExists = Found End Function |
Afin de boucler sur toutes les colonnes pour en vérifier l'existence avant la permutation des colonnes, une fonction recevant un array des colonnes à permuter est utilisée. Afin d'éviter de boucler sur toutes les colonnes, on sort de la boucle dès qu'une colonne de l'array n'a pas été trouvée dans le tableau
| Code VB : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | ''' 'Author Pierre Fauconnier 'Date 31/07/2018 'Comment Cheks if all columns exist in the table 'Returns True if all columns exist, else 0 'Param Table ListObject to check 'Param Columns Array based 0 with columnnames to check ''' Function ColumnsExist(Table As ListObject, Columns) As Boolean Dim Ok As Boolean Dim Index As Long Ok = True Do While Index <= UBound(Columns) And Ok Ok = ColumnExists(Table, Columns(Index)) Index = Index + 1 Loop ColumnsExist = Ok End Function |
La fonction de permutation proprement dite teste d'abord l'existence des colonnes, puis permute les colonnes en les repoussant à droite du tableau, et enfin, en fonction de l'argument précisant que l'on garde ou pas les colonnes du tableau non renseignées, elle supprime les colonnes au delà de la dernière trouvée dans l'array ou elle les repousse à droite du tableau. J'utilise ici une fonction car je peux alors renvoyer une valeur en fonction du résultat de l'exécution (-1 si tout est ok, 0 si une mauvaise colonne a été renseignée et le numéro d'erreur rencontrée si erreur).
| Code VB : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | ''' 'Author Pierre Fauconnier 'Date 31/07/2018 'Comment Permute columns in structured table (listobject) 'Returns Long -1 if ok, 0 if wrong columnname, Error number if problem 'Param Table ListObject structured table to permute 'Param Columns Array based 0 contening the names of colums to reorder 'Param KeepAllColumns Boolean Allows to keep of delete columns not in parameter Columns. True by default ''' Function PermuteTableColumns(Table As ListObject, ByVal Columns, Optional KeepAllColumns As Boolean = True) As Long Dim lc As ListColumn Dim Counter As Long Dim Position As Long On Error GoTo EndHandler If ColumnsExist(Table, Columns) Then For Counter = 0 To UBound(Columns) If Table.ListColumns(Columns(Counter)).Index < Table.ListColumns.Count Then Table.ListColumns(Columns(Counter)).Range.Cut Table.ListColumns(Table.ListColumns.Count).Range.Offset(0, 1).Insert Shift:=xlToRight End If Next If Not KeepAllColumns Then Do While Table.ListColumns.Count > UBound(Columns) + 1 Table.ListColumns(1).Delete Loop Else For Counter = 1 To Table.ListColumns.Count - (UBound(Columns) + 1) Table.ListColumns(1).Range.Cut Table.ListColumns(Table.ListColumns.Count).Range.Offset(0, 1).Insert Shift:=xlToRight Next End If PermuteTableColumns = -1 Else PermuteTableColumns = 0 End If EndHandler: Application.CutCopyMode = False If Err <> 0 Then PermuteTableColumns = Err.Number End Function |
On remarquera ici que je n'ai pas désactivé le screenupdating car ce n'est pas la responsabilité de la fonction de permutation. Il appartient au code qui l'appelle de gérer cela, tel que je l'illustre dans le code suivant, qui va permuter les trois premières colonnes d'un tableau de quatre et supprimer la colonne superflue.
| Code VB : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | Sub MoveColumnsAndDeleteColumns() Dim Result As Long Dim ScreenRefresh As Boolean ScreenRefresh = Application.ScreenUpdating Application.ScreenUpdating = False Result = PermuteTableColumns(shToDelete.ListObjects(1), Array("ID", "Dernier Login", "Prénom"), False) Select Case Result Case -1 MsgBox "La permutation a été réalisée" Case 0 MsgBox "Une colonne renseignée n'existe pas dans la table" Case Else MsgBox "L'erreur " & Result & " a été rencontrée" End Select Application.ScreenUpdating = ScreenRefresh End Sub |
Comme vous le voyez, ce n'est pas très compliqué et le code tient en quelques lignes. Ce code est générique et peut s'appliquer à n'importe quel tableau structuré. Vous pouvez prendre le module Table du fichier joint et le considérer comme un outil, à enrichir avec vos propres codes de manipulation de tableaux structurés.
PS: J'ai adapté le fichier pour mieux isoler les tests et le code de permutation proprement dit, et j'ai corrigé le code de permutation pour qu'il tienne compte de tous les cas rencontrés.
Dans le troisième tutoriel de la trilogie sur les tableaux structurés, je vous offrirai un TableManager plus complet à considérer comme un TableTools, un "framework" de gestion des tableaux structurés et des données qui s'y trouvent.
Bon code
