Izmantojot VBA sadalīšanas funkciju
Funkcija VBA Split ļauj atdalīt sastāvdaļas no standarta teksta virknes, kur katrs komponents izmanto noteiktu norobežotāja rakstzīmi, piem. komats vai kols. To ir vieglāk izmantot, nekā rakstīt kodu, lai atrastu norobežotājus virknē un pēc tam iegūtu vērtības.
To var izmantot, ja lasāt rindā no komatatdalītas vērtības (CSV faila) vai ja jums ir pasta adrese, kas ir vienā rindā, bet vēlaties to redzēt kā vairākas rindas.
Sintakse ir šāda:
| 1 | Sadalīt izteiksmi, norobežotājs [pēc izvēles], ierobežojums [pēc izvēles], salīdzināt [pēc izvēles] |
Funkcijai VBA Split ir četri parametri:
- Izteiksme - Teksta virkne, kuru vēlaties sadalīt dažādās daļās.
- Atdalītājs (neobligāti)- virkne vai neizdrukājama rakstzīme - nosaka atdalītāja rakstzīmi, kas tiks izmantota sadalīšanai. Ja nav norādīta atdalītāja rakstzīme, tiek izmantota atstarpes noklusējuma vērtība.
- Ierobežot (neobligāti) - skaits - nosaka, cik sadalījumu tiks veikta. Ja tukšs, visi pieejamie sadalījumi tiks veikti virknē. Ja tas ir iestatīts uz 1, tad sadalījumi netiks veikti. Būtībā tas ļauj nošķirt noteiktu vērtību skaitu, sākot no virknes sākuma, piem. kur virkne ir ļoti gara un jums ir nepieciešami tikai pirmie trīs sadalījumi.
- Salīdzināt (neobligāti) - Ja jūsu norobežotājs ir teksta rakstzīme, to izmanto, lai pārslēgtu, vai norobežotājs ir reģistrjutīgs vai nē. Vērtības ir vbBinaryCompare (reģistrjutīgas) un vbTextCompare (nav reģistrjutīgas).
Sadalīšanas funkcija vienmēr atgriež masīvu.
Vienkāršs sadalītās funkcijas piemērs
| 123456789101112 | Sub SplitExample ()'Definējiet mainīgosDim MyArray () kā virkne, mana virkne kā virkne, es kā variants'Parauga virkne ar atstarpju atdalītājiemMyString = "Viens divi trīs četri"'Izmantojiet sadalīšanas funkciju, lai sadalītu virknes sastāvdaļasMyArray = Split (MyString)atkārtojiet masīvu, kas izveidots, lai parādītu katru vērtībuKatram I In MyArrayMsgBox ITālāk esBeigu apakš |
Šajā piemērā neviens norobežotājs nav norādīts, jo starp visiem vārdiem ir atstarpe, tāpēc var izmantot noklusējuma norobežotāju (atstarpi).
Masīvam nav izmēru, un tas ir iestatīts kā virkne. Mainīgais I, kas tiek izmantots cilnē For… Next, ir jāizmēra kā variants.
Kad šis kods tiek palaists, tas parādīs četrus ziņojumu lodziņus, pa vienam katram sadalījumam, piem. Viens divi trīs. Četri.
Ņemiet vērā: ja starp vārdiem virknē ir dubultā atstarpe, tas tiks novērtēts kā sadalījums, lai gan tajā nav nekā. Tas var nebūt rezultāts, kuru vēlaties redzēt.
Šo problēmu var novērst, izmantojot funkciju Aizstāt, lai dubultās atstarpes aizstātu ar vienu atstarpi:
| 1 | MyString = Aizstāt (MyString, "", "") |
Aizmugurējā vai vadošā vieta var radīt arī problēmas, veidojot tukšu sadalījumu. Tos bieži ir ļoti grūti pamanīt. Jūs varat noņemt šīs svešās vietas, izmantojot apgriešanas funkciju:
| 1 | MyString = apgriezt (MyString) |
Sadalītās funkcijas izmantošana ar norobežotāja rakstzīmi
Mēs varam izmantot semikolu atdalītāju (;). Tas bieži atrodams e -pasta adrešu virknēs, lai atdalītu adreses. Iespējams, jums tiek nosūtīts e -pasts, kas tiek kopīgots ar vairākiem kolēģiem, un jūs vēlaties savā darblapā redzēt sarakstu ar to, kam tas ir nosūtīts. Jūs varat viegli kopēt e -pasta adreses no e -pasta lodziņiem “Kam” vai “Kopēt” un savā kodā.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Definējiet mainīgosDim MyArray () kā virkne, mana virkne kā virkne, es kā iespēja, N kā vesels skaitlis'Parauga virkne ar semikolu atdalītājiemMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Izmantojiet sadalīšanas funkciju, lai sadalītu virknes sastāvdaļasMyArray = Sadalīts (MyString, ";")'Notīriet darblapuActiveSheet.UsedRange.Clearatkārtojiet masīvuN = 0 līdz UBound (MyArray)Ievietojiet katru e -pasta adresi darblapas pirmajā kolonnāDiapazons ("A" & N + 1). Vērtība = MyArray (N)Nākamais N.Beigu apakš |
Ņemiet vērā, ka For… Next cilpa tiek izmantota, lai atkārtotu masīvu. Masīva pirmais elements vienmēr sākas ar nulli, un augšējās robežas funkcija tiek izmantota, lai iegūtu maksimālo elementu skaitu.
Pēc šī koda palaišanas darblapa izskatīsies šādi:
Limita parametra izmantošana sadalītā funkcijā
Limita parametrs ļauj noteikt virkni sadalījumu no virknes sākuma. Diemžēl jūs nevarat nodrošināt sākuma pozīciju vai veicamo sadalījumu diapazonu, tāpēc tas ir diezgan vienkārši. Jūs varat izveidot savu VBA kodu, lai izveidotu funkciju, lai to izdarītu, un tas tiks izskaidrots vēlāk šajā rakstā.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Izveidojiet mainīgosDim MyArray () kā virkne, mana virkne kā virkne, es kā iespēja, N kā vesels skaitlis'Virknes paraugs ar komatu atdalītājiemMyString = "Viens, divi, trīs, četri, pieci, seši"'Izmantojiet sadalīšanas funkciju, lai sadalītu virknes sastāvdaļasMyArray = Split (MyString, ",", 4)'Notīriet darblapuActiveSheet.UsedRange.Clear'Atkārtojiet masīvuN = 0 līdz UBound (MyArray)'Katru sadalījumu ievietojiet darblapas pirmajā kolonnāDiapazons ("A" & N + 1). Vērtība = MyArray (N)Nākamais N.Beigu apakš |
Pēc šī koda palaišanas darblapa izskatīsies šādi:
Tikai pirmās trīs sadalījuma vērtības tiek parādītas atsevišķi. Pēdējās trīs vērtības tiek parādītas kā viena gara virkne, un tās netiek sadalītas.
Ja izvēlaties robežvērtību, kas ir lielāka par virknes norobežotāju skaitu, tas neradīs kļūdu. Virkne tiks sadalīta visās tās sastāvdaļās tā, it kā robežvērtība nebūtu norādīta.
Salīdzināšanas parametra izmantošana sadalītā funkcijā
Parametrs Salīdzināt nosaka, vai norobežotājs ir reģistrjutīgs vai nē. Tas nav piemērojams, ja norobežotāji ir komati, semikoli vai koli.
Piezīme. Tā vietā jūs vienmēr varat novietot opciju Salīdzināt tekstu <> moduļa augšdaļā, lai novērstu visa moduļa reģistrjutību.
| 123456789101112131415 | Sub SplitByCompareExample ()'Izveidojiet mainīgosDim MyArray () kā virkne, mana virkne kā virkne, es kā iespēja, N kā vesels skaitlis'Parauga virkne ar X norobežotājiemMyString = "OneXTwoXThreexFourXFivexSix"'Izmantojiet sadalīšanas funkciju, lai sadalītu virknes sastāvdaļasMyArray = Split (MyString, "X",, vbBinaryCompare)'Notīriet darblapuActiveSheet.UsedRange.Clearatkārtojiet masīvuN = 0 līdz UBound (MyArray)Ievietojiet katru sadalījumu darblapas pirmajā kolonnāDiapazons ("A" & N + 1). Vērtība = MyArray (N)Nākamais N.Beigu apakš |
Šajā piemērā sadalāmā virkne kā atdalītāju izmanto rakstzīmi “X”. Tomēr šajā virknē ir apvienoti lielie un mazie burti “X”. Funkcijas Sadalīšana parametrā Salīdzināt tiek izmantoti lielie burti “X”.
Ja parametrs Salīdzināt ir iestatīts uz vbBinaryCompare, tad mazie burti “x” tiks ignorēti un darblapa izskatīsies šādi:
Ja parametrs Salīdzināt ir iestatīts uz vbTextCompare, tad sadalījumā tiks izmantotas mazās burta “x” rakstzīmes un darblapa izskatīsies šādi:
Ņemiet vērā, ka vērtība šūnā A6 ir saīsināta, jo tajā ir mazais burts “x”. Tā kā sadalījums nav reģistrjutīgs, jebkurš norobežotājs, kas ir daļa no apakšvirknes, izraisīs sadalīšanu.
Tas ir svarīgs punkts, kas jāpatur prātā, izmantojot teksta norobežotāju un vbTextCompare. Jūs varat viegli nonākt pie nepareiza rezultāta.
Neizdrukājamu rakstzīmju izmantošana kā norobežotāja rakstzīme
Kā norobežotāju varat izmantot neizdrukājamas rakstzīmes, piemēram, ratiņu atgriešanu (rindas pārtraukumu).
Šeit mēs izmantojam vbCr, lai norādītu pārvadājuma atgriešanu <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Izveidojiet mainīgosDim MyArray () kā virkne, mana virkne kā virkne, es kā iespēja, N kā vesels skaitlis'Parauga virkne ar ratiņu atgriešanas norobežotājiemMyString = "Viens" & vbCr & "Divi" & vbCr & "Trīs" & vbCr & "Četri" & vbCr & "Pieci" & vbCr & "Seši"'Izmantojiet sadalīšanas funkciju, lai sadalītu virknes sastāvdaļasMyArray = Split (MyString, vbCr,, vbTextCompare)'Notīriet darblapuActiveSheet.UsedRange.Clear'Atkārtojiet masīvuN = 0 līdz UBound (MyArray)Ievietojiet katru sadalījumu darblapas pirmajā kolonnāDiapazons ("A" & N + 1). Vērtība = MyArray (N)Nākamais N.Beigu apakš |
Šajā piemērā virkne tiek veidota, izmantojot atdalītāju vbCr (ratiņu atgriešanās rakstzīme).
Palaižot šo kodu, jūsu darblapa izskatīsies šādi:
Pievienošanās funkcijas izmantošana, lai mainītu sadalījumu
Funkcija Savienot atkārtoti apvienos visus masīva elementus, bet izmantojot noteiktu norobežotāju. Ja nav norādīta atdalītāja rakstzīme, tiks izmantota atstarpe.
| 123456789101112131415 | Sub JoinExample ()'Izveidojiet mainīgosDim MyArray () kā virkne, mana virkne kā virkne, es kā iespēja, N kā vesels skaitlisAptumšojiet mērķi kā virkni'Virknes paraugs ar komatu atdalītājiemMyString = "Viens, divi, trīs, četri, pieci, seši"Ievietojiet MyString šūnā A1Diapazons ("A1"). Vērtība = MyString'Izmantojiet sadalīšanas funkciju, lai sadalītu virknes sastāvdaļasMyArray = Sadalīts (MyString, ",")“Izmantojiet funkciju Pievienoties, lai no jauna izveidotu sākotnējo virkni, izmantojot atdalītāju ar semikoluMērķis = Pievienoties (MyArray, ”;”)'Novietojiet rezultātu virkni šūnā A2Diapazons ("A2"). Vērtība = mērķisBeigu apakš |
Šis kods sadala virkni ar komatu atdalītājiem masīvā un atkal savieno to kopā, izmantojot semikolu atdalītājus.
Pēc šī koda palaišanas darblapa izskatīsies šādi:
Šūnai A1 ir sākotnējā virkne ar komatu atdalītājiem, un šūnai A2 ir jaunā savienotā virkne ar semikolu atdalītājiem.
Sadalīšanas funkcijas izmantošana vārdu skaitīšanai
Paturot prātā, ka virknes mainīgais programmā Excel VBA var būt līdz 2 Gb garš, varat izmantot sadalīšanas funkciju, lai tekstā noteiktu vārdu skaitu. Acīmredzot Microsoft Word to dara automātiski, taču tas varētu būt noderīgi vienkāršam teksta failam vai tekstam, kas nokopēts no citas lietojumprogrammas.
| 1234567891011121314 | ApakšnumursOfWordsExample ()'Izveidojiet mainīgosDim MyArray () kā virkne, MyString kā virkne'Parauga virkne ar atstarpju atdalītājiemMyString = "Viens divi trīs četri pieci seši"'Noņemiet dubultās atstarpesMyString = Aizstāt (MyString, "", "")“Noņemiet visas priekšējās vai beigu vietasMyString = Apgriezt (MyString)'Izmantojiet sadalīšanas funkciju, lai sadalītu virknes sastāvdaļasMyArray = Split (MyString)'Parādiet vārdu skaitu, izmantojot funkciju UBoundMsgBox "Vārdu skaits" un UBound (MyArray) + 1Beigu apakš |
Viena no šī vārdu skaitīšanas koda briesmām ir tā, ka to izmetīs dubultās atstarpes un priekšējās un beigu vietas. Ja tie ir, tie tiks uzskaitīti kā papildu vārdi, un vārdu skaits beigsies kā neprecīzs.
Lai noņemtu šīs papildu atstarpes, kods izmanto funkcijas Aizstāt un Apgriezt.
Pēdējā koda rindiņa parāda vārdu skaitu, kas atrasts, izmantojot funkciju UBound, lai iegūtu masīva maksimālo elementu skaitu un pēc tam palielinātu to par 1. Tas ir tāpēc, ka pirmais masīva elements sākas ar nulli.
Adreses sadalīšana darblapas šūnās
Pasta adreses bieži ir garas teksta virknes ar komatu atdalītājiem. Iespējams, vēlēsities sadalīt katru adreses daļu atsevišķā šūnā.
| 123456789101112131415 | ApakšadresePiemērs ()'Izveidojiet mainīgosDim MyArray () kā virkne, mana virkne kā virkne, N kā vesels skaitlis'Iestatiet virkni ar Microsoft Corporation adresiMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"“Izmantojiet sadalīšanas funkciju, lai sadalītu virkni, izmantojot komatu atdalītājuMyArray = Sadalīts (MyString, ",")'Notīriet darblapuActiveSheet.UsedRange.Clearatkārtojiet masīvuN = 0 līdz UBound (MyArray)'Katru sadalījumu ievietojiet darblapas pirmajā kolonnāDiapazons ("A" & N + 1). Vērtība = MyArray (N)Nākamais N.Beigu apakš |
Izpildot šo kodu, tiks izmantots komatu atdalītājs, lai katru adreses rindu ievietotu atsevišķā šūnā:
Ja vēlaties atgriezt tikai pasta indeksu (pēdējā masīva elementu), varat izmantot kodu:
| 123456789101112 | ApakšadreseZipCodeExample ()'Izveidojiet mainīgosDim MyArray () kā virkne, mana virkne kā virkne, N kā vesels skaitlis, temp kā virkne'Iestatiet virkni ar Microsoft Corporation adresiMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"“Izmantojiet sadalīšanas funkciju, lai sadalītu virkni, izmantojot komatuMyArray = Sadalīts (MyString, ",")'Notīriet darblapuActiveSheet.UsedRange.ClearIevietojiet pasta indeksu šūnā A1Diapazons ("A1"). Vērtība = MyArray (UBound (MyArray))Beigu apakš |
Tas izmantos tikai masīva pēdējo elementu, kas tiek atrasts, izmantojot funkciju UBound.
No otras puses, iespējams, vēlēsities redzēt visas rindas vienā šūnā, lai tās varētu izdrukāt uz adreses etiķetes:
| 1234567891011121314151617 | ApakšadresePiemērs ()'Izveidojiet mainīgosDim MyArray () kā virkne, mana virkne kā virkne, N kā vesels skaitlis, temp kā virkne'Iestatiet virkni ar Microsoft Corporation adresiMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"“Izmantojiet sadalīšanas funkciju, lai sadalītu virkni, izmantojot komatuMyArray = Sadalīts (MyString, ",")'Notīriet darblapuActiveSheet.UsedRange.Clearatkārtojiet masīvuN = 0 līdz UBound (MyArray)'ievietojiet katru masīva elementu un rindas plūsmas rakstzīmi virknēTemp = Temp & MyArray (N) & vbLfNākamais N."Ievietojiet virkni darblapāDiapazons ("A1") = tempBeigu apakš |
Šis piemērs darbojas tāpat kā iepriekšējais, izņemot to, ka tas izveido pagaidu virkni no visiem masīva elementiem, bet ievietojot rindas plūsmas rakstzīmi pēc katra elementa.
Pēc koda palaišanas darblapa izskatīsies šādi:
Sadalīt virkni darblapas šūnās
Jūs varat kopēt sadalīto masīvu darblapas šūnās <> tikai ar vienu komandu:
| 12345678910 | Sub CopyToRange ()'Izveidojiet mainīgosDim MyArray () kā virkne, MyString kā virkne'Parauga virkne ar atstarpju atdalītājiemMyString = "Viens, divi, trīs, četri, pieci, seši"'Izmantojiet sadalīšanas funkciju, lai sadalītu virknes sastāvdaļasMyArray = Sadalīts (MyString, ",")'Kopējiet masīvu darblapāDiapazons ("A1: A" & UBound (MyArray) + 1). Vērtība = WorksheetFunction.Transpose (MyArray)Beigu apakš |
Kad šis kods ir izpildīts, jūsu darblapa izskatīsies šādi:
Jaunas funkcijas izveidošana, lai ļautu sadalīt no noteiktā punkta
Sadalīšanas funkcijas parametrs Limit ļauj norādīt tikai augšējo robežu, kurā vēlaties pārtraukt sadalīšanu. Tas vienmēr sākas no virknes sākuma.
Būtu ļoti noderīgi, ja būtu līdzīga funkcija, kurā varat norādīt virknes sadalījuma sākuma punktu un sadalījumu skaitu, ko vēlaties redzēt no šī brīža. Tas arī iegūs tikai jūsu norādītos sadalījumus masīvā, nevis milzīgu virknes vērtību kā masīva pēdējo elementu.
Lai to izdarītu, VBA varat viegli izveidot funkciju (sauktu par SplitSlicer):
| 123456789101112131415161718192021222324 | Funkcija SplitSlicer (Mērķis kā virkne, Del kā virkne, Sākt kā vesels skaitlis, N kā vesels skaitlis)'Izveidot masīva mainīgoDim MyArray () kā virkne'Uzņemiet sadalījumu, izmantojot sākuma mainīgo, izmantojot atdalītāja rakstzīmiMyArray = Split (Target, Del, Start)“Pārbaudiet, vai sākuma parametrs nav lielāks par sadalījumu skaitu - tas var radīt problēmasJa Sākt> UBound (MyArray) + 1 Tad“Parādīt kļūdu un iziet no funkcijasMsgBox "Sākuma parametrs ir lielāks par pieejamo sadalījumu skaitu"SplitSlicer = MyArrayIziet funkcijaBeigas Ja'Ievietojiet virknē pēdējo masīva elementuMērķis = MyArray (UBound (MyArray))'Sadaliet virkni, izmantojot ierobežojumu NMyArray = Split (Target, Del, N)“Pārbaudiet, vai augšējā robeža ir lielāka par nulli, jo kods noņem pēdējo elementuJa UBound (MyArray)> 0 Tad'Izmantojiet ReDim, lai noņemtu masīva pēdējo elementuReDim saglabā MyArray (UBound (MyArray) - 1)Beigas Ja'Atgrieziet jauno masīvuSplitSlicer = MyArrayBeigu funkcija |
Šī funkcija ir veidota ar četriem parametriem:
- Mērķis - virkne - šī ir ievades virkne, kuru vēlaties sadalīt
- Del - virkne vai neizdrukājama rakstzīme - šī ir atdalītāja rakstzīme, kuru izmantojat, piem. komats, kols
- Sākt - numurs - tas ir jūsu šķēles sākuma sadalījums
- N - numurs - tas ir sadalījumu skaits, ko vēlaties veikt savā šķēlītē
Neviens no šiem parametriem nav obligāts vai tiem ir noklusējuma vērtības, taču jūs varat to iekļaut funkcijas kodā, ja vēlaties to vēl pagarināt.
Funkcija izmanto funkciju Sadalīt, lai izveidotu masīvu, izmantojot parametru Sākt kā ierobežojumu. Tas nozīmē, ka masīva elementi sadalīs līdz sākuma parametram, bet pārējā virkne būs pēdējais elements un netiks sadalīta.
Masīva pēdējais elements tiek pārsūtīts atpakaļ uz virkni, izmantojot funkciju UBound, lai noteiktu, kurš tas ir.
Pēc tam virkne atkal tiek sadalīta masīvā, izmantojot N kā ierobežojuma mainīgo. Tas nozīmē, ka virkne tiks sadalīta līdz pozīcijai N, pēc kuras pārējā virkne veidos masīva pēdējo elementu.
ReDim paziņojums tiek izmantots, lai noņemtu pēdējo elementu, jo mēs vēlamies tikai konkrētos masīvā atstātos elementus. Ņemiet vērā, ka tiek izmantots parametrs Saglabāt, pretējā gadījumā visi masīvā esošie dati tiks zaudēti.
Pēc tam jaunais masīvs tiek atgriezts kodā, no kura tas tika izsaukts.
Ņemiet vērā, ka kods ir “pārbaudīts pret kļūdām”. Lietotāji bieži darīs dīvainas lietas, par kurām jūs neuzskatījāt. Piemēram, ja viņi mēģina izmantot funkciju ar parametru Sākt vai N lielāks par virknē pieejamo sadalījumu skaitu, tas, visticamāk, izraisīs funkcijas kļūmi.
Kods ir iekļauts, lai pārbaudītu sākuma vērtību, kā arī lai pārliecinātos, ka ir kāds elements, kuru var noņemt, kad masīvā tiek izmantots paziņojums ReDim.
Šeit ir kods, lai pārbaudītu funkciju:
| 123456789101112 | Sub TestSplitSlicer ()'Izveidojiet mainīgosDim MyArray () kā virkne, MyString kā virkne'Definējiet virknes paraugu ar komatu atdalītājiemMyString = "Viens, divi, trīs, četri, pieci, seši, septiņi, astoņi, deviņi, desmit"'Izmantojiet Splitslicer funkciju, lai definētu jaunu masīvuMyArray = SplitSlicer (MyString, ",", 4, 3)'Notīriet aktīvo lapuActiveSheet.UsedRange.Clear'Kopējiet masīvu darblapāDiapazons ("A1: A" & UBound (MyArray) + 1). Vērtība = WorksheetFunction.Transpose (MyArray)Beigu apakš |
Palaidiet šo kodu, un jūsu darblapa izskatīsies šādi:
