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.
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).
''''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 AsString)AsBooleanDim Found AsBooleanDim Index AsLong
Index = 1DoWhile Index <= Table.ListColumns.Count AndNot Found
If StrComp(Table.ListColumns(Index).Name, ColumnName, vbTextCompare) = 0Then Found = True
Index = Index + 1Loop
ColumnExists = Found
EndFunction
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
''''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)AsBooleanDim Ok AsBooleanDim Index AsLong
Ok = TrueDoWhile Index <= UBound(Columns)And Ok
Ok = ColumnExists(Table, Columns(Index))
Index = Index + 1Loop
ColumnsExist = Ok
EndFunction
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).
''''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 AsBoolean = True)AsLongDim lc As ListColumn
Dim Counter AsLongDim Position AsLongOnErrorGoTo EndHandler
If ColumnsExist(Table, Columns)ThenFor Counter = 0To 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
EndIfNextIfNot KeepAllColumns ThenDoWhile Table.ListColumns.Count > UBound(Columns) + 1
Table.ListColumns(1).Delete
LoopElseFor Counter = 1To Table.ListColumns.Count - (UBound(Columns) + 1)
Table.ListColumns(1).Range.Cut
Table.ListColumns(Table.ListColumns.Count).Range.Offset(0, 1).Insert Shift:=xlToRight
NextEndIf
PermuteTableColumns = -1Else
PermuteTableColumns = 0EndIf
EndHandler:
Application.CutCopyMode = FalseIf Err <> 0Then PermuteTableColumns = Err.Number
EndFunction
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.
Sub MoveColumnsAndDeleteColumns()Dim Result AsLongDim ScreenRefresh AsBoolean
ScreenRefresh = Application.ScreenUpdating
Application.ScreenUpdating = False
Result = PermuteTableColumns(shToDelete.ListObjects(1), Array("ID", "Dernier Login", "Prénom"), False)SelectCase Result
Case-1
MsgBox "La permutation a été réalisée"Case0
MsgBox "Une colonne renseignée n'existe pas dans la table"CaseElse
MsgBox "L'erreur " & Result & " a été rencontrée"EndSelect
Application.ScreenUpdating = ScreenRefresh
EndSub
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.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité,
merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.