Excel VBA: Permutation de colonnes dans un tableau structuré
Un tutoriel de Pierre Fauconnier

Le , par Pierre Fauconnier, ResponsableOffice & Excel
Salut,

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


Vous avez aimé cette actualité ? Alors partagez-la avec vos amis en cliquant sur les boutons ci-dessous :
Responsables bénévoles de la rubrique Excel : Pierre Fauconnier - Arkham46 -