De VBA-splitfunctie gebruiken
Met de VBA Split-functie kunt u de componentonderdelen scheiden vanuit een standaardtekstreeks waarbij elke component een specifiek scheidingsteken gebruikt, b.v. een komma of een dubbele punt. Het is gemakkelijker te gebruiken dan het schrijven van code om naar de scheidingstekens in de tekenreeks te zoeken en vervolgens de waarden te extraheren.
Het kan worden gebruikt als u een regel inleest van een door komma's gescheiden waarde (CSV-bestand) of als u een postadres heeft dat allemaal op één regel staat, maar u het als meerdere regels wilt zien.
De syntaxis is:
| 1 | Uitdrukking splitsen, scheidingsteken [optioneel], limiet [optioneel], vergelijken [optioneel] |
De VBA Split-functie heeft vier parameters:
- Uitdrukking - De reeks tekst die u in verschillende delen wilt splitsen.
- scheidingsteken (optioneel)- tekenreeks of niet-afdrukbaar teken - Definieert het scheidingsteken dat voor de splitsing wordt gebruikt. Als er geen scheidingsteken is opgegeven, wordt de standaard spatie gebruikt.
- Begrenzing (optioneel) - aantal - Bepaalt hoeveel splitsingen worden gemaakt. Indien leeg, worden alle beschikbare splitsingen binnen de tekenreeks gemaakt. Als deze is ingesteld op 1, worden er geen splitsingen gemaakt. Kortom, het stelt u in staat om een specifiek aantal waarden te scheiden vanaf het begin van de string, b.v. waar de snaar erg lang is en je alleen de eerste drie splitsingen nodig hebt.
- Vergelijken (optioneel) - Als uw scheidingsteken een tekstteken is, wordt dit gebruikt om te wisselen of het scheidingsteken hoofdlettergevoelig is of niet. Waarden zijn vbBinaryCompare (hoofdlettergevoelig) en vbTextCompare (niet hoofdlettergevoelig).
De split-functie retourneert altijd een array.
Eenvoudig voorbeeld van de splitsfunctie
| 123456789101112 | Sub SplitExample()'Definieer variabelen'Dim MyArray() As String, MyString As String, I As Variant'Voorbeeldreeks met scheidingstekens voor spatiesMyString = "Een Twee Drie Vier"'Gebruik de functie Splitsen om de samenstellende delen van de string te verdelen'MijnArray = Splitsen(MijnString)'doorloop de array die is gemaakt om elke waarde weer te geven'Voor elke I in MyArrayMsgBox Ivolgende ikEinde sub |
In dit voorbeeld is er geen scheidingsteken opgegeven omdat alle woorden een spatie ertussen hebben, dus het standaard scheidingsteken (spatie) kan worden gebruikt.
De array heeft geen afmetingen en is ingesteld als een tekenreeks. De variabele I, die wordt gebruikt in de For… Next-lus, moet als variant worden gedimensioneerd.
Wanneer deze code wordt uitgevoerd, worden er vier berichtvensters weergegeven, één voor elk van de splitsingen, b.v. Een twee drie. Vier.
Merk op dat als er een dubbele spatie is tussen de woorden in de string, dit zal worden geëvalueerd als een splitsing, hoewel er niets in staat. Dit is misschien niet het resultaat dat u wilt zien.
U kunt dit probleem oplossen door de functie Vervangen te gebruiken om dubbele spaties te vervangen door een enkele spatie:
| 1 | MijnString = Vervangen(MijnString, " ", " ") |
Een volg- of voorloopspatie kan ook problemen veroorzaken door een lege splitsing te produceren. Deze zijn vaak erg moeilijk te zien. U kunt deze overbodige spaties verwijderen met behulp van de Trim-functie:
| 1 | MijnString = Trim(MijnString) |
De splitsfunctie gebruiken met een scheidingsteken
We kunnen een scheidingsteken van een puntkomma (;) gebruiken. Dit wordt vaak gevonden in e-mailadresreeksen om de adressen te scheiden. Mogelijk hebt u een e-mail ontvangen die met een aantal collega's is gedeeld en wilt u in uw werkblad een lijst zien van wie deze is gegaan. U kunt de e-mailadressen eenvoudig kopiëren uit de e-mailvakken 'Aan' of 'Kopiëren' en in uw code.
| 123456789101112131415 | Sub SplitBySemicolonExample()'Definieer variabelen'Dim MyArray() As String, MyString As String, I As Variant, N As Integer'Voorbeeldreeks met scheidingstekens voor puntkomma'sMyString = "[email protected];[email protected];[email protected];[email protected]"'Gebruik de functie Splitsen om de samenstellende delen van de string te verdelen'MijnArray = Split(MijnString, ";")'Wis het werkblad'ActiveSheet.UsedRange.Clear'door de array heen lopen'Voor N = 0 Naar UBound (MyArray)'Plaats elk e-mailadres in de eerste kolom van het werkblad'Bereik ("A" & N + 1). Waarde = MyArray (N)Volgende NEinde sub |
Merk op dat een For… Next-lus wordt gebruikt om door de array te itereren. Het eerste element in de array begint altijd bij nul en de functie Bovengrens wordt gebruikt om het maximale aantal elementen te krijgen.
Na het uitvoeren van deze code ziet uw werkblad er als volgt uit:
Een limietparameter gebruiken in een splitfunctie
Met de parameter limit kan een specifiek aantal splitsingen worden gedaan vanaf het begin van de string. Helaas kunt u geen startpositie of een reeks splitsingen opgeven, dus het is vrij eenvoudig. U kunt uw eigen VBA-code bouwen om een functie te maken om dit te doen, en dit wordt later in dit artikel uitgelegd.
| 123456789101112131415 | Sub SplitsenMetLimitExample()'Variabelen maken'Dim MyArray() As String, MyString As String, I As Variant, N As Integer'Voorbeeldreeks met kommascheidingstekens'MyString = "Een, Twee, Drie, Vier, Vijf, Zes"'Gebruik de functie Splitsen om de samenstellende delen van de string te verdelen'MijnArray = Splitsen(MijnString, ",", 4)'Wis het werkblad'ActiveSheet.UsedRange.Clear'Itereer door de array'Voor N = 0 Naar UBound (MyArray)'Plaats elke splitsing in de eerste kolom van het werkblad'Bereik ("A" & N + 1). Waarde = MyArray (N)Volgende NEinde sub |
Nadat u deze code hebt uitgevoerd, ziet uw werkblad er als volgt uit:
Alleen de eerste drie gesplitste waarden worden afzonderlijk weergegeven. De laatste drie waarden worden weergegeven als één lange reeks en worden niet gesplitst.
Als u een grenswaarde kiest die groter is dan het aantal scheidingstekens binnen een string, zal dit geen fout opleveren. De string wordt opgesplitst in al zijn samenstellende delen alsof de grenswaarde niet was opgegeven.
De vergelijkingsparameter gebruiken in een splitfunctie
De parameter Compare bepaalt of het scheidingsteken hoofdlettergevoelig is of niet. Dit is niet van toepassing als de scheidingstekens komma's, puntkomma's of dubbele punten zijn.
Opmerking: in plaats daarvan kunt u altijd de optie Tekst vergelijken <> bovenaan uw module plaatsen om hoofdlettergevoeligheid voor de hele module te elimineren.
| 123456789101112131415 | Sub SplitByCompareExample()'Variabelen maken'Dim MyArray() As String, MyString As String, I As Variant, N As Integer'Voorbeeldreeks met X-scheidingstekens'MyString = "OneXTwoXThreexFourXFivexSix"'Gebruik de functie Splitsen om de samenstellende delen van de string te verdelen'MyArray = Split(MyString, "X", , vbBinaryCompare)'Wis het werkblad'ActiveSheet.UsedRange.Clear'door de array heen lopen'Voor N = 0 Naar UBound (MyArray)'Plaats elke splitsing in de eerste kolom van het werkblad'Bereik ("A" & N + 1). Waarde = MyArray (N)Volgende NEinde sub |
In dit voorbeeld gebruikt de te splitsen tekenreeks het teken 'X' als scheidingsteken. In deze tekenreeks is er echter een combinatie van hoofdletters en kleine letters 'X'. De parameter Compare in de Split-functie gebruikt een hoofdletter 'X'.
Als de parameter Compare is ingesteld op vbBinaryCompare, worden de kleine 'x'-tekens genegeerd en ziet uw werkblad er als volgt uit:
Als de parameter Compare is ingesteld op vbTextCompare, worden de kleine letters 'x' gebruikt in de splitsing en ziet uw werkblad er als volgt uit:
Merk op dat de waarde in cel A6 wordt afgekapt omdat deze een kleine letter 'x' bevat. Omdat de splitsing niet hoofdlettergevoelig is, zal elk scheidingsteken dat deel uitmaakt van een subtekenreeks een splitsing veroorzaken.
Dit is een belangrijk punt om in gedachten te houden bij het gebruik van een tekstscheidingsteken en vbTextCompare. U kunt gemakkelijk eindigen met het verkeerde resultaat.
Niet-afdrukbare tekens gebruiken als scheidingsteken
U kunt niet-afdrukbare tekens als scheidingsteken gebruiken, zoals een regelterugloop (een regeleinde).
Hier gebruiken we de vbCr om een regelterugloop te specificeren <>
| 123456789101112131415 | Sub SplitByNonPrintableExample()'Variabelen maken'Dim MyArray() As String, MyString As String, I As Variant, N As Integer'Voorbeeldreeks met regelterugloopscheidingstekens'MyString = "Een" & vbCr & "Twee" & vbCr & "Drie" & vbCr & "Vier" & vbCr & "Vijf" & vbCr & "Zes"'Gebruik de functie Splitsen om de samenstellende delen van de string te verdelen'MyArray = Split(MyString, vbCr, , vbTextCompare)'Wis het werkblad'ActiveSheet.UsedRange.Clear'Itereer door de array'Voor N = 0 Naar UBound (MyArray)'Plaats elke splitsing in de eerste kolom van het werkblad'Bereik ("A" & N + 1). Waarde = MyArray (N)Volgende NEinde sub |
In dit voorbeeld wordt een string opgebouwd met vbCr (carriage return character) als scheidingsteken.
Wanneer deze code wordt uitgevoerd, ziet uw werkblad er als volgt uit:
De samenvoegfunctie gebruiken om een splitsing ongedaan te maken
De functie Join voegt alle elementen van een array opnieuw samen, maar met een gespecificeerd scheidingsteken. Als er geen scheidingsteken is opgegeven, wordt een spatie gebruikt.
| 123456789101112131415 | Sub JoinExample()'Variabelen maken'Dim MyArray() As String, MyString As String, I As Variant, N As IntegerDim doel als string'Voorbeeldreeks met kommascheidingstekens'MyString = "Een, Twee, Drie, Vier, Vijf, Zes"'Plaats MyString in cel A1'Bereik ("A1"). Waarde = MyString'Gebruik de functie Splitsen om de samenstellende delen van de string te verdelen'MijnArray = Splitsen(MijnString, ",")'Gebruik de functie Join om de originele tekenreeks opnieuw te maken met een scheidingsteken voor puntkomma'sDoel = Deelnemen (MyArray,”;”)'Plaats de resultaatreeks in cel A2'Bereik ("A2"). Waarde = DoelEinde sub |
Deze code splitst een tekenreeks met kommascheidingstekens in een array en voegt deze weer samen met scheidingstekens met puntkomma's.
Na het uitvoeren van deze code ziet uw werkblad er als volgt uit:
Cel A1 heeft de originele tekenreeks met kommascheidingstekens en cel A2 heeft de nieuwe samengevoegde tekenreeks met scheidingstekens met puntkomma's.
De splitsfunctie gebruiken om woorden te tellen
Rekening houdend met het feit dat een stringvariabele in Excel VBA tot 2 GB lang kan zijn, kunt u de functie splitsen gebruiken om woorden te tellen in een stuk tekst. Het is duidelijk dat Microsoft Word dit automatisch doet, maar dit kan handig zijn voor een eenvoudig tekstbestand of tekst die uit een andere toepassing is gekopieerd.
| 1234567891011121314 | Sub AantalWoordenVoorbeeld()'Variabelen maken'Dim MyArray() als string, MyString als string'Voorbeeldreeks met scheidingstekens voor spatiesMyString = "Een twee drie vier vijf zes"'Verwijder dubbele spaties'MijnString = Vervangen(MijnString, " ", " ")'Verwijder alle voorloop- of volgspatiesMijnString = Trim(MijnString)'Gebruik de functie Splitsen om de samenstellende delen van de string te verdelen'MijnArray = Splitsen(MijnString)'Toon aantal woorden met de UBound-functie'MsgBox "Aantal woorden" & UBound(MyArray) + 1Einde sub |
Een van de gevaren van deze code voor het tellen van woorden is dat deze wordt gegooid door dubbele spaties en voorloop- en volgspaties. Als deze aanwezig zijn, worden ze als extra woorden geteld en wordt het aantal woorden als onnauwkeurig beschouwd.
De code gebruikt de functies Replace en Trim om deze extra spaties te verwijderen.
De laatste coderegel geeft het aantal woorden weer dat is gevonden met behulp van de UBound-functie om het maximale elementnummer van de array te krijgen en dit vervolgens met 1 te verhogen. Dit komt omdat het eerste array-element bij nul begint.
Een adres splitsen in werkbladcellen
E-mailadressen zijn vaak lange tekstreeksen met kommascheidingstekens. Misschien wilt u elk deel van het adres in een aparte cel splitsen.
| 123456789101112131415 | SubadresVoorbeeld()'Variabelen maken'Dim MyArray() als string, MyString als string, N als geheel getal'Tekenreeks instellen met Microsoft Corporation-adresMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 VS"'Gebruik de functie splitsen om de tekenreeks te delen met een kommascheidingsteken'MijnArray = Splitsen(MijnString, ",")'Wis het werkblad'ActiveSheet.UsedRange.Clear'door de array heen lopen'Voor N = 0 Naar UBound (MyArray)'Plaats elke splitsing in de eerste kolom van het werkblad'Bereik ("A" & N + 1). Waarde = MyArray (N)Volgende NEinde sub |
Als u deze code uitvoert, wordt het kommascheidingsteken gebruikt om elke regel van het adres in een afzonderlijke cel te plaatsen:
Als je alleen de postcode wilt retourneren (laatste array-element), dan kun je de code gebruiken:
| 123456789101112 | SubadresZipCodeVoorbeeld()'Variabelen maken'Dim MyArray() als string, MyString als string, N als geheel getal, Temp als string'Tekenreeks instellen met Microsoft Corporation-adresMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 VS"'Gebruik de functie splitsen om de tekenreeks te delen met een kommascheidingsteken'MijnArray = Splitsen(MijnString, ",")'Wis het werkblad'ActiveSheet.UsedRange.Clear'Zet de postcode in cel A1'Bereik ("A1"). Waarde = MyArray(UBound(MyArray))Einde sub |
Hiermee wordt alleen het laatste element in de array gebruikt, dat wordt gevonden met behulp van de UBound-functie.
Aan de andere kant wilt u misschien alle regels in één cel zien, zodat ze op een adreslabel kunnen worden afgedrukt:
| 1234567891011121314151617 | SubadresVoorbeeld()'Variabelen maken'Dim MyArray() als string, MyString als string, N als geheel getal, Temp als string'Tekenreeks instellen met Microsoft Corporation-adresMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 VS"'Gebruik de functie splitsen om de tekenreeks te delen met een kommascheidingsteken'MijnArray = Splitsen(MijnString, ",")'Wis het werkblad'ActiveSheet.UsedRange.Clear'door de array heen lopen'Voor N = 0 Naar UBound (MyArray)'plaats elk array-element plus een regelinvoerteken in een string'Temp = Temp & MyArray(N) & vbLfVolgende N'Zet het touwtje op het werkblad'Bereik ("A1") = TempEinde sub |
Dit voorbeeld werkt op dezelfde manier als het vorige, behalve dat het een tijdelijke reeks van alle array-elementen maakt, maar een regelinvoerteken invoegt na elk element.
Het werkblad ziet er als volgt uit nadat de code is uitgevoerd:
String splitsen in werkbladcellen
U kunt de Split-array naar werkbladcellen <> kopiëren met slechts één opdracht:
| 12345678910 | Sub CopyToRange()'Variabelen maken'Dim MyArray() als string, MyString als string'Voorbeeldreeks met scheidingstekens voor spatiesMyString = "Een, Twee, Drie, Vier, Vijf, Zes"'Gebruik de functie Splitsen om de samenstellende delen van de string te verdelen'MijnArray = Splitsen(MijnString, ",")'Kopieer de array naar het werkblad'Range ("A1:A" & UBound(MyArray) + 1).Waarde = WorksheetFunction.Transpose(MyArray)Einde sub |
Wanneer deze code is uitgevoerd, ziet uw werkblad er als volgt uit:
Een nieuwe functie maken om splitsen vanaf een bepaald punt toe te staan
Met de parameter Limit in de Split-functie kunt u alleen een bovengrens specificeren waar u wilt dat het splitsen stopt. Het begint altijd vanaf het begin van de string.
Het zou erg handig zijn om een vergelijkbare functie te hebben waar je het beginpunt van de splitsing binnen de string kunt specificeren, en het aantal splitsingen dat je vanaf dat punt wilt zien. Het zal ook alleen de splitsingen extraheren die u in de array hebt opgegeven, in plaats van een enorme tekenreekswaarde te hebben als het laatste element in de array.
Hiervoor kun je eenvoudig zelf een functie (SplitSlicer genaamd) bouwen in VBA:
| 123456789101112131415161718192021222324 | Functie SplitSlicer (Target As String, Del As String, Start As Integer, N As Integer)'Maak arrayvariabele'Dim MyArray() als string'Leg de splitsing vast met behulp van de startvariabele met behulp van het scheidingstekenMyArray = Splitsen (Target, Del, Start)'Controleer of de startparameter groter is dan het aantal splitsingen - dit kan problemen veroorzakenAls Start > UBound(MyArray) + 1 Dan'Fout weergeven en functie verlaten'MsgBox "Startparameter is groter dan het aantal beschikbare splitsingen"SplitSlicer = MijnArrayFunctie verlatenStop als'Zet het laatste array-element in de string'Doel = MyArray(UBound(MyArray))'Split de string met N als limiet'MyArray = Splitsen (Doel, Del, N)'Controleer of de bovengrens groter is dan nul omdat de code het laatste element verwijdert'Als UBound(MyArray) > 0 dan'Gebruik ReDim om het laatste element van de array te verwijderenReDim MyArray behouden(UBound(MyArray) - 1)Stop als'Retourneer de nieuwe array'SplitSlicer = MyArrayFunctie beëindigen |
Deze functie is gebouwd met vier parameters:
- Doelwit - string - dit is de invoerstring die je wilt splitsen
- Del - tekenreeks of niet-afdrukbaar teken - dit is het scheidingsteken dat u gebruikt, b.v. komma, dubbele punt
- Begin - nummer - dit is de startsplitsing voor uw slice
- N - aantal - dit is het aantal splitsingen dat u binnen uw segment wilt doen
Geen van deze parameters is optioneel of heeft standaardwaarden, maar u kunt dat in de code voor de functie verwerken als u deze verder wilt uitbreiden.
De functie gebruikt de functie Split om een array te maken met de parameter Start als de limiet. Dit betekent dat de array-elementen de splitsingen tot aan de startparameter zullen vasthouden, maar de rest van de string zal het laatste element zijn en niet worden gesplitst.
Het laatste element in de array wordt terug overgebracht naar een string met behulp van de UBound-functie om te bepalen welk element dit is.
De string wordt dan weer in de array gesplitst, waarbij N als limietvariabele wordt gebruikt. Dit betekent dat de string wordt gesplitst tot positie N, waarna de rest van de string het laatste element in de array zal vormen.
De ReDim-instructie wordt gebruikt om het laatste element te verwijderen, omdat we alleen de specifieke elementen in de array willen hebben. Merk op dat de parameter Preserve wordt gebruikt, anders gaan alle gegevens in de array verloren.
De nieuwe array wordt vervolgens teruggestuurd naar de code waaruit deze is aangeroepen.
Merk op dat de code 'foutbestendig' is. Gebruikers zullen vaak vreemde dingen doen waar je zelf niet aan hebt gedacht. Als ze bijvoorbeeld proberen de functie te gebruiken met de parameter Start of N groter dan het beschikbare aantal splitsingen in de tekenreeks, zal de functie waarschijnlijk mislukken.
Er is code opgenomen om de Start-waarde te controleren en ook om er zeker van te zijn dat er een element is dat kan worden verwijderd wanneer de ReDim-instructie op de array wordt gebruikt.
Hier is de code om de functie te testen:
| 123456789101112 | SubtestSplitSlicer()'Variabelen maken'Dim MyArray() als string, MyString als string'Definieer voorbeeldtekenreeks met kommascheidingstekens'MyString = "Een, Twee, Drie, Vier, Vijf, Zes, Zeven, Acht, Negen, Tien"'Gebruik de functie Splitslicer om een nieuwe array te definiëren'MyArray = SplitSlicer(MyString, ",", 4, 3)'Het actieve blad wissen'ActiveSheet.UsedRange.Clear'Kopieer de array naar het werkblad'Range ("A1:A" & UBound(MyArray) + 1).Waarde = WorksheetFunction.Transpose(MyArray)Einde sub |
Voer deze code uit en uw werkblad ziet er als volgt uit:
