VBA Split -toiminnon käyttäminen
VBA Split -toiminnon avulla voit erottaa komponentit vakiotekstimerkkijonosta, jossa jokainen komponentti käyttää tiettyä erotinmerkkiä, esim. pilkku tai kaksoispiste. Se on helpompi käyttää kuin koodin kirjoittaminen etsimään merkkijonosta erotinmerkit ja purkamaan sitten arvot.
Sitä voidaan käyttää, jos luet riviä pilkulla erotetusta arvosta (CSV-tiedosto) tai sinulla on postiosoite, joka on kaikki yhdellä rivillä, mutta haluat nähdä sen useina riveinä.
Syntaksi on:
| 1 | Jaa lauseke, erotin [valinnainen], raja [valinnainen], vertaa [valinnainen] |
VBA Split -toiminnolla on neljä parametria:
- Ilmaisu - Tekstijono, jonka haluat jakaa eri osiin.
- Erotin (valinnainen)- merkkijono tai ei -tulostettava merkki - Määrittää erotinmerkin, jota käytetään jaossa. Jos erotinmerkkiä ei ole annettu, käytetään välilyönnin oletusarvoa.
- Raja (valinnainen) - numero - Määrittää kuinka monta jakoa tehdään. Jos tyhjä, kaikki käytettävissä olevat halkeamat tehdään merkkijonon sisällä. Jos se on asetettu arvoon 1, halkaisuja ei tehdä. Periaatteessa sen avulla voit erottaa tietyn määrän arvoja, jotka alkavat merkkijonon alussa, esim. jossa merkkijono on erittäin pitkä ja tarvitset vain kolme ensimmäistä halkaisua.
- Vertailla (valinnainen) - Jos erotin on tekstimerkki, sitä käytetään vaihtamaan, onko erotin kirjainkoolla vai ei. Arvot ovat vbBinaryCompare (isot ja pienet kirjaimet) ja vbTextCompare (eivät erota kirjaimia).
Split -funktio palauttaa aina taulukon.
Yksinkertainen esimerkki Split -toiminnosta
| 123456789101112 | AlajakoEsimerkki ()'Määrittele muuttujatDim MyArray () merkkijonona, MyString merkkijonona, minä vaihtoehtona'Näytejono, jossa on välilyöntejäMyString = "Yksi kaksi kolme neljä"'Käytä Split -toimintoa merkkijonon osien jakamiseenMyArray = Jaettu (MyString)'iteroi jokaisen arvon näyttämiseksi muodostetun taulukon läpiJokaiselle I In MyArraylleViestilaatikko I.Seuraavaksi minäEnd Sub |
Tässä esimerkissä erotinta ei ole määritetty, koska kaikkien sanojen välissä on väli, joten oletuserotinta (välilyöntiä) voidaan käyttää.
Taulukolla ei ole mittoja, ja se asetetaan merkkijonoksi. Muuttuja I, jota käytetään For… Next -silmukassa, on mitoitettava muunnelmana.
Kun tämä koodi suoritetaan, se näyttää neljä viestilaatikkoa, yksi kullekin jaolle, esim. Yksi kaksi kolme. Neljä.
Huomaa, että jos merkkijonon sanojen välissä on kaksinkertainen välilyönti, tämä arvioidaan jakautuneeksi, vaikka siinä ei ole mitään. Tämä ei ehkä ole tulos, jonka haluat nähdä.
Voit korjata tämän ongelman korvaamalla kaksoisvälit yhdellä välilyönnillä Korvaa -toiminnolla:
| 1 | MyString = Korvaa (MyString, "", "") |
Jäljessä oleva tai johtava tila voi myös aiheuttaa ongelmia tuottamalla tyhjän jaon. Näitä on usein erittäin vaikea nähdä. Voit poistaa nämä ylimääräiset tilat käyttämällä Trim -toimintoa:
| 1 | MyString = Leikkaa (MyString) |
Jaetun toiminnon käyttäminen erotinmerkin kanssa
Voimme käyttää puolipisteen erotinta (;). Tämä löytyy usein sähköpostiosoitteiden merkkijonoista osoitteiden erottamiseksi. Saatat saada sinulle sähköpostiviestin, joka on jaettu useiden kollegoidesi kanssa, ja haluat nähdä laskentataulukossasi luettelon siitä, kenelle se on lähetetty. Voit helposti kopioida sähköpostiosoitteet sähköpostin Vastaanottaja- tai Kopioi -ruuduista koodiin.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Määrittele muuttujatDim MyArray () merkkijonona, myString merkkijonona, minä vaihtoehtona, N kokonaislukuna'Näytteen merkkijono puolipiste -erottimillaOmaString = "[email protected]; [email protected]; [email protected]; [email protected]"'Käytä Split -toimintoa merkkijonon osien jakamiseenMyArray = Jaettu (MyString, ";")'Tyhjennä laskentataulukkoActiveSheet.UsedRange.Clear'iteroi taulukon läpiN = 0 UBoundille (MyArray)'Aseta jokainen sähköpostiosoite laskentataulukon ensimmäiseen sarakkeeseenAlue ("A" & N + 1). Arvo = MyArray (N)Seuraava N.End Sub |
Huomaa, että For… Next -silmukkaa käytetään iteroimaan taulukon läpi. Taulukon ensimmäinen elementti alkaa aina nollasta, ja Yläraja -funktiota käytetään suurimman elementtimäärän saamiseen.
Tämän koodin suorittamisen jälkeen laskentataulukosi näyttää tältä:
Rajaparametrin käyttäminen jakotoiminnossa
Rajaparametri sallii tietyn määrän jakoja merkkijonon alusta. Valitettavasti et voi antaa aloitusasentoa tai useita halkeamia, joten se on melko yksinkertainen. Voit rakentaa oman VBA -koodisi toiminnon luomiseksi tätä varten, ja tämä selitetään myöhemmin tässä artikkelissa.
| 123456789101112131415 | AlajakoWithLimitExample ()'Luo muuttujiaDim MyArray () merkkijonona, myString merkkijonona, minä vaihtoehtona, N kokonaislukuna'Näytteen merkkijono pilkkuerottimillaMyString = "Yksi, kaksi, kolme, neljä, viisi, kuusi"'Käytä Split -toimintoa merkkijonon osien jakamiseenMyArray = Jaettu (MyString, ",", 4)'Tyhjennä laskentataulukkoActiveSheet.UsedRange.Clear'Toista taulukon läpiN = 0 UBoundille (MyArray)'Aseta jokainen jako laskentataulukon ensimmäiseen sarakkeeseenAlue ("A" & N + 1). Arvo = MyArray (N)Seuraava N.End Sub |
Kun olet suorittanut tämän koodin, laskentataulukkosi näyttää tältä:
Vain kolme ensimmäistä jakoarvoa näytetään erikseen. Kolme viimeistä arvoa näytetään yhtenä pitkänä merkkijonona, eivätkä ne jakaudu.
Jos valitset raja -arvon, joka on suurempi kuin merkkijonon erottimien määrä, tämä ei aiheuta virhettä. Merkkijono jaetaan kaikkiin sen osiin ikään kuin raja -arvoa ei olisi annettu.
Vertailuparametrin käyttäminen Split -toiminnossa
Vertaa-parametri määrittää, onko erottimessa kirjainkoon ero vai ei. Tätä ei sovelleta, jos erottimet ovat pilkkuja, puolipisteitä tai kaksoispisteitä.
Huomautus: Sen sijaan voit aina sijoittaa vaihtoehdon vertailuteksti <> moduulin yläosaan, jotta koko moduulin kirjainkoon ero ei poistu.
| 123456789101112131415 | Sub SplitByCompareExample ()'Luo muuttujiaDim MyArray () merkkijonona, myString merkkijonona, minä vaihtoehtona, N kokonaislukuna'Esimerkkijono, jossa on X -erotinMyString = "OneXTwoXThreexFourXFivexSix"'Käytä Split -toimintoa merkkijonon osien jakamiseenMyArray = Jaettu (MyString, "X",, vbBinaryCompare)'Tyhjennä laskentataulukkoActiveSheet.UsedRange.Clear'iteroi taulukon läpiN = 0 UBoundille (MyArray)'Aseta jokainen jako laskentataulukon ensimmäiseen sarakkeeseenAlue ("A" & N + 1). Arvo = MyArray (N)Seuraava N.End Sub |
Tässä esimerkissä jaettava merkkijono käyttää X -merkkiä erottimena. Tässä merkkijonossa on kuitenkin sekoitus isoja ja pieniä X -merkkejä. Split -toiminnon Vertaa -parametri käyttää isoja kirjaimia "X".
Jos Vertaa -parametriksi on asetettu vbBinaryCompare, pienet x -merkit jätetään huomiotta ja laskentataulukosi näyttää tältä:
Jos Vertaa -parametrin arvoksi on asetettu vbTextCompare, jaossa käytetään pieniä x -merkkejä ja laskentataulukkosi näyttää tältä:
Huomaa, että solun A6 arvo katkaistaan, koska se sisältää pienen x -merkin. Koska jaossa ei ole kirjainkoon eroa, kaikki alimerkkijonon muodostavat erotimet aiheuttavat jaon.
Tämä on tärkeää pitää mielessä käytettäessä tekstin erotinta ja vbTextCompare. Voit helposti päätyä väärään tulokseen.
Tulostamattomien merkkien käyttäminen erotinmerkkinä
Voit käyttää erottimena muita kuin tulostettavia merkkejä, kuten vaunun paluuta (rivinvaihto).
Tässä käytämme vbCr: tä vaunun palautuksen <> määrittämiseen
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Luo muuttujiaDim MyArray () merkkijonona, myString merkkijonona, minä vaihtoehtona, N kokonaislukuna'Näytejono, jossa on vaunun palautusrajatMyString = "Yksi" & vbCr & "Kaksi" & vbCr & "Kolme" & vbCr & "Neljä" & vbCr & "Viisi" & vbCr & "Kuusi"'Käytä Split -toimintoa merkkijonon osien jakamiseenMyArray = Jaettu (MyString, vbCr,, vbTextCompare)'Tyhjennä laskentataulukkoActiveSheet.UsedRange.Clear'Toista taulukon läpiN = 0 UBoundille (MyArray)'Aseta jokainen jako laskentataulukon ensimmäiseen sarakkeeseenAlue ("A" & N + 1). Arvo = MyArray (N)Seuraava N.End Sub |
Tässä esimerkissä merkkijono muodostetaan käyttämällä erottimena vbCr (kelkan paluumerkki).
Kun tämä koodi suoritetaan, laskentataulukkosi näyttää tältä:
Liittotoiminnon käyttäminen jaon kääntämiseen
Liitä-toiminto yhdistää kaikki taulukon elementit uudelleen, mutta käyttämällä määritettyä erotinta. Jos erotinmerkkiä ei ole määritetty, käytetään välilyöntiä.
| 123456789101112131415 | Alaliittymäesimerkki ()'Luo muuttujiaDim MyArray () merkkijonona, myString merkkijonona, minä vaihtoehtona, N kokonaislukunaHimmennä kohde merkkijonona'Näytteen merkkijono pilkkuerottimillaMyString = "Yksi, kaksi, kolme, neljä, viisi, kuusi"'Aseta MyString soluun A1Alue ("A1"). Arvo = Oma merkkijono'Käytä Split -toimintoa merkkijonon osien jakamiseenMyArray = Jaettu (MyString, ",")Luo alkuperäinen merkkijono käyttämällä puolipiste-erotinta yhdistämistoiminnon avullaKohde = Liity (MyArray, ”;”)'Aseta tulosjono soluun A2Alue ("A2"). Arvo = tavoiteEnd Sub |
Tämä koodi jakaa pilkkuerottimilla varustetun merkkijonon matriisiksi ja yhdistää sen takaisin puolipiste-erottimilla.
Tämän koodin suorittamisen jälkeen laskentataulukosi näyttää tältä:
Solussa A1 on alkuperäinen merkkijono, jossa on pilkkuerottimet, ja solussa A2 on uusi yhdistetty merkkijono, jossa on puolipiste-erotin.
Jaa -funktion käyttäminen sanamäärän laskemiseen
Pidä mielessä, että Excel VBA: n merkkijonomuuttuja voi olla jopa 2 Gt pitkä, voit käyttää jakotoimintoa sanojen laskemiseen tekstikappaleessa. On selvää, että Microsoft Word tekee sen automaattisesti, mutta tästä voi olla hyötyä yksinkertaiselle tekstitiedostolle tai toisesta sovelluksesta kopioidulle tekstille.
| 1234567891011121314 | AlanumeroOfWordsExample ()'Luo muuttujiaDim MyArray () merkkijonona, MyString merkkijonona'Näytejono, jossa on välilyöntejäMyString = "Yksi kaksi Kolme Neljä Viisi Kuusi"'Poista mahdolliset kaksoisvälitMyString = Korvaa (MyString, "", "")'Poista kaikki edessä tai takana olevat tilatMyString = Leikkaa (MyString)'Käytä Split -toimintoa merkkijonon osien jakamiseenMyArray = Jaettu (MyString)'Näytä sanamäärä UBound -toiminnollaMsgBox "Sanojen määrä" ja UBound (MyArray) + 1End Sub |
Yksi tämän sanamäärän koodin vaaroista on se, että kaksoisvälit sekä edessä ja takana olevat välilyönnit heittävät sen. Jos nämä ovat läsnä, ne lasketaan ylimääräisiksi sanoiksi ja sanamäärä päättyy virheelliseksi.
Koodi käyttää korvaus- ja leikkaustoimintoja näiden ylimääräisten välilyöntien poistamiseen.
Viimeinen koodirivi näyttää sanojen määrän, jotka on löydetty käyttämällä UBound -funktiota taulukon maksimielementtien lukumäärän saamiseksi ja sitten lisäämällä sitä yhdellä. Tämä johtuu siitä, että ensimmäinen taulukkoelementti alkaa nollasta.
Osoitteen jakaminen laskentataulukon soluiksi
Sähköpostiosoitteet ovat usein pitkiä tekstijonoja, joissa on pilkkuerottimet. Haluat ehkä jakaa osoitteen jokaisen osan erilliseen soluun.
| 123456789101112131415 | AlaosoiteEsimerkki ()'Luo muuttujiaDim MyArray () merkkijonona, MyString merkkijonona, N kokonaislukuna'Määritä merkkijono Microsoft Corporation -osoitteellaMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Jaa jakotoiminnolla merkkijono pilkkuerottimellaMyArray = Jaettu (MyString, ",")'Tyhjennä laskentataulukkoActiveSheet.UsedRange.Clear'iteroi taulukon läpiN = 0 UBoundille (MyArray)'Aseta jokainen jako laskentataulukon ensimmäiseen sarakkeeseenAlue ("A" & N + 1). Arvo = MyArray (N)Seuraava N.End Sub |
Tämän koodin suorittaminen käyttää pilkkuerotinta osoitteen jokaisen rivin sijoittamiseen erilliseen soluun:
Jos haluat palauttaa vain postinumeron (viimeinen taulukkoelementti), voit käyttää koodia:
| 123456789101112 | AlaosoiteZipCodeExample ()'Luo muuttujiaDim MyArray () merkkijonona, merkkijono merkkijonona, N kokonaislukuna, temp merkkijonona'Määritä merkkijono Microsoft Corporation -osoitteellaMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Jaa jakotoiminnolla merkkijono pilkkuerottimellaMyArray = Jaettu (MyString, ",")'Tyhjennä laskentataulukkoActiveSheet.UsedRange.Clear'Kirjoita postinumero soluun A1Alue ("A1"). Arvo = MyArray (UBound (MyArray))End Sub |
Tämä käyttää vain taulukon viimeistä elementtiä, joka löydetään käyttämällä UBound -toimintoa.
Toisaalta saatat haluta nähdä kaikki rivit yhdessä solussa, jotta ne voidaan tulostaa osoitetarraan:
| 1234567891011121314151617 | AlaosoiteEsimerkki ()'Luo muuttujiaDim MyArray () merkkijonona, merkkijono merkkijonona, N kokonaislukuna, temp merkkijonona'Määritä merkkijono Microsoft Corporation -osoitteellaMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Jaa jakotoiminnolla merkkijono pilkkuerottimellaMyArray = Jaettu (MyString, ",")'Tyhjennä laskentataulukkoActiveSheet.UsedRange.Clear'iteroi taulukon läpiN = 0 UBoundille (MyArray)'aseta jokainen taulukkoelementti ja rivinsyöttömerkki merkkijonoonLämpötila = Lämpötila & MyArray (N) & vbLfSeuraava N.'Laita merkkijono laskentataulukkoonAlue ("A1") = LämpötilaEnd Sub |
Tämä esimerkki toimii samalla tavalla kuin edellinen, paitsi että se luo väliaikaisen merkkijonon kaikista matriisielementeistä, mutta lisää rivinsiirtomerkin jokaisen elementin perään.
Laskentataulukko näyttää tältä koodin suorittamisen jälkeen:
Jaa merkkijono laskentataulukon soluiksi
Voit kopioida Split -taulukon laskentataulukon soluiksi <> yhdellä komennolla:
| 12345678910 | Sub CopyToRange ()'Luo muuttujiaDim MyArray () merkkijonona, MyString merkkijonona'Näytejono, jossa on välilyöntejäMyString = "Yksi, kaksi, kolme, neljä, viisi, kuusi"'Käytä Split -toimintoa merkkijonon osien jakamiseenMyArray = Jaettu (MyString, ",")Kopioi taulukko laskentataulukkoonAlue ("A1: A" & UBound (MyArray) + 1). Arvo = WorksheetFunction.Transpose (MyArray)End Sub |
Kun tämä koodi on suoritettu, laskentataulukkosi näyttää tältä:
Uuden toiminnon luominen jakamisen sallimiseksi tietystä pisteestä
Split -toiminnon Limit -parametrin avulla voit määrittää vain ylärajan, jossa halkaisu halutaan lopettaa. Se alkaa aina merkkijonon alusta.
Olisi erittäin hyödyllistä, jos sinulla olisi samanlainen toiminto, jossa voit määrittää merkkijonon jakamisen aloituspisteen ja halkaisujen määrän, jotka haluat nähdä siitä lähtien. Se myös purkaa vain määrittämäsi halkeamat taulukkoon sen sijaan, että sillä olisi valtava merkkijonoarvo taulukon viimeisenä elementtinä.
Voit helposti rakentaa toiminnon (nimeltään SplitSlicer) itse VBA: ssa tätä varten:
| 123456789101112131415161718192021222324 | Toiminto SplitSlicer (kohde merkkijonona, del merkkijonona, aloita kokonaislukuna, N kokonaislukuna)'Luo taulukon muuttujaDim MyArray () merkkijonona'Kaappaa jako käyttämällä aloitusmuuttujaa käyttämällä erotinmerkkiäMyArray = Jaettu (Kohde, Del, Aloita)"Tarkista, onko aloitusparametri suurempi kuin halkaisujen määrä - tämä voi aiheuttaa ongelmiaJos Käynnistä> UBound (MyArray) + 1 Sitten'Näyttövirhe ja poistu toiminnostaMsgBox "Aloitusparametri on suurempi kuin käytettävissä olevien jakojen määrä"SplitSlicer = MyArrayLopeta toimintoLoppu Jos'Laita viimeinen taulukkoelementti merkkijonoonKohde = MyArray (UBound (MyArray))'Jaa merkkijono käyttäen rajana NMyArray = Jaettu (kohde, Del, N)"Tarkista, että yläraja on suurempi kuin nolla, kun koodi poistaa viimeisen elementinJos UBound (MyArray)> 0 Sitten'Poista ReDimin avulla taulukon viimeinen elementtiReDim Preserve MyArray (UBound (MyArray) - 1)Loppu Jos'Palauta uusi taulukkoSplitSlicer = MyArrayLopeta toiminto |
Tämä toiminto perustuu neljään parametriin:
- Kohde - merkkijono - tämä on syöttöjono, jonka haluat jakaa
- Del - merkkijono tai ei -tulostettava merkki - tämä on erotinmerkki, jota käytät esim. pilkku, kaksoispiste
- alkaa - numero - tämä on viipaleesi aloitusjako
- N - numero - tämä on halkaisujen määrä, jotka haluat tehdä viipaleesi sisällä
Mikään näistä parametreista ei ole valinnainen tai niillä on oletusarvoja, mutta voit lisätä ne funktion koodiin, jos haluat laajentaa sitä edelleen.
Funktio käyttää Split -funktiota taulukon luomiseen käyttämällä Limit -parametria Start. Tämä tarkoittaa, että matriisielementit pitävät halkeamat aloitusparametrilla, mutta merkkijonon loppuosa on viimeinen elementti, eikä sitä jaeta.
Taulukon viimeinen elementti siirretään takaisin merkkijonoon UBound -funktion avulla sen määrittämiseksi, mikä elementti tämä on.
Merkkijono jaetaan sitten uudelleen taulukkoon käyttäen rajamuuttujana N. Tämä tarkoittaa, että merkkijonolle tehdään halkeamat kohtaan N asti, minkä jälkeen loppu merkkijono muodostaa taulukon viimeisen elementin.
ReDim -käskyä käytetään viimeisen elementin poistamiseen, koska haluamme vain tietyt elementit, jotka jäävät taulukkoon. Huomaa, että Säilytä -parametria käytetään, muuten kaikki taulukon tiedot menetetään.
Uusi taulukko palautetaan sitten siihen koodiin, josta se on kutsuttu.
Huomaa, että koodi on "virheenkestävä". Käyttäjät tekevät usein outoja asioita, joita et ole harkinnut. Jos esimerkiksi he yrittävät käyttää funktiota Start- tai N -parametrilla, joka on suurempi kuin merkkijonossa käytettävissä oleva halkeamien määrä, tämä todennäköisesti aiheuttaa toiminnon epäonnistumisen.
Mukana on koodi, jolla tarkistetaan aloitusarvo ja varmistetaan, että on olemassa elementti, joka voidaan poistaa, kun ReDim -käskyä käytetään taulukossa.
Tässä on koodi toiminnon testaamiseksi:
| 123456789101112 | AlatestiSplitSlicer ()'Luo muuttujiaDim MyArray () merkkijonona, MyString merkkijonona'Määritä näytejono pilkkuerottimillaMyString = "Yksi, kaksi, kolme, neljä, viisi, kuusi, seitsemän, kahdeksan, yhdeksän, kymmenen"'Käytä Splitslicer -toimintoa uuden taulukon määrittämiseenMyArray = SplitSlicer (MyString, ",", 4, 3)'Tyhjennä aktiivinen taulukkoActiveSheet.UsedRange.ClearKopioi taulukko laskentataulukkoonAlue ("A1: A" & UBound (MyArray) + 1). Arvo = WorksheetFunction.Transpose (MyArray)End Sub |
Suorita tämä koodi ja laskentataulukkosi näyttää tältä:
