A VBA osztott funkció használata
A VBA Split funkció lehetővé teszi az alkatrészek szétválasztását egy szabványos szöveges karakterláncon belül, ahol minden összetevő egy meghatározott határoló karaktert használ, pl. vessző vagy kettőspont. Könnyebb használni, mint kódot írni, hogy megkeressük a karakterláncban lévő elválasztókat, majd kinyerjük az értékeket.
Használható, ha vesszővel elválasztott értékből (CSV fájl) olvas egy sort, vagy levelezési címe egy sorban van, de több sorban szeretné látni.
A szintaxis a következő:
| 1 | Felosztott kifejezés, elválasztó [nem kötelező], korlát [nem kötelező], összehasonlítás [nem kötelező] |
A VBA Split funkciónak négy paramétere van:
- Kifejezés - Az a szövegsor, amelyet különböző részekre szeretne bontani.
- Elhatároló (választható)- karakterlánc vagy nem nyomtatható karakter - Meghatározza a felosztáshoz használt határoló karaktert. Ha nincs elválasztó karakter, akkor az alapértelmezett szóközt használja.
- Határ (választható) - szám - Megadja, hogy hány szétválasztás történik. Ha üres, az összes rendelkezésre álló felosztás a karakterláncon belül történik. Ha 1 -re van állítva, akkor nem lesz felosztás. Alapvetően lehetővé teszi, hogy elkülönítsen egy bizonyos számú értéket a karakterlánc elején, pl. ahol a húr nagyon hosszú, és csak az első három felosztásra van szüksége.
- Hasonlítsa össze (választható) - Ha a határoló szöveges karakter, akkor ezzel lehet váltani, hogy a határoló megkülönbözteti-e a kis- és nagybetűket. Az értékek a vbBinaryCompare (kis- és nagybetű-érzékeny) és vbTextCompare (nem kis- és nagybetűk).
A split függvény mindig tömböt ad vissza.
Egyszerű példa az osztott függvényre
| 123456789101112 | RészosztásPélda ()'Definiálja a változókatDim MyArray () mint String, MyString As String, I As Variant'Minta karakterlánc szóközzelMyString = "Egy kettő három négy"'Használja az Osztás funkciót a karakterlánc összetevőinek felosztásáhozMyArray = Osztott (MyString)'iteráljon az egyes értékek megjelenítésére létrehozott tömbönFor Every I In MyArrayMsgBox I.Következő énEnd Sub |
Ebben a példában nincs megadva elválasztó, mert minden szó között van szóköz, így az alapértelmezett határoló (szóköz) használható.
A tömbnek nincs mérete, és karakterláncként van beállítva. A For… Next ciklusban használt I változót változatként kell méretezni.
Amikor ezt a kódot futtatja, négy üzenetdoboz jelenik meg, egy a felosztásokhoz, pl. Egy kettő három. Négy.
Ne feledje, hogy ha kettős szóköz van a karakterláncban szereplő szavak között, akkor ezt felosztásnak tekintjük, bár nincs benne semmi. Lehet, hogy nem ez az eredmény, amit látni szeretne.
Ezt a problémát a Csere funkció használatával javíthatja, ha a kettős szóközöket egyetlen szóközre cseréli:
| 1 | MyString = Csere (MyString, "", "") |
A záró vagy vezető mező is problémákat okozhat, ha üres felosztást hoz létre. Ezeket gyakran nagyon nehéz észrevenni. Ezeket az idegen tereket a Trim funkció használatával távolíthatja el:
| 1 | MyString = Vágás (MyString) |
Az osztott funkció használata elválasztó karakterrel
Használhatunk pontosvesszőt (;). Ez gyakran megtalálható az e -mail címsorokban a címek elválasztásához. Előfordulhat, hogy e -mailt küld Önnek, amelyet számos kollégájával megosztanak, és látni szeretné a munkalapon, hogy kinek a címe szerepel. Könnyedén átmásolhatja az e -mail címeket a „Címzett” vagy „Másolás” mezőkből a kódba.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Definiálja a változókatDim MyArray () mint karakterlánc, a MyString mint karakterlánc, én mint változat, N mint egész'Mintafüzér pontosvessző -határolókkalMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Használja az Osztás funkciót a karakterlánc összetevőinek felosztásáhozMyArray = Osztott (MyString, ";"))'Törölje a munkalapotActiveSheet.UsedRange.Clear'ismételje meg a tömbötN = 0 esetén UBound (MyArray)'Helyezze az egyes e -mail címeket a munkalap első oszlopábaTartomány ("A" & N + 1). Érték = MyArray (N)Következő N.End Sub |
Ne feledje, hogy a For… Next ciklus a tömb iterálására szolgál. A tömb első eleme mindig nulláról indul, és a Felső korlát funkciót használjuk a maximális elemszám eléréséhez.
A kód futtatása után a munkalapja így fog kinézni:
Határparaméter használata osztott függvényben
A limit paraméter lehetővé teszi, hogy a karakterlánc elejétől meghatározott számú osztást végezzen. Sajnos nem tudja megadni a kiindulási pozíciót vagy a felosztások sorát, ezért ez meglehetősen alapvető. Létrehozhat saját VBA -kódot egy funkció létrehozásához, és ezt a cikk későbbi részében ismertetjük.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Hozzon létre változókatDim MyArray () mint karakterlánc, a MyString mint karakterlánc, én mint változat, N mint egész'Minta karakterlánc vesszővel elválasztókkalMyString = "Egy, kettő, három, négy, öt, hat"'Használja az Osztás funkciót a karakterlánc összetevőinek felosztásáhozMyArray = Osztott (MyString, ",", 4)'Törölje a munkalapotActiveSheet.UsedRange.Clear- Ismételje meg a tömbötN = 0 esetén UBound (MyArray)'Helyezze az egyes felosztásokat a munkalap első oszlopábaTartomány ("A" & N + 1). Érték = MyArray (N)Következő N.End Sub |
A kód futtatása után a munkalapja így fog kinézni:
Csak az első három felosztási érték jelenik meg külön. A későbbi három érték egy hosszú karakterláncként jelenik meg, és nem válik szét.
Ha olyan határértéket választ, amely nagyobb, mint a karakterláncon belüli elválasztók száma, ez nem okoz hibát. A karakterlánc minden részre fel lesz osztva, mintha a határértéket nem adták volna meg.
Az összehasonlító paraméter használata osztott függvényben
Az Összehasonlítás paraméter határozza meg, hogy a határoló megkülönbözteti-e a kis- és nagybetűket. Ez nem alkalmazható, ha a határolók vessző, pontosvessző vagy kettőspont.
Megjegyzés: Ehelyett mindig elhelyezheti a Option Compare Text <> lehetőséget a modul tetején, hogy megszüntesse a kis- és nagybetűk megkülönböztetését.
| 123456789101112131415 | Sub SplitByCompareExample ()'Hozzon létre változókatDim MyArray () mint karakterlánc, a MyString mint karakterlánc, én mint változat, N mint egész'Minta karakterlánc X határolókkalMyString = "OneXTwoXThreexFourXFivexSix"'Használja az Osztás funkciót a karakterlánc összetevőinek felosztásáhozMyArray = Osztott (MyString, "X",, vbBinaryCompare)'Törölje a munkalapotActiveSheet.UsedRange.Clear'ismételje meg a tömbötN = 0 esetén UBound (MyArray)'Helyezze az egyes felosztásokat a munkalap első oszlopábaTartomány ("A" & N + 1). Érték = MyArray (N)Következő N.End Sub |
Ebben a példában az osztandó karakterlánc az „X” karaktert használja elválasztóként. Ebben a karakterláncban azonban nagy és kisbetűs „X” karakterek szerepelnek. A Split függvény Compare paramétere nagybetűs „X” karaktert használ.
Ha az Összehasonlítás paraméter beállítása vbBinaryCompare, akkor a kisbetűs „x” karaktereket figyelmen kívül hagyja, és a munkalapja így néz ki:
Ha az Összehasonlítás paraméter vbTextCompare értékre van állítva, akkor a felosztásban a kisbetűs „x” karaktereket kell használni, és a munkalapja így fog kinézni:
Ne feledje, hogy az A6 cella értéke csonka, mert kisbetűs „x” karaktert tartalmaz. Mivel a felosztás nem különbözteti meg a kis- és nagybetűket, az allánc részét képező elválasztók felosztást okoznak.
Ezt fontos szem előtt tartani a szöveghatároló és a vbTextCompare használatakor. Könnyen lehet, hogy rossz eredménnyel zárul.
Nem nyomtatható karakterek használata elválasztó karakterként
Elhatárolóként nem nyomtatható karaktereket is használhat, mint például kocsivissza (sortörés).
Itt a vbCr segítségével adjuk meg a kocsi visszatérését <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Hozzon létre változókatDim MyArray () mint karakterlánc, a MyString mint karakterlánc, én mint változat, N mint egész'Minta karakterlánc kocsi visszatérő határolókkalMyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"'Használja az Osztás funkciót a karakterlánc összetevőinek felosztásáhozMyArray = Osztott (MyString, vbCr,, vbTextCompare)'Törölje a munkalapotActiveSheet.UsedRange.Clear- Ismételje meg a tömbötN = 0 esetén UBound (MyArray)'Helyezze az egyes felosztásokat a munkalap első oszlopábaTartomány ("A" & N + 1). Érték = MyArray (N)Következő N.End Sub |
Ebben a példában egy karakterlánc épül fel vbCr (kocsi visszatérési karakter) elválasztóként.
A kód futtatásakor a munkalapja így fog kinézni:
A Csatlakozás funkció használata a felosztás megfordításához
A Csatlakozás függvény újra összekapcsolja a tömb összes elemét, de meghatározott határoló segítségével. Ha nincs megadva elválasztó karakter, akkor szóközt kell használni.
| 123456789101112131415 | Sub JoinPélda ()'Hozzon létre változókatDim MyArray () mint karakterlánc, a MyString mint karakterlánc, én mint változat, N mint egészCél halványítása karakterláncként'Minta karakterlánc vesszővel elválasztókkalMyString = "Egy, kettő, három, négy, öt, hat"'Helyezze a MyString -et az A1 cellábaTartomány ("A1"). Érték = MyString'Használja az Osztás funkciót a karakterlánc összetevőinek felosztásáhozMyArray = Osztott (MyString, ",")'Használja a Csatlakozás funkciót az eredeti karakterlánc újbóli létrehozásához, pontosvessző-elválasztó segítségévelCél = Csatlakozás (MyArray, ”;”)'Helyezze az eredményláncot az A2 cellábaTartomány ("A2"). Érték = célEnd Sub |
Ez a kód tömbre bontja a vesszővel elválasztó karakterláncokat, majd pontosvessző-elválasztókkal visszailleszti őket.
A kód futtatása után a munkalapja így fog kinézni:
Az A1 cella az eredeti karakterláncot vesszővel elválasztja, az A2 cella pedig az új összekapcsolt karakterláncot pontosvessző-elválasztókkal.
Az osztott függvény használata szószámláláshoz
Figyelembe véve, hogy az Excel VBA karakterlánc -változója akár 2 Gb hosszú is lehet, a felosztási funkcióval szövegesen is megszámolhatja a szavakat. Nyilvánvaló, hogy a Microsoft Word automatikusan elvégzi, de ez hasznos lehet egy egyszerű szöveges fájl vagy egy másik alkalmazásból másolt szöveg esetén.
| 1234567891011121314 | AlszámOfWordsExample ()'Hozzon létre változókatDim MyArray () mint karakterlánc, MyString mint karakterlánc'Minta karakterlánc szóközzelMyString = "Egy kettő három négy öt hat"'Törölje a kettős szóközöketMyString = Csere (MyString, "", "")'Távolítson el minden vezető vagy záró teretMyString = Vágás (MyString)'Használja az Osztás funkciót a karakterlánc összetevőinek felosztásáhozMyArray = Osztott (MyString)'Szavak számának megjelenítése az UBound funkcióvalMsgBox "Szavak száma" és UBound (MyArray) + 1End Sub |
Ennek a szószámkódnak az egyik veszélye, hogy kettős szóközök, valamint az első és a végső szóközök dobják el. Ha ezek jelen vannak, akkor azokat extra szavaknak számítjuk, és a szavak száma pontatlan lesz.
A kód a Replace és a Trim funkciókat használja az extra szóközök eltávolítására.
Az utolsó kódsor megjeleníti az UBound függvénnyel talált szavak számát, hogy megkapja a tömb maximális elemszámát, majd növelje 1 -gyel. Ez azért van, mert az első tömb elem nulláról indul.
Cím felosztása munkalap celláira
Az e -mail címek gyakran hosszú szövegsorok, vesszővel elválasztva. Érdemes lehet a cím minden részét külön cellára osztani.
| 123456789101112131415 | AlcímPélda ()'Hozzon létre változókatDim MyArray () mint karakterlánc, a MyString mint karakterlánc, N mint egész'Állítsa be a karakterláncot a Microsoft Corporation címévelMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Használja a split funkciót a karakterlánc felosztásához vesszővelMyArray = Osztott (MyString, ",")'Törölje a munkalapotActiveSheet.UsedRange.Clear'ismételje meg a tömbötN = 0 esetén UBound (MyArray)'Helyezze az egyes felosztásokat a munkalap első oszlopábaTartomány ("A" & N + 1). Érték = MyArray (N)Következő N.End Sub |
Ennek a kódnak a futtatása a vessző elválasztóját használja a cím minden sorának külön cellába helyezéséhez:
Ha csak az irányítószámot akarta visszaadni (utolsó tömb elem), akkor használhatja a kódot:
| 123456789101112 | AlcímZipCodeExample ()'Hozzon létre változókatDim MyArray () karakterláncként, MyString mint karakterlánc, N mint egész, Temp, mint karakterlánc'Állítsa be a karakterláncot a Microsoft Corporation címévelMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Használja a split funkciót a karakterlánc felosztásához vesszővelMyArray = Osztott (MyString, ",")'Törölje a munkalapotActiveSheet.UsedRange.Clear'Írja az irányítószámot az A1 cellábaTartomány ("A1"). Érték = MyArray (UBound (MyArray))End Sub |
Ez csak a tömb utolsó elemét fogja használni, amelyet az UBound függvény segítségével találunk meg.
Másrészről érdemes látni az összes sort egy cellában, hogy kinyomtathassák őket egy címkére:
| 1234567891011121314151617 | AlcímPélda ()'Hozzon létre változókatDim MyArray () karakterláncként, MyString mint karakterlánc, N mint egész, Temp, mint karakterlánc'Állítsa be a karakterláncot a Microsoft Corporation címévelMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Használja a split funkciót a karakterlánc felosztásához vesszővelMyArray = Osztott (MyString, ",")'Törölje a munkalapotActiveSheet.UsedRange.Clear'ismételje meg a tömbötN = 0 esetén UBound (MyArray)'helyezzen minden tömb elemet és egy sor előtolás karaktert egy karakterláncbaTemp = Temp & MyArray (N) & vbLfKövetkező N.- Tegye a karakterláncot a munkalapraTartomány ("A1") = HőmEnd Sub |
Ez a példa ugyanúgy működik, mint az előző, azzal a különbséggel, hogy ideiglenes karakterláncot hoz létre az összes tömb elemből, de minden elem után beilleszt egy sor előtolási karaktert.
A munkalap a kód futtatása után így fog kinézni:
Karakterlánc felosztása munkalap celláira
A Split tömböt egyetlen parancs segítségével másolhatja a munkalap celláiba <>:
| 12345678910 | Sub CopyToRange ()'Hozzon létre változókatDim MyArray () mint karakterlánc, MyString mint karakterlánc'Minta karakterlánc szóközzelMyString = "Egy, kettő, három, négy, öt, hat"'Használja az Osztás funkciót a karakterlánc összetevőinek felosztásáhozMyArray = Osztott (MyString, ",")Másolja a tömböt a munkalapraTartomány ("A1: A" & UBound (MyArray) + 1). Value = WorksheetFunction.Transpose (MyArray)End Sub |
A kód futtatása után a munkalapja így néz ki:
Új függvény létrehozása, amely lehetővé teszi a felosztást egy adott pontból
Az Osztás funkció Limit paramétere csak olyan felső határ megadását teszi lehetővé, ahol a felosztást le kell állítani. Mindig a karakterlánc elejétől kezdődik.
Nagyon hasznos lenne egy hasonló függvény, ahol megadhatja a szétválasztás kezdőpontját a karakterláncon belül, és a felosztások számát, amit ettől a ponttól látni szeretne. Ezenkívül csak a megadott felosztásokat vonja ki a tömbbe, ahelyett, hogy hatalmas karakterláncérték lenne a tömb utolsó eleme.
Ehhez egyszerűen létrehozhat egy funkciót (SplitSlicer néven) a VBA -ban:
| 123456789101112131415161718192021222324 | Funkció SplitSlicer (Cél, mint karakterlánc, Del, mint karakterlánc, Kezdés egészként, N, mint egész)'Hozzon létre tömbváltozótDim MyArray () karakterláncként'Rögzítse a felosztást a start változóval a határoló karakter használatávalMyArray = Split (Cél, Del, Start)„Ellenőrizze, hogy az indítási paraméter nagyobb -e, mint a felosztások száma - ez problémákat okozhatHa Start> UBound (MyArray) + 1 Majd„Hibajelenség és kilépés a funkcióbólMsgBox "A Start paraméter nagyobb, mint a rendelkezésre álló felosztások száma"SplitSlicer = MyArrayKilépés funkcióVége Ha'Tegye az utolsó tömb elemet a karakterláncbaCél = MyArray (UBound (MyArray))'Oszd szét a karakterláncot, és használd a korlátotMyArray = Osztott (cél, Del, N)„Ellenőrizze, hogy a felső korlát nagyobb -e nullánál, mivel a kód eltávolítja az utolsó elemetHa UBound (MyArray)> 0 Akkor'A ReDim segítségével távolítsa el a tömb utolsó elemétReDim Preserve MyArray (UBound (MyArray) - 1)Vége Ha'Adja vissza az új tömbötSplitSlicer = MyArrayVége funkció |
Ez a funkció négy paraméterrel épül fel:
- Cél - string - ez az a bemeneti karakterlánc, amelyet fel szeretne osztani
- Del - karakterlánc vagy nem nyomtatható karakter - ez az elválasztó karakter, amelyet pl. vessző, kettőspont
- Rajt - szám - ez a szelet kezdő felosztása
- N - szám - ez az a felosztás, amelyet a szeleten belül meg szeretne tenni
Ezen paraméterek egyike sem opcionális, vagy nem rendelkezik alapértelmezett értékekkel, de ezt a funkció kódjába is belefoglalhatja, ha tovább szeretné bővíteni.
A függvény a Split függvény segítségével tömböt hoz létre a Start paraméterrel, mint határértékkel. Ez azt jelenti, hogy a tömb elemek feltartják a felosztásokat a start paraméterig, de a karakterlánc többi része az utolsó elem lesz, és nem lesz felosztva.
A tömb utolsó eleme visszakerül egy karakterláncba az UBound függvény segítségével annak meghatározásához, hogy ez melyik elem.
A karakterlánc ezután ismét felosztásra kerül a tömbre, N -t használva határváltozóként. Ez azt jelenti, hogy a karakterlánc felosztja az N pozíciót, majd a karakterlánc többi része képezi a tömb utolsó elemét.
A ReDim utasítás az utolsó elem eltávolítására szolgál, mivel csak a tömbben maradt konkrét elemeket szeretnénk. Ne feledje, hogy a Preserve paramétert használja, különben a tömb minden adata elvész.
Az új tömb ezután visszatér ahhoz a kódhoz, amelyből hívták.
Ne feledje, hogy a kód „hibamentes”. A felhasználók gyakran olyan furcsa dolgokat tesznek, amelyekre Ön nem gondolt. Például, ha megpróbálják használni a függvényt a Start vagy N paraméterrel, ami meghaladja a karakterláncban rendelkezésre álló felosztások számát, ez valószínűleg a funkció meghibásodását eredményezi.
A kód tartalmazza a Start érték ellenőrzését, és azt is, hogy van -e olyan elem, amelyet el lehet távolítani, amikor a ReDim utasítást használja a tömbön.
Itt található a funkció tesztelésének kódja:
| 123456789101112 | Sub TestSplitSlicer ()'Hozzon létre változókatDim MyArray () mint karakterlánc, MyString mint karakterlánc'Határozza meg a minta karakterláncát vesszővelMyString = "Egy, kettő, három, négy, öt, hat, hét, nyolc, kilenc, tíz"'Használja a Splitslicer függvényt új tömb meghatározásáhozMyArray = SplitSlicer (MyString, ",", 4, 3)'Törölje az aktív lapotActiveSheet.UsedRange.ClearMásolja a tömböt a munkalapraTartomány ("A1: A" & UBound (MyArray) + 1). Value = WorksheetFunction.Transpose (MyArray)End Sub |
Futtassa ezt a kódot, és a munkalapja így fog kinézni:
