Deze zelfstudie laat zien hoe u de methoden Zoeken en vervangen in Excel VBA kunt gebruiken.
VBA zoeken
Excel heeft uitstekende ingebouwde Vind en Zoeken en vervangen gereedschap.
Ze kunnen worden geactiveerd met de sneltoetsen CTRL + F (Zoeken) of CTRL + H (Vervangen) of via het lint: Home > Bewerken > Zoek & Selecteer.
Door te klikken Opties, kunt u geavanceerde zoekopties zien:
U kunt eenvoudig toegang krijgen tot zowel de methoden Zoeken als Vervangen met behulp van VBA. Deze ingebouwde methoden zijn veel sneller dan alles wat u zelf in VBA zou kunnen schrijven.
VBA-voorbeeld zoeken
Om de zoekfunctie te demonstreren, hebben we de volgende gegevensset in Blad1 gemaakt.
Als u mee wilt doen, voert u de gegevens in uw eigen werkmap in.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Zoeken zonder optionele parameters
Wanneer u de VBA Find-methode gebruikt, zijn er veel optionele parameters die u kunt instellen.
We raden ten zeerste aan om alle parameters te definiëren wanneer u de Zoekmethode gebruikt!
Als u de optionele parameters niet definieert, gebruikt VBA de momenteel geselecteerde parameters in het zoekvenster van Excel. Dit betekent dat u mogelijk niet weet welke zoekparameters worden gebruikt wanneer de code wordt uitgevoerd. Zoeken kan worden uitgevoerd op de hele werkmap of op een blad. Het kan zoeken naar formules of waarden. Er is geen manier om te weten, tenzij u handmatig controleert wat er momenteel is geselecteerd in het zoekvenster van Excel.
Voor de eenvoud beginnen we met een voorbeeld zonder dat er optionele parameters zijn gedefinieerd.
Voorbeeld van eenvoudig zoeken
Laten we eens kijken naar een eenvoudig Find-voorbeeld:
| 123456789 | SubtestZoeken()Dim MyRange als bereikStel MyRange = Sheets("Blad1") in.UsedRange.Find("werknemer")MsgBox MyRange.AdresMsgBox MyRange.ColumnMsgBox MyRange.RowEinde sub |
Deze code zoekt naar "werknemer" in het Gebruikte bereik van Blad1. Als het "werknemer" vindt, wijst het het eerst gevonden bereik toe aan bereikvariabele MyRange.
Vervolgens worden berichtvakken weergegeven met het adres, de kolom en de rij van de gevonden tekst.
In dit voorbeeld worden de standaard zoekinstellingen gebruikt (ervan uitgaande dat ze niet zijn gewijzigd in het zoekvenster van Excel):
- De zoektekst komt gedeeltelijk overeen met de celwaarde (een exacte celovereenkomst is niet vereist)
- De zoekopdracht is niet hoofdlettergevoelig.
- Zoek zoekt alleen in één werkblad
Deze instellingen kunnen worden gewijzigd met verschillende optionele parameters (hieronder besproken).
Methodenotities zoeken
- Zoeken selecteert niet de cel waarin de tekst wordt gevonden. Het identificeert alleen het gevonden bereik, dat u in uw code kunt manipuleren.
- De Find-methode zal alleen de eerste gevonden instantie vinden.
- U kunt jokertekens (*) gebruiken, b.v. zoek op 'E*'
niets gevonden
Als de zoektekst niet bestaat, blijft het bereikobject leeg. Dit veroorzaakt een groot probleem wanneer uw code de locatiewaarden probeert weer te geven omdat ze niet bestaan. Dit resulteert in een foutmelding die u niet wilt.
Gelukkig kunt u binnen VBA testen op een leeg bereikobject met behulp van de Is-operator:
| 1 | Als MyRange niets is, dan is |
De code toevoegen aan ons vorige voorbeeld:
| 12345678910111213 | SubtestZoeken()Dim MyRange als bereikStel MyRange = Sheets("Blad1") in.UsedRange.Find("werknemer")Als MyRange niets is, dan isMsgBox MyRange.AdresMsgBox MyRange.ColumnMsgBox MyRange.RowAndersMsgBox "Niet gevonden"Stop alsEinde sub |
Zoek parameters
Tot nu toe hebben we alleen gekeken naar een eenvoudig voorbeeld van het gebruik van de Find-methode. Er zijn echter een aantal optionele parameters beschikbaar om u te helpen uw zoekopdracht te verfijnen
| Parameter | Type | Beschrijving | Waarden |
| Wat | Verplicht | De waarde om naar te zoeken | Elk gegevenstype zoals een tekenreeks of numeriek |
| Na | Optioneel | Enkele celverwijzing om uw zoekopdracht te beginnen | Mobiel adres |
| Kijk in | Optioneel | Gebruik formules, waarden, opmerkingen voor zoeken | xlWaarden, xlFormules, xlOpmerkingen |
| Kijk naar | Optioneel | Overeenkomen met een deel of het geheel van een cel | xlGeheel, xlDeel |
| ZoekenOrder | Optioneel | De volgorde om in te zoeken - rijen of kolommen | xlByRows, xlByColummns |
| Zoekrichting | Optioneel | Richting voor zoeken om in te gaan - vooruit of achteruit | xlVolgende, xlVorige |
| MatchCase | Optioneel | Zoeken is hoofdlettergevoelig of niet | Waar of niet waar |
| MatchByte | Optioneel | Alleen gebruikt als u dubbelbyte taalondersteuning hebt geïnstalleerd, b.v. Chinese taal | Waar of niet waar |
| ZoekFormaat | Optioneel | Zoeken op celformaat toestaan | Waar of niet waar |
Na parameter en zoek meerdere waarden
Je gebruikt de Na parameter om de startcel voor uw zoekopdracht op te geven. Dit is handig als er meer dan één exemplaar is van de waarde waarnaar u zoekt.
Als een zoekopdracht al één waarde heeft gevonden en je weet dat er meer waarden zullen worden gevonden, dan gebruik je de Find-methode met de parameter ‘After’ om de eerste instantie vast te leggen en vervolgens die cel als startpunt voor de volgende zoekopdracht.
U kunt dit gebruiken om meerdere exemplaren van uw zoektekst te vinden:
| 123456789101112131415161718192021222324252627282930313233343536 | SubtestMultipleFinds()Dim MyRange als bereik, OldRange als bereik, FindStr als string'Zoek naar de eerste instantie van "'Light & Heat'Stel MyRange = Sheets ("Blad1") in.UsedRange.Find("Licht en warmte")'Indien niet gevonden, sluit dan af'Als MyRange niets is, sluit dan Sub af'Toon eerste gevonden adresMsgBox MyRange.Adres'Maak een kopie van het bereikobject'Stel OldRange = MyRange in'Voeg het adres toe aan de tekenreeks die wordt afgebakend met een "|" karakterFindStr = FindStr & "|" & MijnBereik.Adres'Itereer door het bereik op zoek naar andere instanties'Doen'Zoek naar 'Licht & Warmte' met het eerder gevonden adres als de parameter 'Na'Set MyRange = Sheets("Blad1").UsedRange.Find("Light & Heat", After:=Bereik(OldRange.Address))'Als het adres al is gevonden, verlaat dan de do-lus - dit stopt continue loopingAls InStr (FindStr, MyRange.Address) sluit dan Do . af'Laatst gevonden adres weergeven'MsgBox MyRange.Adres'Voeg het laatste adres toe aan de reeks adressen'FindStr = FindStr & "|" & MijnBereik.Adres'maak een kopie van het huidige bereik'Stel OldRange = MyRange inLusEinde sub |
Deze code doorloopt het gebruikte bereik en geeft het adres elke keer weer als er een instantie van 'Licht en warmte' wordt gevonden
Merk op dat de code blijft herhalen totdat een duplicaat adres wordt gevonden in FindStr, in welk geval het de Do-lus zal verlaten.
LookIn-parameter
U kunt de LookIn-parameter om op te geven in welk onderdeel van de cel u wilt zoeken. U kunt waarden, formules of opmerkingen in een cel opgeven.
- xlWaarden - Zoekt celwaarden (de uiteindelijke waarde van een cel na berekening)
- xlFormules - Zoekt binnen de celformule zelf (wat er ook in de cel wordt ingevoerd)
- xlOpmerkingen - Zoekt in celnotities
- xlOpmerkingenThreaded - Zoekt in celopmerkingen
Ervan uitgaande dat er een formule op het werkblad is ingevoerd, kunt u deze voorbeeldcode gebruiken om de eerste locatie van een formule te vinden:
| 12345678910 | Subtest LookIn()Dim MyRange als bereikSet MyRange = Sheets("Blad1").UsedRange.Find("=", LookIn:=xlFormulas)Als MyRange niets is, dan isMsgBox MyRange.AdresAndersMsgBox "Niet gevonden"Stop alsEinde sub |
Als de 'LookIn'-parameter was ingesteld op xlValues, zou de code een 'Not Found'-bericht weergeven. In dit voorbeeld wordt B10 geretourneerd.
De LookAt-parameter gebruiken
De Kijk naar parameter bepaalt of zoeken zal zoeken naar een exacte celovereenkomst of zoeken naar een cel die de zoekwaarde bevat.
- xlGeheel - Vereist dat de hele cel overeenkomt met de zoekwaarde
- xlPart - Zoekt binnen een cel naar de zoekreeks
Dit codevoorbeeld lokaliseert de eerste cel met de tekst "light". Met Kijk naar:=xlPart, het zal een match opleveren voor "Light & Heat".
| 123456789 | Subtest LookAt()Dim MyRange als bereikSet MyRange = Sheets("Blad1").UsedRange.Find("light", Lookat:=xlPart)Als MyRange niets is, dan isMsgBox MyRange.AdresAndersMsgBox "Niet gevonden"Stop alsEinde sub |
Indien xlGeheel was ingesteld, zou een overeenkomst alleen terugkeren als de celwaarde "licht" was.
Zoekopdrachtparameter
De Zoekopdrachtparameter bepaalt hoe de zoekopdracht in het hele bereik zal worden uitgevoerd.
- xlRijen - Zoeken gebeurt rij voor rij
- xlXolumns - Zoeken gebeurt kolom voor kolom
| 123456789 | Sub TestZoekenOrder()Dim MyRange als bereikSet MyRange = Sheets("Blad1").UsedRange.Find("employee", SearchOrder:=xlColumns)Als MyRange niets is, dan isMsgBox MyRange.AdresAndersMsgBox "Niet gevonden"Stop alsEinde sub |
Dit beïnvloedt welke match als eerste wordt gevonden.
Gebruikmakend van de testgegevens die eerder in het werkblad zijn ingevoerd, wanneer de zoekvolgorde kolommen is, is de gevonden cel A5. Wanneer de zoekvolgordeparameter wordt gewijzigd in xlRows, is de gevonden cel C4
Dit is belangrijk als u dubbele waarden binnen het zoekbereik heeft en u de eerste instantie onder een bepaalde kolomnaam wilt vinden.
Zoekrichting-parameter
De SearchDirection-parameter bepaalt in welke richting de zoekopdracht zal gaan - effectief vooruit of achteruit.
- xlVolgende - Zoek naar de volgende overeenkomende waarde in het bereik
- xlVorige - Zoeken naar vorige overeenkomende waarde in bereik
Nogmaals, als er dubbele waarden binnen het zoekbereik zijn, kan dit een effect hebben op welke als eerste wordt gevonden.
| 12345678910 | SubtestZoekrichting()Dim MyRange als bereikSet MyRange = Sheets("Blad1").UsedRange.Find("heat", SearchDirection:=xlPrevious)Als MyRange niets is, dan isMsgBox MyRange.AdresAndersMsgBox "Niet gevonden"Stop alsEinde sub |
Door deze code op de testgegevens te gebruiken, zal een zoekrichting van xlPrevious een locatie van C9 opleveren. Als u de parameter xlNext gebruikt, wordt een locatie van A4 geretourneerd.
De parameter Volgende betekent dat het zoeken in de linkerbovenhoek van het zoekbereik begint en naar beneden werkt. De parameter Vorige betekent dat het zoeken in de rechterbenedenhoek van het zoekbereik begint en naar boven werkt.
MatchByte-parameter
De MatchBye-parameter wordt alleen gebruikt voor talen die een dubbele byte gebruiken om elk teken weer te geven, zoals Chinees, Russisch en Japans.
Als deze parameter is ingesteld op 'True', zal Find alleen dubbelbyte-tekens matchen met double-byte-tekens. Als de parameter is ingesteld op 'False', dan komt een dubbelbyteteken overeen met enkele of dubbelbytetekens.
Zoekopmaakparameter
De SearchFormat-parameter stelt u in staat om te zoeken naar overeenkomende celformaten. Dit kan een bepaald lettertype zijn dat wordt gebruikt, of een vet lettertype of een tekstkleur. Voordat u deze parameter gebruikt, moet u de vereiste indeling voor de zoekopdracht instellen met de eigenschap Application.FindFormat.
Hier is een voorbeeld van hoe het te gebruiken:
| 12345678910111213 | Sub TestSearchFormat()Dim MyRange als bereikApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueSet MyRange = Sheets("Blad1").UsedRange.Find("heat", Searchformat:=True)Als MyRange niets is, dan isMsgBox MyRange.AdresAndersMsgBox "Niet gevonden"Stop alsApplication.FindFormat.ClearEinde sub |
In dit voorbeeld is de Formaat zoeken eigenschap is ingesteld om te zoeken naar een vetgedrukt lettertype. De instructie Find zoekt vervolgens naar het woord 'heat' door de parameter SearchFormat in te stellen op True, zodat alleen een exemplaar van die tekst wordt geretourneerd als het lettertype vet is.
In de eerder getoonde voorbeeldwerkbladgegevens, retourneert dit A9, de enige cel met het woord 'warmte' in een vetgedrukt lettertype.
Zorg ervoor dat de eigenschap FindFormat aan het einde van de code is gewist. Als u dit niet doet, zal uw volgende zoekopdracht hier nog steeds rekening mee houden en onjuiste resultaten opleveren.
Waar u een SearchFormat-parameter gebruikt, kunt u ook een jokerteken (*) als zoekwaarde gebruiken. In dit geval zoekt het naar elke waarde met een vetgedrukt lettertype:
| 1 | Set MyRange = Sheets("Blad1").UsedRange.Find("*", Searchformat:=True) |
Meerdere parameters gebruiken
Alle hier besproken zoekparameters kunnen desgewenst in combinatie met elkaar worden gebruikt.
U kunt bijvoorbeeld de parameter 'LookIn' combineren met de parameter 'MatchCase', zodat u naar de hele celtekst kijkt, maar deze is hoofdlettergevoelig
| 123456789 | SubtestMultipleParameters()Dim MyRange als bereikSet MyRange = Sheets("Blad1").UsedRange.Find("Light & Heat", LookAt:=xlWhole, MatchCase:=True)Als MyRange niets is, dan isMsgBox MyRange.AdresAndersMsgBox "Niet gevonden"Stop alsEinde sub |
In dit voorbeeld geeft de code A4 terug, maar als we slechts een deel van de tekst hebben gebruikt, b.v. 'warmte', zou er niets worden gevonden omdat we overeenkomen met de hele celwaarde. Het zou ook mislukken omdat de zaak niet overeenkomt.
| 1 | Set MyRange = Sheets("Blad1").UsedRange.Find("heat", LookAt:=xlWhole, MatchCase:=True) |
Vervangen in Excel VBA
Er is, zoals je mag verwachten, een functie Vervangen in Excel VBA, die op een vergelijkbare manier werkt als 'Zoeken', maar de waarden op de gevonden cellocatie vervangt door een nieuwe waarde.
Dit zijn de parameters die u kunt gebruiken in een Replace-methodeinstructie. Deze werken op precies dezelfde manier als voor de methode Find. Het enige verschil met 'Zoeken' is dat u een vervangingsparameter moet opgeven.
| Naam | Type | Beschrijving | Waarden |
| Wat | Verplicht | De waarde om naar te zoeken | Elk gegevenstype zoals een tekenreeks of numeriek |
| Vervanging | Verplicht | De vervangende string. | Elk gegevenstype zoals een tekenreeks of numeriek |
| Kijk naar | Optioneel | Overeenkomen met een deel of het geheel van een cel | xlDeel of xlGeheel |
| ZoekenOrder | Optioneel | De volgorde om in te zoeken - Rijen of Kolommen | xlByRows of xlByColumns |
| MatchCase | Optioneel | Zoeken is hoofdlettergevoelig of niet | Waar of niet waar |
| MatchByte | Optioneel | Alleen gebruikt als u ondersteuning voor taal met dubbele bytes hebt geïnstalleerd | Waar of niet waar |
| ZoekFormaat | Optioneel | Zoeken op celformaat toestaan | Waar of niet waar |
| Formaat vervangen | Optioneel | Het vervangformaat voor de methode. | Waar of niet waar |
De parameter Replace Format zoekt naar een cel met een bepaalde notatie, b.v. vetgedrukt op dezelfde manier als de SearchFormat-parameter werkt in de Find-methode. U moet eerst de eigenschap Application.FindFormat instellen, zoals weergegeven in de eerder getoonde voorbeeldcode zoeken
Vervangen zonder optionele parameters
Op zijn eenvoudigst hoeft u alleen aan te geven wat u zoekt en waarmee u het wilt vervangen.
| 123 | SubtestVervangen()Sheets("Blad1").UsedRange.Replace What:="Light & Heat", Replacement:="L & H"Einde sub |
Merk op dat de Find-methode alleen het eerste exemplaar van de overeenkomende waarde retourneert, terwijl de Replace-methode door het hele gespecificeerde bereik werkt en alles vervangt waarop een overeenkomst wordt gevonden.
De hier getoonde vervangende code vervangt elke instantie van 'Light & Heat' met 'L & H' door het hele bereik van cellen gedefinieerd door het UsedRange-object
VBA gebruiken om tekst in een VBA-tekstreeks te zoeken of te vervangen
De bovenstaande voorbeelden werken prima bij het gebruik van VBA om te communiceren met Excel-gegevens. Voor interactie met VBA-strings kunt u echter ingebouwde VBA-functies gebruiken, zoals INSTR en REPLACE.
U kunt de INSTR-functie om een tekstreeks binnen een langere reeks te zoeken.
| 123 | SubtestInstr()MsgBox InStr("Dit is MijnTekst-tekenreeks", "MijnTekst")Einde sub |
Deze voorbeeldcode retourneert de waarde 9, wat de nummerpositie is waar 'MijnTekst' wordt gevonden in de tekenreeks die moet worden doorzocht.
Merk op dat het hoofdlettergevoelig is. Als 'MijnTekst' allemaal kleine letters is, wordt de waarde 0 geretourneerd, wat betekent dat de zoekreeks niet is gevonden. Hieronder bespreken we hoe u hoofdlettergevoeligheid kunt uitschakelen.
INSTR - Start
Er zijn nog twee optionele parameters beschikbaar. U kunt het startpunt voor de zoekopdracht specificeren:
| 1 | MsgBox InStr(9, "Dit is MijnTekst-tekenreeks", "MijnTekst") |
Het startpunt is gespecificeerd als 9, dus het zal nog steeds 9 retourneren. Als het startpunt 10 was, zou het 0 (geen overeenkomst) retourneren omdat het startpunt te ver naar voren zou liggen.
INSTR - Hoofdlettergevoeligheid
U kunt ook een parameter Compare instellen op vbBinairVergelijken of vbTekstVergelijken. Als u deze parameter instelt, moet de instructie een startparameterwaarde hebben.
- vbBinairVergelijken - Hoofdlettergevoelig (standaard)
- vbTekstVergelijken - Niet hoofdlettergevoelig
| 1 | MsgBox InStr(1, "Dit is MyText string", "mytext", vbTextCompare) |
Deze instructie retourneert nog steeds 9, ook al is de zoektekst in kleine letters.
Om hoofdlettergevoeligheid uit te schakelen, kunt u ook Option Compare Text bovenaan uw codemodule declareren.
VBA-functie vervangen
Als je karakters in een string wilt vervangen door andere tekst binnen je code, dan is de Replace methode hier ideaal voor:
| 123 | SubtestVervangen()MsgBox Replace("Dit is MijnTekst-tekenreeks", "MijnTekst", "Mijn Tekst")Einde sub |
Deze code vervangt ‘MyText’ door ‘My Text’. Merk op dat de zoekreeks hoofdlettergevoelig is, aangezien een binaire vergelijking de standaard is.
U kunt ook andere optionele parameters toevoegen:
- Begin - definieert de positie in de initiële string van waaruit de vervanging moet beginnen. In tegenstelling tot de methode Zoeken, retourneert het een afgekapte tekenreeks die begint bij het tekennummer dat is gedefinieerd door de parameter Start.
- Graaf - bepaalt het aantal te maken vervangingen. Vervangen zal standaard elk exemplaar van de gevonden zoektekst wijzigen, maar u kunt dit beperken tot een enkele vervanging door de parameter Count in te stellen op 1
- Vergelijken - net als bij de zoekmethode kunt u een binaire zoekopdracht of een tekstzoekopdracht specificeren met vbBinairVergelijken of vbTekstVergelijken. Binair is hoofdlettergevoelig en tekst is niet hoofdlettergevoelig
| 1 | MsgBox Replace("Dit is MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Deze code retourneert 'Mijn tekstreeks (mijntekst)'. Dit komt omdat het gegeven startpunt 9 is, dus de nieuwe geretourneerde tekenreeks begint bij teken 9. Alleen de eerste 'MijnTekst' is gewijzigd omdat de parameter Count is ingesteld op 1.
De Replace-methode is ideaal voor het oplossen van problemen zoals namen van mensen die apostrofs bevatten, b.v. O'Flynn. Als u enkele aanhalingstekens gebruikt om een tekenreekswaarde te definiëren en er is een apostrof, dan zal dit een fout veroorzaken omdat de code de apostrof zal interpreteren als het einde van de tekenreeks en de rest van de tekenreeks niet zal herkennen.
U kunt de methode Vervangen gebruiken om de apostrof door niets te vervangen en deze volledig te verwijderen.
