Trouver les mots répétés dans les textes des cellules

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


Sujets que vous pourriez aussi aimer :


Fonction VBA dans une mise en forme conditionnelle

C'est un développement VBA original que nous proposons de réaliser dans ce nouveau chapitre. L'idée est de créer une fonction VBA à appliquer dans une règle de mise en forme conditionnelle, pour repérer en couleur toutes les cellules de produits, appartenant à une catégorie ou à une famille déjà présente dans l'énumération.



Sur l'exemple finalisé illustré par la capture, des codes articles sont énumérés dans un tableau. Comme leurs signatures l'imposent, ils sont tous faits de séquences séparées par des tirets. Lorsque ces séquences se répètent dans d'autres codes, cela signifie que les articles en question appartiennent à la même catégorie ou famille. Une fonction VBA Excel exploitée dans une règle de mise en forme conditionnelle appliquée sur ce tableau, fait surgir en couleur toutes les cellules pour lesquelles un fragment qu'elles portent, se retrouve nécessairement dans au moins une autre cellule du tableau.

Classeur Excel à télécharger
Pour développer cette nouvelle solution, nous suggérons d'appuyer les travaux sur cette feuille Excel abritant ces codes articles.

• Télécharger le classeur Excel doublons-textes-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,
• Dans la boîte de dialogue, cocher la case Débloquer et valider par Ok,
• Puis, double cliquer sur le fichier pour l'ouvrir dans Excel,

Nous retrouvons bien le tableau des codes articles sur l'unique feuille de ce classeur.

La fonction et ses paramètres
Vous le savez, l'analyse d'une mise en forme conditionnelle est chronologique. Elle débute son étude à partir de la première cellule de la plage sur laquelle elle est appliquée, pour rejoindre la dernière. C'est la raison pour laquelle nous devons créer une fonction avec deux paramètres en attente. Le premier doit représenter la première cellule de la plage. Le second doit représenter la plage elle-même. Donc il s'agit de signer deux variables de type Range.

De plus et ça aussi vous le savez, pour que cette fonction soit portable, il faut l'inscrire dans la bibliothèque Excel. Quant à nous et pour les besoins de la formation, nous allons nous contenter dans l'enregistrer en local, dans un module du classeur actif.

• 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,

De cette façon, nous affichons sa feuille de code au centre de l'écran. Elle est encore vierge à ce stade.

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

Function doublonsFrag(cel As Range, plage As Range) As Integer

End Function

Elle est donc signée avec les deux paramètres que nous avons annoncés. De plus, elle est typée comme une fonction renvoyant une valeur entière (As Integer). En effet, elle a pour objectif de dénombrer la présence des fragments de chaque cellule dans les autres de la plage. Dans la règle de mise en forme conditionnelle, c'est cette indication numérique retournée qui permettra de déclencher des attributs de mise en forme de repérage.

Les variables
Des variables sont nécessaires pour ce développement. Il faut compter chaque fragment. Il faut parcourir chaque cellule de la plage. Cette plage doit être représentée par un tableau à analyser cellule à cellule.

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

...
Dim i As Byte: Dim compteur As Integer
Dim cellule As Range
Dim fragment As Variant: Dim tableau As Variant

fragments = Split(cel.Value, "-")
Set tableau = plage
compteur = 0
...

Nous utiliserons la variable i comme variable de boucle pour parcourir tous les fragments de la cellule en cours d'analyse. La variable compteur doit être incrémentée à chaque fois que l'un des fragments est trouvé dans une autre cellule de la plage. C'est elle que nous utiliserons pour retourner le résultat de l'analyse de la fonction. La variable cellule typée naturellement comme un Range doit intervenir dans une boucle For Each pour parcourir toutes les cellules de la plage passée en second paramètre de la fonction et dont la variable tableau doit prendre possession. Enfin, la variable fragments doit être transformée en tableau de variables.

C'est d'ailleurs la première affectation que nous entreprenons, grâce à la fonction Split, pour découper la cellule passée en premier paramètre sur le tiret. C'est ainsi que la variable fragments renferme désormais tous les morceaux à trouver dans les autres cellules. Ensuite, nous initialisons l'objet tableau sur la plage de cellules passée en second paramètre de la fonction. Puis, nous initialisons la variable compteur à zéro.

Parcourir toutes les cellules
Pour chercher chaque fragment dans toutes les cellules de la plage, nous devons les parcourir.

• A la suite du code VBA, créer la boucle For Each suivante :

...
For Each cellule In tableau

Next cellule
...

Nous engageons une boucle For Each sur la variable cellule pour parcourir toutes celles qui sont contenues dans la collection représentée par la variable tableau.

Ignorer la cellule active
Dans cette boucle en revanche, la cellule active ne doit pas être comparée à elle-même car sinon, chaque fragment sera nécessairement trouvé sans qu'il soit doublé pour autant. C'est la raison pour laquelle nous devons engager une instruction conditionnelle afin de tester la cellule en cours d'analyse.

• Dans les bornes de la boucle, créer le test suivant :

...
If (cel.Address <> cellule.Address) Then

End If
...

Si la cellule passée en premier paramètre de la fonction (cel.Address) n'est pas la même que la cellule en cours d'analyse dans la plage (cellule.Address), alors nous poursuivons le traitement. Implicitement et le cas échéant, nous ignorons la cellule en cours. La propriété Address d'un objet de type Range, renvoie les coordonnées de la plage ou de la cellule concernée.

Parcourir chaque fragment
Désormais, pour chaque cellule passée en revue, nous devons vérifier si les fragments de la cellule passée en premier paramètre de la fonction s'y trouvent. Pour chaque cellule et donc à l'intérieur de la boucle For Each, nous devons créer une nouvelle boucle parcourant le tableau de variables nommé fragments.

• Dans l'instruction conditionnelle de la boucle, créer la nouvelle boucle suivante :

...
For i = LBound(fragments) To UBound(fragments)
If InStr(1, cellule.Value, fragments(i), 1) > 0 Then compteur = compteur + 1
Next i
...

Ce sont les fonctions VBA LBound et UBound qui renseignent respectivement sur les bornes inférieures et supérieures d'un tableau de variables qui leur est passé en paramètre. C'est ainsi dans une boucle For Next, que nous parcourons tous les fragments de la cellule, qui avaient été séparés par la fonction Split. Puis et grâce à la fonction InStr (In String), nous cherchons la présence de chaque fragment dans la cellule en cours d'analyse, donc dans chaque cellule, puisque nous sommes dans une boucle parente For Each. Dès lors que la fonction InStr répond par une valeur positive, nous incrémentons la variable compteur. C'est elle qui indiquera si des fragments de la cellule sont répétés dans la plage.

Retourner le décompte
A l'issue du traitement, une fonction doit rendre son verdict. Une fonction VBA répond par son propre nom. Donc, nous devons lui affecter le potentiel cumul réalisé dans la variable compteur.

• A la fin du code, après la boucle For Each, ajouter l'affectation suivante :

...
doublonsFrag = compteur
...

Il ne nous reste plus qu'à tester cette fonction dans une règle de mise en forme conditionnelle à appliquer sur le tableau.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille (ALT + Tab),
• Sélectionner toutes les cellules du tableau, soit la plage de cellules B5:G14,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle,
• Inscrire le nom de la nouvelle fonction suivi d'une parenthèse, soit : doublonsFrag(,
• Désigner la première cellule du tableau en cliquant sur sa cellule B5, ce qui donne : $B$5,
• Enfoncer trois fois la touche F4 du clavier pour supprimer les dollars,

En effet, l'analyse doit se déplacer chronologiquement sur les cellules suivantes.

• Taper un point-virgule (;) pour passer dans l'argument de la plage,
• Désigner tout le tableau, soit la plage de cellules $B$5:$G$14,

Cette fois nous conservons les dollars car l'analyse doit toujours porter dans les bornes de cette plage.

• Fermer la parenthèse de la fonction doublonsFrag,
• En bas de la boîte de dialogue, cliquer sur le bouton Format,

• Dans celle qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un orange assez vif et valider par Ok,
• De retour sur la première boîte, valider de nouveau par ok pour revenir sur la feuille,

Comme vous pouvez l'apprécier, toutes les cellules portant un fragment de code répété dans au moins une autre cellule du tableau, sont illuminées dans une couleur orange, pour bien les distinguer.