fbpx
Minuty czytania: 5

Jak pracować z zakresami komórek (Range) w VBA?

Kontynuujemy naszą podróż po podstawach VBA. Z tego artykułu dowiesz się, jak w VBA pracować z zakresami komórek (Range). To jedna z najbardziej kluczowych umiejętności. Automatyzując pracę często chcemy wykonać jakieś działania właśnie na zakresach.

Kolorowy obraz komórek

W kolejnych akapitach dowiesz się:

  • jakie są metody na zapisywanie wartości do pojedynczej komórki, jak i do zakresów komórek,
  • jakie są najbardziej użyteczne metody i właściwości związane z zakresami,
  • poznasz metody na znalezienie ostatniego użytego wiersza w zakresie oraz używanego zakresu komórek.

W dalszej części artykułu będę posługiwał się słowem Range, które może oznaczać zarówno pojedynczą komórkę jak i zakres komórek.

Jak zapisywać wartość do komórek w VBA?

Generalnie masz 3 możliwości, aby odwołać się do komórki lub zakresu w VBA:

  1. za pomocą właściwości Range
  2. za pomocą właściwości Cells
  3. lub wykorzystując nazwane zakresy komórek

Pomiędzy Range, a Cells występuje kilka różnic ale zauważysz najważniejsze z nich w dalszej części artykułu oraz kiedy napiszę o pętlach w VBA.

Najlepiej będzie zrozumieć te 3 przypadki na przykładach. Załóżmy, że chcesz stworzyć kod, który w komórce A1 umieści napis „Excelness”. Możesz to zrobić tak:

Range("A1").Value = "Excelness"

jak i tak:

Cells(1,1).Value = "Excelness"

I pewnie dostrzegasz już pierwszą różnicę. W metodzie Range odwołujemy się do adresu komórki w formacie litera kolumny (A) i numer wiersza (1). Natomiast w metodzie Cells podajemy kolejno: numer wiersza (1) i numer kolumny (1) do której chcemy się odwołać. Adres komórki A1 musimy umieścić w cudzysłowie. Parametr .Value jest opcjonalny ale warto go używać, szczególnie na początku przygody z VBA.

Jeżeli komórkę A1 nazwiesz np. „Parametr” to w VBA możesz się do niej odwołać również tak:

Range("Parametr").Value = "Excelness" 

A co z zakresami? Załóżmy, że zakres A1:B20 chcesz wypełnić liczbą 100. Zapisz tak:

Range("A1:B20").Value = 100

lub

Range(Cells(1,1), Cells(20,2)).Value = 100

Jeżeli musisz odwołać się do rozdzielnych zakresów – użyj przecinka, aby je rozdzielić:

Range("A1:B10, E2:E10").Value = 100 
Wprowadzanie wartości do zakresu komórek Range.

Natomiast używając poniższego sposobu – wstawisz wartości do wybranych komórek, ale pominiesz komórki pomiędzy nimi:

Range("A4,E4") = "Excelness"
Wprowadzanie wartości do rozdzielnego zakresu komórek Range.

W dalszej nauce VBA, na pewno będziesz chciał używać pętli. W takich przypadkach przydaje się wykorzystanie poniższego odwołania, ponieważ numery wierszy mogą być zmienną (co oznacza, że wartość będzie się zmieniała po uruchomieniu pętli, np. od 1 do 100)

Range("A" & 6, "E" & 9) = "Excelness"
Wprowadzanie tekstu do zakresu komórek Range.

Dobrze jest także wiedzieć, że w ramach jakiegoś zakresu możesz wykorzystać Cells, aby odwołać się do jakieś komórki w ramach tego zakresu:

Range("A2:D10").Cells(4, 2).Value = 1000
Wyznaczanie zakresu w zakresie i wprowadzanie wartości za pomocą VBA.

Jeszcze innym sposobem na odwoływanie się do komórek i zakresów jest wykorzystanie właściwości: Offset oraz Resize.

Najbardziej użyteczne metody i właściwości w pracy z zakresami (Range)

Zacznijmy od właściwości Offset. Dzięki niej, możesz „przenieść się” z odwołaniem z jednego zakresu / komórki do drugiego. W poniższym przykładzie, zaczynając w komórce A1, przenosimy się 7 wierszy w dół i 2 kolumny na prawo i w nowej komórce wstawiamy wartość 100.

Używając wartości ujemnych (np. Offset(-2, -2)) możesz poruszać się także w górę i w prawo.

Range("A1").Offset(7, 2).Value = 100
Wykorzystanie Offset na obiekcie Range

Za pomocą Resize, będziesz mógł natomiast rozszerzyć wstępnie zdefiniowany zakres o daną ilość wierszy i kolumn. Na przykład poniższy kod rozszerza zakres z komórki A1, na 5 wierszy i 3 kolumny, i w nowo zdefiniowanym zakresie wstawia wartość 100.

Range("A1").Resize(5, 3).Value = 100
Wykorzystanie Resize na obiekcie Range

Przykłady innych przydatnych właściwości i metod znajdziesz w poniższej tabeli. Najlepiej jak sam przetestujesz jak działają. Podobno człowiek uczy się najszybciej jak sam coś robi 😉

Często polecanym sposobem nauki jest używanie rejestratora makr. W ten sposób możesz sprawdzić: składnię, argumenty, czy też w ogóle kluczowe słowa jakie używane są do wykonywania określonych czynności, np. sortowania. Musisz być jednak świadomy, że rejestrowany w ten sposób kod VBA nie jest optymalny i często ma wiele nadmiarowych poleceń.

Tabela użytecznych metod i właściwości obiektu Range.

Jak znaleźć ostatni wiersz w zakresie (Range) lub używany obszar?

Jeżeli jesteś początkujący w VBA, to może Cię zastanawiać dlaczego ma Cię interesować odnajdywanie ostatniego użytego wiersza (czy kolumny) w zakresie.

To ważny temat, ponieważ wielokrotnie będziesz tworzył kod, którego zadaniem będzie przetwarzanie zakresu, wiersz po wierszu. A przecież każdy zakres jest ograniczony, a co ważniejsze może się zmieniać! Na przykład mogą pojawiać się nowe dane. Dlatego musisz wiedzieć jak wyznaczyć ostatni wiersz (kolumnę) w danym zakresie.

Metod jest kilka i chyba każda ma wady i zalety. Mam nadzieję, że odkryjemy je wspólnie w kolejnych artykułach. Tymczasem opiszę te (trzy) sposoby.

Użyj właściwości End obiektu Range

Powiedzmy, że w aktywnym arkuszu masz jakąś tabelkę w zakresie od A1 do F1000. Codziennie doklejasz tam nowe dane. Twój kod VBA musi za każdym razem znaleźć ostatni użyty wiersz.

MsgBox Range("A1").End(xlDown).Row

W powyższym kodzie używamy właściwości End obiektu Range. End ma argument xlDown, co oznacza ni mniej nie więcej, tylko przejście z komórki A1 do ostatnio użytego wiersza w kolumnie A – dokładnie tak jakbyś użył skrótu CTRL + strzałka w dół.

Dla porządku, powyższy kod wykorzystuje właściwość Row, aby odczytać numer wiersza tej ostatniej komórki oraz funkcję MsgBox, która po prostu wyświetla ten numer w okienku dialogowym.

Okienko dialogowe MsgBox

Poeksperymentuj z innymi argumentami właściwości End: xlUp, xlToLeft, xlToRight, aby przekonać się jak możesz odnajdywać pierwszy użyty wiersz, pierwszą użytą kolumnę oraz ostatnią użytą kolumnę.

Użyj właściwości CurrentRegion obiektu Range

Kolejna metoda wykorzystuje CurrentRegion, czyli zaznaczenie użytego obszaru. To tak jakbyś w opisywanym zakresie A1:F1000, przykładowo aktywował komórkę F10 i użył skrótu CTRL+A. Cały obszar A1:F1000 – zostanie zaznaczony.

MsgBox Range("F10").CurrentRegion.Rows.Count

Jak pewnie się domyśliłeś – powyższy kod zlicza ilość wierszy w aktywnym zakresie – fragment .Rows.Count. Podobnie jak wcześniej MsgBox wyświetli liczbę tych wierszy. Co oczywiste ich liczba wyznacza numer ostatniego wiersza.

Wykorzystaj metodę SpecialCells

Wspominałem wcześniej o SpecialCells we fragmencie o najbardziej użytecznych metodach i właściwościach.

MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address

Argument tej metody – xlCellTypeLastCell – odnajduje ostatnią użytą komórkę w zakresie. Tym razem użyłem właściwości Address, aby wyświetlić adres tej komórki.


Chcesz darmowy szablon Excel do prowadzenia projektów i śledzenia zadań?


Podsumowanie

Jeżeli teraz napiszę, że poruszyliśmy kluczowe tematy w zakresie VBA, to w kolejnym artykule pewnie będę się powtarzał 🙂 W końcu to ma być cykl, który opisuje właśnie te najważniejsze obszary, dając Ci dobre podstawy do dalszych poszukiwań i eksperymentów.

Jednak zarządzanie zakresami w VBA jest naprawdę kluczowe. Tak jak kluczowe jest dla poprawnego używania Excela. Wyobrażasz sobie użycie funkcji, formuł, tworzenie tabel przestawnych czy wykresów bez wiedzy o zakresach? No właśnie… ja też nie 🙂

Mam nadzieję, że przybliżyłem Ci ten temat i zachęciłem do własnych ćwiczeń. Jeżeli natrafisz na problem – daj znać. Rozwiążemy go wspólnie!


Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.

Kategorie: VBA

2 Komentarze

Jankesd · 8 października 2019 o 15:52

Dzień dobry art. extra zrobiony. Chyba będzie cały cykl w przyszłości lub kurs online. Jak dla Mnie „pedarda” za przeproszeniem. Tłumaczenie zagadnienia rewelacyjnie prosto i rzeczowo i każdy powinien zrozumieć. Po proszę więcej takich art. chociaż raz w tygodniu.

    Łukasz Pietrzak · 9 października 2019 o 20:16

    Dziękuję 🙂 Niestety, nie mogę pisać na temat VBA raz w tygodniu, bo czeka także kilka innych ale równie ciekawych tematów. Ale obiecuję, że ciąg dalszy nastąpi!

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *