Použití funkce rozdělení VBA
Funkce VBA Split vám umožňuje oddělit části součásti ze standardního textového řetězce, kde každá komponenta používá konkrétní znak oddělovače, např. čárka nebo dvojtečka. Je snazší než psaní kódu hledat oddělovače v řetězci a poté extrahovat hodnoty.
Lze jej použít, pokud čtete řádek z hodnoty oddělené čárkami (soubor CSV) nebo máte poštovní adresu, která je na jednom řádku, ale chcete ji zobrazit jako více řádků.
Syntaxe je:
| 1 | Rozdělit výraz, oddělovač [volitelně], limit [volitelně], porovnat [volitelně] |
Funkce VBA Split má čtyři parametry:
- Výraz - Řetězec textu, který chcete rozdělit na různé části.
- Oddělovač (volitelný)- řetězec nebo netisknutelný znak - Definuje znak oddělovače, který bude použit pro rozdělení. Pokud není zadán žádný oddělovač, použije se výchozí mezera.
- Omezit (volitelný) - číslo - Definuje, kolik rozdělení bude provedeno. Pokud je prázdné, budou v řetězci provedena všechna dostupná rozdělení. Pokud je nastavena na 1, nebudou dělena žádná rozdělení. V zásadě vám to umožňuje oddělit určitý počet hodnot začínajících na začátku řetězce, např. kde řetězec je velmi dlouhý a potřebujete pouze první tři rozdělení.
- Porovnat (volitelný) - Pokud je vaším oddělovačem textový znak, pak se používá k přepínání, zda oddělovač rozlišuje velká a malá písmena nebo ne. Hodnoty jsou vbBinaryCompare (rozlišující malá a velká písmena) a vbTextCompare (nerozlišují velká a malá písmena).
Funkce rozdělení vždy vrací pole.
Jednoduchý příklad funkce rozdělení
| 123456789101112 | Dílčí rozdělení Příklad ()"Definujte proměnné."Dim MyArray () jako řetězec, MyString jako řetězec, já jako varianta"Ukázkový řetězec s oddělovači mezer."MyString = "Jedna dvě tři čtyři"'Pomocí funkce Rozdělit rozdělte jednotlivé části řetězceMyArray = Split (MyString)'iterujte pole vytvořené pro zobrazení každé hodnotyZa každý I In MyArrayMsgBox IPříště jáEnd Sub |
V tomto příkladu není zadán žádný oddělovač, protože všechna slova mají mezeru mezi nimi, takže lze použít výchozí oddělovač (mezeru).
Pole nemá žádné rozměry a je nastaveno jako řetězec. Proměnná I, která se používá ve smyčce For… Next, musí být dimenzována jako varianta.
Když je tento kód spuštěn, zobrazí čtyři pole se zprávami, jedno pro každé rozdělení, např. Jedna dvě tři. Čtyři.
Všimněte si, že pokud je mezi slovy v řetězci dvojitá mezera, bude to vyhodnoceno jako rozdělení, i když v něm není nic. To nemusí být výsledek, který byste chtěli vidět.
Tento problém můžete vyřešit pomocí funkce Nahradit a nahradit dvojité mezery jediným mezerou:
| 1 | MyString = Nahradit (MyString, "", "") |
Koncový nebo úvodní prostor může také způsobit problémy vytvořením prázdného rozdělení. Ty jsou často velmi obtížně viditelné. Tyto nadbytečné mezery můžete odstranit pomocí funkce Oříznout:
| 1 | MyString = Trim (MyString) |
Použití funkce rozdělení se znakem oddělovače
Můžeme použít oddělovač středníku (;). To se často nachází v řetězcích e -mailových adres k oddělení adres. Může vám být zaslán e -mail, který je sdílen s řadou kolegů, a chcete v seznamu zobrazit seznam osob, kterým byl odeslán. E -mailové adresy můžete snadno zkopírovat z polí „Komu“ nebo „Kopírovat“ do svého kódu.
| 123456789101112131415 | Sub SplitBySemicolonExample ()"Definujte proměnné."Dim MyArray () jako řetězec, MyString jako řetězec, I jako varianta, N jako celé číslo„Ukázkový řetězec s oddělovači středníkůMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Pomocí funkce Rozdělit rozdělte jednotlivé části řetězceMyArray = Rozdělit (MyString, ";")„Vymažte pracovní listActiveSheet.UsedRange.Clear'opakujte polePro N = 0 až UBound (MyArray)'Umístěte každou e -mailovou adresu do prvního sloupce listuRozsah ("A" & N + 1). Hodnota = MyArray (N)Další N.End Sub |
Všimněte si, že smyčka For… Next se používá k iteraci polem. První prvek v poli vždy začíná na nule a k získání maximálního počtu prvků se používá funkce Upper Bound.
Po spuštění tohoto kódu bude váš list vypadat takto:
Použití parametru limitu ve funkci rozdělení
Parametr limit umožňuje provést konkrétní počet rozdělení od začátku řetězce. Bohužel nemůžete poskytnout počáteční pozici nebo rozsah rozdělení, které je třeba provést, takže je to docela základní. Můžete vytvořit svůj vlastní kód VBA a vytvořit funkci, která to provede, a to bude vysvětleno dále v tomto článku.
| 123456789101112131415 | Sub SplitWithLimitExample ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec, I jako varianta, N jako celé číslo"Ukázkový řetězec s oddělovači čárky."MyString = "Jeden, dva, tři, čtyři, pět, šest"'Pomocí funkce Rozdělit rozdělte jednotlivé části řetězceMyArray = Split (MyString, ",", 4)„Vymažte pracovní listActiveSheet.UsedRange.Clear"Opakujte pole."Pro N = 0 až UBound (MyArray)'Každé rozdělení rozdělte do prvního sloupce listuRozsah ("A" & N + 1). Hodnota = MyArray (N)Další N.End Sub |
Po spuštění tohoto kódu bude váš list vypadat takto:
Samostatně jsou zobrazeny pouze první tři hodnoty rozdělení. Pozdější tři hodnoty jsou zobrazeny jako jeden dlouhý řetězec a nerozdělují se.
Pokud zvolíte mezní hodnotu, která je větší než počet oddělovačů v řetězci, nedojde k chybě. Řetězec bude rozdělen na všechny jeho součásti, jako by nebyla uvedena mezní hodnota.
Použití parametru Porovnání ve funkci rozdělení
Parametr Porovnat určuje, zda oddělovač rozlišuje velká a malá písmena nebo ne. To neplatí, pokud jsou oddělovači čárky, středníky nebo dvojtečky.
Poznámka: Místo toho můžete vždy umístit Option Compare Text <> v horní části modulu, aby se eliminovala citlivost na velká a malá písmena pro celý modul.
| 123456789101112131415 | Dílčí SplitByCompareExample ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec, I jako varianta, N jako celé číslo"Ukázkový řetězec s oddělovači X."MyString = "OneXTwoXThreexFourXFivexSix"'Pomocí funkce Rozdělit rozdělte jednotlivé části řetězceMyArray = Split (MyString, "X",, vbBinaryCompare)„Vymažte pracovní listActiveSheet.UsedRange.Clear'opakujte polePro N = 0 až UBound (MyArray)'Každé rozdělení rozdělte do prvního sloupce listuRozsah ("A" & N + 1). Hodnota = MyArray (N)Další N.End Sub |
V tomto případě používá řetězec, který má být rozdělen, jako oddělovač znak „X“. V tomto řetězci je však směs velkých a malých písmen „X“. Parametr Porovnat ve funkci Rozdělit používá velká písmena „X“.
Pokud je parametr Porovnat nastaven na vbBinaryCompare, budou malá písmena „x“ ignorována a váš list bude vypadat takto:
Pokud je parametr Porovnat nastaven na vbTextCompare, pak se v rozdělení použijí malá písmena „x“ a váš list bude vypadat takto:
Všimněte si, že hodnota v buňce A6 je zkrácena, protože obsahuje malá písmena „x“. Protože rozdělení nerozlišuje velká a malá písmena, způsobí rozdělení jakýkoli oddělovač, který je součástí dílčího řetězce.
Toto je důležitý bod, který je třeba mít na paměti při použití oddělovače textu a vbTextCompare. Můžete snadno skončit se špatným výsledkem.
Použití netisknutelných znaků jako znak oddělovače
Jako oddělovač můžete použít netisknutelné znaky, například návrat na konec řádku (zalomení řádku).
Zde používáme vbCr k určení návratu vozíku <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec, I jako varianta, N jako celé číslo"Ukázkový řetězec s oddělovači návratu vozíku."MyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"'Pomocí funkce Rozdělit rozdělte jednotlivé části řetězceMyArray = Split (MyString, vbCr,, vbTextCompare)„Vymažte pracovní listActiveSheet.UsedRange.Clear"Opakujte pole."Pro N = 0 až UBound (MyArray)'Každé rozdělení rozdělte do prvního sloupce listuRozsah ("A" & N + 1). Hodnota = MyArray (N)Další N.End Sub |
V tomto případě je řetězec vytvořen pomocí oddělovače vbCr (znak pro návrat na začátek řádku).
Po spuštění tohoto kódu bude váš list vypadat takto:
Použití funkce Připojit k obrácení rozdělení
Funkce Join znovu připojí všechny prvky pole, ale pomocí zadaného oddělovače. Pokud není zadán žádný oddělovač, použije se mezera.
| 123456789101112131415 | Příklad připojení ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec, I jako varianta, N jako celé čísloDim Target jako řetězec"Ukázkový řetězec s oddělovači čárky."MyString = "Jeden, dva, tři, čtyři, pět, šest"„Umístěte MyString do buňky A1Rozsah („A1“). Hodnota = MyString'Pomocí funkce Rozdělit rozdělte jednotlivé části řetězceMyArray = Rozdělit (MyString, ",")'Pomocí funkce Připojit znovu vytvořte původní řetězec pomocí oddělovače středníkemCíl = Připojit se (MyArray, ”;”)'Umístěte řetězec výsledků do buňky A2Rozsah ("A2"). Hodnota = cílEnd Sub |
Tento kód rozdělí řetězec s oddělovači čárkami do pole a spojí jej zpět dohromady pomocí oddělovačů středníkem.
Po spuštění tohoto kódu bude váš list vypadat takto:
Buňka A1 má původní řetězec s oddělovači čárky a buňka A2 má nový spojený řetězec s oddělovači středníkem.
Pomocí funkce Rozdělit proveďte počet slov
Vzhledem k tomu, že řetězcová proměnná v aplikaci Excel VBA může být až 2 Gb dlouhá, můžete použít funkci rozdělení k počítání slov v textu. Microsoft Word to samozřejmě dělá automaticky, ale to by mohlo být užitečné pro jednoduchý textový soubor nebo text zkopírovaný z jiné aplikace.
| 1234567891011121314 | PodčísloOfwordExample ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec"Ukázkový řetězec s oddělovači mezer."MyString = "Jeden dva tři čtyři pět šest""Odstraňte všechny mezery."MyString = Nahradit (MyString, "", "")"Odstraňte všechny mezery na začátku nebo na konci."MyString = Trim (MyString)'Pomocí funkce Rozdělit rozdělte jednotlivé části řetězceMyArray = Split (MyString)'Zobrazit počet slov pomocí funkce UBound.'MsgBox „Počet slov“ a UBound (MyArray) + 1End Sub |
Jedním z nebezpečí tohoto kódu počtu slov je, že bude vyvolán dvojitými mezerami a úvodními a koncovými mezerami. Pokud jsou přítomna, budou započítána jako další slova a počet slov skončí jako nepřesný.
Kód používá k nahrazení těchto mezer funkce Nahradit a Oříznout.
Konečný řádek kódu zobrazuje počet slov nalezených pomocí funkce UBound k získání maximálního počtu prvků pole a poté jeho zvýšení o 1. Důvodem je, že první prvek pole začíná nulou.
Rozdělení adresy do buněk pracovního listu
Poštovní adresy jsou často dlouhé řetězce textu s oddělovači čárkami. Možná budete chtít rozdělit každou část adresy do samostatné buňky.
| 123456789101112131415 | Podadresa Příklad ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec, N jako celé číslo'Nastavte řetězec s adresou Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Pomocí funkce rozdělení rozdělte řetězec pomocí oddělovače čárkyMyArray = Rozdělit (MyString, ",")„Vymažte pracovní listActiveSheet.UsedRange.Clear'opakujte polePro N = 0 až UBound (MyArray)'Každé rozdělení rozdělte do prvního sloupce listuRozsah ("A" & N + 1). Hodnota = MyArray (N)Další N.End Sub |
Spuštěním tohoto kódu použijete oddělovač čárky k vložení každého řádku adresy do samostatné buňky:
Pokud jste chtěli vrátit pouze PSČ (poslední prvek pole), můžete použít kód:
| 123456789101112 | PodadresaZipCodeExample ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec, N jako celé číslo, teplota jako řetězec'Nastavte řetězec s adresou Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Pomocí funkce rozdělení rozdělte řetězec pomocí oddělovače čárkyMyArray = Rozdělit (MyString, ",")„Vymažte pracovní listActiveSheet.UsedRange.Clear„Vložte PSČ do buňky A1Rozsah ("A1"). Hodnota = MyArray (UBound (MyArray))End Sub |
To použije pouze poslední prvek v poli, který je nalezen pomocí funkce UBound.
Na druhou stranu možná budete chtít vidět všechny řádky v jedné buňce, aby je bylo možné vytisknout na adresní štítek:
| 1234567891011121314151617 | Podadresa Příklad ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec, N jako celé číslo, teplota jako řetězec'Nastavte řetězec s adresou Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Pomocí funkce rozdělení rozdělte řetězec pomocí oddělovače čárkyMyArray = Rozdělit (MyString, ",")„Vymažte pracovní listActiveSheet.UsedRange.Clear'opakujte polePro N = 0 až UBound (MyArray)'umístěte každý prvek pole plus znak posuvu řádku do řetězce.'Temp = Temp & MyArray (N) & vbLfDalší N."Vložte řetězec na list."Rozsah ("A1") = teplotaEnd Sub |
Tento příklad funguje stejným způsobem jako předchozí, kromě toho, že vytváří dočasný řetězec všech prvků pole, ale po každém prvku vloží znak posuvu řádku.
Po spuštění kódu bude list vypadat takto:
Rozdělit řetězec na buňky listu
Pole Split můžete zkopírovat do buněk listu <> jediným příkazem:
| 12345678910 | Dílčí CopyToRange ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec"Ukázkový řetězec s oddělovači mezer."MyString = "Jeden, dva, tři, čtyři, pět, šest"'Pomocí funkce Rozdělit rozdělte jednotlivé části řetězceMyArray = Rozdělit (MyString, ",")`` Zkopírujte pole do listuRozsah ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)End Sub |
Po spuštění tohoto kódu bude váš list vypadat takto:
Vytvoření nové funkce umožňující rozdělení z daného bodu
Parametr Limit ve funkci Split umožňuje zadat pouze horní limit, kde má rozdělení přestat. Vždy začíná od začátku řetězce.
Bylo by velmi užitečné mít podobnou funkci, kde můžete určit počáteční bod rozdělení v řetězci a počet rozdělení, které chcete od tohoto bodu dále vidět. Bude také extrahovat pouze rozdělení, která jste zadali do pole, místo toho, aby jako poslední prvek v poli měla obrovskou hodnotu řetězce.
Ve VBA můžete snadno vytvořit funkci (nazývanou SplitSlicer), abyste to udělali:
| 123456789101112131415161718192021222324 | Funkce SplitSlicer (Target As String, Del As String, Start As Integer, N As Integer)`` Vytvořte proměnnou poleDim MyArray () jako řetězec'Zachyťte rozdělení pomocí počáteční proměnné pomocí znaku oddělovačeMyArray = Split (Target, Del, Start)„Zkontrolujte, zda je počáteční parametr větší než počet rozdělení - to může způsobit problémyPokud Start> UBound (MyArray) + 1 Then„Zobrazení chyby a ukončení funkceMsgBox "Počáteční parametr je větší než počet dostupných rozdělení"SplitSlicer = MyArrayFunkce ukončeníKonec If'Vložte poslední prvek pole do řetězce.'Target = MyArray (UBound (MyArray))'Rozdělte řetězec pomocí N jako limituMyArray = Split (Target, Del, N)„Zkontrolujte, zda je horní limit větší než nula, protože kód odebírá poslední prvekPokud je UBound (MyArray)> 0, pak'Pomocí ReDim odeberte konečný prvek poleReDim Preserve MyArray (UBound (MyArray) - 1)Konec If"Vraťte nové pole."SplitSlicer = MyArrayKoncová funkce |
Tato funkce je postavena na čtyřech parametrech:
- cílová - řetězec - toto je vstupní řetězec, který chcete rozdělit
- Del - řetězec nebo netisknutelný znak - toto je znak oddělovače, který používáte např. čárka, dvojtečka
- Start - číslo - toto je počáteční rozdělení vašeho řezu
- N. - číslo - toto je počet rozdělení, které chcete ve svém řezu provést
Žádný z těchto parametrů není volitelný ani nemá výchozí hodnoty, ale můžete jej zapracovat do kódu funkce, pokud jej chcete dále rozšířit.
Funkce používá funkci Split k vytvoření pole pomocí parametru Start jako limitu. To znamená, že prvky pole budou obsahovat rozdělení až na počáteční parametr, ale zbývající část řetězce bude posledním prvkem a nebude rozdělena.
Poslední prvek v poli je přenesen zpět do řetězce pomocí funkce UBound k určení, o který prvek se jedná.
Řetězec se poté znovu rozdělí do pole pomocí N jako limitní proměnné. To znamená, že pro řetězec bude provedeno rozdělení do polohy N, poté zbytek řetězce vytvoří poslední prvek v poli.
Příkaz ReDim se používá k odebrání posledního prvku, protože chceme pouze konkrétní prvky ponechané v poli. Všimněte si toho, že je použit parametr Zachovat, jinak budou všechna data v poli ztracena.
Nové pole se pak vrátí do kódu, ze kterého bylo voláno.
Všimněte si, že kód je „odolný proti chybám“. Uživatelé budou často dělat podivné věci, o kterých jste neuvažovali. Pokud se například pokusí použít funkci s parametrem Start nebo N vyšším, než je dostupný počet rozdělení v řetězci, pravděpodobně to způsobí selhání funkce.
Kód je zahrnut ke kontrole počáteční hodnoty a také k zajištění, že existuje prvek, který lze odebrat, když je v poli použit příkaz ReDim.
Zde je kód k otestování funkce:
| 123456789101112 | Dílčí testSplitSlicer ()`` Vytvořte proměnnéDim MyArray () jako řetězec, MyString jako řetězec'Definujte ukázkový řetězec s oddělovači čárkami.'MyString = "Jeden, dva, tři, čtyři, pět, šest, sedm, osm, devět, deset"'Pomocí funkce Splitslicer definujte nové poleMyArray = SplitSlicer (MyString, ",", 4, 3)„Vymažte aktivní listActiveSheet.UsedRange.Clear`` Zkopírujte pole do listuRozsah ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)End Sub |
Spusťte tento kód a váš list bude vypadat takto:
