Korištenje VBA Split funkcije
Funkcija VBA Split omogućuje vam odvajanje dijelova komponente unutar standardnog tekstualnog niza gdje svaka komponenta koristi određeni znak razgraničenja, npr. zarez ili dvotočka. Lakše je koristiti nego pisati kôd za traženje graničnika u nizu, a zatim izdvajanje vrijednosti.
Može se koristiti ako čitate u retku iz vrijednosti odvojene zarezima (CSV datoteka) ili imate poštansku adresu koja se nalazi u jednom retku, ali želite je vidjeti kao više redaka.
Sintaksa je:
| 1 | Podijeljeni izraz, graničnik [izborno], ograničenje [neobavezno], usporedba [neobavezno] |
VBA Split funkcija ima četiri parametra:
- Izraz - Niz teksta koji želite podijeliti na različite dijelove.
- Razdjelnik (izborno)- niz ili znak koji se ne može ispisati - Određuje znak razgraničenja koji će se koristiti za podjelu. Ako nije naveden razdjelnik, tada se koristi zadani razmak.
- Ograničiti (izborno) - broj - Određuje koliko će podjela biti napravljeno. Ako je prazno, svi će se rasporedi podijeliti unutar niza. Ako je postavljeno na 1, neće se napraviti podjele. U osnovi, omogućuje vam odvajanje određenog broja vrijednosti počevši od početka niza, npr. gdje je žica vrlo duga i potrebna su vam samo prva tri dijela.
- Usporedi (izborno) - Ako je vaš razdjelnik tekstualni znak, to se koristi za prebacivanje hoće li razdjelnik razlikovati velika ili mala slova. Vrijednosti su vbBinaryCompare (osjetljivo na velika i mala slova) i vbTextCompare (ne razlikuju velika i mala slova).
Funkcija split uvijek vraća niz.
Jednostavan primjer funkcije Split
| 123456789101112 | Sub SplitExample ()'Definirajte varijableDim MyArray () Kao niz, MyString kao niz, ja kao varijanta'Niz uzoraka s razdjelnicimaMyString = "Jedan dva tri četiri"'Pomoću funkcije Split podijelite sastavne dijelove nizaMyArray = Podijeli (MyString)'ponavljajte kroz niz kreiran za prikaz svake vrijednostiZa svakoga u MyArrayuMsgBox ISljedeći jaKraj podm |
U ovom primjeru nije naveden razdjelnik jer sve riječi imaju razmak između njih, pa se može koristiti zadani razdjelnik (razmak).
Niz nema dimenzija i postavljen je kao niz. Varijabla I, koja se koristi u petlji For … Next, mora biti dimenzionirana kao varijanta.
Kada se ovaj kôd pokrene, prikazat će četiri okvira s porukama, po jedan za svaki od dijeljenja, npr. Jedan dva tri. Četiri.
Imajte na umu da ako postoji dvostruki razmak između riječi u nizu, to će se ocijeniti kao podijeljeno, iako nema ništa u sebi. To možda nije rezultat koji želite vidjeti.
Ovaj problem možete riješiti korištenjem funkcije Zamijeni da zamijenite sve dvostruke razmake jednim razmakom:
| 1 | MyString = Zamijeni (MyString, "", "") |
Zadnji ili vodeći prostor također može uzrokovati probleme stvaranjem praznog dijela. Često ih je vrlo teško vidjeti. Ove vanjske prostore možete ukloniti pomoću funkcije Trim:
| 1 | MyString = Trim (MyString) |
Korištenje funkcije Split s znakom razdjelnika
Možemo upotrijebiti razdjelnik točke sa zarezom (;). To se često nalazi u nizovima adresa e -pošte za odvajanje adresa. Možda vam je poslana poruka e -pošte koju dijelite s brojnim kolegama i želite vidjeti popis na svom radnom listu kome je to poslao. E -adrese možete jednostavno kopirati iz okvira za e -poštu "Prima" ili "Kopiraj" u svoj kôd.
| 123456789101112131415 | Pod SplitBySemicolonExample ()'Definirajte varijableDim MyArray () Kao niz, MyString kao niz, I kao varijanta, N kao cijeli broj'Uzorak niza s graničnicima u zarezuMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Pomoću funkcije Split podijelite sastavne dijelove nizaMyArray = Split (MyString, ";")'Očistite radni listActiveSheet.UsedRange.Clear'ponavljati kroz nizZa N = 0 prema UBound (MyArray)'Stavite svaku adresu e -pošte u prvi stupac radnog listaRaspon ("A" & N + 1). Vrijednost = MyArray (N)Sljedeći N.Kraj podm |
Imajte na umu da se petlja For… Next koristi za ponavljanje kroz niz. Prvi element u nizu uvijek počinje od nule, a funkcija Gornja granica koristi se za dobivanje maksimalnog broja elemenata.
Nakon pokretanja ovog koda, vaš će radni list izgledati ovako:
Korištenje graničnog parametra u podijeljenoj funkciji
Ograničeni parametar dopušta da se od početka niza izvede određeni broj podjela. Nažalost, ne možete dati početnu poziciju ili niz podjela koje je potrebno izvesti, pa je to prilično osnovno. Možete stvoriti vlastiti VBA kôd za stvaranje funkcije za to, a to će biti objašnjeno kasnije u ovom članku.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Stvorite varijableDim MyArray () Kao niz, MyString kao niz, I kao varijanta, N kao cijeli broj'Primjer niza s graničnicima zarezaMyString = "Jedan, dva, tri, četiri, pet, šest"'Pomoću funkcije Split podijelite sastavne dijelove nizaMyArray = Split (MyString, ",", 4)'Očistite radni listActiveSheet.UsedRange.Clear»Ponavljajte nizZa N = 0 prema UBound (MyArray)'Stavite svaki rascjep u prvi stupac radnog listaRaspon ("A" & N + 1). Vrijednost = MyArray (N)Sljedeći N.Kraj podm |
Nakon što pokrenete ovaj kôd, vaš će radni list izgledati ovako:
Samo su prve tri podijeljene vrijednosti prikazane zasebno. Kasnije tri vrijednosti prikazane su kao jedan dugi niz i ne dijele se.
Ako odaberete graničnu vrijednost koja je veća od broja graničnika unutar niza, to neće proizvesti pogrešku. Niz će se podijeliti na sve njegove sastavne dijelove kao da nije navedena granična vrijednost.
Korištenje parametra Usporedi u podijeljenoj funkciji
Parametar Usporedi određuje je li graničnik osjetljiv na velika ili mala slova. To se ne primjenjuje ako su graničnici zarezi, točke sa zarezom ili dvotočke.
Napomena: Umjesto toga, uvijek možete postaviti opciju Usporedi tekst <> na vrh vašeg modula kako biste uklonili velika i mala slova za cijeli modul.
| 123456789101112131415 | Pod SplitByCompareExample ()'Stvorite varijableDim MyArray () Kao niz, MyString kao niz, I kao varijanta, N kao cijeli broj'Uzorak niza s razdjelnicima XMyString = "OneXTwoXThreexFourXFivexSix"'Pomoću funkcije Split podijelite sastavne dijelove nizaMyArray = Split (MyString, "X",, vbBinaryCompare)'Očistite radni listActiveSheet.UsedRange.Clear'ponavljati kroz nizZa N = 0 prema UBound (MyArray)'Stavite svaki rascjep u prvi stupac radnog listaRaspon ("A" & N + 1). Vrijednost = MyArray (N)Sljedeći N.Kraj podm |
U ovom primjeru niz koji se dijeli koristi znak 'X' kao graničnik. Međutim, u ovom nizu postoji mješavina velikih i malih slova 'X' znakova. Parametar Usporedi u funkciji Split koristi veliko slovo ‘X’.
Ako je parametar Usporedi postavljen na vbBinaryCompare, mala slova "x" će se zanemariti, a vaš radni list izgledat će ovako:
Ako je parametar Usporedi postavljen na vbTextCompare, tada će se mali slojevi ‘x’ koristiti u podjeli, a vaš će radni list izgledati ovako:
Imajte na umu da je vrijednost u ćeliji A6 skraćena jer sadrži mala slova "x". Budući da podjela ne razlikuje velika i mala slova, svaki razdjelnik koji čini dio podniza uzrokovat će podjelu.
Ovo je važno imati na umu pri korištenju razdjelnika teksta i vbTextCompare. Lako možete završiti s pogrešnim rezultatom.
Korištenje znakova koji se ne mogu ispisati kao znak razdjelnika
Kao razdjelnik možete koristiti znakove koji se ne mogu ispisivati, kao što je povratak nosača (prijelom retka).
Ovdje koristimo vbCr za navođenje povratne oznake <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Stvorite varijableDim MyArray () Kao niz, MyString kao niz, I kao varijanta, N kao cijeli broj'Uzorak niza s graničnicima za povratak nosačaMyString = "Jedan" & vbCr & "Dva" & vbCr & "Tri" & vbCr & "Četiri" & vbCr & "Pet" & vbCr & "Šest"'Pomoću funkcije Split podijelite sastavne dijelove nizaMyArray = Podijeli (MyString, vbCr,, vbTextCompare)'Očistite radni listActiveSheet.UsedRange.Clear»Ponavljajte nizZa N = 0 prema UBound (MyArray)'Stavite svaki rascjep u prvi stupac radnog listaRaspon ("A" & N + 1). Vrijednost = MyArray (N)Sljedeći N.Kraj podm |
U ovom primjeru, niz se gradi pomoću vbCr (znak za vraćanje nosača) kao graničnika.
Kada se ovaj kôd pokrene, vaš će radni list izgledati ovako:
Korištenje funkcije pridruživanja za preokretanje razdvajanja
Funkcija Join ponovno će pridružiti sve elemente niza, ali pomoću navedenog razdjelnika. Ako nije naveden znak razgraničenja tada će se koristiti razmak.
| 123456789101112131415 | Sub JoinExample ()'Stvorite varijableDim MyArray () Kao niz, MyString kao niz, I kao varijanta, N kao cijeli brojZatamni metu kao niz'Primjer niza s graničnicima zarezaMyString = "Jedan, dva, tri, četiri, pet, šest"'Postavite MyString u ćeliju A1Raspon ("A1"). Vrijednost = MyString'Pomoću funkcije Split podijelite sastavne dijelove nizaMyArray = Split (MyString, ",")'Upotrijebite funkciju Pridruži se za ponovno stvaranje izvornog niza pomoću graničnika sa zarezomCilj = Pridruživanje (MyArray, ”;”)'Postavite rezultirajući niz u ćeliju A2Raspon ("A2"). Vrijednost = CiljKraj podm |
Ovaj kôd razdvaja niz s graničnicima zareza u niz, i ponovno ga spaja pomoću graničnika sa zarezom.
Nakon pokretanja ovog koda vaš radni list izgledat će ovako:
Ćelija A1 ima izvorni niz s graničnicima zareza, a ćelija A2 ima novi spojeni niz s graničnicima u zarezu.
Korištenje funkcije Split za brojanje riječi
Imajući na umu da varijabla niza u Excelu VBA može biti dugačka do 2 Gb, funkciju podjele možete koristiti za brojanje riječi u komadu teksta. Očigledno, Microsoft Word to radi automatski, ali to bi moglo biti korisno za jednostavnu tekstualnu datoteku ili tekst kopiran iz druge aplikacije.
| 1234567891011121314 | PodbrojOd riječiWexample ()'Stvorite varijableDim MyArray () kao niz, MyString kao niz'Niz uzoraka s razdjelnicimaMyString = "Jedan dva tri četiri pet šest šest"'Uklonite sve dvostruke razmakeMyString = Zamijeni (MyString, "", "")'Uklonite sve vodeće ili krajnje razmakeMyString = Trim (MyString)'Pomoću funkcije Split podijelite sastavne dijelove nizaMyArray = Podijeli (MyString)'Prikažite broj riječi pomoću funkcije UBoundMsgBox "Broj riječi" & UBound (MyArray) + 1Kraj podm |
Jedna od opasnosti ovog koda za brojanje riječi je ta što će ga izbaciti dvostruki razmaci te vodeći i krajnji razmaci. Ako postoje, one će se računati kao dodatne riječi, a broj riječi će završiti kao netočan.
Kôd koristi funkcije Zamijeni i Odreži za uklanjanje ovih dodatnih razmaka.
Završni kodni redak prikazuje broj riječi pronađenih pomoću funkcije UBound za dobivanje maksimalnog broja elemenata niza, a zatim njegovo povećanje za 1. To je zato što prvi element niza počinje od nule.
Razdvajanje adrese u ćelije radnog lista
E -mail adrese često su dugački nizovi teksta s graničnicima zareza. Bilo bi dobro da svaki dio adrese podijelite u zasebnu ćeliju.
| 123456789101112131415 | Pod adresaPrimjer ()'Stvorite varijableDim MyArray () kao niz, MyString kao niz, N kao cijeli broj'Postavite niz s adresom korporacije MicrosoftMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Pomoću funkcije split podijelite niz pomoću graničnika zarezaMyArray = Split (MyString, ",")'Očistite radni listActiveSheet.UsedRange.Clear'ponavljati kroz nizZa N = 0 prema UBound (MyArray)'Stavite svaki rascjep u prvi stupac radnog listaRaspon ("A" & N + 1). Vrijednost = MyArray (N)Sljedeći N.Kraj podm |
Pokretanje ovog koda će koristiti graničnik zareza za stavljanje svakog retka adrese u zasebnu ćeliju:
Ako želite samo vratiti poštanski broj (posljednji element niza), tada biste mogli koristiti kôd:
| 123456789101112 | Pod adresaZipCodeExample ()'Stvorite varijableDim MyArray () kao niz, MyString kao niz, N kao cijeli broj, temp kao niz'Postavite niz s adresom korporacije MicrosoftMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Pomoću funkcije split podijelite niz pomoću graničnika zarezaMyArray = Split (MyString, ",")'Očistite radni listActiveSheet.UsedRange.Clear'Stavite poštanski broj u ćeliju A1Raspon ("A1"). Vrijednost = MyArray (UBound (MyArray))Kraj podm |
Ovo će koristiti samo zadnji element u nizu, koji se nalazi pomoću funkcije UBound.
S druge strane, možda biste htjeli vidjeti sve retke u jednoj ćeliji kako bi se mogli ispisati na naljepnici adrese:
| 1234567891011121314151617 | Pod -adresa Primjer ()'Stvorite varijableDim MyArray () kao niz, MyString kao niz, N kao cijeli broj, temp kao niz'Postavite niz s adresom korporacije MicrosoftMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Pomoću funkcije split podijelite niz pomoću graničnika zarezaMyArray = Split (MyString, ",")'Očistite radni listActiveSheet.UsedRange.Clear'ponavljati kroz nizZa N = 0 prema UBound (MyArray)'stavite svaki element niza i znak unosa retka u nizTemp = Temp & MyArray (N) & vbLfSljedeći N.'Stavite niz na radni listRaspon ("A1") = TempKraj podm |
Ovaj primjer radi na isti način kao i prethodni, osim što stvara privremeni niz svih elemenata niza, ali umetanje znaka unosa retka nakon svakog elementa.
Radni list će izgledati ovako nakon pokretanja koda:
Podijeli niz u ćelije radnog lista
Niz Split možete kopirati u ćelije radnog lista <> sa samo jednom naredbom:
| 12345678910 | Sub CopyToRange ()'Stvorite varijableDim MyArray () kao niz, MyString kao niz'Niz uzoraka s razdjelnicimaMyString = "Jedan, dva, tri, četiri, pet, šest"'Pomoću funkcije Split podijelite sastavne dijelove nizaMyArray = Split (MyString, ",")'Kopirajte niz u radni listRaspon ("A1: A" & UBound (MyArray) + 1) .Value = Radni list Funkcija. Transpozicija (MyArray)Kraj podm |
Kad se ovaj kôd pokrene, vaš će radni list izgledati ovako:
Stvaranje nove funkcije za dopuštanje cijepanja s zadane točke
Parametar Limit u funkciji Split omogućuje vam samo da odredite gornju granicu na kojoj želite da se razdvajanje zaustavi. Uvijek počinje od početka niza.
Bilo bi jako korisno imati sličnu funkciju u kojoj možete navesti početnu točku podjele unutar niza i broj podjela koje želite vidjeti od te točke nadalje. Također će izdvojiti samo podjele koje ste naveli u nizu, umjesto da ima ogromnu vrijednost niza kao posljednji element u nizu.
Lako možete sami izgraditi funkciju (koja se naziva SplitSlicer) u VBA -i kako biste to učinili:
| 123456789101112131415161718192021222324 | Funkcija SplitSlicer (Target As String, Del As String, Start As Integer, N As Integer)'Stvori varijablu nizaDim MyArray () Kao niz'Uhvatite podjelu pomoću varijable start pomoću znaka razdjelnikaMyArray = Split (Target, Del, Start)‘Provjerite je li startni parametar veći od broja podjela - to može uzrokovati problemeAko je Start> UBound (MyArray) + 1 Zatim‘Pogreška prikaza i izlaz iz funkcijeMsgBox "Parametar početka veći je od broja raspoloživih podjela"SplitSlicer = MyArrayIzlazna funkcijaZavrši ako'Stavite zadnji element niza u nizCilj = MyArray (UBound (MyArray))'Podijelite niz koristeći N kao granicuMyArray = Split (Target, Del, N)‘Provjerite je li gornja granica veća od nule jer kôd uklanja zadnji elementAko je UBound (MyArray)> 0 Tada'Upotrijebite ReDim za uklanjanje završnog elementa nizaReDim Preserve MyArray (UBound (MyArray) - 1)Završi ako'Vrati novi nizSplitSlicer = MyArrayZavršna funkcija |
Ova je funkcija izgrađena s četiri parametra:
- Cilj - string - ovo je ulazni niz koji želite podijeliti
- Del - niz ili znak za ispis - ovo je znak razgraničenja koji koristite npr. zarez, dvotočka
- Početak - broj - ovo je početna podjela za vašu krišku
- N - broj - ovo je broj podjela koje želite napraviti unutar svoje kriške
Nijedan od ovih parametara nije neobavezan ili ima zadane vrijednosti, ali to možete unijeti u kôd funkcije ako ga želite dodatno proširiti.
Funkcija koristi funkciju Split za stvaranje niza koristeći parametar Start kao limit. To znači da će elementi niza držati podjele do početnog parametra, ali ostatak niza bit će posljednji element i neće se dijeliti.
Posljednji element u nizu prenosi se natrag u niz pomoću funkcije UBound kako bi se utvrdilo koji je to element.
Niz se zatim ponovno dijeli u niz, koristeći N kao graničnu varijablu. To znači da će se podjele izvršiti za niz do položaja N, nakon čega će ostatak niza tvoriti posljednji element u nizu.
Naredba ReDim koristi se za uklanjanje posljednjeg elementa jer samo želimo da određeni elementi ostanu u nizu. Imajte na umu da se koristi parametar Preserve, inače će svi podaci u nizu biti izgubljeni.
Novi niz se tada vraća u kôd iz kojeg je pozvan.
Imajte na umu da je kôd 'zaštićen od pogrešaka'. Korisnici će često činiti čudne stvari o kojima niste razmišljali. Na primjer, ako pokušaju koristiti funkciju s parametrom Start ili N većim od dostupnog broja podjela u nizu, to će vjerojatno uzrokovati neuspjeh funkcije.
Kôd je uključen za provjeru početne vrijednosti, kao i za provjeru da li postoji element koji se može ukloniti kada se naredba ReDim koristi u nizu.
Evo koda za testiranje funkcije:
| 123456789101112 | Pod TestSplitSlicer ()'Stvorite varijableDim MyArray () kao niz, MyString kao niz'Definirajte uzorak niza s graničnicima zarezaMyString = "Jedan, dva, tri, četiri, pet, šest, sedam, osam, devet, deset"'Pomoću funkcije Splitslicer definirajte novi nizMyArray = SplitSlicer (MyString, ",", 4, 3)'Očistite aktivni listActiveSheet.UsedRange.Clear'Kopirajte niz u radni listRaspon ("A1: A" & UBound (MyArray) + 1) .Value = Radni list Funkcija. Transpozicija (MyArray)Kraj podm |
Pokrenite ovaj kôd i vaš će radni list izgledati ovako:
