Podczas pracy z Excelem większość czasu spędzasz w obszarze arkusza roboczego - zajmując się komórkami i zakresami.
A jeśli chcesz zautomatyzować pracę w Excelu za pomocą VBA, musisz wiedzieć, jak pracować z komórkami i zakresami za pomocą VBA.
Istnieje wiele różnych rzeczy, które można zrobić z zakresami w VBA (takie jak zaznaczanie, kopiowanie, przenoszenie, edycja itp.).
Aby omówić ten temat, podzielę ten samouczek na sekcje i pokażę, jak pracować z komórkami i zakresami w Excel VBA na przykładach.
Zacznijmy.
Wszystkie kody, o których wspominam w tym samouczku, należy umieścić w edytorze VB. Przejdź do sekcji „Gdzie umieścić kod VBA”, aby dowiedzieć się, jak to działa.Jeśli interesuje Cię nauka VBA w prosty sposób, zajrzyj na mój Szkolenie Excel VBA online.
Wybór komórki / zakresu w Excelu za pomocą VBA
Aby pracować z komórkami i zakresami w programie Excel przy użyciu VBA, nie musisz go wybierać.
W większości przypadków lepiej nie wybierać komórek ani zakresów (jak zobaczymy).
Mimo to ważne jest, aby przejść przez tę sekcję i zrozumieć, jak to działa. Będzie to kluczowe w nauce VBA, a wiele omówionych tutaj pojęć zostanie wykorzystanych w tym samouczku.
Zacznijmy więc od bardzo prostego przykładu.
Wybór pojedynczej komórki za pomocą VBA
Jeśli chcesz wybrać pojedynczą komórkę w aktywnym arkuszu (powiedzmy A1), możesz użyć poniższego kodu:
Sub SelectCell() Range("A1").Wybierz End Sub
Powyższy kod ma obowiązkową część „Sub” i „End Sub” oraz wiersz kodu, który wybiera komórkę A1.
Zakres („A1”) mówi VBA adres komórki, do której chcemy się odnieść.
Wybierz jest metodą obiektu Range i wybiera komórki/zakres określony w obiekcie Range. Odwołania do komórek muszą być ujęte w podwójnych cudzysłowach.
Ten kod pokaże błąd, jeśli arkusz wykresu jest arkuszem aktywnym. Arkusz wykresu zawiera wykresy i nie jest powszechnie używany. Ponieważ nie ma w nim komórek/zakresów, powyższy kod nie może go wybrać i spowoduje wyświetlenie błędu.
Zauważ, że ponieważ chcesz wybrać komórkę w aktywnym arkuszu, wystarczy podać adres komórki.
Ale jeśli chcesz wybrać komórkę w innym arkuszu (powiedzmy Arkusz2), musisz najpierw aktywować Arkusz2, a następnie wybrać w nim komórkę.
Sub SelectCell() Worksheets("Arkusz2").Activate Range("A1").Wybierz End Sub
Podobnie możesz również aktywować skoroszyt, a następnie aktywować w nim określony arkusz, a następnie wybrać komórkę.
Sub SelectCell() Workbooks("Book2.xlsx").Worksheets("Sheet2").Activate Range("A1").Wybierz End Sub
Zauważ, że kiedy odwołujesz się do skoroszytów, musisz użyć pełnej nazwy wraz z rozszerzeniem pliku (.xlsx w powyższym kodzie). Jeśli skoroszyt nigdy nie został zapisany, nie musisz używać rozszerzenia pliku.
Teraz te przykłady nie są zbyt przydatne, ale w dalszej części tego samouczka zobaczysz, jak możemy użyć tych samych koncepcji do kopiowania i wklejania komórek w programie Excel (przy użyciu VBA).
Tak jak wybieramy komórkę, możemy również wybrać zakres.
W przypadku zakresu może to być stały zakres rozmiarów lub zmienny zakres rozmiarów.
W ustalonym zakresie rozmiarów będziesz wiedział, jak duży jest zakres i możesz użyć dokładnego rozmiaru w kodzie VBA. Ale dzięki zakresowi o zmiennej wielkości nie masz pojęcia, jak duży jest zakres i musisz użyć odrobiny magii VBA.
Zobaczmy, jak to zrobić.
Wybór stałego zakresu rozmiarów
Oto kod, który wybierze zakres A1:D20.
Sub SelectRange() Zakres("A1:D20").Wybierz End Sub
Innym sposobem na zrobienie tego jest użycie poniższego kodu:
Sub SelectRange() Zakres("A1", "D20"). Wybierz End Sub
Powyższy kod pobiera adres lewej górnej komórki (A1) i adres prawej dolnej komórki (D20) i wybiera cały zakres. Ta technika staje się przydatna, gdy pracujesz z zakresami o różnych rozmiarach (jak zobaczymy, gdy właściwość End zostanie omówiona w dalszej części tego samouczka).
Jeśli chcesz, aby wybór nastąpił w innym skoroszycie lub innym arkuszu, musisz podać VBA dokładne nazwy tych obiektów.
Na przykład poniższy kod wybierze zakres A1: D20 w arkuszu Sheet2 w skoroszycie Book2.
Sub SelectRange() Workbooks("Book2.xlsx").Worksheets("Sheet1").Activate Range("A1:D20").Wybierz End Sub
A co, jeśli nie wiesz, ile jest tam rzędów. Co zrobić, jeśli chcesz zaznaczyć wszystkie komórki, które mają w sobie wartość.
W takich przypadkach należy skorzystać z metod przedstawionych w następnej sekcji (przy wyborze zakresu o zmiennej wielkości).
Wybór zakresu o zmiennej wielkości
Istnieją różne sposoby zaznaczania zakresu komórek. Wybrana metoda będzie zależeć od struktury danych.
W tej sekcji omówię kilka przydatnych technik, które są naprawdę przydatne podczas pracy z zakresami w VBA.
Wybierz opcję Używając właściwości CurrentRange
W przypadkach, gdy nie wiesz, ile wierszy/kolumn zawiera dane, możesz użyć właściwości CurrentRange obiektu Range.
Właściwość CurrentRange obejmuje wszystkie ciągłe wypełnione komórki w zakresie danych.
Poniżej znajduje się kod, który wybierze bieżący region, w którym znajduje się komórka A1.
Sub SelectCurrentRegion() Range("A1").CurrentRegion.Select End Sub
Powyższa metoda jest dobra, gdy masz wszystkie dane w postaci tabeli bez żadnych pustych wierszy/kolumn.
Ale jeśli masz puste wiersze/kolumny w swoich danych, nie wybierze tych po pustych wierszach/kolumnach. Na poniższym obrazku kod CurrentRegion wybiera dane do wiersza 10, ponieważ wiersz 11 jest pusty.
W takich przypadkach można użyć właściwości UsedRange obiektu Worksheet.
Wybierz Używając właściwości UsedRange
UsedRange pozwala odnosić się do dowolnych komórek, które zostały zmienione.
Tak więc poniższy kod zaznaczy wszystkie używane komórki w aktywnym arkuszu.
Sub SelectUsedRegion() ActiveSheet.UsedRange.Select End Sub
Zauważ, że jeśli masz odległą komórkę, która została użyta, zostanie ona uwzględniona przez powyższy kod i wszystkie komórki do momentu wybrania tej używanej komórki.
Wybierz używając właściwości End
Ta część jest naprawdę przydatna.
Właściwość End pozwala wybrać ostatnią wypełnioną komórkę. Pozwala to naśladować działanie klawiszy strzałek Control w dół/w górę lub klawiszy Control w prawo/w lewo.
Spróbujmy to zrozumieć na przykładzie.
Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz szybko wybrać ostatnio wypełnione komórki w kolumnie A.
Problem polega na tym, że dane mogą się zmieniać i nie wiesz, ile komórek jest wypełnionych. Jeśli musisz to zrobić za pomocą klawiatury, możesz wybrać komórkę A1, a następnie użyć klawisza Control + Strzałka w dół, aby wybrać ostatnią wypełnioną komórkę w kolumnie.
Zobaczmy teraz, jak to zrobić za pomocą VBA. Ta technika przydaje się, gdy chcesz szybko przejść do ostatniej wypełnionej komórki w kolumnie o zmiennej wielkości
Sub GoToLastFilledCell() Range("A1").End(xlDown).Wybierz End Sub
Powyższy kod przeskoczyłby do ostatniej wypełnionej komórki w kolumnie A.
Podobnie możesz użyć End(xlToRight), aby przejść do ostatniej wypełnionej komórki z rzędu.
Sub GoToLastFilledCell() Range("A1").End(xlToRight).Wybierz End Sub
A co, jeśli chcesz zaznaczyć całą kolumnę zamiast przeskakiwać do ostatniej wypełnionej komórki.
Możesz to zrobić za pomocą poniższego kodu:
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown)).Wybierz End Sub
W powyższym kodzie użyliśmy pierwszego i ostatniego odwołania do komórki, którą musimy wybrać. Bez względu na to, ile jest wypełnionych komórek, powyższy kod wybierze wszystkie.
Zapamiętaj powyższy przykład, w którym wybraliśmy zakres A1:D20, używając następującego wiersza kodu:
Zakres („A1″,D20”)
Tutaj A1 to lewa górna komórka, a D20 to prawa dolna komórka zakresu. Tej samej logiki możemy użyć przy wyborze zakresów o zmiennej wielkości. Ale ponieważ nie znamy dokładnego adresu prawej dolnej komórki, użyliśmy właściwości End, aby ją uzyskać.
W Range(„A1”, Range(„A1”).End(xlDown)), „A1” odnosi się do pierwszej komórki, a Range(„A1”).End(xlDown) odnosi się do ostatniej komórki. Ponieważ udostępniliśmy oba odwołania, metoda Select wybiera wszystkie komórki między tymi dwoma odwołaniami.
Podobnie możesz również wybrać cały zestaw danych, który ma wiele wierszy i kolumn.
Poniższy kod zaznaczy wszystkie wypełnione wiersze/kolumny, zaczynając od komórki A1.
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Wybierz End Sub
W powyższym kodzie użyliśmy Range(„A1”).End(xlDown).End(xlToRight), aby uzyskać odwołanie do prawej dolnej wypełnionej komórki zbioru danych.
Różnica między używaniem CurrentRegion i End
Jeśli zastanawiasz się, po co używać właściwości End do wybierania wypełnionego zakresu, gdy mamy właściwość CurrentRegion, pozwól, że powiem Ci różnicę.
Za pomocą właściwości End można określić komórkę początkową. Na przykład, jeśli masz dane w A1:D20, ale pierwszy wiersz to nagłówki, możesz użyć właściwości End, aby wybrać dane bez nagłówków (za pomocą poniższego kodu).
Sub SelectFilledCells() Range("A2", Range("A2").End(xlDown).End(xlToRight)).Wybierz End Sub
Ale CurrentRegion automatycznie wybierze cały zestaw danych, w tym nagłówki.
Do tej pory w tym samouczku widzieliśmy, jak odnosić się do szeregu komórek na różne sposoby.
Zobaczmy teraz, w jaki sposób możemy wykorzystać te techniki, aby wykonać pewną pracę.
Kopiuj komórki / zakresy za pomocą VBA
Jak wspomniałem na początku tego samouczka, zaznaczanie komórki nie jest konieczne do wykonywania na niej działań. W tej sekcji zobaczysz, jak kopiować komórki i zakresy bez ich zaznaczania.
Zacznijmy od prostego przykładu.
Kopiowanie pojedynczej komórki
Jeśli chcesz skopiować komórkę A1 i wkleić ją do komórki D1, zrobi to poniższy kod.
Sub CopyCell() Range("A1").Copy Range("D1") End Sub
Zauważ, że metoda kopiowania obiektu zakresu kopiuje komórkę (tak jak Control + C) i wkleja ją w określonym miejscu docelowym.
W powyższym przykładowym kodzie miejsce docelowe jest określone w tym samym wierszu, w którym używasz metody Copy. Jeśli chcesz, aby Twój kod był jeszcze bardziej czytelny, możesz użyć poniższego kodu:
Sub CopyCell() Range("A1").Copy Destination:=Range("D1") End Sub
Powyższe kody skopiują i wkleją w nim wartość, a także formatowanie/formuły.
Jak mogłeś już zauważyć, powyższy kod kopiuje komórkę bez jej zaznaczania. Bez względu na to, gdzie jesteś w arkuszu, kod skopiuje komórkę A1 i wklei ją w D1.
Należy również zauważyć, że powyższy kod zastąpiłby dowolny istniejący kod w komórce D2. Jeśli chcesz, aby program Excel informował Cię, czy w komórce D1 jest już coś, bez nadpisywania go, możesz użyć poniższego kodu.
Sub CopyCell() If Range("D1") "" Then Response = MsgBox("Czy chcesz nadpisać istniejące dane", vbYesNo) End If If Response = vbYes Then Range("A1").Copy Range("D1 ") Koniec, jeśli Koniec Sub
Kopiowanie ustalonego zakresu
Jeśli chcesz skopiować A1:D20 do J1:M20, możesz użyć poniższego kodu:
Sub CopyRange() Range("A1:D20").Copy Range("J1") End Sub
W komórce docelowej wystarczy podać adres komórki w lewym górnym rogu. Kod automatycznie skopiuje dokładnie skopiowany zakres do miejsca docelowego.
Tej samej konstrukcji można użyć do skopiowania danych z jednego arkusza do drugiego.
Poniższy kod skopiuje A1:D20 z aktywnego arkusza do Sheet2.
Sub CopyRange() Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
Powyższe kopiuje dane z aktywnego arkusza. Dlatego przed uruchomieniem kodu upewnij się, że arkusz zawierający dane jest arkuszem aktywnym. Aby być bezpiecznym, możesz również określić nazwę arkusza roboczego podczas kopiowania danych.
Sub CopyRange() Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
Dobrą rzeczą w powyższym kodzie jest to, że bez względu na to, który arkusz jest aktywny, zawsze skopiuje dane z Sheet1 i wklei je do Sheet2.
Możesz również skopiować nazwany zakres, używając jego nazwy zamiast odwołania.
Na przykład, jeśli masz nazwany zakres o nazwie „SalesData”, możesz użyć poniższego kodu, aby skopiować te dane do Arkusza2.
Sub CopyRange() Range("SalesData").Copy Worksheets("Sheet2").Range("A1") End Sub
Jeśli zakres nazwanego zakresu obejmuje cały skoroszyt, nie musisz znajdować się w arkuszu, który zawiera nazwanego zakresu, aby uruchomić ten kod. Ponieważ nazwany zakres jest objęty zakresem skoroszytu, możesz uzyskać do niego dostęp z dowolnego arkusza przy użyciu tego kodu.
Jeśli masz tabelę o nazwie Table1, możesz użyć poniższego kodu, aby skopiować ją do Sheet2.
Sub CopyTable() Range("Table1[#All]").Copy Worksheets("Sheet2").Range("A1") End Sub
Możesz także skopiować zakres do innego skoroszytu.
W poniższym przykładzie kopiuję tabelę programu Excel (Tabela1) do skoroszytu Book2.
Sub CopyCurrentRegion() Range("Table1[#All]").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1") End Sub
Ten kod zadziała tylko wtedy, gdy skoroszyt jest już otwarty.
Kopiowanie zakresu o zmiennej wielkości
Jednym ze sposobów kopiowania zakresów o zmiennej wielkości jest przekonwertowanie ich na nazwane zakresy lub tabelę Excel i użycie kodów, jak pokazano w poprzedniej sekcji.
Ale jeśli nie możesz tego zrobić, możesz użyć właściwości CurrentRegion lub End obiektu zakresu.
Poniższy kod skopiuje bieżący region w aktywnym arkuszu i wklei go w Sheet2.
Sub CopyCurrentRegion() Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1") End Sub
Jeśli chcesz skopiować pierwszą kolumnę zestawu danych do ostatniej wypełnionej komórki i wkleić ją do Arkusza2, możesz użyć poniższego kodu:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1") End Sub
Jeśli chcesz skopiować zarówno wiersze, jak i kolumny, możesz użyć poniższego kodu:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1") End Sub
Zauważ, że wszystkie te kody nie wybierają komórek podczas wykonywania. Ogólnie rzecz biorąc, znajdziesz tylko kilka przypadków, w których musisz wybrać komórkę/zakres przed rozpoczęciem pracy.
Przypisywanie zakresów do zmiennych obiektów
Do tej pory używaliśmy pełnego adresu komórek (takich jak Skoroszyty(„Książka2.xlsx”).Kartki robocze(„Arkusz1”).Zakres(„A1”)).
Aby ułatwić zarządzanie kodem, możesz przypisać te zakresy do zmiennych obiektów, a następnie użyć tych zmiennych.
Na przykład w poniższym kodzie przypisałem zakres źródłowy i docelowy do zmiennych obiektu, a następnie użyłem tych zmiennych do skopiowania danych z jednego zakresu do drugiego.
Sub CopyRange() Dim SourceRange jako zakres Dim DestinationRange jako zakres Ustaw SourceRange = Worksheets("Sheet1").Range("A1:D20") Set DestinationRange = Worksheets("Sheet2").Range("A1") SourceRange.Copy DestinationRange Napis końcowy
Zaczynamy od zadeklarowania zmiennych jako obiektów Range. Następnie przypisujemy zakres do tych zmiennych za pomocą instrukcji Set. Po przypisaniu zakresu do zmiennej możesz po prostu użyć zmiennej.
Wprowadź dane w następnej pustej komórce (przy użyciu pola wprowadzania)
Możesz użyć pól wprowadzania, aby umożliwić użytkownikowi wprowadzenie danych.
Załóżmy na przykład, że masz zestaw danych poniżej i chcesz wprowadzić rekord sprzedaży, możesz użyć pola wprowadzania w VBA. Używając kodu możemy upewnić się, że wypełni on dane w kolejnym pustym wierszu.
Sub EnterData() Dim RefRange jako zakres Ustaw RefRange = Range("A1").End(xlDown).Offset(1, 0) Ustaw ProductCategory = RefRange.Offset(0, 1) Ustaw ilość = RefRange.Offset(0, 2 ) Set Amount = RefRange.Offset(0, 3) RefRange.Value = RefRange.Offset(-1, 0).Value + 1 ProductCategory.Value = InputBox("Kategoria produktu") Quantity.Value = InputBox("Ilość") Kwota.Wartość = InputBox("Kwota") Koniec Sub
Powyższy kod używa pola wprowadzania VBA, aby uzyskać dane wejściowe od użytkownika, a następnie wprowadza dane wejściowe do określonych komórek.
Zauważ, że nie użyliśmy dokładnych odwołań do komórek. Zamiast tego użyliśmy właściwości End i Offset, aby znaleźć ostatnią pustą komórkę i wypełnić w niej dane.
Ten kod jest daleki od użyteczności. Na przykład, jeśli wprowadzisz ciąg tekstowy, gdy w polu wprowadzania pojawi się pytanie o ilość lub kwotę, zauważysz, że program Excel na to pozwala. Możesz użyć warunku If, aby sprawdzić, czy wartość jest liczbowa, czy nie, a następnie odpowiednio na to zezwolić.
Pętla przez komórki/zakresy
Do tej pory widzieliśmy, jak wybierać, kopiować i wprowadzać dane w komórkach i zakresach.
W tej sekcji zobaczymy, jak przechodzić przez zbiór komórek/wierszy/kolumn w zakresie. Może to być przydatne, gdy chcesz przeanalizować każdą komórkę i wykonać na jej podstawie jakąś akcję.
Na przykład, jeśli chcesz podświetlić co trzeci wiersz w zaznaczeniu, musisz przejść w pętli i sprawdzić numer wiersza. Podobnie, jeśli chcesz podświetlić wszystkie ujemne komórki, zmieniając kolor czcionki na czerwony, musisz przejrzeć i przeanalizować wartość każdej komórki.
Oto kod, który przejdzie przez wiersze w wybranych komórkach i podświetli alternatywne wiersze.
Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Ustaw Myrange = Zaznaczenie dla każdego Myrow In Myrange.Rows If Myrow.Row Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Powyższy kod wykorzystuje funkcję MOD do sprawdzenia numeru wiersza w zaznaczeniu. Jeśli numer wiersza jest parzysty, zostanie podświetlony na niebiesko.
Oto kolejny przykład, w którym kod przechodzi przez każdą komórkę i podświetla komórki, które mają w niej wartość ujemną.
Sub HighlightAlternateRows() Dim Myrange jako zakres Dim Mycell jako zakres Ustaw Myrange = Zaznaczenie dla każdej Mycell in Myrange If Mycell < 0 Then Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Zauważ, że możesz zrobić to samo, używając formatowania warunkowego (które jest dynamiczne i jest lepszym sposobem na zrobienie tego). Ten przykład służy wyłącznie do pokazania, jak działa pętla z komórkami i zakresami w VBA.
Gdzie umieścić kod VBA
Zastanawiasz się, gdzie w skoroszycie programu Excel znajduje się kod VBA?
Excel ma zaplecze VBA zwane edytorem VBA. Musisz skopiować i wkleić kod w oknie kodu modułu VB Editor.
Oto kroki, aby to zrobić:
- Przejdź do zakładki Deweloper.
- Kliknij opcję Visual Basic. Spowoduje to otwarcie edytora VB w zapleczu.
- W okienku Eksplorator projektu w edytorze VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu, do którego chcesz wstawić kod.Jeśli nie widzisz Eksploratora projektów, przejdź do zakładki Widok i kliknij Eksplorator projektów.
- Przejdź do Wstaw i kliknij Moduł. Spowoduje to wstawienie obiektu modułu do skoroszytu.
- Skopiuj i wklej kod w oknie modułu.
