Praca z komórkami i zakresami w programie Excel VBA (Wybierz, skopiuj, Przenieś, Edytuj)
podczas pracy z programem Excel większość czasu spędzasz w obszarze arkusza roboczego – zajmując się komórkami i zakresami.
a jeśli chcesz zautomatyzować swoją 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żesz zrobić z zakresami w VBA (np. wybierz, Kopiuj, przenieś, edytuj itp.).
aby omówić ten temat, podzielę ten samouczek na sekcje i pokażę, jak pracować z komórkami i zakresami w Excelu VBA za pomocą przykładów.,
zaczynajmy.
Jeśli jesteś zainteresowany nauką VBA w prosty sposób, sprawdź moje szkolenie online Excel VBA.
Ten samouczek obejmuje:
Wybieranie komórki/zakresu w programie Excel za pomocą VBA
aby pracować z komórkami i zakresami w programie Excel za pomocą VBA, nie musisz go wybierać.
w większości przypadków lepiej nie wybierać komórek lub zakresów (jak zobaczymy).,
mimo to ważne jest, abyś przejrzał tę sekcję i zrozumiał, jak to działa. Będzie to kluczowe w Twojej nauce VBA i wiele pojęć omówionych tutaj będzie używanych w tym samouczku.
Zacznijmy więc od bardzo prostego przykładu.
Wybieranie 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").SelectEnd Sub
powyższy kod zawiera obowiązkową część ” Sub ” i „End Sub” oraz wiersz kodu, który wybiera komórkę A1.,
Range(„A1”) informuje VBA o adresie komórki, do której chcemy się odwołać.
Select jest metodą obiektu Range i wybiera komórki / zakres określony w obiekcie Range. Odwołania do komórek muszą być zamknięte w podwójnych cudzysłowach.
Ten kod wyświetli błąd, jeśli arkusz wykresu jest aktywnym arkuszem. Arkusz Wykresów zawiera wykresy i nie jest szeroko stosowany. Ponieważ nie ma w nim komórek/zakresów, powyższy kod nie może go wybrać i skończyłby się błędem.
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 Sheet2), musisz najpierw aktywować Sheet2, a następnie wybrać komórkę w nim.
Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
podobnie możesz również aktywować skoroszyt, a następnie aktywować w nim określony arkusz roboczy, a następnie wybrać komórkę.
Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
zauważ, że gdy 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 zobaczysz w dalszej części tego samouczka, jak możemy używać tych samych pojęć do kopiowania i wklejania komórek w Excelu (za pomocą VBA).
tak jak wybieramy komórkę, możemy również wybrać zakres.
w przypadku zakresu może to być stały zakres rozmiaru lub zmienny zakres rozmiaru.
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 z zakresem o zmiennej wielkości, nie masz pojęcia, jak duży jest zakres i musisz użyć trochę magii VBA.
zobaczmy, jak to zrobić.,
wybór zakresu o ustalonej wielkości
oto kod, który wybierze zakres A1:D20.
Sub SelectRange()Range("A1:D20").SelectEnd Sub
innym sposobem jest użycie poniższego kodu:
Sub SelectRange()Range("A1", "D20").SelectEnd Sub
powyższy kod pobiera adres górnej lewej komórki (A1) i dolnej prawej komórki (D20) i wybiera cały zakres. Ta technika staje się przydatna, gdy pracujesz z zakresami o różnej wielkości (jak zobaczymy, gdy właściwość End zostanie omówiona w dalszej części tego samouczka).,
Jeśli chcesz, aby zaznaczenie miało miejsce w innym skoroszycie lub innym arkuszu roboczym, musisz podać VBA dokładne nazwy tych obiektów.
na przykład poniższy kod wybierze zakres A1: D20 w arkuszu arkuszowym Sheet2 w skoroszycie Book2.
Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub
teraz, co jeśli nie wiesz, ile wierszy jest tam. Co zrobić, jeśli chcesz wybrać wszystkie komórki, które mają w sobie wartość.
w takich przypadkach należy użyć metod pokazanych w następnej sekcji(przy wyborze zakresu o różnej wielkości).,
Wybieranie zakresu o różnej wielkości
istnieją różne sposoby wybierania zakresu komórek. Wybrana metoda zależy od struktury danych.
w tym dziale omówię kilka przydatnych technik, które są naprawdę przydatne podczas pracy z zakresami w VBA.
wybierz właściwość CurrentRange
W przypadku, 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 sąsiadujące ze sobą 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.SelectEnd Sub
powyższa metoda jest dobra, gdy masz wszystkie dane jako tabelę bez żadnych pustych wierszy/kolumn.
ale jeśli masz puste wiersze/kolumny w danych, nie wybierze tych po pustych wierszach/kolumnach. Na poniższym obrazku bieżący kod wybiera dane do wiersza 10, ponieważ wiersz 11 jest pusty.
w takich przypadkach możesz użyć właściwości UsedRange obiektu arkusza roboczego.,
Wybierz przy użyciu właściwości UsedRange
UsedRange pozwala odwoływać się do dowolnych komórek, które zostały zmienione.
więc poniższy kod wybierze wszystkie używane komórki w aktywnym arkuszu.
Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub
zauważ, że w przypadku, gdy masz daleko od komórki, która została użyta, będzie ona brana pod uwagę przez powyższy kod i wszystkie komórki, dopóki używana komórka nie zostanie wybrana.
Wybierz używając właściwości End
teraz ta część jest naprawdę przydatna.
właściwość End pozwala wybrać ostatnią wypełnioną komórkę., Pozwala to naśladować efekt sterowania klawiszem strzałek w dół/w górę lub sterowania prawym / lewym klawiszem.
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ół, a następnie wybrać ostatnią wypełnioną komórkę w kolumnie.
teraz zobaczmy, jak to zrobić za pomocą VBA., Ta technika jest przydatna, gdy chcesz szybko przejść do ostatniej wypełnionej komórki w kolumnie o różnej wielkości
Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub
powyższy kod przeskoczy do ostatniej wypełnionej komórki w kolumnie A.
podobnie możesz użyć końca(xlToRight), aby przejść do ostatniej wypełnionej komórki w wierszu.
Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub
Co zrobić, jeśli chcesz wybrać całą kolumnę zamiast przeskakiwać do ostatniej wypełnionej komórki.,
możesz to zrobić używając poniższego kodu:
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd 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 za pomocą następującego wiersza kodu:
Range(„A1″,”D20”)
tutaj A1 to górna lewa komórka, a D20 to dolna prawa komórka w zakresie. Możemy użyć tej samej logiki przy wyborze zakresów różnej wielkości., Ale ponieważ nie znamy dokładnego adresu dolnej prawej komórki, użyliśmy własności końcowej, aby ją zdobyć.
In Range(„A1”, Range („A1”).End (xldown)), „A1” odnosi się do pierwszej komórki i zakresu („A1”).End (xlDown) odnosi się do ostatniej komórki. Ponieważ dostarczyliśmy oba odniesienia, metoda Select wybiera wszystkie komórki między tymi dwoma odniesieniami.
Podobnie można wybrać cały zestaw danych, który ma wiele wierszy i kolumn.
poniższy kod wybierze wszystkie wypełnione wiersze/kolumny, zaczynając od komórki A1.,
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub
w powyższym kodzie użyliśmy Range(„A1”).End(xlDown).End (xlToRight), aby uzyskać odniesienie do dolnej prawej wypełnionej komórki zbioru danych.
różnica między używaniem CurrentRegion i End
Jeśli zastanawiasz się, dlaczego użyj właściwości End, aby wybrać wypełniony zakres, 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ę startową., 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(używając poniższego kodu).
Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd 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.
teraz zobaczmy, w jaki sposób możemy rzeczywiście użyć tych technik, aby wykonać jakąś pracę.,
Kopiuj komórki/zakresy za pomocą VBA
jak wspomniałem na początku tego samouczka, wybranie komórki nie jest konieczne do wykonywania na niej działań. W tej sekcji zobaczysz, jak kopiować komórki i zakresy, nawet bez ich wybierania.
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 range kopiuje komórkę (tak jak Control +C) i wkleja ją do określonego miejsca docelowego.,
w powyższym przykładzie kod docelowy jest określony w tej samej linii, w której używana jest metoda kopiowania. 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ść oraz formatowanie/formuły.
Jak mogłeś już zauważyć, powyższy kod kopiuje komórkę bez jej wybierania. Bez względu na to, gdzie jesteś w arkuszu, kod skopiuje komórkę A1 i wklei ją do D1.
zauważ również, że powyższy kod nadpisze dowolny istniejący kod w komórce D2., Jeśli chcesz, aby Excel informował Cię, czy w komórce D1 jest już coś bez nadpisywania, możesz użyć poniższego kodu.
kopiowanie zakresu o ustalonej wielkości
Jeśli chcesz skopiować A1: D20 w 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 lewej górnej komórki. Kod automatycznie skopiuje dokładny skopiowany zakres do miejsca docelowego.
możesz użyć tej samej konstrukcji, aby skopiować dane z jednego arkusza do drugiego.
poniższy kod skopiuje A1:D20 z aktywnego arkusza do arkusza Sheet2.,
Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
powyższe kopiuje dane z aktywnego arkusza. Przed uruchomieniem kodu upewnij się, że arkusz zawierający dane jest aktywnym arkuszem. 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 odniesienia.,
na przykład, jeśli masz nazwany zakres o nazwie 'SalesData', możesz użyć poniższego kodu, aby skopiować te dane do Sheet2.
Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub
Jeśli zakres nazwanego zakresu jest całym skoroszytem, nie musisz znajdować się na arkuszu z nazwanym zakresem, aby uruchomić ten kod. Ponieważ nazwany zakres jest ograniczony dla skoroszytu, możesz uzyskać do niego dostęp z dowolnego arkusza za pomocą 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").Copy Worksheets("Sheet2").Range("A1")End Sub
Możesz również skopiować zakres do innego skoroszytu.,
w poniższym przykładzie kopiuję tabelę Excel (Table1) do skoroszytu Book2.
Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub
Ten kod będzie dział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ć CurrentRegion lub End właściwości obiektu range.
poniższy kod skopiuje bieżący region w aktywnym arkuszu i wklei go w Arkusz2.,
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 arkusza 2, 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ć wiersze, a także 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 nad nią.,
Przypisywanie zakresów do zmiennych obiektowych
do tej pory korzystaliśmy z pełnego adresu komórek (np. skoroszytów(„Book2.xlsx”).Arkusze („Arkusz1”).Zakres(„A1”)).
aby ułatwić zarządzanie kodem, możesz przypisać te zakresy do zmiennych obiektowych, a następnie użyć tych zmiennych.
na przykład w poniższym kodzie przypisałem zakres źródłowy i docelowy do zmiennych obiektowych, a następnie wykorzystałem te zmienne do kopiowania danych z jednego zakresu do drugiego.
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żna po prostu użyć zmiennej.
wprowadź dane w następnej pustej komórce (za pomocą pola wprowadzania)
możesz użyć pól wprowadzania, aby umożliwić użytkownikowi wprowadzanie danych.
na przykład, załóżmy, że masz zestaw danych poniżej i chcesz wprowadzić rekord sprzedaży, możesz użyć pola wprowadzania w VBA. Za pomocą kodu możemy upewnić się, że wypełnia on dane w następnym pustym wierszu.,
powyższy kod wykorzystuje pole wejściowe VBA, aby pobrać dane wejściowe od użytkownika, a następnie wprowadzić dane wejściowe do określonych komórek.
zauważ, że nie użyliśmy dokładnych odniesień 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 wpiszesz ciąg tekstowy, gdy pole wprowadzania zapyta 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 ją zezwolić.,
zapętlanie komórek / zakresów
do tej pory widzieliśmy, jak wybierać, kopiować i wprowadzać dane w komórkach i zakresach.
w tej sekcji zobaczymy, jak zapętlić zbiór komórek/wierszy/kolumn w zakresie. Może to być przydatne, gdy chcesz przeanalizować każdą komórkę i wykonać pewne działania na jej podstawie.
na przykład, jeśli chcesz podświetlić co trzeci wiersz zaznaczenia, musisz przebić się przez pętlę i sprawdzić numer wiersza., Podobnie, jeśli chcesz podświetlić wszystkie ujemne komórki, zmieniając kolor czcionki na czerwony, musisz zapętlić i przeanalizować wartość każdej komórki.
oto kod, który będzie pętlą przez wiersze w wybranych komórkach i podświetla alternatywne wiersze.
Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub
powyższy kod wykorzystuje funkcję MOD, aby sprawdzić numer wiersza w zaznaczeniu. Jeśli numer wiersza jest parzysty, zostanie podświetlony kolorem cyjanowym.
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 As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub
zauważ, że możesz zrobić to samo za pomocą formatowania warunkowego(które jest dynamiczne i lepszym sposobem na to). Ten przykład ma na celu pokazanie, jak pętla działa z komórkami i zakresami w VBA.
gdzie umieścić kod VBA
Excel ma backend VBA o nazwie edytor VBA. Musisz skopiować i wkleić kod w oknie kodu modułu edytora VB.
oto kroki, aby to zrobić:
- przejdź do karty programista.,
- kliknij opcję Visual Basic. Spowoduje to otwarcie edytora VB w backendzie.
- w okienku Project Explorer w edytorze VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu, do którego chcesz wstawić kod. Jeśli nie widzisz Eksploratora projektu, przejdź do zakładki widok i kliknij Eksplorator projektu.
- przejdź do wstawki i kliknij na moduł. Spowoduje to wstawienie obiektu modułu do skoroszytu.
- skopiuj i wklej kod w oknie modułu.,
Możesz również polubić następujące samouczki programu Excel:
- praca z arkuszami roboczymi za pomocą VBA.
- praca ze skoroszytami przy użyciu VBA.
- tworzenie funkcji zdefiniowanych przez Użytkownika w Excelu.
- dla następnej pętli w Excelu VBA – poradnik dla początkujących z przykładami.
- Jak korzystać z funkcji Excel VBA InStr (z praktycznymi przykładami).
- Excel VBA Msgbox.
- Jak nagrać makro w Excelu.
- Jak uruchomić makro w Excelu.
- Jak utworzyć dodatek w Excelu.,
- osobisty skoroszyt makr Excel / Zapisz & używaj makr we wszystkich skoroszytach.
- Excel VBA Events-prosty (i Kompletny) przewodnik.
- Obsługa błędów Excel VBA.
- jak sortować dane w Excelu za pomocą VBA (przewodnik krok po kroku).
- 24 przydatne przykłady makr Excela dla początkujących VBA (gotowe do użycia).