Discussion :

[Philippe Tulliez]

La petite histoire
En début de ce mois alors que je dispensais une formation VBA à quelques personnes d’une organisation internationale, j’ai été confronté à une question intéressante de l’une des participantes.
La semaine précédente, je leur avais montré tout l’intérêt de quitter l’usage des listes de données classiques pour se tourner vers les tableaux structurés.
Cette participante ayant suivi à la lettre mes conseils m’a donc interpellé ce jour-là pour me dire qu’il y avait un souci car les étiquettes de colonnes des tableaux qu’ils utilisaient précédemment s’adaptaient automatiquement à la langue de l’utilisateur grâce à une formule qui affichait les titres suivant le choix de la langue alors qu'avec les tableaux structurés il y avait un problème.
En effet, il est impossible d’avoir des formules dans la ligne des titres d’un tableau structuré.
J’ai promis une solution pour le lendemain et le soir qui a suivi cette formation j’ai programmé une petite procédure qui permet de visualiser les titres dans la langue sélectionnée par l’utilisateur et c'est cette procédure que je vous propose.

Quelle astuce ai-je utilisé ?
L’astuce consiste à utiliser le format personnalisé d’excel ce qui permet de conserver l’intégrité totale de la valeur présente dans la cellule
Exemple :
En A1, tapons lblName et ensuite allons dans le format de cellule personnalisé et tapons ;;; "Nom" après confirmation, nous pouvons visualiser Nom mais dans la cellule, nous avons bien lblName
L’astuce ayant été trouvée, il suffisait de l’adapter en VBA

Que faut-il faire pour traduire cela en VBA ?

• construire une table de Mapping
• une liste déroulante avec la liste des langues utilisées
• une procédure qui modifiera la propriété NumberFormat de l’objet Range
• une procédure événementielle qui invoquera la procédure

Les outils
Pour obtenir cela.



La ligne de titre du tableau structuré


La table de Mapping
Cette table nommée T_mapLanguages est présente dans la feuille dont le CodeName est shtParameter.
Pour ajouter une nouvelle langue, il suffit d'ajouter une colonne en entrant en première ligne le code de la langue et ensuite d'y insérer la traduction correspondante.
Attention : Pour que cette nouvelle langue soit prise en compte par la liste déroulante il y a lieu d'insérer cette colonne avant la dernière (EN)



La procédure TranslationLabel

Code :

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
Function TranslationLabel(TableToTranslate As ListObject, _
                          LanguageUsed As String, _
                          LookupTable As ListObject) As Boolean
  ' Author  : Philippe Tulliez http://magicoffice.be
 
  ' Arguments
  '   TableToTranslate  Table où se trouve les titres à traduire
  '   LanguageUsed      Langue utilisée
  '   LookupTable       Table de mappage (Clé + mots traduits par colonne de langue)
  '
  ' Déclaration
  Const Mask As String = ";;;""[NewLabel]"""
  Dim Cell As Range
  Dim CelLanguage As Range
  ' Translation
  With LookupTable
    For Each Cell In TableToTranslate.HeaderRowRange 'LookupTable.ListColumns(1)
      Set CelLanguage = .ListColumns(1).Range.Find(What:=Cell.Value, LookAt:=xlWhole)
      If Not CelLanguage Is Nothing Then
         Cell.NumberFormat = Replace(Mask, "[NewLabel]", CelLanguage.Cells(1, .ListColumns(LanguageUsed).Index))
      End If
    Next
  End With
  Set Cell = Nothing: Set CelLanguage = Nothing
End Function

La procédure événementielle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Address(external:=True) = Range("pLanguage").Address(external:=True) Then
     mMultilingualManager.TranslationAll
  End If
End Sub

La procédure TranslationAll
Cette procédure est appelée par la procédure événementielle Workbook_SheetChange.
Elle parcoure toutes les feuilles du classeur (ThisWorkbook) et invoque la fonction TranslationLabel pour chaque tableaux structurés présents dans une feuille de ce classeur à l'exception de ceux qui seraient présents dans la feuille dont la propriété CodeName est shtParameter

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub TranslationAll()
  ' Procédure parcourant l'ensemble des ListObject du classeur pour y effectuer la traduction
  Dim oSht As Worksheet
  Dim oLst As ListObject
  For Each oSht In ThisWorkbook.Worksheets
    If oSht.ListObjects.Count And StrComp(oSht.CodeName, "shtParameter") Then
       For Each oLst In oSht.ListObjects
         TranslationLabel oLst, Range("pLanguage"), shtParameter.ListObjects("T_mapLanguages")
       Next
    End If
  Next
End Sub

Tutoriels à lire pour en savoir plus

• Création, enregistrement et fusion de formats personnalisés
• Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)

Le fichier exemple à télécharger

[Pierre Fauconnier]

Yeah!! Bravo Philippe.

C'est Noël sur DVP! Quatre contributions en quelques jours qui, en plus, tournent autour des tableaux structurés qui sont incontournables pour une utilisation professionnelle d'Excel et une programmation plus souple, plus sécurisée, pérenne et évolutive en VBA...

Continue!! On en redemande

[Qwazerty]

Salut

Uff, génial ce truc. En plus le nom de la colonne ne change pas... du coup inutile de bricoler dans les macros pour faire la correspondance.

Merci !!
J'aime les forums de partage

(Bon, il va falloir mettre les bouchées doubles!)
++
Qwaz