Pierre Fauconnier

Salut.

Il arrive régulièrement que l'on doive recomposer des chaines de caractères en concaténant plusieurs sous-chaines. C'est le cas, par exemple, lorsque l'on recompose une chaine SQL en Access pour modifier une requête de sélection, ou en Excel lorsque l'on crée le texte d'une formule qui sera utilisée par AVALUATE.

Je vais illustrer la technique en recomposant un lien hypertexte contenant des parties fixes, qui ne changent donc jamais, et des parties mobiles. Cet exemple simple permettra de comprendre la technique mise en place. Dans plusieurs de mes prochains billets, je l'utiliserai dans des cas plus complexes.

Je dois recomposer le lien suivant:
<A href="https://www.google.fr/maps/dir/Place du Perron,+4910+Theux/Place Verte,+4900+Spa">Cliquez ici pour la navigation.</A>dans lequel on comprend bien que les deux adresses sont variables.

Je peux bien sûr utiliser le code suivant:

Code vba :

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

1
2
3
4
5
6
7
8
9
Sub Test1()
  Dim Message As String
  Dim AdresseDepart As String, AdresseArrivee As String
 
  AdresseDepart = "Place du Perron,+4910+Theux"
  AdresseArrivee = "Place Verte,+4900+Spa"""
 
  Message = "<A href=""https://www.google.fr/maps/dir/" & AdresseDepart & "/" & AdresseArrivee & """>Cliquez ici pour la navigation.</A>"
End Sub

On remarque que le doublement des guillemets et l'insertion du / entre les adresses complexifient la saisie du code. L'exemple cité ici est simple, mais vous pourrez imaginer la complexification avec un nombre plus élevé de variables à insérer, surtout si elles sont de différents types (dates, valeurs décimales, ...) (un peu comme dans cette discussion qui aligne un nombre invraisemblable de "mauvaises pratiques". Allez vous y retrouver dans tous ces guillemets... ).

Pour éviter cette recomposition à la volée, j'utilise un système de balises à remplacer par les bonnes valeurs, comme dans l'exemple suivant où les adresses sont balisées dans la chaîne générique, ces balises étant remplacées par les variables correctes après coup. Par convention, les balises que j'utilise sont bornées par les accolades {}.

Code vba :

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

1
2
3
4
5
6
7
8
9
10
11
Sub Test2()
  Dim Message As String
  Dim AdresseDepart As String, AdresseArrivee As String
 
  AdresseDepart = "Place du Perron,+4910+Theux"
  AdresseArrivee = "Place Verte,+4900+Spa"""
 
  Message = "<A href=""https://www.google.fr/maps/dir/{depart}/{arrivee}"">Cliquez ici pour la navigation.</A>"
  Message = Replace(Message, "{depart}", AdresseDepart, , , vbTextCompare)
  Message = Replace(Message, "{arrivee}", AdresseArrivee, , , vbTextCompare)
End Sub

Bien entendu, il y aura autant de Replace qu'il y a de valeurs à remplacer. Je déconseille l'écriture suivante qui, si elle tient plus ou moins la route pour deux valeurs, devient vite ingérable. Je rappelle que l'idée est de simplifier le code, pas d'en complexifier la saisie.

Code vba :

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

1
2
3
4
5
6
7
8
9
10
Sub Test3()
  Dim Message As String
  Dim AdresseDepart As String, AdresseArrivee As String
 
  AdresseDepart = "Place du Perron,+4910+Theux"
  AdresseArrivee = "Place Verte,+4900+Spa"""
 
  Message = "<A href=""https://www.google.fr/maps/dir/{depart}/{arrivee}"">Cliquez ici pour la navigation.</A>"
  Message = Replace(Replace(Message, "{depart}", AdresseDepart, , , vbTextCompare), "{arrivee}", AdresseArrivee, , , vbTextCompare)
End Sub

J'ai donc opté pour la création d'une fonction générique qui va traiter cela en une fois grâce à un tableau de paires Clé/Valeur qui sera utilisé au sein d'une boucle. Cette fonction, générique et utilisable en VBA (non liée à Excel, donc), sera bien sûr placée dans mon module Tools.

Code vba :

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

1
2
3
4
5
6
7
8
Public Function ReplaceStrings(Source As String, Parameters) As String
  Dim I As Long
 
  ReplaceStrings = Source
  For I = LBound(Parameters) To UBound(Parameters) Step 2
    ReplaceStrings = Replace(ReplaceStrings, Parameters(I), Parameters(I + 1), 1, -1, vbTextCompare)
  Next I
End Function

Son utilisation est très simple. Il suffit de lui passer la chaine contenant les balises {...} et le tableau des paires Clé/Valeur à remplacer.

Code vba :

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

1
2
3
4
5
6
7
8
9
10
Sub Test4()
  Dim Message As String
  Dim AdresseDepart As String, AdresseArrivee As String
 
  AdresseDepart = "Place du Perron,+4910+Theux"
  AdresseArrivee = "Place Verte,+4900+Spa"""
 
  Message = "<A href=""https://www.google.fr/maps/dir/{depart}/{arrivee}"">Cliquez ici pour la navigation.</A>"
  Message = ReplaceStrings(Message, Array("{depart}", AdresseDepart, "{arrivee}", AdresseArrivee))
End Sub

Parmi les avantages de cette technique, je citerai:

• la lisibilité du code;
• la possibilité de remplacer des textes grâce à une ligne de code dans le code "métier";
• la possibilité d'utiliser un tableau de paires clé/valeur sur un texte générique, ce tableau pouvant être des cellules Excel, un fichier CSV, un extract XML;
• l'utilisation de l'argument Compare = vbCompareText qui ne lie pas la fonction aux options de compilation éventuellement présentes au début du module.

Dans certains de mes prochains billets qui illustreront des snippets (procédures ou fonctions génériques à réutiliser dans le code), cette fonction ReplaceStrings aura une place de choix...

Bon travail avec VBA