Kun työskentelet VBA: n kanssa Excelissä, saatat joutua jakamaan merkkijonon eri osiin erottimen perusteella.
Jos sinulla on esimerkiksi osoite, voit käyttää VBA Split -toimintoa osoitteen eri osien erottamiseen toisistaan pilkulla (joka olisi tässä tapauksessa erotin).
SPLIT on Excel VBA: n sisäänrakennettu merkkijonotoiminto, jonka avulla voit jakaa tekstimerkkijonon erottimen perusteella.
Excel VBA SPLIT -toiminto - syntaksi
Jaa (lauseke, [erotin], [raja], [vertaa])
- Ilmaisu: Tämä on merkkijono, jonka haluat jakaa erottimen perusteella. Esimerkiksi osoiteesimerkin tapauksessa koko osoite olisi "lauseke". Jos kyseessä on nollapituinen merkkijono (“”), SPLIT-funktio palauttaa tyhjän taulukon.
- Erotin: Tämä on valinnainen argumentti. Tämä on erotin, jota käytetään lausekkeen argumentin jakamiseen. Osoite -esimerkissämme pilkku on erotin, jota käytetään osoitteen jakamiseen eri osiin. Jos et määritä tätä argumenttia, välilyöntiä pidetään oletuserottimena. Jos annat nollapituisen merkkijonon (“”), funktio palauttaa koko lauseke-merkkijonon.
- Raja: Tämä on valinnainen argumentti. Tässä määrität palautettavien alimerkkijonojen kokonaismäärän. Jos esimerkiksi haluat palauttaa vain lausekkeen argumentin kolme ensimmäistä alimerkkijonoa, tämä olisi 3. Jos et määritä tätä argumenttia, oletusarvo on -1, joka palauttaa kaikki alimerkkijonot.
- Vertailla: Tämä on valinnainen argumentti. Tässä määrität vertailun tyypin, jonka haluat SPLIT -funktion suorittavan alijonoja arvioitaessa. Seuraavat vaihtoehdot ovat käytettävissä:
- Kun Vertaa on 0: Tämä on binäärinen vertailu. Tämä tarkoittaa sitä, että jos erottimesi on tekstimerkkijono (sanotaan ABC), tämä merkitsee isoja ja pieniä kirjaimia. "ABC" ei olisi yhtä kuin "abc".
- Kun Vertaa on 1: Tämä on tekstin vertailu. Tämä tarkoittaa sitä, että jos erotin on tekstimerkkijono (sanotaan esimerkiksi ABC), vaikka se olisi "abc" lausekkeen merkkijonossa, se katsotaan erottimeksi.
Nyt kun olemme käsitelleet SPLIT -toiminnon perusteet, katsotaan muutamia käytännön esimerkkejä.
Esimerkki 1 - Jaa sanat lauseeseen
Oletetaan, että minulla on teksti - "Nopea ruskea kettu hyppää laiskan koiran yli".
Voin käyttää SPLIT -funktiota saadakseni tämän lauseen jokaisen sanan erilliseksi matriisin kohteeksi.
Alla oleva koodi olisi tämä:
Sub SplitWords () Dim TextStrng merkkijonona Dim Tulos () Kuten merkkijono TextStrng = "Nopea ruskea kettu hyppää laiskan koiran yli" Result () = Split (TextStrng) End Sub
Vaikka koodi ei tee mitään hyödyllistä, se auttaa sinua ymmärtämään, mitä VBA: n Split -toiminto tekee.
Split -toiminto jakaa tekstimerkkijonon ja määrittää jokaisen sanan Result -matriisille.
Joten tässä tapauksessa:
- Tulos (0) tallentaa arvon "The"
- Tulos (1) tallentaa arvon "Nopea"
- Tulos (2) tallentaa arvon "Ruskea" ja niin edelleen.
Tässä esimerkissä olemme määrittäneet vain ensimmäisen argumentin - joka on jaettava teksti. Koska erottimia ei ole määritetty, se käyttää välilyöntiä oletuserottimena.
Tärkeä muistiinpano:
- VBA SPLIT -funktio palauttaa taulukon, joka alkaa tukiasemasta 0.
- Kun SPLIT -funktion tulos on määritetty taulukolle, kyseinen taulukko on ilmoitettava merkkijonotyypiksi. Jos ilmoitat sen vaihtoehtotietotyypiksi, se näyttää tyyppivirhevirheen). Huomaa yllä olevassa esimerkissä, että olen ilmoittanut Result () merkkijonotyypiksi.
Esimerkki 2 - Laske lauseessa olevien sanojen määrä
Voit käyttää SPLIT -funktiota saadaksesi lauseen sanojen kokonaismäärän. Temppu tässä on laskea matriisin elementtien lukumäärä, jonka saat, kun jaat tekstin.
Alla oleva koodi näyttäisi sanomakentän sanamäärällä:
Sub WordCount () Dim TextStrng merkkijonona Dim WordCount kokonaislukuna Dim Result () String TextStrng = "Nopea ruskea kettu hyppää laiskan koiran yli" Result = Split (TextStrng) WordCount = UBound (Result ()) + 1 MsgBox Sanamäärä on "& WordCount End Sub
Tässä tapauksessa UBound -funktio kertoo meille taulukon ylärajan (eli enimmäismäärän matriisin elementtejä). Koska taulukon pohja on 0, 1 lisätään sanojen kokonaismäärän saamiseksi.
Voit käyttää samanlaista koodia luodaksesi mukautetun toiminnon VBA: han, joka ottaa tekstin syötteeksi ja palauttaa sanamäärän.
Alla oleva koodi luo tämän toiminnon:
Funktio WordCount (CellRef As Range) Dim TextStrng merkkijonona Dim Tulos () String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Function
Kun olet luonut, voit käyttää WordCount -toimintoa aivan kuten mitä tahansa muuta tavallista toimintoa.
Tämä toiminto käsittelee myös sanojen välissä olevia etu-, loppu- ja kaksoisvälejä. Tämä on tehty mahdolliseksi käyttämällä TRIM -toimintoa VBA -koodissa.
Jos haluat oppia lisää tämän kaavan toiminnasta lauseen sanamäärän laskemiseksi tai haluat tietää muun kuin VBA-kaavan tavan saada sanamäärä, tutustu tähän opetusohjelmaan.
Esimerkki 3 - Muun rajaajan kuin välilyönnin käyttäminen
Kahdessa edellisessä esimerkissä olemme käyttäneet vain yhtä argumenttia SPLIT -funktiossa ja loput olivat oletusargumentteja.
Kun käytät jotakin muuta erotinta, sinun on määritettävä se SPLIT -kaavassa.
Alla olevassa koodissa SPLIT -funktio palauttaa erottimena pilkkuun perustuvan taulukon ja näyttää sitten viestin, jossa jokainen sana on erillisellä rivillä.
Sub CommaSeparator () Dim TextStrng merkkijonona Dim Tulos () Jono himmeänä DisplayText As String TextStrng = "The, Quick, Brown, Fox, Jump, Over, The, Lazy, Dog" Result = Split (TextStrng, ",") For i = LBound (tulos ()) - UBound (tulos ()) DisplayText = DisplayText & Result (i) & vbNewLine Seuraava i MsgBox DisplayText End Sub
Yllä olevassa koodissa olen käyttänyt For Next -silmukkaa käymään läpi jokaisen Result -matriisin elementin ja määritä se DisplayText -muuttujalle.
Esimerkki 4 - Jaa osoite kolmeen osaan
SPLIT -toiminnolla voit määrittää, kuinka monta jakoa haluat saada. Jos esimerkiksi en määritä mitään, jokaista erottimen esiintymää käytetään merkkijonon jakamiseen.
Mutta jos määritän rajaksi 3, merkkijono jaetaan vain kolmeen osaan.
Jos minulla on esimerkiksi seuraava osoite:
2703 Winifred Way, Indianapolis, Indiana, 46204
Voin käyttää VBA: n Split -toimintoa tämän osoitteen jakamiseen kolmeen osaan.
Se jakaa kaksi ensimmäistä pilkkuerottimen perusteella ja jäljellä olevasta osasta tulee taulukon kolmas elementti.
Alla oleva koodi näyttäisi osoitteen kolmella eri rivillä viestiruudussa:
Sub CommaSeparator () Dim TextStrng merkkijonona Dim Tulos () Jono himmeänä DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Result = Split (TextStrng, ",", 3) For i = LBound (Result ( )) To UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub
Yksi tämän käytännön käyttötarkoituksista voisi olla se, kun haluat jakaa yhden rivin osoitteen viesti -ruudussa näkyvään muotoon. Sitten voit luoda mukautetun funktion, joka palauttaa osoitteen jaettuna kolmeen osaan (jokainen osa uudella rivillä).
Seuraava koodi toimisi näin:
Funktio ThreePartAddress (cellRef As Range) Dim TextStrng merkkijonona Dim Result () merkkijono himmennettynä DisplayText merkkijonona Result = Split (cellRef, ",", 3) For i = LBound (Result ()) to UBound (Result ()) DisplayText = DisplayText & Trim (Tulos (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) End Function
Kun tämä koodi on moduulissa, voit käyttää työkirjan toimintoa (ThreePartAddress) aivan kuten mitä tahansa muuta Excel -toimintoa.
Tämä toiminto ottaa yhden argumentin - soluviittauksen, jolla on osoite.
Huomaa, että jotta tuloksena oleva osoite näkyy kolmella eri rivillä, sinun on käytettävä solun kääretekstimuotoa (se on Tasaus -ryhmän Aloitus -välilehdessä). Jos Wrap Text -muoto ei ole käytössä, näet koko osoitteen yhtenä rivinä.
Esimerkki 5 - Hae kaupungin nimi osoitteesta
VBA: n Split -toiminnolla voit määrittää, mitä osaa tuloksena olevasta taulukosta haluat käyttää.
Oletetaan esimerkiksi, että jaan seuraavan osoitteen erottajana olevan pilkun perusteella:
2703 Winifred Way, Indianapolis, Indiana, 46204
Tuloksena oleva taulukko näyttäisi jotain alla olevan kuvan mukaisesti:
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
Koska tämä on taulukko, voin näyttää tai palauttaa tietyn osan tästä taulukosta.
Alla on mukautetun funktion koodi, jossa voit määrittää numeron ja se palauttaa kyseisen elementin taulukosta. Jos esimerkiksi haluan valtion nimen, voin määrittää 3 (koska se on taulukon kolmas elementti).
Funktio ReturnNthElement (CellRef as Range, ElementNumber as Integer) Dim Dim Result () String Result = Split (CellRef, ",") ReturnNthElement = Result (ElementNumber - 1) End Function
Yllä oleva funktio sisältää kaksi argumenttia, soluviittauksen, jossa on osoite ja palautettavan elementin numeron. Split -toiminto jakaa osoiteelementit ja määrittää sen Result -muuttujalle.
Sitten se palauttaa elementin numeron, jonka määritit toiseksi argumentiksi. Huomaa, että koska perusta on 0, ElementNumber-1: tä käytetään osoitteen oikean osan palauttamiseen.
Jos haluat kaupungin nimen, voit käyttää 2: ta toisena argumenttina. Jos käytät numeroa, joka on suurempi kuin elementtien kokonaismäärä, se palauttaa #ARVO! virhe.
Voit yksinkertaistaa koodia edelleen alla esitetyllä tavalla:
Funktio ReturnNthElement (CellRef as Range, ElementNumber as Integer) ReturnNthElement = Split (CellRef, ",") (ElementNumber - 1) End Function
Yllä olevassa koodissa tulosmuuttujan käyttämisen sijaan se palauttaa vain määritetyn elementin numeron.
Joten jos sinulla on Split ("Hyvää huomenta") (0), se palauttaa vain ensimmäisen elementin, joka on "Hyvä".
Samoin yllä olevassa koodissa se palauttaa vain määritetyn elementin numeron.
