Korzystanie z funkcji podziału VBA
Funkcja VBA Split umożliwia oddzielenie części składowych ze standardowego ciągu tekstowego, w którym każdy składnik używa określonego znaku ogranicznika, np. przecinek lub dwukropek. Jest łatwiejszy w użyciu niż pisanie kodu w celu wyszukania ograniczników w ciągu, a następnie wyodrębnienia wartości.
Może być używany, jeśli czytasz wiersz z wartości rozdzielanej przecinkami (plik CSV) lub masz adres pocztowy, który znajduje się w jednym wierszu, ale chcesz go zobaczyć jako wiele wierszy.
Składnia to:
| 1 | Wyrażenie dzielone, separator[opcjonalne], limit[opcjonalne], porównaj[opcjonalne] |
Funkcja VBA Split ma cztery parametry:
- Wyrażenie - Ciąg tekstu, który chcesz podzielić na różne części.
- Ogranicznik (opcjonalny)- ciąg znaków lub znak niedrukowalny - określa znak ogranicznika, który zostanie użyty do podziału. Jeśli nie podano znaku ogranicznika, używana jest spacja domyślna.
- Limit (opcjonalny) - liczba - Określa, ile zostanie wykonanych podziałów. Jeśli puste, wszystkie dostępne podziały zostaną wykonane w ciągu. Jeśli jest ustawiony na 1, nie zostaną wykonane żadne podziały. Zasadniczo umożliwia wydzielenie określonej liczby wartości zaczynając od początku ciągu, np. gdzie struna jest bardzo długa i potrzebujesz tylko pierwszych trzech podziałów.
- Porównywać (opcjonalny) - Jeśli twój ogranicznik jest znakiem tekstowym, to służy do przełączania, czy ogranicznik ma rozróżniać wielkość liter, czy nie. Wartości to vbBinaryCompare (z uwzględnieniem wielkości liter) i vbTextCompare (bez uwzględniania wielkości liter).
Funkcja split zawsze zwraca tablicę.
Prosty przykład funkcji Split
| 123456789101112 | Przykład podziału podrzędnego()'Zdefiniuj zmienne'Dim MyArray() As String, MyString As String, I As Variant'Przykładowy ciąg z ogranicznikami spacjiMyString = "jeden dwa trzy cztery"'Użyj funkcji Split, aby podzielić części składowe ciąguMojaTablica = Podziel(Mój Ciąg)'iterowanie przez tablicę utworzoną, aby pokazać każdą wartośćDla każdego I w MyArraySkrzynka wiad. IDalejNapis końcowy |
W tym przykładzie nie określono ogranicznika, ponieważ między wszystkimi słowami znajduje się spacja, więc można użyć domyślnego ogranicznika (spacji).
Tablica nie ma wymiarów i jest ustawiona jako ciąg. Zmienna I, która jest używana w pętli For… Next, musi być zwymiarowana jako wariant.
Po uruchomieniu tego kodu zostaną wyświetlone cztery okna komunikatów, po jednym dla każdego podziału, np. Raz Dwa Trzy. Cztery.
Zauważ, że jeśli między słowami w łańcuchu jest podwójna spacja, zostanie to ocenione jako podział, chociaż nic w nim nie będzie. To może nie być wynik, który chcesz zobaczyć.
Możesz rozwiązać ten problem, używając funkcji Replace, aby zastąpić wszystkie podwójne spacje pojedynczą spacją:
| 1 | MyString = Replace(MyString, " ", " ") |
Spacja końcowa lub wiodąca może również powodować problemy, tworząc pusty podział. Często bardzo trudno je zobaczyć. Te dodatkowe spacje można usunąć za pomocą funkcji Przytnij:
| 1 | MójCiąg = Przytnij(MójCiąg) |
Korzystanie z funkcji podziału ze znakiem ogranicznika
Możemy użyć separatora w postaci średnika (;). Jest to często spotykane w ciągach adresów e-mail w celu oddzielenia adresów. Możesz otrzymać wiadomość e-mail, która jest udostępniana wielu współpracownikom i chcesz zobaczyć w arkuszu listę osób, do których dotarł. Możesz łatwo skopiować adresy e-mail z pól „Do” lub „Kopiuj” do kodu.
| 123456789101112131415 | Sub SplitWedług średnikaPrzykład()'Zdefiniuj zmienne'Dim MyArray() As String, MyString As String, I As Variant, N As Integer'Przykładowy ciąg z ogranicznikami średnikowymiMójCiąg = "[email protected];[email protected];[email protected];[email protected]"'Użyj funkcji Split, aby podzielić części składowe ciąguMojaTablica = Podziel(Mój Ciąg, ";")„Wyczyść arkusz”ActiveSheet.UsedRange.Clear'iterowanie po tablicyDla N = 0 To UBound(MyArray)'Umieść każdy adres e-mail w pierwszej kolumnie arkuszaZakres("A" & N + 1). Wartość = Moja tablica (N)Następny NNapis końcowy |
Zauważ, że pętla For… Next jest używana do iteracji po tablicy. Pierwszy element tablicy zawsze zaczyna się od zera, a funkcja Upper Bound służy do uzyskania maksymalnej liczby elementów.
Po uruchomieniu tego kodu arkusz będzie wyglądał tak:
Używanie parametru limitu w funkcji podziału
Parametr limit umożliwia wykonanie określonej liczby podziałów od początku ciągu. Niestety, nie możesz podać pozycji startowej ani zakresu splitów do wykonania, więc jest to dość podstawowe. Możesz zbudować własny kod VBA, aby utworzyć funkcję do tego, co zostanie wyjaśnione w dalszej części tego artykułu.
| 123456789101112131415 | Sub SplitWithLimitExample()'Utwórz zmienneDim MyArray() As String, MyString As String, I As Variant, N As Integer'Przykładowy ciąg znaków z przecinkamiMój Ciąg = "Jeden,Dwa,Trzy,Cztery,Pięć,Sześć"'Użyj funkcji Split, aby podzielić części składowe ciąguMojaTablica = Podziel(Mój Ciąg, ",", 4)„Wyczyść arkusz”ActiveSheet.UsedRange.Clear'Iteracja przez tablicęDla N = 0 To UBound(MyArray)'Umieść każdy podział w pierwszej kolumnie arkuszaZakres("A" & N + 1). Wartość = Moja tablica (N)Następny NNapis końcowy |
Po uruchomieniu tego kodu arkusz będzie wyglądał tak:
Tylko pierwsze trzy wartości podziału są wyświetlane osobno. Ostatnie trzy wartości są wyświetlane jako jeden długi ciąg i nie są dzielone.
Jeśli wybierzesz wartość graniczną większą niż liczba ograniczników w ciągu, nie spowoduje to błędu. Łańcuch zostanie podzielony na wszystkie części składowe, tak jakby nie podano wartości granicznej.
Używanie parametru porównania w funkcji podziału
Parametr Compare określa, czy w ograniczniku jest rozróżniana wielkość liter, czy nie. Nie ma to zastosowania, jeśli ograniczniki to przecinki, średniki lub dwukropki.
Uwaga: Zamiast tego zawsze możesz umieścić opcję Porównaj tekst <> u góry modułu, aby wyeliminować rozróżnianie wielkości liter w całym module.
| 123456789101112131415 | Sub SplitByCompareExample()'Utwórz zmienneDim MyArray() As String, MyString As String, I As Variant, N As Integer'Przykładowy ciąg z ogranicznikami XMój Ciąg = „JedenXDwaXTrzyxCzteryXPięćXSześć”'Użyj funkcji Split, aby podzielić części składowe ciąguMyArray = Split(MyString, "X", , vbBinaryCompare)„Wyczyść arkusz”ActiveSheet.UsedRange.Clear'iterowanie po tablicyDla N = 0 To UBound(MyArray)'Umieść każdy podział w pierwszej kolumnie arkuszaZakres("A" & N + 1). Wartość = Moja tablica (N)Następny NNapis końcowy |
W tym przykładzie ciąg do podziału używa znaku „X” jako ogranicznika. Jednak w tym ciągu występuje mieszanka wielkich i małych liter „X”. Parametr Compare w funkcji Split używa dużej litery „X”.
Jeśli parametr Compare jest ustawiony na vbBinaryCompare, małe litery „x” zostaną zignorowane, a arkusz będzie wyglądał tak:
Jeśli parametr Compare jest ustawiony na vbTextCompare, w podziale zostaną użyte małe litery „x”, a arkusz będzie wyglądał tak:
Zauważ, że wartość w komórce A6 jest obcinana, ponieważ zawiera małą literę „x”. Ponieważ w podziale nie jest rozróżniana wielkość liter, każdy ogranicznik, który stanowi część podciągu, spowoduje podział.
Jest to ważna kwestia, o której należy pamiętać podczas korzystania z separatora tekstu i vbTextCompare. Możesz łatwo skończyć z niewłaściwym wynikiem.
Używanie znaków niedrukowalnych jako znaku rozdzielającego
Jako ogranicznika można używać znaków niedrukowalnych, takich jak powrót karetki (podział wiersza).
Tutaj używamy vbCr, aby określić powrót karetki <>
| 123456789101112131415 | Sub SplitByNiedrukowalnyPrzykład()'Utwórz zmienneDim MyArray() As String, MyString As String, I As Variant, N As Integer'Przykładowy ciąg z ogranicznikami powrotu karetkiMyString = "Jeden" & vbCr & "Dwa" & vbCr & "Trzy" & vbCr & "Cztery" & vbCr & "Pięć" & vbCr & "Sześć"'Użyj funkcji Split, aby podzielić części składowe ciąguMyArray = Split (Mój Ciąg, vbCr, , vbTextCompare)„Wyczyść arkusz”ActiveSheet.UsedRange.Clear'Iteracja przez tablicęDla N = 0 To UBound(MyArray)'Umieść każdy podział w pierwszej kolumnie arkuszaZakres("A" & N + 1). Wartość = Moja tablica (N)Następny NNapis końcowy |
W tym przykładzie ciąg jest tworzony przy użyciu vbCr (znaku powrotu karetki) jako ogranicznika.
Po uruchomieniu tego kodu arkusz będzie wyglądał tak:
Używanie funkcji łączenia do odwracania podziału
Funkcja Join ponownie połączy wszystkie elementy tablicy, ale przy użyciu określonego ogranicznika. Jeśli nie zostanie określony znak ogranicznika, zostanie użyta spacja.
| 123456789101112131415 | Przykład dołączenia podrzędnego()'Utwórz zmienneDim MyArray() As String, MyString As String, I As Variant, N As IntegerPrzyciemnij cel jako ciąg'Przykładowy ciąg znaków z przecinkamiMój Ciąg = "Jeden,Dwa,Trzy,Cztery,Pięć,Sześć"„Umieść MyString w komórce A1”Zakres("A1").Value = MójCiąg'Użyj funkcji Split, aby podzielić części składowe ciąguMojaTablica = Podziel (Mój Ciąg, ",")'Użyj funkcji Join, aby odtworzyć oryginalny ciąg za pomocą separatora średnikiemCel = Dołącz(Moja Tablica”;”)'Umieść ciąg wynikowy w komórce A2Zakres("A2").Wartość = CelNapis końcowy |
Ten kod dzieli ciąg znaków z przecinkami na tablicę i łączy go z powrotem za pomocą średników.
Po uruchomieniu tego kodu twój arkusz będzie wyglądał tak:
Komórka A1 ma oryginalny ciąg z ogranicznikami przecinków, a komórka A2 ma nowy ciąg połączony z ogranicznikami średnikami.
Używanie funkcji Split do liczenia słów
Mając na uwadze, że zmienna ciągu w Excel VBA może mieć długość do 2 GB, możesz użyć funkcji podziału, aby zliczyć słowa w fragmencie tekstu. Oczywiście Microsoft Word robi to automatycznie, ale może to być przydatne w przypadku zwykłego pliku tekstowego lub tekstu skopiowanego z innej aplikacji.
| 1234567891011121314 | Liczba podrzędnaPrzykładSłów()'Utwórz zmienneDim MyArray() As String, MyString As String'Przykładowy ciąg z ogranicznikami spacjiMyString = „Raz dwa trzy cztery pięć sześć”'Usuń podwójne spacjeMyString = Replace(MyString, " ", " ")'Usuń wszelkie spacje wiodące i końcoweMójCiąg = Przytnij(MójCiąg)'Użyj funkcji Split, aby podzielić części składowe ciąguMojaTablica = Podziel(Mój Ciąg)'Pokaż liczbę słów za pomocą funkcji UBoundMsgBox „Liczba słów” i UBound(MyArray) + 1Napis końcowy |
Jednym z niebezpieczeństw tego kodu liczenia słów jest to, że będzie on rzucany przez podwójne spacje oraz spacje wiodące i końcowe. Jeśli są obecne, zostaną policzone jako dodatkowe słowa, a liczba słów zakończy się jako niedokładna.
Kod używa funkcji Replace i Trim do usunięcia tych dodatkowych spacji.
Ostatnia linia kodu wyświetla liczbę słów znalezionych przy użyciu funkcji UBound w celu uzyskania maksymalnego numeru elementu tablicy, a następnie zwiększenia go o 1. Dzieje się tak, ponieważ pierwszy element tablicy zaczyna się od zera.
Dzielenie adresu na komórki arkusza
Adresy e-mail to często długie ciągi tekstu z przecinkami. Możesz podzielić każdą część adresu na oddzielną komórkę.
| 123456789101112131415 | Przykład podadresu()'Utwórz zmienneDim MyArray() As String, MyString As String, N As Integer'Skonfiguruj ciąg z adresem firmy Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Użyj funkcji split, aby podzielić ciąg za pomocą separatora przecinkaMojaTablica = Podziel (Mój Ciąg, ",")„Wyczyść arkusz”ActiveSheet.UsedRange.Clear'iterowanie po tablicyDla N = 0 To UBound(MyArray)'Umieść każdy podział w pierwszej kolumnie arkuszaZakres("A" & N + 1). Wartość = Moja tablica (N)Następny NNapis końcowy |
Uruchomienie tego kodu spowoduje użycie przecinka do umieszczenia każdego wiersza adresu w osobnej komórce:
Jeśli chcesz tylko zwrócić kod pocztowy (ostatni element tablicy), możesz użyć kodu:
| 123456789101112 | PodadresPrzykładKoduPocztowego()'Utwórz zmienneDim MyArray() As String, MyString As String, N As Integer, Temp As String'Skonfiguruj ciąg z adresem firmy Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Użyj funkcji split, aby podzielić ciąg za pomocą separatora przecinkaMojaTablica = Podziel (Mój Ciąg, ",")„Wyczyść arkusz”ActiveSheet.UsedRange.Clear„Umieść kod pocztowy w komórce A1”Range("A1").Value = MyArray(UBound(MyArray))Napis końcowy |
Spowoduje to użycie tylko ostatniego elementu w tablicy, który zostanie znaleziony za pomocą funkcji UBound.
Z drugiej strony możesz chcieć zobaczyć wszystkie wiersze w jednej komórce, aby można je było wydrukować na etykiecie adresowej:
| 1234567891011121314151617 | Przykład podadresu()'Utwórz zmienneDim MyArray() As String, MyString As String, N As Integer, Temp As String'Skonfiguruj ciąg z adresem firmy Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Użyj funkcji split, aby podzielić ciąg za pomocą separatora przecinkaMojaTablica = Podziel (Mój Ciąg, ",")„Wyczyść arkusz”ActiveSheet.UsedRange.Clear'iterowanie po tablicyDla N = 0 To UBound(MyArray)'umieść każdy element tablicy oraz znak nowego wiersza w łańcuchuTemp = Temp i Moja tablica (N) i vbLfNastępny N'Umieść ciąg w arkuszuZakres ("A1") = TempNapis końcowy |
Ten przykład działa tak samo, jak poprzedni, z wyjątkiem tego, że tworzy tymczasowy ciąg wszystkich elementów tablicy, ale wstawia znak nowego wiersza po każdym elemencie.
Arkusz będzie wyglądał tak po uruchomieniu kodu:
Podziel ciąg na komórki arkusza
Możesz skopiować tablicę Split do komórek arkusza roboczego <> za pomocą tylko jednego polecenia:
| 12345678910 | Sub CopyToRange()'Utwórz zmienneDim MyArray() As String, MyString As String'Przykładowy ciąg z ogranicznikami spacjiMój Ciąg = "Jeden,Dwa,Trzy,Cztery,Pięć,Sześć"'Użyj funkcji Split, aby podzielić części składowe ciąguMojaTablica = Podziel (Mój Ciąg, ",")'Skopiuj tablicę do arkusza roboczegoRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)Napis końcowy |
Po uruchomieniu tego kodu arkusz będzie wyglądał tak:
Tworzenie nowej funkcji umożliwiającej oddzielenie od danego punktu
Parametr Limit w funkcji Split umożliwia tylko określenie górnego limitu, przy którym podział ma się zakończyć. Zawsze zaczyna się od początku ciągu.
Bardzo przydatne byłoby posiadanie podobnej funkcji, w której można określić punkt początkowy podziału w ciągu i liczbę podziałów, które chcesz zobaczyć od tego punktu. Wyciągnie również tylko określone podziały do tablicy, zamiast posiadania ogromnej wartości ciągu jako ostatniego elementu w tablicy.
Możesz łatwo zbudować funkcję (zwaną SplitSlicer) w VBA, aby to zrobić:
| 123456789101112131415161718192021222324 | Funkcja SplitSlicer(Target As String, Del As String, Start As Integer, N As Integer)'Utwórz zmienną tablicowąDim MyArray() jako ciąg'Przechwyć podział, używając zmiennej początkowej, używając znaku ogranicznikaMyArray = Split(Cel, Del, Start)„Sprawdź, czy parametr początkowy jest większy niż liczba podziałów – może to powodować problemy”Jeśli Start > UBound(MyArray) + 1 Wtedy„Wyświetl błąd i wyjdź z funkcji”MsgBox "Parametr startu jest większy niż liczba dostępnych podziałów"SplitSlicer = Moja tablicaWyjdź z funkcjiZakończ, jeśli'Wstaw ostatni element tablicy do ciągu znakówCel = MyArray(UBound(MyArray))'Podziel ciąg, używając N jako limituMojaTablica = Podziel (Cel, Del, N)„Sprawdź, czy górny limit jest większy od zera, ponieważ kod usuwa ostatni element”Jeśli UBound (MyArray) > 0 Wtedy'Użyj ReDim, aby usunąć ostatni element tablicyReDim Zachowaj MyArray(UBound(MyArray) - 1)Zakończ, jeśli'Zwróć nową tablicęSplitSlicer = Moja tablicaKoniec funkcji |
Ta funkcja jest zbudowana z czterech parametrów:
- Cel - ciąg - jest to ciąg wejściowy, który chcesz podzielić
- Del - ciąg znaków lub znak niedrukowalny - jest to znak ogranicznika, którego używasz np. przecinek, dwukropek
- Początek - liczba - to jest początkowy podział dla twojego plasterka
- n - liczba - jest to liczba podziałów, które chcesz wykonać w swoim plasterku
Żaden z tych parametrów nie jest opcjonalny ani nie ma wartości domyślnych, ale możesz wprowadzić to w kodzie funkcji, jeśli chcesz go dalej rozszerzyć.
Funkcja używa funkcji Split do utworzenia tablicy z parametrem Start jako Limitem. Oznacza to, że elementy tablicy będą przechowywać podziały aż do parametru start, ale pozostała część ciągu będzie ostatnim elementem i nie zostanie podzielona.
Ostatni element tablicy jest przesyłany z powrotem do ciągu za pomocą funkcji UBound w celu określenia, który to element.
Łańcuch jest następnie ponownie dzielony na tablicę, używając N jako zmiennej limitu. Oznacza to, że zostaną wykonane podziały łańcucha do pozycji N, po czym reszta łańcucha utworzy ostatni element tablicy.
Instrukcja ReDim służy do usunięcia ostatniego elementu, ponieważ chcemy, aby w tablicy pozostały tylko określone elementy. Zwróć uwagę, że używany jest parametr Preserve, w przeciwnym razie wszystkie dane w tablicy zostaną utracone.
Nowa tablica jest następnie zwracana do kodu, z którego została wywołana.
Pamiętaj, że kod jest „odporny na błędy”. Użytkownicy często robią dziwne rzeczy, których nie brałeś pod uwagę. Na przykład, jeśli spróbują użyć funkcji z parametrem Start lub N większym niż dostępna liczba podziałów w ciągu, prawdopodobnie spowoduje to niepowodzenie funkcji.
Dołączony jest kod w celu sprawdzenia wartości Start, a także upewnienia się, że istnieje element, który można usunąć, gdy w tablicy zostanie użyta instrukcja ReDim.
Oto kod do testowania funkcji:
| 123456789101112 | PodtestSplitSlicer()'Utwórz zmienneDim MyArray() As String, MyString As String'Zdefiniuj przykładowy ciąg za pomocą przecinkówMój Ciąg = "Jeden,Dwa,Trzy,Cztery,Pięć,Sześć,Siedem,Osiem,Dziewięć,Dziesięć"'Użyj funkcji Splitslicer, aby zdefiniować nową tablicęMojaTablica = SplitSlicer(Mój Ciąg, ",", 4, 3)'Wyczyść aktywny arkuszActiveSheet.UsedRange.Clear'Skopiuj tablicę do arkusza roboczegoRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)Napis końcowy |
Uruchom ten kod, a arkusz będzie wyglądał tak:
