Hier, j'ai reçu un e-mail d'un de mes lecteurs - June.
Elle voulait savoir comment appliquer le format de police gras à une partie spécifique d'une chaîne dans une cellule. Par exemple, appliquez le format gras uniquement au mot « Hello » de « Hello World ».
Et elle voulait faire ça pour des centaines de cellules à la fois.
Puisqu'il n'y a aucune fonctionnalité intégrée dans Excel qui peut faire cela, j'ai créé une macro simple qui utilise le Fonction Excel VBA InStr (vous verrez comment faire cela dans l'exemple 4 de ce tutoriel).
Mais d'abord, voyons comment fonctionne la fonction Excel VBA InStr !
Fonction Excel VBA InStr
Dans ce tutoriel, je vais expliquer l'utilisation de la fonction InStr dans Excel VBA et voir quelques exemples pratiques où elle peut être utilisée.
Fonction Excel VBA InStr - Introduction
La fonction InStr trouve la position d'une sous-chaîne spécifiée dans la chaîne et renvoie la première position de son occurrence.
Par exemple, si vous souhaitez trouver la position de « x » dans « Excel », l'utilisation de la fonction Excel VBA InStr renverrait 2.
Syntaxe de la fonction InStr
InStr( [Démarrer], Chaîne1, Chaîne2, [Comparer] )
- [Début] - (argument facultatif) il s'agit d'une valeur entière qui indique à la fonction InStr la position de départ à partir de laquelle elle doit commencer à chercher. Par exemple, si je veux que la recherche commence depuis le début, j'entrerai la valeur 1. Si je veux qu'elle commence par le troisième caractère, j'utiliserai 3. En cas d'omission, la valeur par défaut de 1 est prise.
- Chaîne1 - Il s'agit de la chaîne principale (ou de la chaîne parent) dans laquelle vous souhaitez rechercher. Par exemple, si vous recherchez la position de x dans Excel, la chaîne 1 serait « Excel ».
- Chaîne2 - C'est la sous-chaîne que vous recherchez. Par exemple, si vous recherchez la position de x dans Excel, String2 serait x.
- [Comparer] - (argument facultatif) Vous pouvez spécifier l'une des trois valeurs suivantes pour l'argument [comparer] :
- vbBinaryComparer - Cela ferait une comparaison caractère par caractère. Par exemple, si vous recherchez "x" dans "Excel", il renverra 2, mais si vous recherchez "X" dans "Excel", il renverra 0 car X est en majuscule. Vous pouvez également utiliser 0 au lieu de vbBinaryCompare. Si l'argument [Comparer] est omis, il est pris par défaut.
- vbTextComparer - Cela ferait une comparaison textuelle. Par exemple, si vous recherchez « x » ou « X » dans Excel, il renverra 2 dans les deux cas. Cet argument ignore la casse. Vous pouvez également utiliser 1 au lieu de vbTextCompare.
- vbBase de donnéesComparer - Ceci est utilisé uniquement pour Microsoft Access. Il utilise les informations de la base de données pour effectuer la comparaison. Vous pouvez également utiliser 2 au lieu de vbDatabaseCompare.
Notes supplémentaires sur la fonction Excel VBA InStr :
- InStr est une fonction VBA et non une fonction de feuille de calcul. Cela signifie que vous ne pouvez pas l'utiliser dans la feuille de calcul.
- Si String2 (qui est la sous-chaîne dont vous recherchez la position) est vide, la fonction renvoie la valeur de l'argument [Start].
- Si la fonction InStr ne peut pas trouver la sous-chaîne dans la chaîne principale, elle retournera 0.
Voyons maintenant un exemple d'utilisation de la fonction Excel VBA InStr
Exemple 1 - Trouver la position depuis le début
Dans cet exemple, j'utiliserai la fonction InStr pour trouver la position de « V » dans « Excel VBA » depuis le début.
Le code pour cela serait :
Sub FindFromBeginning() Dim Position As Integer Position = InStr(1, "Excel VBA", "V", vbBinaryCompare) MsgBox Position End Sub
Lorsque vous exécutez ce code, il affichera une boîte de message avec la valeur 7, qui est la position de « V » dans la chaîne « Excel VBA ».
Exemple 2 - Trouver la position à partir du début du deuxième mot
Supposons que je veuille trouver la position de "le" dans la phrase - "Le renard brun rapide saute par-dessus le chien paresseux"
Cependant, je veux que la recherche commence par le deuxième mot.
Dans ce cas, nous devons modifier l'argument [Start] pour nous assurer qu'il spécifie la position à partir de laquelle le deuxième mot commence.
Voici le code qui fera cela :
Sub FindFromSecondWord() Dim Position As Integer Position = InStr(4, "Le renard brun rapide saute par-dessus le chien paresseux", "le", vbBinaryCompare) MsgBox Position End Sub
Ce code affichera la boîte de message avec la valeur 32 car nous avons spécifié la position de départ comme 4. Par conséquent, il ignore le premier « Le » et trouve le deuxième « le » dans la phrase.
Si vous souhaitez le rendre plus dynamique, vous pouvez améliorer le code afin qu'il ignore automatiquement le premier mot.
Voici le code amélioré qui fera cela :
Sub FindFromSecondWord() Dim StartingPosition As Integer Dim Position As Integer StartingPosition = InStr(1, "Le renard brun rapide saute par-dessus le chien paresseux", " ", vbBinaryCompare) Position = InStr(StartingPosition, "Le renard brun rapide saute par-dessus le paresseux chien", "le", vbBinaryCompare) MsgBox Position End Sub
Ce code trouve d'abord la position d'un caractère espace et le stocke dans la variable StartingPosition.
Il utilise ensuite cette variable comme position de départ pour rechercher le mot « le ».
Par conséquent, il renvoie 32 (qui est la position de départ de « le » après le premier mot).
Exemple 3 - Recherche de la position de @ dans l'adresse e-mail
Vous pouvez facilement créer une fonction personnalisée pour trouver la position de @ dans une adresse e-mail à l'aide de la fonction Excel VBA InStr.
Voici le code pour créer la fonction personnalisée :
Fonction FindPosition(Ref As Range) As Integer Dim Position As Integer Position = InStr(1, Ref, "@") FindPosition = Position End Function
Vous pouvez maintenant utiliser cette fonction personnalisée comme n'importe quelle autre fonction de feuille de calcul. Il prendra une référence de cellule en entrée et vous donnera la position de @ dedans.
De même, vous pouvez créer une fonction personnalisée pour trouver la position de n'importe quelle sous-chaîne dans la chaîne principale.
Exemple 4 - Mettre en surbrillance une partie de chaîne dans les cellules
C'est la requête qui m'a été posée par June (mon lecteur qui m'a aussi inspiré pour écrire ce tutoriel).
Voici un exemple de données au format que June m'a envoyé :
Sa requête était de mettre en gras les nombres en dehors de la parenthèse.
Voici le code que j'ai créé qui fait cela:
Sub Bold() Dim rCell As Range Dim Char As Entier pour chaque rCell dans la sélection CharCount = Len(rCell) Char = InStr(1, rCell, "(") rCell.Characters(1, Char - 1).Font.Bold = True Next rCell End Sub
Le code ci-dessus utilise la boucle For Each pour parcourir chacune des cellules de la sélection. Il identifie la position du caractère crochet ouvrant à l'aide de la fonction InStr. Il modifie ensuite la police du texte avant le crochet.
Pour utiliser ce code, vous devez copier et coller dans un module dans l'éditeur VB.
Une fois que vous avez copié collé le code, sélectionnez les cellules dans lesquelles vous souhaitez effectuer cette mise en forme et exécutez la macro (comme indiqué ci-dessous).
Vous aimerez peut-être également les didacticiels Excel VBA suivants :
- Fonction Excel VBA SPLIT.
- Fonction TRIM VBA.
- Le guide ultime des boucles Excel VBA.
- Un guide du débutant sur l'utilisation de la boucle For Next dans Excel VBA.
- Comment créer et utiliser un complément Excel.
- Comment combiner plusieurs classeurs dans un seul classeur Excel.
- Comment compter les cellules colorées dans Excel
- Exemples de macros Excel VBA utiles pour les débutants.
- Comment trier les données dans Excel à l'aide de VBA (un guide étape par étape)
