Naudojant VBA padalijimo funkciją
Funkcija VBA Split leidžia atskirti komponentus nuo standartinės teksto eilutės, kur kiekvienas komponentas naudoja tam tikrą skiriamąjį ženklą, pvz. kablelis arba dvitaškis. Lengviau naudoti, nei rašyti kodą, ieškoti eilutėje esančių skyriklių ir tada išgauti reikšmes.
Jis gali būti naudojamas, jei skaitote eilutę iš kableliais atskirtos vertės (CSV failas) arba turite pašto adresą, kuris yra vienoje eilutėje, tačiau norite jį matyti kaip kelias eilutes.
Sintaksė yra tokia:
| 1 | Išskaidyti išraišką, atskirti [neprivaloma], apriboti [neprivaloma], palyginti [neprivaloma] |
VBA Split funkcija turi keturis parametrus:
- Išraiška - Teksto eilutė, kurią norite padalyti į skirtingas dalis.
- Skyriklis (neprivaloma)- eilutė arba nespausdinamas simbolis - apibrėžia skiriamąjį ženklą, kuris bus naudojamas skaidymui. Jei nėra skiriamojo simbolio, naudojamas numatytasis tarpas.
- Riboti (neprivaloma) - skaičius - nustato, kiek padalijimų bus padaryta. Jei tuščias, visi turimi skaidymai bus padaryti eilutėje. Jei jis nustatytas į 1, padalijimai nebus atlikti. Iš esmės tai leidžia atskirti tam tikrą skaičių verčių, pradedant nuo eilutės pradžios, pvz. kai eilutė yra labai ilga ir jums reikia tik pirmųjų trijų skilimų.
- Palyginti (neprivaloma) - Jei jūsų skiriamieji ženklai yra teksto simboliai, jie naudojami norint perjungti, ar skiriamieji ženklai yra didžiosios ir mažosios raidės. Vertės yra „vbBinaryCompare“ (didžiosios ir mažosios raidės) ir „vbTextCompare“ (neskiriamos didžiosios ir mažosios raidės).
Skaidymo funkcija visada grąžina masyvą.
Paprastas padalijimo funkcijos pavyzdys
| 123456789101112 | Sub SplitEamample ()'Apibrėžkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė, aš kaip variantas„Eilutės pavyzdys su tarpų ribotuvaisMyString = "Vienas du trys keturi"„Naudokite skaidymo funkciją, kad padalintumėte eilutės sudedamąsias dalisMyArray = Split (MyString)„pakartokite masyvą, sukurtą taip, kad būtų parodyta kiekviena vertėUž kiekvieną aš „MyArray“„MsgBox I“Toliau ašPabaiga Sub |
Šiame pavyzdyje ribotuvas nenurodytas, nes tarp visų žodžių yra tarpas, todėl galima naudoti numatytąjį skyriklį (tarpą).
Masyvas neturi matmenų ir yra nustatytas kaip eilutė. Kintamasis I, naudojamas cikle „For… Next“, turi būti matuojamas kaip variantas.
Kai šis kodas bus paleistas, jame bus rodomi keturi pranešimų langeliai, po vieną kiekvienam skilimui, pvz. Vienas du trys. Keturi.
Atminkite, kad jei tarp eilutės žodžių yra dvigubas tarpas, tai bus vertinama kaip suskaidymas, nors jame nieko nėra. Tai gali būti ne rezultatas, kurį norite matyti.
Šią problemą galite išspręsti naudodami funkciją Pakeisti, kad pakeistumėte dvigubas erdves viena vieta:
| 1 | MyString = Pakeisti (MyString, "", "") |
Užpakalinė arba pirmaujanti erdvė taip pat gali sukelti problemų sukurdama tuščią skilimą. Tai dažnai labai sunku pastebėti. Šias pašalines vietas galite pašalinti naudodami apipjaustymo funkciją:
| 1 | MyString = Apkirpti (MyString) |
Skaidymo funkcijos naudojimas su skiriamuoju simboliu
Galime naudoti kabliataškio ribotuvą (;). Tai dažnai randama el. Pašto adresų eilutėse, skirtose atskirti adresus. Jums gali būti išsiųstas el. Laiškas, kuris yra bendrinamas su daugeliu kolegų, ir norite savo darbalapyje matyti sąrašą, kam jis buvo skirtas. Pašto adresus galite lengvai nukopijuoti iš el. Laiškų „Kam“ arba „Kopijuoti“ ir į savo kodą.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Apibrėžkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė, aš kaip variantas, N kaip sveikasis skaičius„Eilutės pavyzdys su dvitaškio skiriamuoju ženkluMyString = "[email protected]; [email protected]; [email protected]; [email protected]"„Naudokite skaidymo funkciją, kad padalintumėte eilutės sudedamąsias dalis„MyArray“ = padalintas („MyString“, „;“)'Išvalykite darbalapįActiveSheet.UsedRange.Clear„kartokite masyvąN = 0 iki „UBound“ („MyArray“)Įdėkite kiekvieną el. Pašto adresą į pirmąjį darbalapio stulpelįDiapazonas („A“ ir N + 1). Vertė = „MyArray“ (N)Kitas N.Pabaiga Sub |
Atminkite, kad ciklas „For… Next“ naudojamas kartoti masyvą. Pirmasis masyvo elementas visada prasideda nuo nulio, o viršutinės ribos funkcija naudojama maksimaliam elementų skaičiui gauti.
Paleidus šį kodą, jūsų darbalapis atrodys taip:
Ribinio parametro naudojimas skaidant funkciją
Ribinis parametras leidžia atlikti tam tikrą padalijimų skaičių nuo eilutės pradžios. Deja, jūs negalite pateikti pradinės padėties ar daugybės padalijimų, kuriuos reikia atlikti, todėl tai yra gana paprasta. Galite sukurti savo VBA kodą, kad sukurtumėte tam skirtą funkciją, ir tai bus paaiškinta vėliau šiame straipsnyje.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė, aš kaip variantas, N kaip sveikasis skaičius„Eilutės pavyzdys su kableliaisMyString = "Vienas, du, trys, keturi, penki, šeši"„Naudokite skaidymo funkciją, kad padalintumėte eilutės sudedamąsias dalis„MyArray“ = padalintas („MyString“, „,“, 4)'Išvalykite darbalapįActiveSheet.UsedRange.Clear„Pakartokite masyvąN = 0 iki „UBound“ („MyArray“)„Įdėkite kiekvieną skiltį į pirmąjį darbalapio stulpelįDiapazonas („A“ ir N + 1). Vertė = „MyArray“ (N)Kitas N.Pabaiga Sub |
Paleidus šį kodą, jūsų darbalapis atrodys taip:
Tik pirmosios trys padalijimo vertės rodomos atskirai. Vėlesnės trys reikšmės rodomos kaip viena ilga eilutė ir nėra padalijamos.
Jei pasirinksite ribinę vertę, kuri yra didesnė už eilutėje esančių skyriklių skaičių, tai nesukels klaidos. Eilutė bus padalyta į visas sudedamąsias dalis, tarsi nebūtų pateikta ribinė vertė.
Palyginimo parametro naudojimas skaidant funkciją
Palyginimo parametras nustato, ar skiriamieji ženklai yra didžiosios ir mažosios raidės. Tai netaikoma, jei skiriamieji ženklai yra kableliai, dvitaškiai arba dvitaškiai.
Pastaba: Vietoj to visada galite įdėti parinktį Palyginti tekstą <> modulio viršuje, kad pašalintumėte didžiųjų ir mažųjų raidžių jautrumą visam moduliui.
| 123456789101112131415 | Sub SplitByCompareExample ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė, aš kaip variantas, N kaip sveikasis skaičius„Eilutės pavyzdys su X ribotuvaisMyString = "OneXTwoXThreexFourXFivexSix"„Naudokite skaidymo funkciją, kad padalintumėte eilutės sudedamąsias dalisMyArray = Split (MyString, "X",, vbBinaryCompare)'Išvalykite darbalapįActiveSheet.UsedRange.Clear„kartokite masyvąN = 0 iki „UBound“ („MyArray“)„Įdėkite kiekvieną skiltį į pirmąjį darbalapio stulpelįDiapazonas („A“ ir N + 1). Vertė = „MyArray“ (N)Kitas N.Pabaiga Sub |
Šiame pavyzdyje skaidoma eilutė naudoja simbolį „X“ kaip ribotuvą. Tačiau šioje eilutėje yra didžiųjų ir mažųjų raidžių „X“ simbolių mišinys. Funkcijos „Split“ parametras „Palyginti“ naudoja didžiąsias „X“ simbolius.
Jei parametras „Palyginti“ nustatytas kaip „vbBinaryCompare“, mažosios raidės „x“ bus ignoruojamos ir jūsų darbalapis atrodys taip:
Jei parametras „Palyginti“ nustatytas kaip „vbTextCompare“, padalijime bus naudojamos mažosios raidės „x“, o jūsų darbalapis atrodys taip:
Atminkite, kad A6 langelio reikšmė yra sutrumpinta, nes joje yra mažosios raidės „x“. Kadangi padalijimas neskiria didžiųjų ir mažųjų raidžių, bet koks skirstytuvas, kuris yra antrinės eilutės dalis, įvyks suskaidymas.
Tai svarbu atsiminti, kai naudojate teksto ribotuvą ir „vbTextCompare“. Galite lengvai gauti neteisingą rezultatą.
Nespausdinamų simbolių naudojimas kaip skiriamųjų ženklų simbolis
Kaip ribotuvą galite naudoti nespausdinamus simbolius, pvz., Vežimo grįžimą (eilutės pertrauka).
Čia mes naudojame vbCr, norėdami nurodyti vežimo grąžą <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė, aš kaip variantas, N kaip sveikasis skaičius„Pavyzdinė eilutė su vežimėlio grąžinimo ribotuvaisMyString = "Vienas" & vbCr & "Du" & vbCr & "Trys" & vbCr & "Keturi" & vbCr & "Penki" & vbCr & "Šeši"„Naudokite skaidymo funkciją, kad padalintumėte eilutės sudedamąsias dalisMyArray = Split (MyString, vbCr,, vbTextCompare)'Išvalykite darbalapįActiveSheet.UsedRange.Clear„Pakartokite masyvąN = 0 iki „UBound“ („MyArray“)„Įdėkite kiekvieną skiltį į pirmąjį darbalapio stulpelįDiapazonas („A“ ir N + 1). Vertė = „MyArray“ (N)Kitas N.Pabaiga Sub |
Šiame pavyzdyje eilutė sukurta naudojant vbCr (vežimo grąžinimo simbolį) kaip ribotuvą.
Kai šis kodas bus paleistas, jūsų darbalapis atrodys taip:
Prisijungimo funkcijos naudojimas padalijimui pakeisti
Funkcija „Prisijungti“ vėl sujungs visus masyvo elementus, bet naudodama nurodytą ribotuvą. Jei nenurodytas skiriamasis simbolis, bus naudojamas tarpas.
| 123456789101112131415 | Sub JoinExample ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė, aš kaip variantas, N kaip sveikasis skaičiusPritemdyti taikinį kaip eilutę„Eilutės pavyzdys su kableliaisMyString = "Vienas, du, trys, keturi, penki, šeši"„Įdėkite„ MyString “į langelį A1Diapazonas („A1“). Vertė = „MyString“„Naudokite skaidymo funkciją, kad padalintumėte eilutės sudedamąsias dalis„MyArray“ = padalintas („MyString“, „,“)„Naudokite funkciją„ Prisijungti “, kad iš naujo sukurtumėte pradinę eilutę naudodami dvitaškio skiriamąjį ženkląTikslas = prisijungti („MyArray“; “)“„Įdėkite rezultatų eilutę į langelį A2Diapazonas („A2“). Vertė = tikslasPabaiga Sub |
Šis kodas į masyvą padalija eilutę su kableliais ir vėl sujungia ją naudodami dvitaškių skyriklių.
Paleidus šį kodą, jūsų darbalapis atrodys taip:
A1 langelyje yra originali eilutė su kableliais, o A2 langelyje yra nauja sujungta eilutė su dvitaškiais.
„Split“ funkcijos naudojimas žodžių skaičiavimui
Turint omenyje, kad „Excel VBA“ eilutės kintamasis gali būti iki 2 Gb ilgio, galite naudoti padalijimo funkciją, kad suskaičiuotumėte žodžius tekste. Akivaizdu, kad „Microsoft Word“ tai daro automatiškai, tačiau tai gali būti naudinga paprastam tekstiniam failui arba tekstui, nukopijuotam iš kitos programos.
| 1234567891011121314 | Antrinis skaičiusOfWordsExample ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė„Eilutės pavyzdys su tarpų ribotuvaisMyString = "Vienas du trys keturi penki šeši"„Pašalinkite dvigubus tarpusMyString = Pakeisti (MyString, "", "")„Pašalinkite visas priekines ar užpakalines vietasMyString = Apkirpti (MyString)„Naudokite skaidymo funkciją, kad padalintumėte eilutės sudedamąsias dalisMyArray = Split (MyString)„Rodykite žodžių skaičių naudodami funkciją„ UBound “„MsgBox“ „Žodžių skaičius“ ir „UBound“ („MyArray“) + 1Pabaiga Sub |
Vienas iš šio žodžių skaičiaus kodo pavojų yra tas, kad jį išmes dvigubos ir priekinės bei galinės erdvės. Jei jų yra, jie bus skaičiuojami kaip papildomi žodžiai, o žodžių skaičius bus netikslus.
Kodas naudoja funkcijas Pakeisti ir Apkirpti, kad pašalintų šias papildomas vietas.
Paskutinėje kodo eilutėje rodomas žodžių, rastų naudojant funkciją „UBound“, siekiant gauti maksimalų masyvo elemento skaičių, o po to jį padidinti 1. Taip yra todėl, kad pirmasis masyvo elementas prasideda nuo nulio.
Adreso skaidymas į darbalapio langelius
Pašto adresai dažnai yra ilgos teksto eilutės su kableliais. Galbūt norėsite padalinti kiekvieną adreso dalį į atskirą langelį.
| 123456789101112131415 | Papildomas adresasPavyzdys ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė, N kaip sveikasis skaičius„Nustatykite eilutę naudodami„ Microsoft Corporation “adresąMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"„Norėdami padalinti eilutę naudodami kablelį, naudokite skaidymo funkciją„MyArray“ = padalintas („MyString“, „,“)'Išvalykite darbalapįActiveSheet.UsedRange.Clear„kartokite masyvąN = 0 iki „UBound“ („MyArray“)„Įdėkite kiekvieną skiltį į pirmąjį darbalapio stulpelįDiapazonas („A“ ir N + 1). Vertė = „MyArray“ (N)Kitas N.Pabaiga Sub |
Vykdant šį kodą bus naudojamas kablelis, kad kiekviena adreso eilutė būtų įtraukta į atskirą langelį:
Jei norite grąžinti tik pašto kodą (paskutinį masyvo elementą), galite naudoti kodą:
| 123456789101112 | Papildomas adresasZipCodeExample ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė, N kaip sveikasis, Temp kaip eilutė„Nustatykite eilutę naudodami„ Microsoft Corporation “adresąMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"„Norėdami padalinti eilutę naudodami kablelį, naudokite skaidymo funkciją„MyArray“ = padalintas („MyString“, „,“)'Išvalykite darbalapįActiveSheet.UsedRange.Clear„Įveskite pašto kodą į langelį A1Diapazonas („A1“). Vertė = „MyArray“ („UBound“ („MyArray“))Pabaiga Sub |
Bus naudojamas tik paskutinis masyvo elementas, kuris randamas naudojant funkciją „UBound“.
Kita vertus, galbūt norėsite matyti visas eilutes viename lange, kad jas būtų galima atspausdinti ant adreso etiketės:
| 1234567891011121314151617 | Papildomas adresasPavyzdys ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė, N kaip sveikasis, Temp kaip eilutė„Nustatykite eilutę naudodami„ Microsoft Corporation “adresąMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"„Norėdami padalinti eilutę naudodami kablelį, naudokite skaidymo funkciją„MyArray“ = padalintas („MyString“, „,“)'Išvalykite darbalapįActiveSheet.UsedRange.Clear„kartokite masyvąN = 0 iki „UBound“ („MyArray“)'į eilutę įdėkite kiekvieną masyvo elementą ir eilutės sklaidos ženkląTemp = Temp & MyArray (N) & vbLfKitas N.„Įdėkite eilutę į darbalapįDiapazonas ("A1") = tempPabaiga Sub |
Šis pavyzdys veikia taip pat, kaip ir ankstesnis, išskyrus tai, kad sukuriama laikina visų masyvo elementų eilutė, tačiau po kiekvieno elemento įterpiamas eilutės sklaidos simbolis.
Paleidus kodą, darbalapis atrodys taip:
Skaidyti eilutę į darbalapio langelius
„Split“ masyvą galite nukopijuoti į darbalapio langelius <> tik viena komanda:
| 12345678910 | Sub CopyToRange ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė„Eilutės pavyzdys su tarpų ribotuvaisMyString = "Vienas, du, trys, keturi, penki, šeši"„Naudokite skaidymo funkciją, kad padalintumėte eilutės sudedamąsias dalis„MyArray“ = padalintas („MyString“, „,“)„Nukopijuokite masyvą į darbalapįDiapazonas („A1: A“ ir „UBound“ („MyArray“) + 1). Value = WorksheetFunction.Transpose („MyArray“)Pabaiga Sub |
Kai šis kodas bus paleistas, jūsų darbalapis atrodys taip:
Sukurkite naują funkciją, leidžiančią suskaidyti iš tam tikro taško
Funkcijos „Split“ parametras „Limit“ leidžia nurodyti tik viršutinę ribą, kurioje norite suskaidyti. Jis visada prasideda nuo eilutės pradžios.
Būtų labai naudinga turėti panašią funkciją, kurioje galite nurodyti eilutės padalijimo pradžios tašką ir padalijimų skaičių, kurį norite matyti nuo to momento. Tai taip pat išskleis tik jūsų nurodytus skaidymus į masyvą, o ne turės didžiulę eilutės reikšmę kaip paskutinį masyvo elementą.
Norėdami tai padaryti, VBA galite lengvai sukurti funkciją (vadinamą „SplitSlicer“):
| 123456789101112131415161718192021222324 | Funkcija „SplitSlicer“ (Tikslas kaip eilutė, Del kaip eilutė, Pradėti kaip sveikasis skaičius, N kaip sveikasis skaičius)„Sukurkite masyvo kintamąjįDim MyArray () kaip eilutė„Užfiksuokite padalijimą naudodami pradžios kintamąjį, naudodami skiriamąjį ženklą„MyArray“ = padalintas („Target“, „Del“, „Start“)„Patikrinkite, ar pradžios parametras yra didesnis nei padalijimų skaičius - tai gali sukelti problemųJei Pradėti> „UBound“ („MyArray“) + 1 Tada„Rodyti klaidą ir išeiti iš funkcijos„MsgBox“ „Pradžios parametras yra didesnis nei galimų padalijimų skaičius“„SplitSlicer“ = „MyArray“Išėjimo funkcijaPabaiga JeiĮdėkite paskutinį masyvo elementą į eilutęTikslas = „MyArray“ („UBound“ („MyArray“))„Padalinkite eilutę naudodami ribą N“„MyArray“ = padalintas („Target“, „Del“, „N“)„Patikrinkite, ar viršutinė riba yra didesnė už nulį, nes kodas pašalina paskutinį elementąJei „UBound“ („MyArray“)> 0 Tada„Naudokite„ ReDim “, kad pašalintumėte galutinį masyvo elementą„ReDim Preserve MyArray“ („UBound“ („MyArray“) - 1)Pabaiga Jei'Grąžinkite naują masyvą„SplitSlicer“ = „MyArray“Pabaigos funkcija |
Ši funkcija sukurta pagal keturis parametrus:
- Tikslas - eilutė - tai įvesties eilutė, kurią norite padalyti
- Del - eilutė arba nespausdinamas simbolis - tai yra skiriamasis simbolis, kurį naudojate, pvz. kablelis, dvitaškis
- Pradėti - skaičius - tai jūsų skilties pradžios padalijimas
- N - skaičius - tai padalijimų, kuriuos norite atlikti savo skiltyje, skaičius
Nė vienas iš šių parametrų nėra neprivalomas arba neturi numatytųjų verčių, tačiau galite jį įtraukti į funkcijos kodą, jei norite jį išplėsti.
Funkcija naudoja funkciją „Split“, kad sukurtų masyvą, naudojant „Start“ parametrą kaip ribą. Tai reiškia, kad masyvo elementai sulaužys iki pradžios parametro, tačiau likusi eilutės dalis bus paskutinis elementas ir nebus skaidoma.
Paskutinis masyvo elementas perkeliamas atgal į eilutę, naudojant funkciją „UBound“, siekiant nustatyti, kuris tai elementas.
Tada eilutė vėl padalijama į masyvą, naudojant ribinį kintamąjį N. Tai reiškia, kad eilutė bus padalyta iki padėties N, o po to likusi eilutė sudarys paskutinį masyvo elementą.
„ReDim“ teiginys naudojamas paskutiniam elementui pašalinti, nes norime, kad masyve liktų tik konkretūs elementai. Atminkite, kad naudojamas išsaugojimo parametras, kitaip visi masyvo duomenys bus prarasti.
Tada naujas masyvas grąžinamas į kodą, iš kurio jis buvo iškviestas.
Atminkite, kad kodas yra „apsaugotas nuo klaidų“. Vartotojai dažnai darys keistus dalykus, apie kuriuos jūs nesvarstėte. Pvz., Jei jie bando naudoti funkciją su parametru „Start“ arba „N“, viršijančiu turimą padalijimų skaičių eilutėje, tai gali sukelti funkcijos gedimą.
Įtrauktas kodas, skirtas patikrinti pradžios vertę, taip pat įsitikinti, kad yra elementas, kurį galima pašalinti, kai masyve naudojama „ReDim“ formuluotė.
Čia yra kodas funkcijai patikrinti:
| 123456789101112 | Sub TestSplitSlicer ()'Sukurkite kintamuosiusDim MyArray () kaip eilutė, MyString kaip eilutė„Apibrėžkite eilutės pavyzdį kableliaisMyString = "Vienas, du, trys, keturi, penki, šeši, septyni, aštuoni, devyni, dešimt"„Norėdami apibrėžti naują masyvą, naudokite„ Splitslicer “funkciją„MyArray“ = „SplitSlicer“ („MyString“, „,“, 4, 3)'Išvalykite aktyvų lapąActiveSheet.UsedRange.Clear„Nukopijuokite masyvą į darbalapįDiapazonas („A1: A“ ir „UBound“ („MyArray“) + 1). Value = WorksheetFunction.Transpose („MyArray“)Pabaiga Sub |
Paleiskite šį kodą ir jūsų darbalapis atrodys taip:
