Excel VBA InStr-functie - uitgelegd met voorbeelden

Gisteren kreeg ik een e-mail van een van mijn lezers - juni.

Ze wilde weten hoe ze een vetgedrukt lettertype kon toepassen op een specifiek deel van een tekenreeks in een cel. Pas het vetgedrukte formaat bijvoorbeeld alleen toe op het woord 'Hallo' uit 'Hallo wereld'.

En ze wilde dit voor honderden cellen tegelijk doen.

Omdat er geen ingebouwde functionaliteit in Excel is die dat kan, heb ik een eenvoudige macro gemaakt die gebruikmaakt van de Excel VBA InStr-functie (hoe je dit doet zie je in voorbeeld 4 in deze tutorial).

Maar laten we eerst eens kijken hoe de Excel VBA InStr-functie werkt!

Excel VBA InStr-functie

In deze zelfstudie zal ik het gebruik van de InStr-functie in Excel VBA uitleggen en enkele praktische voorbeelden bekijken waar deze kan worden gebruikt.

Excel VBA InStr-functie - Inleiding

De functie InStr vindt de positie van een opgegeven subtekenreeks binnen de tekenreeks en retourneert de eerste positie van zijn optreden.

Als u bijvoorbeeld de positie van 'x' in 'Excel' wilt vinden, zou het gebruik van de Excel VBA InStr-functie 2 retourneren.

Syntaxis van InStr-functie

InStr( [Start], String1, String2, [Vergelijk] )

• [Begin] - (optioneel argument) dit is een geheel getal dat de InStr-functie de startpositie vertelt van waaruit het moet gaan zoeken. Als ik bijvoorbeeld wil dat het zoeken vanaf het begin begint, voer ik de waarde in als 1. Als ik wil dat het met het derde teken begint, gebruik ik 3. Indien weggelaten, wordt de standaardwaarde 1 genomen.
• String1 - Dit is de hoofdreeks (of de bovenliggende reeks) waarin u wilt zoeken. Als u bijvoorbeeld op zoek bent naar de positie van x in Excel, zou String 1 "Excel" zijn.
• String2 - Dit is de substring waarnaar u zoekt. Als u bijvoorbeeld op zoek bent naar de positie van x in Excel, zou String2 x zijn.
• [Vergelijken] - (optioneel argument) U kunt een van de volgende drie waarden opgeven voor het argument [vergelijken]:
  • vbBinairVergelijken - Dit zou karakter voor karakter vergelijken. Als u bijvoorbeeld zoekt naar 'x' in 'Excel', wordt 2 geretourneerd, maar als u naar 'X' zoekt in 'Excel', wordt 0 geretourneerd omdat X in hoofdletters is. U kunt ook 0 gebruiken in plaats van vbBinaryCompare. Als het argument [Vergelijken] wordt weggelaten, wordt dit als standaard beschouwd.
  • vbTekstVergelijken - Dit zou een tekstuele vergelijking opleveren. Als u bijvoorbeeld naar 'x' of 'X' in Excel zoekt, zou dit in beide gevallen 2 opleveren. Dit argument negeert de letter case. U kunt ook 1 gebruiken in plaats van vbTextCompare.
  • vbDatabaseVergelijken - Dit wordt alleen gebruikt voor Microsoft Access. Het gebruikt de informatie in de database om de vergelijking uit te voeren. U kunt ook 2 gebruiken in plaats van vbDatabaseCompare.

Aanvullende opmerkingen over de Excel VBA InStr-functie:

• InStr is een VBA-functie en geen werkbladfunctie. Dit betekent dat je het niet binnen het werkblad kunt gebruiken.
• Als String2 (wat de substring is waarvan je de positie zoekt) leeg is, zou de functie de waarde van het [Start]-argument teruggeven.
• Als de InStr-functie de subtekenreeks binnen de hoofdtekenreeks niet kan vinden, zou deze 0 retourneren.

Laten we nu eens kijken naar een voorbeeld van het gebruik van de Excel VBA InStr-functie

Voorbeeld 1 - De positie vanaf het begin vinden

In dit voorbeeld zal ik de InStr-functie gebruiken om vanaf het begin de positie van 'V' in 'Excel VBA' te vinden.

De code hiervoor zou zijn:

Sub FindFromBeginning() Dim Position As Integer Position = InStr(1, "Excel VBA", "V", vbBinaryCompare) MsgBox Positie End Sub

Wanneer u deze code uitvoert, wordt een berichtvenster weergegeven met de waarde 7, de positie van 'V' in de tekenreeks 'Excel VBA'.

Voorbeeld 2 - De positie vinden vanaf het begin van het tweede woord

Stel, ik wil de positie van 'de' vinden in de zin - 'De snelle bruine vos springt over de luie hond'

Ik wil echter dat de zoekopdracht vanaf het tweede woord begint.

In dit geval moeten we het [Start]-argument wijzigen om er zeker van te zijn dat het de positie aangeeft waar het tweede woord begint.

Hier is de code die dit zal doen:

Sub FindFromSecondWord() Dim Position As Integer Position = InStr(4, "De snelle bruine vos springt over de luie hond", "de", vbBinaryCompare) MsgBox Positie End Sub

Deze code toont het berichtvenster met de waarde 32 omdat we de startpositie als 4 hebben gespecificeerd. Daarom negeert het de eerste 'The' en vindt de tweede 'the' in de zin.

Als u het dynamischer wilt maken, kunt u de code verbeteren zodat deze automatisch het eerste woord negeert.

Hier is de verbeterde code die dit zal doen:

Sub FindFromSecondWord() Dim StartPosition As Integer Dim Position As Integer StartPosition = InStr(1, "De snelle bruine vos springt over de luie hond", " ", vbBinaryCompare) Positie = InStr(StartingPosition, "De snelle bruine vos springt over de luie hond dog", "the", vbBinaryCompare) MsgBox Positie End Sub

Deze code vindt eerst de positie van een spatie en slaat deze op in de variabele StartingPosition.

Vervolgens gebruikt het deze variabele als startpositie om naar het woord 'de' te zoeken.

Daarom retourneert het 32 ​​(wat de startpositie is van 'de' na het eerste woord).

Voorbeeld 3 - De positie van @ in e-mailadres vinden

U kunt eenvoudig een aangepaste functie maken om de positie van @ in een e-mailadres te vinden met behulp van de Excel VBA InStr-functie.

Hier is de code om de aangepaste functie te maken:

Functie FindPosition(Ref As Range) As Integer Dim Position As Integer Position = InStr(1, Ref, "@") FindPosition = Position End Functie

Nu kunt u deze aangepaste functie gebruiken als elke andere werkbladfunctie. Het neemt een celverwijzing als invoer en geeft je de positie van @ erin.

Op dezelfde manier kunt u een aangepaste functie maken om de positie van een subtekenreeks binnen de hoofdtekenreeks te vinden.

Voorbeeld 4 - Een deel van een string markeren in cellen

Dit is de vraag die werd gesteld door June (mijn lezer die me ook inspireerde om deze tutorial te schrijven).

Hier is een voorbeeld van gegevens in het formaat dat June me heeft gestuurd:

Haar vraag was om de cijfers buiten de haakjes vet te maken.

Hier is de code die ik heb gemaakt die dit doet:

Sub Bold() Dim rCell As Range Dim Char As Integer Voor elke rCell In Selectie CharCount = Len(rCell) Char = InStr(1, rCell, "(") rCell.Characters(1, Char - 1).Font.Bold = True Next rCell End Sub

De bovenstaande code gebruikt de For Each-lus om door elk van de cellen in de selectie te gaan. Het identificeert de positie van het openingshaakje met behulp van de InStr-functie. Vervolgens wordt het lettertype van de tekst vóór de haakjes gewijzigd.

Om deze code te gebruiken, moet je een module in de VB-editor kopiëren en plakken.

Nadat u de code hebt gekopieerd en geplakt, selecteert u de cellen waarin u deze opmaak wilt doen en voert u de macro uit (zoals hieronder weergegeven).

Misschien vind je de volgende Excel VBA-zelfstudies misschien ook leuk:

• Excel VBA SPLIT-functie.
• VBA TRIM-functie.
• De ultieme gids voor Excel VBA-loops.
• Een beginnershandleiding voor gebruik voor de volgende lus in Excel VBA.
• Een Excel-invoegtoepassing maken en gebruiken
• Meerdere werkmappen combineren in één Excel-werkmap
• Hoe gekleurde cellen in Excel te tellen
• Handige Excel VBA-macrovoorbeelden voor beginners.
• Gegevens sorteren in Excel met VBA (een stapsgewijze handleiding)