Gisteren kreeg ik een e-mail van een van mijn lezers – juni.
Ze wilde dat weten hoe u vetgedrukte lettertypen toepast 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 doen voor honderden cellen tegelijk.
Aangezien er is geen ingebouwde functionaliteit in Excel die dat kan, ik heb een eenvoudige macro gemaakt die de Excel VBA InStr-functie gebruikt (u zult zien hoe u dit moet doen in Voorbeeld 4 in deze tutorial).
Maar laten we eerst eens kijken hoe de Excel VBA InStr-functie werkt!
Deze tutorial behandelt:
Excel VBA InStr-functie
In deze tutorial zal ik het gebruik van de InStr-functie in Excel VBA uitleggen en enkele praktische voorbeelden zien waar deze kan worden gebruikt.
Excel VBA InStr-functie – Inleiding
InStr-functie vindt de positie van een gespecificeerde substring binnen de string en geeft de eerste positie van zijn voorkomen terug.
Als je bijvoorbeeld de positie van ‘x’ in ‘Excel’ wilt vinden, gebruik je de Excel VBA InStr-functie zou 2 teruggeven.
Syntaxis van InStr-functie
- – (optioneel argument) dit is een integerwaarde die de InStr-functie vertelt vanaf welke startpositie het moet beginnen met zoeken. Als ik bijvoorbeeld wil dat de zoekopdracht vanaf het begin begint, voer ik de waarde in als 1. Als ik wil dat de zoekopdracht begint met het derde teken en verder, gebruik ik 3. Indien weggelaten, wordt de standaardwaarde 1 gebruikt.
- String1 – Dit is de hoofdstring (of de bovenliggende string) waarin u wilt zoeken. Als u bijvoorbeeld zoekt naar de positie van x in Excel, is String 1 ‘Excel’.
- String2 – Dit is de subtekenreeks waarnaar u zoekt. Als u bijvoorbeeld zoekend naar de positie van x in Excel, zou String2 x zijn.
- – (optioneel argument) U kunt een van de volgende drie waarden specificeren voor argument:
- vbBinaryCompare – Dit zou een teken zijn door karaktervergelijking. Als u bijvoorbeeld zoekt naar ‘x’ in ‘Excel’, wordt er 2 geretourneerd, maar als u ‘X’ zoekt in ‘Excel’, wordt 0 geretourneerd omdat X bovenaan staat U kunt ook 0 gebruiken in plaats van vbBinaryCompare. Als het argument wordt weggelaten, wordt dit als standaard gebruikt.
- vbTextCompare – Dit zou een tekstuele vergelijking maken. Als u bijvoorbeeld zoekt naar ‘x’ of ‘X’ in Excel, zou het in beide gevallen 2 retourneren. Dit argument negeert het hoofdlettergebruik. U kunt ook 1 gebruiken in plaats van vbTextCompare.
- vbDatabaseCompare – 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 Excel VBA InStr-functie:
- InStr is een VBA functie en niet een werkbladfunctie. Dit betekent dat je het niet in het werkblad kunt gebruiken.
- Als String2 (de substring waarvan je de positie zoekt) leeg is, retourneert de functie de waarde van het argument.
- Als de InStr-functie de subtekenreeks niet binnen de hoofdreeks 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 functie InStr gebruiken om de positie van ‘V’ in ‘Excel VBA’ vanaf het begin te vinden.
De code hiervoor zou zijn:
Sub FindFromBeginning()Dim Position As IntegerPosition = InStr(1, "Excel VBA", "V", vbBinaryCompare)MsgBox PositionEnd Sub
Wanneer u deze code uitvoert, zal het een berichtvenster tonen met de waarde 7, die is de positie van ‘V’ in de string ‘Excel VBA’.
Voorbeeld 2 – De positie vinden vanaf het begin van het tweede woord
Stel dat ik de positie wil vinden van ‘de’ in de zin – ‘De snelle bruine vos springt over de luie hond’
Ik wil echter dat de zoekopdracht beginnen met het tweede woord en verder.
In dit geval moeten we het argument wijzigen om er zeker van te zijn dat het de positie aangeeft van waar het tweede woord begint.
Hier is de code die zal dit doen:
Sub FindFromSecondWord()Dim Position As IntegerPosition = InStr(4, "The quick brown fox jumps over the lazy dog", "the", vbBinaryCompare)MsgBox PositionEnd Sub
Deze code toont het berichtvenster met de waarde 32 aangezien we de startpositie hebben gespecificeerd als 4. Daarom negeert het de eerste ‘ De ‘en vindt de tweede’ de ‘in de zin.
Als u deze 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 StartingPosition As IntegerDim Position As IntegerStartingPosition = InStr(1, "The quick brown fox jumps over the lazy dog", " ", vbBinaryCompare)Position = InStr(StartingPosition, "The quick brown fox jumps over the lazy dog", "the", vbBinaryCompare)MsgBox PositionEnd Sub
Deze code zoekt eerst de positie van een spatie en slaat deze op in de variabele StartingPosition.
Vervolgens wordt deze variabele gebruikt als de startpositie om naar het woord ‘de’ te zoeken.
Daarom geeft het 32 terug (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:
Nu kunt u deze aangepaste functie gebruiken als elke andere werkbladfunctie. Het neemt een celverwijzing als invoer en geeft u de positie van @ daarin.
Op dezelfde manier kunt u een aangepaste functie om de positie van een substring binnen de hoofdreeks te vinden.
Voorbeeld 4 – Een deel van de tekenreeks in cellen markeren
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 mij heeft gestuurd:
Haar vraag was om de getallen buiten de haakjes vetgedrukt te maken.
Hier is de code die ik heb gemaakt die dit doet:
De bovenstaande code gebruikt de For Each-lus om doorloop elk van de cellen in de selectie. Het identificeert de positie van het openingshaakje met behulp van de InStr-functie. Het verandert dan het lettertype van de tekst vóór de haak.
Om deze code te gebruiken, moet je kopiëren en plakken in een module in de VB-editor.
Zodra je hebt gekopieerd de code, selecteer de cellen waarin u deze opmaak wilt doen en voer de macro uit (zoals hieronder weergegeven).
Misschien vind je de volgende Excel VBA-zelfstudies ook leuk:
- Excel VBA SPLIT-functie.
- VBA TRIM-functie.
- De ultieme gids voor Excel VBA-loops .
- Een beginnershandleiding voor het gebruik van For Next Loop in Excel VBA.
- Een Excel-invoegtoepassing maken en gebruiken.
- Meerdere werkmappen combineren in één Excel-werkmap.
- Hoe u gekleurde cellen in Excel kunt tellen.
- Handige Excel VBA-macrovoorbeelden voor beginners.
- Gegevens in Excel sorteren met VBA (Een stapsgewijze handleiding)