Excel VBA: Permutation de colonnes dans un tableau structuré

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 :


 Poster un commentaire

Avatar de patricktoulon patricktoulon - Membre extrêmement actif https://www.developpez.com
le 05/09/2018 à 11:27
bonjour pierre
avec EVALUATE
test ca

Code : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
Sub teste()
    Dim tabl, myarray
    'les colonnes dans le desordre paar leur noms
    myarray = Array("N°client", "Nom", "Prénom", "Ville", "telephone", "address1", "address2", "Cde_postale", "Email")
    'ou
    'les colonnes dans le desordre par leur indexs
    myarray = Array(1, 2, 3, 7, 8, 4, 5, 6, 9)
    tabl = Permute_columns(Range("t_Contacts[#all]"), myarray)    ''par les indexs de colonnes
    ' retranscrire le tableaux en lieu et place de l'original ou ou on veut
    Cells(25, 1).Resize(UBound(tabl), UBound(tabl, 2)) = tabl
End Sub
Code : Sélectionner tout
1
2
3
4
5
6
7
8
Function Permute_columns(ByVal rang As Range, ByRef colonnes)
    Dim tabl, i&
    If Not IsNumeric(colonnes(0)) Then For i = 0 To UBound(colonnes): colonnes(i) = rang.ListObject.ListColumns(colonnes(i)).Index: Next
With rang
    tabl = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), colonnes)    'mettre les colonnes que l'on veut dans l'ordre voulues
    Permute_columns = tabl
End With
End Function
tu met l'ordre que tu veux dans l'array que ce soit avec les noms ou les indexs
pour inverser juste 2 colonnes tu peux prendre juste le range des 2 colonnes ,inverser et les remettres en lieu et place de l'original
Avatar de Pierre Fauconnier Pierre Fauconnier - Responsable Office & Excel https://www.developpez.com
le 05/09/2018 à 11:41
Salut Patrick,

Et je suis censé faire quoi avec ton code?

  1. il fait une copie du tableau de base;
  2. si on "copie sur place", il écrase le tableau et le remplace par une plage classique;
  3. il supprime les formules;
  4. il impose plusieurs modifications dans la proc appelante;
  5. il transforme les dates en texte;
  6. il supprime les formats et les mises en forme conditionnelles;
  7. il fait plusieurs choses à la fois (permuter ET déplacer), ce qui est une aberration en programmation;
  8. il casse les liens avec d'autres cellules du classeur faisant référence aux cellules du tableau;
  9. il ne renvoie pas un tableau structuré mais une plage classique.


Bref, il casse toute la structure du tableau de base...
Contacter le responsable de la rubrique Accueil