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
La procédure événementielle
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 TranslationAll
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
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
Tutoriels à lire pour en savoir plus
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
- 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
Partager