Pierre Fauconnier

[Actualité] Excel VBA: Permutation de colonnes dans un tableau structuré

par

, 06/08/2018 à 07h18 (4755 Affichages)

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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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