VBA jagamisfunktsiooni kasutamine
Funktsioon VBA Split võimaldab eraldada komponendid tavalisest tekstistringist, kus iga komponent kasutab spetsiifilist eraldusmärki, nt. koma või koolon. Seda on lihtsam kasutada kui koodi kirjutamist, et otsida stringist eraldajaid ja seejärel väärtusi ekstraheerida.
Seda saab kasutada siis, kui loete rida komaeraldusega väärtusest (CSV-fail) või kui teil on postiaadress, mis on kõik ühel real, kuid soovite seda näha mitme reana.
Süntaks on järgmine:
| 1 | Poolitatud avaldis, eraldaja [valikuline], piirang [valikuline], võrdlus [valikuline] |
Funktsioonil VBA Split on neli parameetrit:
- Väljendus - Tekstistring, mille soovite eri osadeks jagada.
- Eraldaja (valikuline)- string või mitteprinditav märk - määrab eraldusmärgi, mida jagamisel kasutatakse. Kui eraldusmärke pole ette nähtud, kasutatakse tühiku vaikeseadet.
- Piira (valikuline) - number - määrab, mitu lõhet tehakse. Kui see on tühi, tehakse stringis kõik saadaolevad jaotused. Kui see on seatud väärtusele 1, siis jaotusi ei tehta. Põhimõtteliselt võimaldab see eraldada teatud arvu väärtusi, alustades stringi algusest, nt. kus string on väga pikk ja vajate ainult kolme esimest lõhet.
- Võrdlema (valikuline) - Kui teie eraldaja on tekstimärk, kasutatakse seda, et muuta, kas eraldaja on tõstutundlik või mitte. Väärtused on vbBinaryCompare (tõstutundlik) ja vbTextCompare (ei ole tõstutundlik).
Funktsioon split tagastab alati massiivi.
Jagatud funktsiooni lihtne näide
| 123456789101112 | AlajaotusNäide ()'Määrake muutujadDim MyArray () stringina, MyString stringina, mina variandina'Näidisring tühikutegaMyString = "Üks kaks kolm neli"'Stringi komponentide jagamiseks kasutage funktsiooni SplitMyArray = Split (MyString)'Korda iga väärtuse kuvamiseks loodud massiiviIga I In MyArray jaoksMsgBox IEdasi minaEnd Sub |
Selles näites pole eraldajat määratud, kuna kõigi sõnade vahel on tühik, seega saab kasutada vaikimisi eraldajat (tühikut).
Massiivil pole mõõtmeid ja see määratakse stringina. Muutuja I, mida kasutatakse ahelas For… Next, tuleb mõõta variandina.
Selle koodi käivitamisel kuvatakse neli sõnumikasti, üks iga jaotuse kohta, nt Üks kaks kolm. Neli.
Pange tähele, et kui stringi sõnade vahel on kahekordne tühik, hinnatakse seda lõheks, ehkki selles pole midagi. See ei pruugi olla tulemus, mida soovite näha.
Selle probleemi saate lahendada, kasutades asendusfunktsiooni, et asendada topeltruumid ühe tühikuga.
| 1 | MyString = Asenda (MyString, "", "") |
Lõpp- või esiruum võib põhjustada probleeme ka tühja lõigu tekitamisega. Neid on sageli väga raske näha. Neid kõrvalisi ruume saate eemaldada funktsiooni Trim abil.
| 1 | MyString = kärpimine (MyString) |
Jaotamisfunktsiooni kasutamine eraldusmärgiga
Võime kasutada semikoolonit (;). Seda leidub sageli e -posti aadresside stringides aadresside eraldamiseks. Teile võidakse saata e -kiri, mida jagatakse paljude kolleegidega ja soovite oma töölehel näha loendit, kellele see on läinud. Saate e -posti aadresse hõlpsasti kopeerida e -posti kastidest „Saaja” või „Kopeeri” ja oma koodi.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Määrake muutujadDim MyArray () stringina, MyString stringina, mina variandina, N täisarvuna'Näidisnool semikooloniga eraldajategaMinuString = "[email protected]; [email protected]; [email protected]; [email protected]"'Stringi komponentide jagamiseks kasutage funktsiooni SplitMyArray = Jagatud (MyString, ";")'Tühjendage töölehtActiveSheet.UsedRange.Clear'iteraat läbi massiiviN = 0 jaoks UBound (MyArray)„Asetage iga e -posti aadress töölehe esimesse veerguVahemik ("A" & N + 1) .Väärtus = MyArray (N)Järgmine N.End Sub |
Pange tähele, et massiivi kordamiseks kasutatakse tsüklit For… Next. Massiivi esimene element algab alati nullist ja ülemise piiri funktsiooni kasutatakse maksimaalse elementide arvu saamiseks.
Pärast selle koodi käivitamist näeb teie tööleht välja selline:
Piirangu parameetri kasutamine jagatud funktsioonis
Limiidi parameeter võimaldab stringi algusest peale teha teatud arvu lõhesid. Kahjuks ei saa te anda lähtepositsiooni ega erinevaid jaotusi, nii et see on üsna lihtne. Funktsiooni loomiseks saate luua oma VBA -koodi ja seda selgitatakse käesolevas artiklis hiljem.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Loo muutujadDim MyArray () stringina, MyString stringina, mina variandina, N täisarvuna'Näidis string koma eraldajategaMyString = "Üks, kaks, kolm, neli, viis, kuus"'Stringi komponentide jagamiseks kasutage funktsiooni SplitMyArray = Jagatud (MyString, ",", 4)'Tühjendage töölehtActiveSheet.UsedRange.Clear'Korda massiiviN = 0 jaoks UBound (MyArray)„Asetage iga jaotus töölehe esimesse veerguVahemik ("A" & N + 1) .Väärtus = MyArray (N)Järgmine N.End Sub |
Pärast selle koodi käivitamist näeb teie tööleht välja selline:
Eraldi kuvatakse ainult kolm esimest jagatud väärtust. Kolm viimast väärtust kuvatakse ühe pika stringina ja neid ei jagata.
Kui valite piirväärtuse, mis on suurem kui stringi eraldajate arv, ei põhjusta see viga. String jagatakse kõikidesse selle osadesse, nagu poleks piirväärtust esitatud.
Võrdlusparameetri kasutamine poolitatud funktsioonis
Parameeter Võrdle määrab, kas eraldaja on tõstutundlik või mitte. See ei kehti, kui eraldajad on komad, semikoolonid või koolonid.
Märkus. Selle asemel võite alati paigutada suvandi Võrdle teksti <> oma mooduli ülaossa, et välistada kogu mooduli tõstutundlikkus.
| 123456789101112131415 | Sub SplitByCompareExample ()'Loo muutujadDim MyArray () stringina, MyString stringina, mina variandina, N täisarvuna'Näidisring X eraldajategaMyString = "OneXTwoXThreexFourXFivexSix"'Stringi komponentide jagamiseks kasutage funktsiooni SplitMyArray = Split (MyString, "X", vbBinaryCompare)'Tühjendage töölehtActiveSheet.UsedRange.Clear'iteraat läbi massiiviN = 0 jaoks UBound (MyArray)„Asetage iga jaotus töölehe esimesse veerguVahemik ("A" & N + 1) .Väärtus = MyArray (N)Järgmine N.End Sub |
Selles näites kasutab poolitatav string eraldajana märki „X”. Selles stringis on aga segu suurtest ja väikestest X -tähtedest. Funktsiooni Split parameeter Võrdle kasutab suurtähte X.
Kui parameeter Võrdle on seatud väärtusele vbBinaryCompare, ignoreeritakse väiketähti „x” ja teie tööleht näeb välja selline:
Kui parameeter Võrdle on seatud väärtusele vbTextCompare, kasutatakse jaotamisel väiketähti „x” ja teie tööleht näeb välja selline:
Pange tähele, et lahtri A6 väärtus on kärbitud, kuna see sisaldab väiketähte „x”. Kuna jaotus ei ole tõstutundlik, põhjustab eraldusmärk, mis moodustab osa alamstringist, lõhenemist.
Seda tuleb teksti eraldaja ja vbTextCompare kasutamisel silmas pidada. Võite kergesti vale tulemuse saada.
Mitteprinditavate märkide kasutamine eraldusmärgina
Eraldajana saate kasutada mitteprinditavaid märke, näiteks käru tagasitulekut (reavahet).
Siin kasutame vbCr -d, et määrata käru tagastamine <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Loo muutujadDim MyArray () stringina, MyString stringina, mina variandina, N täisarvuna'Näidisnöör koos käru tagasivõtmise eraldajategaMyString = "Üks" & vbCr & "Kaks" & vbCr & "Kolm" & vbCr & "Neli" & vbCr & "Viis" & vbCr & "Kuus"'Stringi komponentide jagamiseks kasutage funktsiooni SplitMyArray = Split (MyString, vbCr,, vbTextCompare)'Tühjendage töölehtActiveSheet.UsedRange.Clear'Korda massiiviN = 0 jaoks UBound (MyArray)„Asetage iga jaotus töölehe esimesse veerguVahemik ("A" & N + 1) .Väärtus = MyArray (N)Järgmine N.End Sub |
Selles näites luuakse string, kasutades eraldajaks vbCr (veo tagastamise märk).
Selle koodi käivitamisel näeb teie tööleht välja selline:
Jaotuse tagasipööramiseks liitumisfunktsiooni kasutamine
Funktsioon Liitmine ühendab uuesti kõik massiivi elemendid, kuid kasutab määratud eraldajat. Kui eraldajat pole määratud, kasutatakse tühikut.
| 123456789101112131415 | AlamliitumineNäide ()'Loo muutujadDim MyArray () stringina, MyString stringina, mina variandina, N täisarvunaHämarda sihtmärk stringina'Näidis string koma eraldajategaMyString = "Üks, kaks, kolm, neli, viis, kuus"„Asetage MyString lahtrisse A1Vahemik ("A1"). Väärtus = MyString'Stringi komponentide jagamiseks kasutage funktsiooni SplitMyArray = Jagatud (MyString, ",")"Kasutage liitumisfunktsiooni, et luua algne string uuesti, kasutades semikoolonitSihtmärk = Liitu (MyArray, ”;”)'Asetage tulemuste string lahtrisse A2Vahemik ("A2"). Väärtus = sihtmärkEnd Sub |
See kood jagab koma eraldajatega stringi massiiviks ja ühendab selle uuesti kokku, kasutades semikooloniga eraldajaid.
Pärast selle koodi käivitamist näeb teie tööleht välja selline:
Lahtris A1 on algne string koos koma eraldajatega ja lahtris A2 on uus ühendatud string semikooloniga eraldajatega.
Funktsiooni Split kasutamine sõnade loendamiseks
Pidades silmas, et Exceli VBA stringi muutuja võib olla kuni 2 Gb pikk, saate tekstilõigus sõnade loendamiseks kasutada split -funktsiooni. Ilmselgelt teeb Microsoft Word seda automaatselt, kuid see võib olla kasulik lihtsa tekstifaili või teisest rakendusest kopeeritud teksti puhul.
| 1234567891011121314 | AlamarvOfWordsExample ()'Loo muutujadDim MyArray () stringina, MyString stringina'Näidisring tühikutegaMyString = "Üks kaks kolm neli viis kuus"'Eemaldage topeltvahedMyString = Asenda (MyString, "", "")„Eemaldage kõik esi- ja tagaruumidMyString = kärpimine (MyString)'Stringi komponentide jagamiseks kasutage funktsiooni SplitMyArray = Split (MyString)'Näidake sõnade arvu, kasutades funktsiooni UBoundMsgBox "Sõnade arv" ja UBound (MyArray) + 1End Sub |
Üks selle sõnade lugemise koodi ohte on see, et seda viskavad kahekordsed tühikud ning esi- ja lõppruumid. Kui need on olemas, loetakse need lisasõnadeks ja sõnade arv muutub lõpuks ebatäpseks.
Kood kasutab nende täiendavate tühikute eemaldamiseks funktsioone Asenda ja Kärbi.
Lõplik koodirida kuvab sõnade arvu, mis on leitud funktsiooni UBound abil, et saada massiivi maksimaalne elementide arv ja seejärel suurendada seda 1 võrra. Seda seetõttu, et esimene massiivi element algab nullist.
Aadressi jagamine töölehe lahtriteks
E -posti aadressid on sageli pikad tekstid, millel on koma eraldajad. Võib -olla soovite jagada aadressi iga osa eraldi lahtriks.
| 123456789101112131415 | Alam -aadressNäide ()'Loo muutujadDim MyArray () stringina, MyString stringina, N täisarvuna'Seadistage string Microsoft Corporationi aadressigaMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Jaotamisfunktsiooni abil jagage string komaeraldajagaMyArray = Jagatud (MyString, ",")'Tühjendage töölehtActiveSheet.UsedRange.Clear'iteraat läbi massiiviN = 0 jaoks UBound (MyArray)„Asetage iga jaotus töölehe esimesse veerguVahemik ("A" & N + 1) .Väärtus = MyArray (N)Järgmine N.End Sub |
Selle koodi käivitamisel kasutatakse aadressi iga rida eraldi lahtrisse komaeraldajat:
Kui soovite tagastada ainult sihtnumbri (viimane massiivi element), saate seda koodi kasutada:
| 123456789101112 | Alam -aadressZipCodeExample ()'Loo muutujadDim MyArray () stringina, MyString stringina, N täisarvuna, temp stringina'Seadistage string Microsoft Corporationi aadressigaMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Jaotamisfunktsiooni abil jagage string komaeraldajagaMyArray = Jagatud (MyString, ",")'Tühjendage töölehtActiveSheet.UsedRange.Clear"Pange sihtnumber lahtrisse A1Vahemik ("A1"). Väärtus = MyArray (UBound (MyArray))End Sub |
See kasutab ainult massiivi viimast elementi, mis leitakse funktsiooni UBound abil.
Teisest küljest võite soovida näha kõiki lahtrite ridu, et neid saaks printida aadressisildile:
| 1234567891011121314151617 | Alam -aadressNäide ()'Loo muutujadDim MyArray () stringina, MyString stringina, N täisarvuna, temp stringina'Seadistage string Microsoft Corporationi aadressigaMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Jaotamisfunktsiooni abil jagage string komaeraldajagaMyArray = Jagatud (MyString, ",")'Tühjendage töölehtActiveSheet.UsedRange.Clear'iteraat läbi massiiviN = 0 jaoks UBound (MyArray)'paigutage stringidesse iga massiivielement ja reavahetusmärkTemp = Temp & MyArray (N) & vbLfJärgmine N."Pange string tööleheleVahemik ("A1") = tempEnd Sub |
See näide töötab samamoodi nagu eelmine, ainult et loob ajutise stringi kõigist massiivielementidest, kuid lisab iga elemendi järel reavahetuse märgi.
Tööleht näeb pärast koodi käivitamist välja selline:
Jagage string töölehe lahtriteks
Massiivi Split saate kopeerida töölehe lahtritesse <> vaid ühe käsuga:
| 12345678910 | Sub CopyToRange ()'Loo muutujadDim MyArray () stringina, MyString stringina'Näidisring tühikutegaMyString = "Üks, kaks, kolm, neli, viis, kuus"'Stringi komponentide jagamiseks kasutage funktsiooni SplitMyArray = Jagatud (MyString, ",")'Kopeerige massiiv tööleheleVahemik ("A1: A" & UBound (MyArray) + 1) .Väärtus = TöölehtFunktsioon. Ülekanne (MyArray)End Sub |
Kui see kood on käivitatud, näeb teie tööleht välja selline:
Uue funktsiooni loomine, mis võimaldab teatud punktist tükeldamist
Funktsiooni Split parameeter Limit võimaldab määrata ainult ülemise piiri, kus soovite lõhestamise peatada. See algab alati stringi algusest.
Oleks väga kasulik omada sarnast funktsiooni, kus saab määrata stringi poolituse alguspunkti ja lõhede arvu, mida soovite sellest hetkest alates näha. Samuti eraldab see ainult teie määratud jaotused massiivi, selle asemel et massiivi viimaseks elemendiks oleks tohutu stringiväärtus.
Selleks saate VBA -sse funktsiooni (nimega SplitSlicer) hõlpsasti luua:
| 123456789101112131415161718192021222324 | Funktsioon SplitSlicer (sihtmärk stringina, del stringina, algus täisarvuna, N täisarvuna)'Loo massiivi muutujaDim MyArray () stringina'Jäädvustage jaotus algmuutuja abil, kasutades eraldajatMyArray = Split (Target, Del, Start)„Kontrollige, kas stardiparameeter on suurem kui jagude arv - see võib põhjustada probleemeKui Start> UBound (MyArray) + 1 Siis„Kuva viga ja väljuge funktsioonistMsgBox "Start -parameeter on suurem kui saadaval olevate jaotuste arv"SplitSlicer = MyArrayVälju funktsioonLõpp Kui'Pange stringi viimane massiivi elementSihtmärk = MyArray (UBound (MyArray))'Jagage string, kasutades piirina NMyArray = jagatud (sihtmärk, Del, N)„Kontrollige, kas ülemine piir on suurem kui null, kuna kood eemaldab viimase elemendiKui UBound (MyArray)> 0 Siis'Kasutage massiivi viimase elemendi eemaldamiseks ReDimiReDim Preserve MyArray (UBound (MyArray) - 1)Lõpp Kui'Tagastage uus massiivSplitSlicer = MyArrayLõppfunktsioon |
See funktsioon koosneb neljast parameetrist:
- Sihtmärk - string - see on sisendstring, mille soovite jagada
- Del - string või mitteprinditav märk - see on eraldusmärk, mida kasutate nt. koma, koolon
- Alusta - number - see on teie viilu algusjaotus
- N - number - see on lõhede arv, mida soovite oma viilul teha
Ükski neist parameetritest ei ole valikuline või neil on vaikeväärtused, kuid saate selle funktsiooni koodi sisestada, kui soovite seda veelgi laiendada.
Funktsioon kasutab funktsiooni Split, et luua massiiv, kasutades Limit parameetrit Start. See tähendab, et massiivi elemendid hoiavad lõhed kuni parameetrini start, kuid ülejäänud string jääb viimaseks elemendiks ja seda ei jagata.
Massiivi viimane element kantakse tagasi stringile, kasutades funktsiooni UBound, et teha kindlaks, milline element see on.
Seejärel jagatakse string uuesti massiiviks, kasutades piirimuutujana N. See tähendab, et stringi lõhed tehakse positsioonini N, mille järel ülejäänud string moodustab massiivi viimase elemendi.
ReDimi avaldust kasutatakse viimase elemendi eemaldamiseks, kuna soovime ainult massiivi jäänud konkreetseid elemente. Pange tähele, et kasutatakse parameetrit Säilita, vastasel juhul lähevad kõik massiivi andmed kaduma.
Seejärel tagastatakse uus massiiv sellele koodile, millelt see kutsuti.
Pange tähele, et kood on „veakindel”. Kasutajad teevad sageli kummalisi asju, mida te pole kaalunud. Näiteks kui nad proovivad kasutada funktsiooni parameetriga Start või N, mis on suurem kui stringis saadaolev poolituste arv, põhjustab see funktsiooni ebaõnnestumise.
Kaasas on kood, et kontrollida väärtust Start ja veenduda, et ReDim -lause kasutamisel massiivis on mõni element, mille saab eemaldada.
Siin on koodi funktsiooni testimiseks:
| 123456789101112 | Alam TestSplitSlicer ()'Loo muutujadDim MyArray () stringina, MyString stringina'Määratlege näidistring koma eraldajategaMyString = "Üks, kaks, kolm, neli, viis, kuus, seitse, kaheksa, üheksa, kümme"'Uue massiivi määratlemiseks kasutage funktsiooni SplitslicerMyArray = SplitSlicer (MyString, ",", 4, 3)'Tühjendage aktiivne lehtActiveSheet.UsedRange.Clear'Kopeerige massiiv tööleheleVahemik ("A1: A" & UBound (MyArray) + 1) .Väärtus = TöölehtFunktsioon. Ülekanne (MyArray)End Sub |
Käivitage see kood ja teie tööleht näeb välja selline:
