Comment utiliser la fonction InStr en VBA dans Excel
InStr cherche un texte à l’intérieur d’un autre et renvoie sa position, le premier caractère d’une chaîne comptant pour 1. Quand le texte cherché est absent, elle renvoie 0 et rien d’autre, jamais une valeur négative ni une erreur. La documentation officielle de Microsoft le précise dans son tableau des valeurs de retour, ce qui change la façon d’écrire le test qui la suit.
Sur la feuille, la fonction la plus proche est CHERCHE, mais l’ordre de ses arguments n’est pas le même, et elle renvoie une erreur plutôt qu’un 0 quand elle ne trouve rien. Le quatrième argument d’InStr, lui, choisit la casse au cas par cas, un réglage que CHERCHE n’a pas. Et pour chercher depuis la fin d’une chaîne plutôt que depuis le début, InStrRev prend le relais, avec un troisième ordre d’arguments qui lui est propre.
Les sections qui suivent posent ces points un par un, chacun sourcé sur la documentation de Microsoft ou sur un calcul de positions qui ne dépend d’aucun réglage caché. Tout se joue sur un classeur de réclamations clients, que le bouton ci-dessous envoie.
InStr VBA
Télécharge le classeur de l’article pour refaire chaque étape sur les mêmes données.
Lire la syntaxe d’InStr, où la position de départ précède la chaîne cherchée
La page officielle de Microsoft écrit la syntaxe ainsi, avec la position de départ entre crochets pour dire qu’elle est facultative.
InStr([start, ]string1, string2[, compare])
La position de départ vient donc avant la chaîne où l’on cherche, elle-même avant la chaîne cherchée, et c’est cet ordre qui surprend le plus. Sur la feuille, CHERCHE commence au contraire par ce qu’on cherche, puis la chaîne où le chercher, et termine par la position de départ, facultative elle aussi.
| Fonction | 1er argument | 2e argument | 3e argument | 4e argument |
|---|---|---|---|---|
| CHERCHE (feuille) | texte cherché | texte où chercher | no_départ, facultatif | aucun |
| InStr (VBA) | start, facultatif | texte où chercher | texte cherché | compare, facultatif |
La documentation le confirme d’ailleurs d’un autre biais, puisqu’elle précise que la position de départ devient obligatoire dès que le quatrième argument, la comparaison, est renseigné. Omettre les deux revient à chercher depuis le premier caractère, la forme la plus courte de la fonction.
Dim SearchString As String, SearchChar As String, MyPos As LongSearchString = "XXpXXpXXPXXP"SearchChar = "P"MyPos = InStr(4, SearchString, SearchChar, 1) ' comparaison textuelle depuis la position 4, rend 6MyPos = InStr(1, SearchString, SearchChar, 0) ' comparaison binaire depuis la position 1, rend 9MyPos = InStr(SearchString, SearchChar) ' comparaison binaire par défaut, rend 9MyPos = InStr(1, SearchString, "W") ' absente, rend 0
Repérer un texte sans jamais lever d’erreur avec InStr
Le dernier appel de l’exemple ci-dessus le montre, InStr rend 0 quand elle ne trouve rien, sans arrêter la macro. La documentation officielle détaille même les cas voisins dans son tableau des valeurs de retour, une chaîne cherchée vide rendant la position de départ elle-même, et une chaîne où chercher vide rendant 0.
Sur la feuille Réclamations du classeur d’exemple, le message de la ligne 4 ne porte aucune trace d’urgence, et InStr le confirme par un simple 0, qu’un If suffit à lire.
If InStr(1, message, "urgent") > 0 Thenpriorite = "Prioritaire"Elsepriorite = "Standard"End If
Sur la feuille, CHERCHE réagit à l’inverse, avec #VALEUR! dès que le texte manque, ce qui oblige à l’envelopper de SIERREUR pour continuer le calcul. Un test sur InStr n’a besoin de rien de tel, puisque 0 est une valeur comme une autre, jamais une erreur qui remonterait plus haut.
Choisir la casse avec le quatrième argument et Option Compare
Le quatrième argument d’InStr, nommé compare, décide si la casse compte dans la recherche, et la documentation officielle en donne les quatre valeurs.
| Constante | Valeur | Effet |
|---|---|---|
| vbUseCompareOption | -1 | Suit le réglage d’Option Compare du module |
| vbBinaryCompare | 0 | Comparaison binaire, la casse compte |
| vbTextCompare | 1 | Comparaison textuelle, la casse ne compte pas |
| vbDatabaseCompare | 2 | Réservé à Microsoft Access |
L’exemple officiel plus haut le prouve avec de vrais nombres, sur la même chaîne « XXpXXpXXPXXP » cherchée pour la lettre « P ». Avec vbTextCompare depuis la position 4, InStr s’arrête au « p » minuscule de la position 6. Avec vbBinaryCompare depuis la position 1, elle ignore ce même « p » et ne s’arrête qu’au « P » majuscule de la position 9.
Sans quatrième argument, c’est Option Compare qui tranche, comme pour un Select Case sur du texte, et un module qui n’en porte aucun compare en binaire par défaut, casse comprise. La différence avec Select Case tient à la portée du réglage, puisque son seul recours est Option Compare ou une conversion par UCase, valable pour tout le module ou pour tout l’appel. Le quatrième argument d’InStr, lui, se pose appel par appel, sans toucher au reste du module.
InStr(1, message, "urgent", vbTextCompare)
Retrouver une seconde occurrence grâce à la position de départ
La position de départ sert surtout à relancer la recherche après ce qu’une première InStr vient de trouver, exactement comme no_départ le fait pour CHERCHE. Les références du classeur d’exemple, au format « REC-2041-A », portent deux tirets, et le numéro qu’elles encadrent se lit en cherchant le second sans revenir au premier.
premierTiret = InStr(1, reference, "-")secondTiret = InStr(premierTiret + 1, reference, "-")numero = Mid(reference, premierTiret + 1, secondTiret - premierTiret - 1)
Écrire la position de départ après la chaîne cherchée, comme le fait CHERCHE, semble naturel quand on vient de la feuille, mais VBA la refuse à cette place. La position de départ est un nombre, et une référence comme « REC-2041-A » n’en est pas un, si bien que VBA lève une erreur d’incompatibilité de type au lieu de chercher quoi que ce soit.
Un appel à deux arguments seulement échappe pourtant à ce piège, puisque InStr(reference, "-") reste valide, VBA y lisant simplement string1 puis string2 sans position de départ. C’est seulement au moment d’ajouter un troisième argument que l’ordre choisi par CHERCHE cesse de fonctionner.
Chercher depuis la fin d’une chaîne avec InStrRev
InStrRev fait le même travail qu’InStr en partant de l’autre bout, et la documentation officielle de Microsoft le résume en une phrase, puisqu’elle « renvoie la position d’une occurrence d’une chaîne dans une autre, à partir de la fin de la chaîne ». Sur une chaîne qui porte plusieurs occurrences du même caractère, c’est donc la dernière qui répond, jamais la première.
InStrRev(stringcheck, stringmatch[, start[, compare]])
Cette syntaxe ajoute un troisième ordre à ceux déjà vus, avec la chaîne où chercher en premier, celle qu’on cherche en second, et la position en troisième, quand InStr la mettait en tête. La documentation le note elle-même, la syntaxe d’InStrRev n’a rien à voir avec celle d’InStr, malgré la ressemblance des deux noms.
La position de départ, omise, vaut -1 plutôt que 1, ce qui revient à démarrer la recherche au dernier caractère de la chaîne. Un start choisi à la main fixe alors la limite droite de la recherche, puisque la documentation précise qu’InStrRev ne retient une occurrence que si la position de son dernier caractère reste inférieure ou égale à ce start.
Extraire l’extension d’un fichier, celle du dernier point
La pièce jointe de la ligne 5 du classeur, « facture.finale.pdf », porte deux points, et c’est exactement le cas où la direction de la recherche change tout. Une InStr ordinaire s’arrêterait au premier point et ferait sortir « finale.pdf » comme extension, alors qu’InStrRev retient le dernier et rend la bonne, « pdf ».
dernierPoint = InStrRev(piece, ".")If dernierPoint > 0 Thenextension = Mid(piece, dernierPoint + 1)Elseextension = "Inconnue"End If
Écrire une macro qui classe des réclamations avec InStr et InStrRev
Ces trois usages se réunissent dans une seule macro, qui parcourt les réclamations du classeur et remplit trois colonnes à leur sujet. La priorité vient du mot urgent, le numéro de la référence de ses deux tirets, et l’extension de la pièce jointe de son dernier point.
L’onglet Réclamations, avant la macro
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Référence | Client | Message | Pièce jointe |
| 2 | REC-2041-A | Dutilleul Motoculture | Colis abîmé pendant le transport, remboursement demandé | photo-dommage.jpg |
| 3 | REC-2042-B | Pharmacie du Parc | URGENT, rupture de stock avant vendredi | bon-commande.pdf |
| 4 | REC-2043-A | Atelier Nogaro | Livraison en retard de trois jours | notes |
| 5 | REC-2044-C | Boulangerie Rivat | Colis urgent, cliente très mécontente | facture.finale.pdf |
| 6 | REC-2045-B | Imprimerie Colas | Rien à signaler pour l’instant | rapport.xlsx |
| 7 | REC-2046-A | Cabinet Delmas | Erreur de facturation, Urgent svp | scan.png |
| 8 | REC-2047-C | Garage Ferrand | Demande d’information sur une pièce | |
| 9 | REC-2048-B | Studio Nolan | Colis manquant depuis lundi | preuve-envoi.jpeg |
Sub Traiter_les_reclamations()Dim feuille As WorksheetDim derniere As LongDim ligne As LongDim reference As StringDim message As StringDim piece As StringDim premierTiret As LongDim secondTiret As LongDim dernierPoint As LongSet feuille = ThisWorkbook.Worksheets("Réclamations")derniere = feuille.Cells(feuille.Rows.Count, "A").End(xlUp).RowFor ligne = 2 To dernierereference = feuille.Cells(ligne, "A").Valuemessage = feuille.Cells(ligne, "C").Valuepiece = feuille.Cells(ligne, "D").ValueIf InStr(1, message, "urgent", vbTextCompare) > 0 Thenfeuille.Cells(ligne, "E").Value = "Prioritaire"Elsefeuille.Cells(ligne, "E").Value = "Standard"End IfpremierTiret = InStr(1, reference, "-")secondTiret = InStr(premierTiret + 1, reference, "-")feuille.Cells(ligne, "F").Value = Mid(reference, premierTiret + 1, secondTiret - premierTiret - 1)dernierPoint = InStrRev(piece, ".")If dernierPoint > 0 Thenfeuille.Cells(ligne, "G").Value = Mid(piece, dernierPoint + 1)Elsefeuille.Cells(ligne, "G").Value = "Inconnue"End IfNext ligneEnd Sub
- 01Ouvre le classeur d’exemple, puis l’éditeur VBA avec Alt+F11.
- 02Choisis Module dans le menu Insertion, puis colle la macro dans la fenêtre qui s’ouvre.
- 03Enregistre le classeur au format Classeur Excel (prenant en charge les macros).
- 04Reviens dans Excel, appuie sur Alt+F8, choisis Traiter_les_reclamations et clique sur Exécuter.
L’onglet Réclamations, après la macro
| A | E | F | G | |
|---|---|---|---|---|
| 1 | Référence | Priorité | Numéro | Extension |
| 2 | REC-2041-A | Standard | 2041 | jpg |
| 3 | REC-2042-B | Prioritaire | 2042 | |
| 4 | REC-2043-A | Standard | 2043 | Inconnue |
| 5 | REC-2044-C | Prioritaire | 2044 | |
| 6 | REC-2045-B | Standard | 2045 | xlsx |
| 7 | REC-2046-A | Prioritaire | 2046 | png |
| 8 | REC-2047-C | Standard | 2047 | Inconnue |
| 9 | REC-2048-B | Standard | 2048 | jpeg |
La priorité suit la présence du mot « urgent », quelle que soit sa casse, la colonne Numéro isole ce qui vit entre les deux tirets de la référence, et l’extension retient toujours le dernier point de la pièce jointe, ou l’absence de point sur les lignes 4 et 8. Le guide sur l’installation d’une macro dans Excel détaille le format à macros et la marque de sécurité qui bloque un fichier téléchargé.
Pour un caractère invisible plutôt qu’un mot entier, l’article sur Trim en VBA montre une autre recherche par InStr, celle d’un espace insécable qu’aucun nettoyage de texte ordinaire ne voit passer. Le guide des macros Excel et du VBA réunit les autres fonctions du langage.
Questions fréquentes
L’ordre des arguments change d’abord, CHERCHE commençant par le texte cherché quand InStr commence par la position de départ. Les valeurs de retour diffèrent aussi, CHERCHE renvoyant #VALEUR! quand le texte est absent, là où InStr renvoie simplement 0.
Oui, tant que rien ne dit le contraire. Sans quatrième argument, InStr suit le réglage Option Compare du module, et un module qui n’en déclare aucun compare en binaire, casse comprise, d’après la documentation officielle de Microsoft.
La position de départ elle-même, d’après le tableau des valeurs de retour de la documentation officielle. InStr(1, "Bonjour", "") rend ainsi 1, et InStr(5, "Bonjour", "") rendrait 5, sans qu’aucun caractère n’ait réellement été trouvé.
InStr cherche depuis le début d’une chaîne et rend la première occurrence, alors qu’InStrRev cherche depuis la fin et rend la dernière. Leurs arguments ne se posent pas non plus dans le même ordre, la position de départ d’InStrRev se plaçant en troisième position, après les deux chaînes.
InStrRev répond directement à ce besoin, sans qu’il faille imbriquer plusieurs InStr. Sur un nom de fichier, InStrRev(nom, ".") retient le point le plus proche de la fin, ce qui donne la bonne extension même quand le nom en porte plusieurs, comme sur une pièce jointe « facture.finale.pdf ».
Ni InStr ni vbTextCompare ne touchent aux accents, qui restent des caractères distincts pour les deux. « Réclamation » et « Reclamation » sont donc deux textes différents pour InStr, et seule une fonction qui les remplace au préalable, ou une comparaison construite à la main, peut les confondre.