Wczoraj dostałem maila od jednego z moich czytelników - czerwiec.
Chciała wiedzieć, jak zastosować pogrubiony format czcionki do określonej części ciągu w komórce. Na przykład zastosuj pogrubiony format tylko do słowa „Hello” z „Hello World”.
I chciała to zrobić dla setek komórek naraz.
Ponieważ w Excelu nie ma wbudowanej funkcji, która mogłaby to zrobić, stworzyłem proste makro, które używa Funkcja Excel VBA InStr (zobaczysz, jak to zrobić w przykładzie 4 w tym samouczku).
Ale najpierw zobaczmy, jak działa funkcja Excel VBA InStr!
Funkcja Excel VBA InStr
W tym samouczku wyjaśnię użycie funkcji InStr w Excel VBA i zobaczę kilka praktycznych przykładów, w których można jej użyć.
Funkcja Excel VBA InStr - wprowadzenie
Funkcja InStr znajduje pozycję określonego podciągu w ciągu i zwraca pierwszą pozycję jego wystąpienia.
Na przykład, jeśli chcesz znaleźć pozycję „x” w „Excelu”, użycie funkcji Excel VBA InStr zwróci 2.
Składnia funkcji InStr
InStr( [Start], String1, String2, [Porównaj] )
- [Początek] - (argument opcjonalny) jest to wartość całkowita, która informuje funkcję InStr o pozycji początkowej, od której powinna zacząć szukać. Na przykład, jeśli chcę, aby wyszukiwanie zaczynało się od początku, wpiszę wartość 1. Jeśli chcę, aby zaczynało się od trzeciego znaku wzwyż, użyję 3. Jeśli pominięto, przyjmowana jest domyślna wartość 1.
- Ciąg1 - To jest główny ciąg (lub ciąg nadrzędny), w którym chcesz szukać. Na przykład, jeśli szukasz pozycji x w programie Excel, ciąg 1 będzie oznaczać „Excel”.
- Ciąg2 - To jest podciąg, którego szukasz. Na przykład, jeśli szukasz pozycji x w Excelu, String2 będzie x.
- [Porównywać] - (argument opcjonalny) Możesz określić jedną z następujących trzech wartości dla argumentu [porównaj]:
- vbBinaryCompare - To pozwoliłoby porównać postać po postaci. Na przykład, jeśli szukasz „x” w „Excelu”, zwróci 2, ale jeśli szukasz „X” w „Excelu”, zwróci 0, ponieważ X jest pisane wielkimi literami. Możesz również użyć 0 zamiast vbBinaryCompare. Jeśli argument [Porównaj] zostanie pominięty, jest przyjmowany jako domyślny.
- vbTextPorównaj - To byłoby porównanie tekstowe. Na przykład, jeśli szukasz „x” lub „X” w programie Excel, zwróci 2 w obu przypadkach. Ten argument ignoruje wielkość liter. Możesz również użyć 1 zamiast vbTextCompare.
- vbDatabasePorównaj - Jest używany tylko w programie Microsoft Access. Do porównania wykorzystuje informacje z bazy danych. Możesz również użyć 2 zamiast vbDatabaseCompare.
Dodatkowe uwagi dotyczące funkcji Excel VBA InStr:
- InStr to funkcja VBA, a nie funkcja arkusza. Oznacza to, że nie możesz go używać w arkuszu.
- Jeśli String2 (który jest podciągiem, którego pozycji szukasz) jest pusty, funkcja zwróci wartość argumentu [Start].
- Jeśli funkcja InStr nie może znaleźć podciągu w głównym ciągu, zwróci 0.
Przyjrzyjmy się teraz przykładowi użycia funkcji Excel VBA InStr
Przykład 1 - Znalezienie pozycji od początku
W tym przykładzie użyję funkcji InStr, aby od początku znaleźć pozycję „V” w „Excel VBA”.
Kod do tego byłby następujący:
Sub FindFromBeginning() Dim Position As Integer Position = InStr(1, "Excel VBA", "V", vbBinaryCompare) MsgBox Position End Sub
Po uruchomieniu tego kodu wyświetli się okno komunikatu z wartością 7, która jest pozycją „V” w ciągu „Excel VBA”.
Przykład 2 – Znalezienie pozycji od początku drugiego słowa
Załóżmy, że chcę znaleźć pozycję „the” w zdaniu: „Szybki brązowy lis przeskakuje nad leniwym psem”
Jednak chcę, aby poszukiwania rozpoczęły się od drugiego słowa.
W takim przypadku musimy zmienić argument [Start], aby upewnić się, że określa on pozycję, od której zaczyna się drugie słowo.
Oto kod, który to zrobi:
Sub FindFromSecondWord() Dim Position As Integer Position = InStr(4, "Szybki brązowy lis przeskakuje nad leniwym psem", "the", vbBinaryCompare) MsgBox Position End Sub
Ten kod wyświetli okno komunikatu z wartością 32, ponieważ określiliśmy pozycję początkową jako 4. Dlatego ignoruje pierwsze „The” i znajduje drugie „the” w zdaniu.
Jeśli chcesz, aby był bardziej dynamiczny, możesz ulepszyć kod, aby automatycznie ignorował pierwsze słowo.
Oto ulepszony kod, który to zrobi:
Sub FindFromSecondWord() Dim StartingPosition As Integer Dim Pozycja As Integer StartingPosition = InStr(1, "Szybki brązowy lis przeskakuje nad leniwym psem", " ", vbBinaryCompare) Position = InStr(StartingPosition, "Szybki brązowy lis przeskakuje nad leniwym pies", "the", vbBinaryCompare) MsgBox Pozycja End Sub
Ten kod najpierw odnajduje pozycję znaku spacji i zapisuje go w zmiennej StartingPosition.
Następnie używa tej zmiennej jako pozycji wyjściowej do wyszukiwania słowa „the”.
Stąd zwraca 32 (co jest początkową pozycją „the” po pierwszym słowie).
Przykład 3 - Znajdowanie pozycji @ w adresie e-mail
Możesz łatwo utworzyć niestandardową funkcję, aby znaleźć pozycję @ w adresie e-mail za pomocą funkcji Excel VBA InStr.
Oto kod do utworzenia funkcji niestandardowej:
Funkcja FindPosition(Ref As Range) As Integer Dim Position As Integer Position = InStr(1, Ref, "@") FindPosition = Pozycja Funkcja końcowa
Teraz możesz używać tej funkcji niestandardowej jak każdej innej funkcji arkusza. Jako dane wejściowe przyjmie odwołanie do komórki i poda w nim pozycję @.
Podobnie możesz utworzyć funkcję niestandardową, aby znaleźć pozycję dowolnego podciągu w ciągu głównym.
Przykład 4 - Podświetlanie części ciągu w komórkach
To jest pytanie, które zostało zadane przez June (mój czytelnik, który również zainspirował mnie do napisania tego samouczka).
Oto przykładowe dane w formacie, który przesłał mi czerwiec:
Jej zapytaniem było pogrubienie liczb poza nawiasem.
Oto kod, który stworzyłem, który to robi:
Sub Bold() Dim rCell As Range Dim Char As Integer Dla każdej rCell w zaznaczeniu CharCount = Len(rCell) Char = InStr(1, rCell, "(") rCell.Characters(1, Char - 1).Font.Bold = True Next rCell End Sub
Powyższy kod używa pętli For Each do przejścia przez każdą komórkę w zaznaczeniu. Identyfikuje pozycję znaku otwierającego nawiasu za pomocą funkcji InStr. Następnie zmienia czcionkę tekstu przed nawiasem.
Aby użyć tego kodu, musisz skopiować i wkleić moduł w edytorze VB.
Po skopiowaniu wklejonego kodu zaznacz komórki, w których chcesz wykonać to formatowanie, i uruchom makro (jak pokazano poniżej).
Możesz także polubić następujące samouczki Excel VBA:
- Funkcja podziału Excela VBA.
- Funkcja przycinania VBA.
- Kompletny przewodnik po pętlach Excel VBA.
- Przewodnik dla początkujących dotyczący używania For Next Loop w Excel VBA.
- Jak utworzyć i używać dodatku programu Excel.
- Jak połączyć wiele skoroszytów w jeden skoroszyt programu Excel.
- Jak liczyć kolorowe komórki w programie Excel.
- Przydatne przykłady makr Excel VBA dla początkujących.
- Jak sortować dane w programie Excel za pomocą VBA (przewodnik krok po kroku)
