Uporaba funkcije VBA Split
Funkcija VBA Split vam omogoča ločevanje sestavnih delov znotraj standardnega besedilnega niza, kjer vsaka komponenta uporablja poseben znak razmejitve, npr. vejica ali dvopičje. Lažje je uporabljati kot pisati kodo za iskanje ločil v nizu in nato ekstrahiranje vrednosti.
Uporabite ga lahko, če berete v vrstici iz vrednosti, ločene z vejico (datoteka CSV), ali če imate poštni naslov v eni vrstici, vendar ga želite videti kot več vrstic.
Sintaksa je:
| 1 | Razdeljen izraz, ločilo [neobvezno], omejitev [neobvezno], primerjava [neobvezno] |
Funkcija VBA Split ima štiri parametre:
- Izraz - Niz besedila, ki ga želite razdeliti na različne dele.
- Ločilo (neobvezno)- niz ali znak, ki ga ni mogoče natisniti - Določa znak razmejevalnika, ki bo uporabljen za razdelitev. Če znak za ločevanje ni naveden, se uporabi privzeti presledek.
- Omejitev (neobvezno) - število - Določa, koliko razdelkov bo narejenih. Če je prazno, bodo v nizu izvedeni vsi razpoložljivi deli. Če je nastavljeno na 1, se razčlenitev ne bo izvedla. V bistvu vam omogoča, da ločite določeno število vrednosti, ki se začnejo na začetku niza, npr. kjer je niz zelo dolg in potrebujete le prve tri delitve.
- Primerjaj (neobvezno) - Če je vaš razmejevalnik besedilni znak, se to uporabi za preklop, ali je ločilo za male in velike črke. Vrednosti sta vbBinaryCompare (občutljivo na velike in male črke) in vbTextCompare (ne razlikuje med velikimi in malimi črkami).
Funkcija split vedno vrne matriko.
Preprost primer deljene funkcije
| 123456789101112 | Sub SplitExample ()'Določite spremenljivkeZatemni MyArray () kot niz, MyString kot niz, jaz kot varianto'Vzorec niza z razmikiMyString = "En dva tri štiri"'S funkcijo Split razdelite sestavne dele nizaMyArray = Razdeli (MyString)"ponovite po nizu, ustvarjenem za prikaz vsake vrednostiZa vsakega v MyArrayjuSporočilo INaslednji jazEnd Sub |
V tem primeru ločila ni podana, ker imajo vse besede presledek med njimi, zato je mogoče uporabiti privzeti ločilo (presledek).
Polje nima dimenzij in je nastavljeno kot niz. Spremenljivko I, ki se uporablja v zanki For… Next, je treba kot varianto dimenzionirati.
Ko se ta koda zažene, bo prikazala štiri poštna polja, po enega za vsako od razdelkov, npr. Eden, dva, tri. Štiri.
Upoštevajte, da če je med besedami v nizu dvojni presledek, bo to ocenjeno kot razcep, čeprav v njem ni ničesar. Morda to ni rezultat, ki ga želite videti.
To težavo lahko odpravite s funkcijo Zamenjaj, da zamenjate vse dvojne presledke z enim presledkom:
| 1 | MyString = Zamenjaj (MyString, "", "") |
Zadnji ali vodilni prostor lahko povzroči tudi težave, saj ustvari prazen razcep. Te je pogosto zelo težko videti. S funkcijo Trim lahko odstranite te tuje prostore:
| 1 | MyString = Trim (MyString) |
Uporaba funkcije Split z znakom za ločevanje
Lahko uporabimo razmejevalnik podpičja (;). To pogosto najdemo v nizih e -poštnih naslovov za ločevanje naslovov. Morda vam je poslano e -poštno sporočilo, ki ga delite s številnimi sodelavci, in želite, da se na vašem delovnem listu prikaže seznam, na koga je šel. E -poštne naslove lahko preprosto kopirate iz e -poštnih polj »Za« ali »Kopiraj« in v kodo.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Določite spremenljivkeDim MyArray () As String, MyString As String, I As Variant, N As Integer„Vzorec niza z ločili med polpičjemMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'S funkcijo Split razdelite sestavne dele nizaMyArray = Razdeli (MyString, ";")'Počisti delovni listActiveSheet.UsedRange.Clear'ponovite po matrikiZa N = 0 proti UBound (MyArray)'Vsak e -poštni naslov postavite v prvi stolpec delovnega listaRazpon ("A" & N + 1). Vrednost = MyArray (N)Naslednji N.End Sub |
Upoštevajte, da se zanka For… Next uporablja za ponovitev po matriki. Prvi element v matriki se vedno začne pri nič, funkcija Upper Bound pa se uporablja za pridobitev največjega števila elementov.
Po zagonu te kode bo vaš delovni list videti tako:
Uporaba mejnega parametra v deljeni funkciji
Omejitveni parameter omogoča, da se od začetka niza izvede določeno število razdelkov. Na žalost ne morete zagotoviti začetnega položaja ali vrste razdelkov, zato je to precej osnovno. Lahko ustvarite svojo kodo VBA, da ustvarite funkcijo za to, kar bo razloženo kasneje v tem članku.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Ustvari spremenljivkeDim MyArray () As String, MyString As String, I As Variant, N As Integer'Vzorec niza z ločili vejiceMyString = "Ena, dva, tri, štiri, pet, šest"'S funkcijo Split razdelite sestavne dele nizaMyArray = Razdeli (MyString, ",", 4)'Počisti delovni listActiveSheet.UsedRange.Clear'Ponovite skozi matrikoZa N = 0 proti UBound (MyArray)'Vsako razdelitev postavite v prvi stolpec delovnega listaRazpon ("A" & N + 1). Vrednost = MyArray (N)Naslednji N.End Sub |
Ko zaženete to kodo, bo vaš delovni list videti tako:
Samo prve tri vrednosti deljenja so prikazane ločeno. Poznejše tri vrednosti so prikazane kot en dolg niz in se ne delijo.
Če izberete mejno vrednost, ki je večja od števila ločil v nizu, to ne bo povzročilo napake. Niz bo razdeljen na vse njegove sestavne dele, kot da mejna vrednost ni podana.
Uporaba parametra Primerjava v deljeni funkciji
S parametrom Primerjajte, ali je razmejevalnik občutljiv na velike in male črke. To ne velja, če so ločila vejice, podpičja ali dvopičja.
Opomba: Namesto tega lahko vedno postavite možnost Primerjaj besedilo <> na vrh modula, da odpravite občutljivost velikih in malih črk za celoten modul.
| 123456789101112131415 | Sub SplitByCompareExample ()'Ustvari spremenljivkeDim MyArray () As String, MyString As String, I As Variant, N As Integer'Vzorec niza z ločilniki XMyString = "OneXTwoXThreexFourXFivexSix"'S funkcijo Split razdelite sestavne dele nizaMyArray = Razdeli (MyString, "X",, vbBinaryCompare)'Počisti delovni listActiveSheet.UsedRange.Clear'ponovite po matrikiZa N = 0 proti UBound (MyArray)'Vsako razdelitev postavite v prvi stolpec delovnega listaRazpon ("A" & N + 1). Vrednost = MyArray (N)Naslednji N.End Sub |
V tem primeru niz, ki ga je treba razdeliti, uporablja znak "X" kot ločilo. Vendar je v tem nizu mešanica velikih in malih črk "X". Parameter Primerjaj v funkciji Razdeli uporablja velik znak "X".
Če je parameter Primerjaj nastavljen na vbBinaryCompare, bodo mali črki "x" prezrti in vaš delovni list bo videti tako:
Če je parameter Primerjava nastavljen na vbTextCompare, bodo v razdelku uporabljeni mali črki ‘x’ in vaš delovni list bo videti tako:
Upoštevajte, da je vrednost v celici A6 okrnjena, ker vsebuje male črke "x". Ker razdelitev ne razlikuje velikih in malih črk, bo kateri koli ločila, ki je del podniz, povzročila razdelitev.
Pri uporabi razmejevalnika besedila in vbTextCompare je treba to upoštevati. Z lahkoto lahko pridete do napačnega rezultata.
Uporaba znakov, ki jih ni mogoče natisniti, kot znaka za ločevanje
Kot ločilo lahko uporabite znake, ki jih ni mogoče natisniti, na primer vrnitev nosilca (prelom vrstice).
Tukaj uporabljamo vbCr za podajanje vrnitve nosilca <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Ustvari spremenljivkeDim MyArray () As String, MyString As String, I As Variant, N As Integer„Vzorec niza z ločilniki za vrnitev nosilcaMyString = "En" & vbCr & "Dva" & vbCr & "Tri" & vbCr & "Štiri" & vbCr & "Pet" & vbCr & "Šest"'S funkcijo Split razdelite sestavne dele nizaMyArray = Razdeli (MyString, vbCr,, vbTextCompare)'Počisti delovni listActiveSheet.UsedRange.Clear'Ponovite skozi nizZa N = 0 proti UBound (MyArray)'Vsako razdelitev postavite v prvi stolpec delovnega listaRazpon ("A" & N + 1). Vrednost = MyArray (N)Naslednji N.End Sub |
V tem primeru je niz sestavljen z uporabo vbCr (znak vrnitve nosilca) kot ločila.
Ko se zažene ta koda, bo vaš delovni list videti tako:
Z uporabo funkcije Join se obrnite na delitev
Funkcija Join se bo znova pridružila vsem elementom matrike, vendar z uporabo določenega ločila. Če znak ločila ni določen, bo uporabljen presledek.
| 123456789101112131415 | Sub JoinExample ()'Ustvari spremenljivkeDim MyArray () As String, MyString As String, I As Variant, N As IntegerZatemni tarčo kot niz'Vzorec niza z ločili vejiceMyString = "Ena, dva, tri, štiri, pet, šest"'MyString postavite v celico A1Obseg ("A1"). Vrednost = MyString'S funkcijo Split razdelite sestavne dele nizaMyArray = Razdeli (MyString, ",")'Uporabite funkcijo Pridruži se, da znova ustvarite izvirni niz z ločilom s podpičjemTarget = Pridruži se (MyArray, ”;”)'Niz rezultata postavite v celico A2Razpon ("A2"). Vrednost = CiljEnd Sub |
Ta koda razdeli niz z ločili vejice v matriko in jo združi z uporabo ločil z vejico.
Po zagonu te kode bo vaš delovni list videti tako:
Celica A1 ima izvirni niz z ločili vejice, celica A2 pa nov združeni niz s pikami.
Uporaba funkcije Split za štetje besed
Če upoštevate, da je nizovna spremenljivka v Excelu VBA lahko dolga do 2 Gb, lahko s funkcijo razcepa štejete besede v kosu besedila. Očitno Microsoft Word to počne samodejno, vendar je to lahko koristno za preprosto besedilno datoteko ali besedilo, kopirano iz druge aplikacije.
| 1234567891011121314 | Sub NumberOfWordsExample ()'Ustvari spremenljivkeZatemni MyArray () kot niz, MyString kot niz'Vzorec niza z razmikiMyString = "En dva tri štiri pet šest šest"'Odstranite vse dvojne presledkeMyString = Zamenjaj (MyString, "", "")„Odstranite vse začetne ali zadnje presledkeMyString = Trim (MyString)'S funkcijo Split razdelite sestavne dele nizaMyArray = Razdeli (MyString)'Prikažite število besed s funkcijo UBoundMsgBox "Število besed" & UBound (MyArray) + 1End Sub |
Ena od nevarnosti te kode štetja besed je, da jo bodo vrgli dvojni presledki ter začetni in zadnji razmiki. Če so te prisotne, se bodo štele kot dodatne besede in število besed bo na koncu nenatančno.
Koda uporablja funkcije Zamenjaj in Odreži, da odstranite te dodatne presledke.
Končna vrstica kode prikazuje število besed, ki jih najdemo s funkcijo UBound, da dobimo največjo število elementov matrike in jo nato povečamo za 1. To je zato, ker se prvi element matrike začne pri nič.
Razdelitev naslova v celice delovnega lista
E -poštni naslovi so pogosto dolgi nizi besedila z vejicami. Morda boste želeli vsak del naslova razdeliti v ločeno celico.
| 123456789101112131415 | Podnaslov Primer ()'Ustvari spremenljivkeZatemni MyArray () kot niz, MyString kot niz, N kot celo število'Nastavite niz z naslovom Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 ZDA"'S funkcijo razdelitve niz razdelite z ločevalnikom vejMyArray = Razdeli (MyString, ",")'Počisti delovni listActiveSheet.UsedRange.Clear'ponovite po matrikiZa N = 0 proti UBound (MyArray)'Vsako razdelitev postavite v prvi stolpec delovnega listaRazpon ("A" & N + 1). Vrednost = MyArray (N)Naslednji N.End Sub |
Zagon te kode bo uporabil ločilo z vejicami, da bo vsako vrstico naslova postavil v ločeno celico:
Če želite le vrniti poštno številko (zadnji element niza), lahko uporabite kodo:
| 123456789101112 | Podnaslov ZipCodeExample ()'Ustvari spremenljivkeDim MyArray () kot niz, MyString kot niz, N kot celo število, Temp kot niz'Nastavite niz z naslovom Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 ZDA"'S funkcijo razdelitve niz razdelite z ločevalnikom vejMyArray = Razdeli (MyString, ",")'Počisti delovni listActiveSheet.UsedRange.Clear"Poštno številko vnesite v celico A1Razpon ("A1"). Vrednost = MyArray (UBound (MyArray))End Sub |
Ta bo uporabil le zadnji element v matriki, ki ga najdemo s funkcijo UBound.
Po drugi strani pa boste morda želeli videti vse vrstice v eni celici, da jih lahko natisnete na etiketo naslova:
| 1234567891011121314151617 | Podnaslov Primer ()'Ustvari spremenljivkeDim MyArray () kot niz, MyString kot niz, N kot celo število, temp kot niz'Nastavite niz z naslovom Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 ZDA"'S funkcijo razdelitve niz razdelite z ločevalnikom vejMyArray = Razdeli (MyString, ",")'Počisti delovni listActiveSheet.UsedRange.Clear'ponovite po matrikiZa N = 0 proti UBound (MyArray)'postavite vsak niz matrike skupaj z znakom za vrstico v nizTemp = Temp & MyArray (N) & vbLfNaslednji N.'Niz postavite na delovni listRazpon ("A1") = TempEnd Sub |
Ta primer deluje na enak način kot prejšnji, le da ustvari začasni niz vseh elementov matrike, vendar za vsakim elementom vstavi znak za vrstico.
Delovni list bo po zagonu kode videti tako:
Razdelite niz v celice delovnega lista
Polje Split lahko kopirate v celice delovnega lista <> z enim samim ukazom:
| 12345678910 | Sub CopyToRange ()'Ustvari spremenljivkeZatemni MyArray () kot niz, MyString kot niz'Vzorec niza z razmikiMyString = "Ena, dva, tri, štiri, pet, šest"'S funkcijo Split razdelite sestavne dele nizaMyArray = Razdeli (MyString, ",")'Kopirajte matriko na delovni listObseg ("A1: A" & UBound (MyArray) + 1). Vrednost = Funkcija delovnega lista. Prenos (MyArray)End Sub |
Ko zaženete to kodo, bo vaš delovni list videti tako:
Ustvarjanje nove funkcije, ki omogoča razdelitev iz dane točke
Parameter Omejitev v funkciji Razdeli vam omogoča, da določite zgornjo mejo, kjer želite, da se ločevanje ustavi. Vedno se začne od začetka niza.
Zelo koristno bi bilo imeti podobno funkcijo, kjer lahko določite začetno točko razcepa v nizu in število razdelkov, ki jih želite videti od te točke naprej. Prav tako bo izvlekel le delitve, ki ste jih določili v matriki, namesto da bi imelo ogromno vrednost niza kot zadnji element v matriki.
V VBA lahko preprosto zgradite funkcijo (imenovano SplitSlicer):
| 123456789101112131415161718192021222324 | Funkcija SplitSlicer (Target As String, Del As String, Start As Integer, N As Integer)'Ustvari spremenljivko matrikeDim MyArray () kot niz'Zajemite razdelitev z začetno spremenljivko z znakom za razmejitevMyArray = Razdeli (Target, Del, Start)„Preverite, ali je začetni parameter večji od števila razdelkov - to lahko povzroči težaveČe Start> UBound (MyArray) + 1 Potem„Prikažite napako in zapustite funkcijoMsgBox "Začetni parameter je večji od števila razpoložljivih razdelkov"SplitSlicer = MyArrayIzhodna funkcijaKonec Če'V niz vstavi zadnji element matrikeTarget = MyArray (UBound (MyArray))'Niz razdelite z omejitvijo NMyArray = Razdeljeno (cilj, Del, N)„Preverite, ali je zgornja meja večja od nič, saj koda odstrani zadnji elementČe je UBound (MyArray)> 0 Potem'Uporabite ReDim za odstranitev končnega elementa matrikeReDim Preserve MyArray (UBound (MyArray) - 1)Konec Če'Vrni novo matrikoSplitSlicer = MyArrayKončana funkcija |
Ta funkcija je zgrajena s štirimi parametri:
- Target - niz - to je vhodni niz, ki ga želite razdeliti
- Del - niz ali znak za tiskanje - to je znak razmejevalnika, ki ga uporabljate npr. vejica, dvopičje
- Začni - številka - to je začetni razdelek za vašo rezino
- N - število - to je število razdelkov, ki jih želite narediti v svoji rezini
Noben od teh parametrov ni neobvezen ali ima privzete vrednosti, vendar ga lahko uporabite za kodo funkcije, če jo želite še razširiti.
Funkcija uporablja funkcijo Split za ustvarjanje matrike s parametrom Start kot omejitvijo. To pomeni, da bodo elementi matrike držali delitve do začetnega parametra, vendar bo preostanek niza zadnji element in ne bo razdeljen.
Zadnji element v matriki se s funkcijo UBound prenese nazaj v niz, da se ugotovi, kateri element je to.
Niz se nato znova razdeli v matriko z uporabo N kot mejne spremenljivke. To pomeni, da bodo za niz izvedeni razcepi do položaja N, nato pa bo preostali niz tvoril zadnji element v matriki.
Stavek ReDim se uporablja za odstranitev zadnjega elementa, saj želimo le, da v matriki ostanejo določeni elementi. Upoštevajte, da je uporabljen parameter Preserve, sicer bodo izgubljeni vsi podatki v matriki.
Nova matrika se nato vrne v kodo, iz katere je bila klicana.
Upoštevajte, da je koda „zaščitena pred napakami“. Uporabniki bodo pogosto počeli čudne stvari, na katere niste pomislili. Če na primer poskusijo uporabiti funkcijo s parametrom Start ali N, ki je večja od razpoložljivega števila razdelkov v nizu, bo to verjetno povzročilo neuspeh funkcije.
Koda je vključena za preverjanje začetne vrednosti in tudi za zagotovitev, da obstaja element, ki ga je mogoče odstraniti, ko je stavek ReDim uporabljen v matriki.
Tu je koda za testiranje funkcije:
| 123456789101112 | Sub TestSplitSlicer ()'Ustvari spremenljivkeZatemni MyArray () kot niz, MyString kot niz'Določite vzorčni niz z ločevalniki vejMyString = "Ena, dva, tri, štiri, pet, šest, sedem, osem, devet, deset"'S funkcijo Splitslicer določite novo matrikoMyArray = SplitSlicer (MyString, ",", 4, 3)'Počisti aktivni listActiveSheet.UsedRange.Clear'Kopirajte matriko na delovni listObseg ("A1: A" & UBound (MyArray) + 1). Vrednost = Funkcija delovnega lista. Prenos (MyArray)End Sub |
Zaženite to kodo in vaš delovni list bo videti tako:
