Couleur de certains mots dans les cellules, fonction VBA

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Mots clés choisis en couleur

Avec ce nouveau chapitre sur les astuces VBA Excel, nous proposons de développer une fonction pour le moins originale. Sa vocation est de faire ressortir automatiquement des mots clés en couleur dans des phrases écrites dans des cellules.

Classeur Excel à télécharger
Pour appuyer ce nouveau développement, nous suggérons de baser l'étude sur une feuille Excel abritant des phrases ainsi qu'une liste de mots clés à considérer.

• Télécharger le classeur mots-en-couleur-dans-cellules.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• En bas du menu contextuel, choisir la commande Propriétés,
• En bas de la boîte de dialogue qui suit, cocher la case Débloquer et valider par Ok,
• Puis, double cliquer sur le fichier pour l'ouvrir dans Excel,

Nous découvrons une feuille constituée de deux colonnes. Celle de gauche héberge quelques phrases dans lesquelles, les mots importants sont à faire ressortir automatiquement. Celle de droite abrite les mots clés à débusquer dans chacune de ces phrases pour les passer en couleur.

La fonction que nous devons construire ne doit rien inscrire là où elle est écrite. Elle doit agir sur les cellules sur lesquelles elle est appliquée. Ici, nous l'appliquerons en colonne D pour agir en colonne B.

Bien sûr et idéalement, cette fonction devrait être enregistrée dans le personal.xlsb (modèle Excel) pour un usage déployé dans tous les classeurs. Ici et pour la formation, nous choisissons de la coder en local.

Créer la fonction
La fonction à créer doit être signée avec deux paramètres. Le premier doit concerner la cellule de la première phrase à analyser. Elles seront ainsi toutes passées en revue par le jeu de la réplication. Le second doit impliquer la plage des mots clés à mettre en valeur dans ces phrases. Nous le comprendrons, elle devra être figée lors de la construction, pour permettre la réplication sur les cellules du dessous.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Module1,

Ainsi, nous affichons sa feuille de code au centre de l'écran.

• Dans cette feuille de code, créer la fonction motsCouleur comme suit :

Function motsCouleur(texte As Range,liste As Range) As String

End Function

Son premier paramètre attend la cellule de la phrase dans laquelle il est question de déceler la présence des mots clés à mettre en valeur. Son second argument attend la liste des mots clés à rechercher dans ces phrases.

Les variables
Trois variables sont ensuite nécessaires pour les traitements à entreprendre.

• Dans les bornes de la fonction, ajouter les déclarations suivantes :

...
Dim longueur As Byte: Dim position As Byte
Dim mot As Variant
...

Nous typons la première variable comme un entier court (As Byte). Elle doit détecter la longueur du mot clé en cours d'analyse dans la chaîne. La deuxième est aussi typée comme un entier court. Elle doit trouver la position du mot clé dans la chaîne pour influer sur sa couleur. La dernière est typée comme un Variant. Rien ne dit en effet que les occurrences à parcourir sont des textes. Nous utiliserons cette variable pour passer en revue toutes les valeurs de la plage des mots clés.

Parcourir chaque mot clé
Pour rechercher chaque mot de la liste dans chaque phrase, dont la première est passée en premier paramètre de la fonction, nous devons les parcourir tous. Pour cela et nous en avons l'habitude, l'arme fatale est une boucle For Each.

• Après les variables, créer la boucle For Each suivante :

...
For Each mot In liste.Value

Next mot
...

Nous enclenchons cette variable mot dans une boucle destinée à parcourir tous les mots clés. C'est à ce moment-là qu'elle prend son type en fonction de la nature de l'occurrence à analyser.

Chercher chaque mot
Grâce à cette boucle, nous allons pouvoir chercher chaque mot clé dans la phrase passée en premier paramètre. C'est la fonction InStr qui répond par une position quand le terme est trouvé.

• Dans la boucle, ajouter les instructions VBA suivantes :

...
For Each mot In liste.Value
position = InStr(texte, mot)
If position > 0 Then
longueur = Len(mot)
Exit For
End If
Next mot
...

Nous stockons la position de la recherche du mot dans la phrase (texte), dans la variable position. Si la fonction InStr répond par une valeur positive (If position > 0 Then), nous savons que le mot clé en cours d'analyse par la boucle a été trouvé. Nous prélevons sa longueur grâce à la fonction VBA Len. Grâce à cette information, nous pourrons ensuite le formater sur l'ensemble de ses caractères dans la phrase. Puis, nous mettons fin à la boucle (Exit For) puisque l'un des mots clés a été trouvé. En effet, dans cette solution, il n'est pas possible de faire ressortir plusieurs mots clés par phrase.

Formater en couleur le mot trouvé
Nous pouvons maintenant exploiter les renseignements récoltés par les variables position et longueur pour isoler le mot trouvé dans la chaîne.

• Après la boucle, créer l'instruction conditionnelle suivante :

...
If position > 0 Then
texte.Characters(position, longueur).Font.ThemeColor = xlThemeColorAccent6
End If
...

Nous agissons directement sur la cellule passée en paramètre (texte). Nous exploitons sa propriété Characters. Elle représente la collection des caractères que renferme son texte. Nous isolons le mot clé en partant de la position trouvée et sur la longueur du mot pour influer sur sa couleur. Le thème vient du fait que l'on choisit cette couleur dans les propositions par défaut de l'outil Couleur de police.

Pour terminer la construction de cette fonction très spéciale par rapport à celles que nous avons bâties jusqu'alors, nous devons simuler le fait qu'elle retourne un résultat. Ce n'est pas le cas bien sûr, puisque c'est contre nature qu'elle n'agisse pas sur la plage où elle est exercée.

• A la fin du code, ajouter la dernière ligne VBA suivante :

...
motsCouleur = ""
...

Avec une chaîne vide en retour, rien n'apparaîtra dans la cellule de la formule. En revanche, la fonction influera bien sur les cellules qui lui sont passées en premier paramètre. Pour en avoir le coeur net, il suffit de tester cette fonction.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Cliquer sur la première cellule de la colonne voisine au premier tableau, soit : D6,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la nouvelle fonction par son nom suivi d'une parenthèse, soit : motsCouleur(,
• Désigner la première chaîne à traiter en cliquant sur sa cellule B6,
• Taper un point-virgule (;) pour passer dans l'argument de la liste des mots clés,
• Désigner cette liste en sélectionnant la plage de cellules G6:G10,
• Puis, la figer en enfonçant la touche F4 du clavier, ce qui donne : $G$6:$G$10,

En effet, nous allons répliquer la logique sur les lignes du dessous et la liste de ces mots clés ne doit pas bouger.

• Fermer la parenthèse de la fonction motsCouleur,
• Puis, valider la formule par le raccourci CTRL + Entrée,

Ainsi, nous gardons active la cellule du résultat. Comme vous pouvez l'apprécier, le mot clé Paris ressort automatiquement en vert dans cette première phrase. Il fait effectivement parti de la liste. Notre fonction l'a repéré et l'a formaté. Et ça aussi vous l'avez remarqué, rien ne s'inscrit dans la case où la formule est construite, comme nous l'avions annoncé.



Maintenant, si vous double cliquez sur la poignée du résultat pour répandre la logique sur la hauteur du tableau, vous constatez que ce sont tous les mots clés décelés dans chacune des phrases qui ressortent automatiquement en couleur. Cette fonction peut donc s'avérer très intéressante lorsqu'il s'agit d'automatiser les opérations de mise en forme dans des tableaux volumineux.