Wanneer u met VBA in Excel werkt, moet u mogelijk een tekenreeks in verschillende delen splitsen op basis van een scheidingsteken.
Als u bijvoorbeeld een adres heeft, kunt u de VBA Split-functie gebruiken om verschillende delen van het adres te krijgen die worden gescheiden door een komma (wat in dit geval het scheidingsteken zou zijn).
SPLIT is een ingebouwde tekenreeksfunctie in Excel VBA die u kunt gebruiken om een tekenreeks te splitsen op basis van het scheidingsteken.
Excel VBA SPLIT-functie - Syntaxis
Splitsen ( Expressie, [Scheidingsteken], [Limiet], [Vergelijken])
- Uitdrukking: Dit is de tekenreeks die u wilt splitsen op basis van het scheidingsteken. In het geval van het adresvoorbeeld zou het volledige adres bijvoorbeeld de 'uitdrukking' zijn. In het geval dat dit een string met een lengte van nul ("") is, zou de SPLIT-functie een lege array retourneren.
- scheidingsteken: Dit is een optioneel argument. Dit is het scheidingsteken dat wordt gebruikt om het argument 'Expressie' te splitsen. In het geval van ons adresvoorbeeld is een komma een scheidingsteken dat wordt gebruikt om het adres in verschillende delen te splitsen. Als u dit argument niet opgeeft, wordt een spatie als standaardscheidingsteken beschouwd. In het geval dat u een tekenreeks met een lengte van nul ("") opgeeft, wordt de volledige tekenreeks 'Uitdrukking' geretourneerd door de functie.
- Begrenzing: Dit is een optioneel argument. Hier specificeert u het totale aantal substrings dat u wilt retourneren. Als u bijvoorbeeld alleen de eerste drie subtekenreeksen van het argument 'Uitdrukking' wilt retourneren, is dit 3. Als u dit argument niet opgeeft, is de standaardwaarde -1, wat alle subtekenreeksen retourneert.
- Vergelijken: Dit is een optioneel argument. Hier specificeert u het type vergelijking dat u wilt dat de SPLIT-functie uitvoert bij het evalueren van de substrings. De volgende opties zijn beschikbaar:
- Wanneer Vergelijken 0 . is: Dit is een binaire vergelijking. Dit betekent dat als uw scheidingsteken een tekenreeks is (laten we zeggen ABC), dit hoofdlettergevoelig is. 'ABC' zou niet gelijk zijn aan 'abc'.
- Wanneer vergelijken 1 . is: Dit is een Tekstvergelijking. Dit betekent dat als uw scheidingsteken een tekenreeks is (laten we zeggen ABC), zelfs als u 'abc' in de tekenreeks 'Expressie' heeft, het als een scheidingsteken zou worden beschouwd.
Nu we de basis van de SPLIT-functie hebben behandeld, laten we een paar praktische voorbeelden bekijken.
Voorbeeld 1 - Splits de woorden in een zin
Stel dat ik de tekst heb - "De snelle bruine vos springt over de luie hond".
Ik kan de SPLIT-functie gebruiken om elk woord van deze zin als een afzonderlijk item in een array te krijgen.
De onderstaande code zou dit doen:
Sub SplitWords() Dim TextStrng As String Dim Result() As String TextStrng = "De snelle bruine vos springt over de luie hond" Result() = Split(TextStrng) End Sub
Hoewel de code niets nuttigs doet, zal het u helpen begrijpen wat de Split-functie in VBA doet.
Split-functie splitst de tekenreeks en wijst elk woord toe aan de resultatenarray.
Dus in dit geval:
- Result(0) slaat de waarde “The” op
- Resultaat(1) slaat de waarde “Quick” op
- Result(2) slaat de waarde "Bruin" op, enzovoort.
In dit voorbeeld hebben we alleen het eerste argument gespecificeerd - dat is de tekst die moet worden gesplitst. Aangezien er geen scheidingsteken is opgegeven, wordt er een spatie als standaard scheidingsteken gebruikt.
Belangrijke notitie:
- VBA SPLIT-functie retourneert een array die begint met basis 0.
- Wanneer het resultaat van de SPLIT-functie is toegewezen aan een array, moet die array worden gedeclareerd als een String-gegevenstype. Als u het als een Variant-gegevenstype declareert, wordt er een type mismatch-fout weergegeven). Houd er in het bovenstaande voorbeeld rekening mee dat ik Result() heb gedeclareerd als een String-gegevenstype.
Voorbeeld 2 - Tel het aantal woorden in een zin
U kunt de SPLIT-functie gebruiken om het totale aantal woorden in een zin te krijgen. De truc hier is om het aantal elementen in de array te tellen dat je krijgt als je de tekst splitst.
De onderstaande code toont een berichtvenster met het aantal woorden:
Sub WordCount() Dim TextStrng As String Dim WordCount As Integer Dim Result() As String TextStrng = "De snelle bruine vos springt over de luie hond" Resultaat = Split(TextStrng) WordCount = UBound(Result()) + 1 MsgBox "The Word Count is " & WordCount End Sub
In dit geval vertelt de functie UBound ons de bovengrens van de array (d.w.z. het maximale aantal elementen dat de array heeft). Aangezien de basis van de array 0 is, wordt 1 toegevoegd om het totale aantal woorden te krijgen.
U kunt een vergelijkbare code gebruiken om een aangepaste functie in VBA te maken die de tekst als invoer gebruikt en het aantal woorden retourneert.
De onderstaande code maakt deze functie aan:
Functie WordCount(CellRef.Text) Dim TextStrng As String Dim Result() As String Result = Split(WorksheetFunction.Trim(CellRef.Text), " ") WordCount = UBound(Result()) + 1 End Function
Eenmaal gemaakt, kunt u de WordCount-functie gebruiken net als elke andere reguliere functie.
Deze functie verwerkt ook voorloop-, volg- en dubbele spaties tussen woorden. Dit is mogelijk gemaakt door de TRIM-functie in de VBA-code te gebruiken.
Als je meer wilt weten over hoe deze formule werkt om het aantal woorden in een zin te tellen of als je meer wilt weten over een niet-VBA-formule om het aantal woorden te krijgen, bekijk dan deze tutorial.
Voorbeeld 3 - Een ander scheidingsteken gebruiken dan een spatieteken
In de vorige twee voorbeelden hebben we slechts één argument gebruikt in de SPLIT-functie en de rest waren de standaardargumenten.
Wanneer u een ander scheidingsteken gebruikt, moet u dat specificeren in de SPLIT-formule.
In de onderstaande code retourneert de SPLIT-functie een array op basis van een komma als scheidingsteken en toont vervolgens een bericht met elk woord op een afzonderlijke regel.
Sub CommaSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "The,Quick,Brown,Fox,Jump,Over,The,Lazy,Dog" Result = Split(TextStrng, ",") For i = LBound(Result()) To UBound(Result()) DisplayText = DisplayText & Result(i) & vbNewLine Volgende i MsgBox DisplayText End Sub
In de bovenstaande code heb ik de For Next-lus gebruikt om door elk element van de array 'Result' te gaan en het toe te wijzen aan de variabele 'DisplayText'.
Voorbeeld 4 - Verdeel een adres in drie delen
Met de SPLIT-functie kunt u aangeven hoeveel aantallen splitsingen u wilt krijgen. Als ik bijvoorbeeld niets opgeef, zou elke instantie van het scheidingsteken worden gebruikt om de tekenreeks te splitsen.
Maar als ik 3 opgeef als de limiet, wordt de string alleen in drie delen gesplitst.
Als ik bijvoorbeeld het volgende adres heb:
2703 Winifred Way, Indianapolis, Indiana, 46204
Ik kan de Split-functie in VBA gebruiken om dit adres in drie delen te verdelen.
Het splitst de eerste twee op basis van het komma-scheidingsteken en het resterende deel wordt het derde element van de array.
De onderstaande code toont het adres in drie verschillende regels in een berichtvenster:
Sub CommaSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Resultaat = Split(TextStrng, ",", 3) For i = LBound(Result( )) Naar UBound(Result()) DisplayText = DisplayText & Result(i) & vbNewLine Volgende i MsgBox DisplayText End Sub
Een van de praktische toepassingen hiervan kan zijn wanneer u een adres met één regel wilt verdelen in het formaat dat wordt weergegeven in het berichtvenster. Vervolgens kunt u een aangepaste functie maken die het adres retourneert in drie delen (met elk deel op een nieuwe regel).
De volgende code zou dit doen:
Functie ThreePartAddress(cellRef As Range) Dim TextStrng As String Dim Result() As String Dim DisplayText As String Result = Split(cellRef, ",", 3) For i = LBound(Result()) To UBound(Result()) DisplayText = DisplayText & Trim(Result(i)) & vbNewLine Next i ThreePartAddress = Mid(DisplayText, 1, Len(DisplayText) - 1) End Function
Zodra u deze code in de module hebt, kunt u de functie (ThreePartAddress) in de werkmap gebruiken, net als elke andere Excel-functie.
Deze functie heeft één argument nodig: de celverwijzing met het adres.
Houd er rekening mee dat om het resulterende adres in drie verschillende regels te laten verschijnen, u de tekstomloopopmaak op de cellen moet toepassen (dit staat op het tabblad Start in de groep Uitlijning). Als het formaat 'Wrap Text' niet is ingeschakeld, ziet u het volledige adres als één enkele regel.
Voorbeeld 5 - Haal de plaatsnaam uit het adres
Met de Split-functie in VBA kunt u specificeren welk deel van de resulterende array u wilt gebruiken.
Stel dat ik het volgende adres splits op basis van de komma als scheidingsteken:
2703 Winifred Way, Indianapolis, Indiana, 46204
De resulterende array zou er ongeveer zo uitzien als hieronder weergegeven:
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
Aangezien dit een array is, kan ik ervoor kiezen om een specifiek deel van deze array weer te geven of te retourneren.
Hieronder staat een code voor een aangepaste functie, waar u een getal kunt opgeven en dat element uit de array retourneert. Als ik bijvoorbeeld de staatsnaam wil, kan ik 3 specificeren (omdat dit het derde element in de array is).
Functie ReturnNthElement(CellRef As Range, ElementNumber As Integer) Dim Result() As String Resultaat = Split(CellRef, ",") ReturnNthElement = Result(ElementNumber - 1) End Function
De bovenstaande functie heeft twee argumenten, de celverwijzing met het adres en het elementnummer dat u wilt retourneren. De functie Split splitst de adreselementen en wijst deze toe aan de variabele Resultaat.
Vervolgens wordt het elementnummer geretourneerd dat u hebt opgegeven als het tweede argument. Merk op dat aangezien de basis 0 is, ElementNumber-1 wordt gebruikt om het juiste deel van het adres te retourneren.
Als u de plaatsnaam wilt, kunt u 2 als tweede argument gebruiken. Als u een getal gebruikt dat hoger is dan het totale aantal elementen, wordt de #WAARDE! fout.
U kunt de code verder vereenvoudigen zoals hieronder wordt weergegeven:
Functie ReturnNthElement(CellRef As Range, ElementNumber As Integer) ReturnNthElement = Split(CellRef, ",")(ElementNumber - 1) End Function
In de bovenstaande code wordt, in plaats van de variabele Resultaat te gebruiken, alleen het opgegeven elementnummer geretourneerd.
Dus als je Split ("Good Morning") (0) hebt, zou het alleen het eerste element retourneren, dat "Good" is.
Evenzo retourneert het in de bovenstaande code alleen het opgegeven elementnummer.
