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.

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 

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"

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"

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

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

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

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ń.

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.

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.

---

---

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.

Podstawy VBA

Czy VBA jestem świętym Graalem, rozwiązującym wszystkie problemy jakie masz w Excelu? Najprostsza odpowiedź brzmi NIE. Ale jednocześnie, dzięki znajomości VBA, z pewnością możesz:

• robić rzeczy jakich nie zrobiłbyś bez tej wiedzy – zyskujesz nowe możliwości,
• automatyzować pracę swoją i innych – zyskujesz czas.

Tym artykułem rozpoczynam cykl, którym zamierzam w uporządkowany sposób przekazać Ci podstawy VBA. Będę w 100% szczery. Nie jestem mistrzem VBA. Ale pomyślałem, że to może być zaleta. Nie będę mówił do Ciebie jak programista i być może dzięki temu ta wiedza będzie bardziej zjadliwa 😉

Zanim przejdziesz dalej, przeczytaj mój artykuł o rejestrowaniu makr. Dzięki temu zyskasz wiedzę jak:

• dodać na wstążce kartę DEVELOPER, która zbiera polecenia związane z makrami i VBA,
• nagrać makro,
• uruchomić edytor VBA i modyfikować zapisany przez rejestrator kod.

To będzie dobry wstęp, aby ruszyć z dalszą nauką. Takie podstawy podstaw VBA 😉

VBA, czyli manipulowanie obiektami

Visual Basic for Applications (VBA) to język programowania, który pozwala na automatyzację pracy z dokumentami w programach Microsoft Office. Kod VBA będzie zapisany w pliku dokumentu MS Office, np. w arkuszu Excela. To oznacza, że aby wywołać działanie takiego programu potrzebujesz najpierw uruchomić dany program. W naszym przypadku – Excel.

VBA wykorzystuje tzw. model obiektowy. Poprzez pisanie kodu VBA zazwyczaj rozumiemy manipulowanie obiektami. Obiekty mają właściwości i metody, za pomocą których wykonujesz działania na tychże obiektach. I jest to w zasadzie klucz do zrozumienia całego języka. No dobra, ale co to właściwie oznacza?

Posłużę się analogią. Weźmy samochód. Samochód, ma: kolor, wielkość, określony silnik, pojemność i wiek. To właściwości samochodu. Samochód możesz także: uruchomić, ruszyć do przodu, zahamować…etc. – to metody jakich możesz użyć.

Przełóżmy to na język Excela. Jeżeli obiektem będzie pojedyncza komórka (samochód) to jej właściwością może być np. adres (A1), a metodą – wyczyszczenie zawartości (Clear)

Obiekty mogą występować w relacjach, tworząc hierarchię. I znowu posłużmy się analogią. Weźmy dom (obiekt podstawowy). Pojedynczy dom jest częścią osiedla (kolekcja) Natomiast osiedla są częścią dzielnicy, a dzielnice – miasta. Mamy więc kolekcję domów, osiedli i w końcu dzielnic.

W Excel VBA mamy na przykład zakres (może być pojedyncza komórka), która jest obiektem w skoroszycie (skoroszytów może być więcej i tworzą kolekcję), a ten z kolei jest częścią zeszytu (pliku Excela) I tak, przykładowo w VBA aby odwołać się do zakresu / komórki A1 możesz użyć kodu:

Workbooks("Zeszyt1").Worksheets("Arkusz1").Range("A1")

Jak pewnie zauważyłeś odwołanie do kolejnych obiektów następuje z wykorzystaniem „.” (kropki)

Właściwości vs. metody

Jeżeli do tej pory wszystko jest dla Ciebie jasne to jesteś na dobrej drodze, aby opanować podstawy VBA 🙂 Sprawę komplikuje trochę fakt, że zarówno właściwości jak i metod używasz tak samo. Co mam na myśli? Spójrz na przykład:

Range("A1").Address
Range("A1").Select

Jak widzisz, mamy ten sam obiekt – Range(„A1”) ale raz mówimy o właściwości (Address), a raz o metodzie (Select) Skąd właściwie masz wiedzieć co jest czym? O tym nieco później.

Podstawy VBA: Właściwości

Właściwości pojawiają się na końcu hierarchii obiektów. Przykładowo:

 Workbooks("Zeszyt1").Worksheets("Arkusz1").Range("A1").Value

To pewnie zamiesza Ci trochę w głowie ale właściwości mogą także zwracać…obiekty.

Część właściwości możesz tylko odczytać, a część zarówno odczytać jak i zapisać. Na przykład Address to właściwość, którą możesz tylko odczytać. To dość logiczne, bo przecież nie możesz zmienić adresu komórki A1.

Z kolei Value, to przykład właściwości którą możesz zarówno odczytać, jak i zapisać. I to również jest logiczne, bo przecież możesz zarówno odczytać zawartość jakieś komórki, jak i zmienić jej wartość.

Podstawy VBA: metody

Co musisz wiedzieć o metodach? Na początku dwie rzeczy. Po pierwsze metoda może zmienić właściwość. Po drugie część metod, zawiera dodatkowe informacje, które precyzują jej działanie. To argumenty metody.

Przykładowo metoda Clear, może zmienić wartość (Value) komórki, czyli jej właściwość.

Range("A1").Clear

Z kolei metoda PasteSpecial (wklej specjalnie) ma 4 dodatkowe argumenty / parametry: Paste, Operation, SkipBlanks, Transponse. Przykładowo możesz zapisać te dodatkowe parametry w metodzie tak:

Range("B1").PasteSpecial xlPasteValues

ale również tak:

Range("B1").PasteSpecial Paste:=xlPasteValues

Jak widzisz w pierwszym przypadku, od razu podajesz wartość parametru (xlPasteValues, oznacza tu wklejenie wartości), a w drugim następuje przypisanie do danego argumentu za pomocą znaków „:=”.

Podstawy VBA: procedura SUB

W zasadzie jesteś gotowy aby napisać swój pierwszy kod. I zanim zabierzemy się do rzeczy, dwa słowa wprowadzenia.

Zazwyczaj (nie zawsze) kod wpisujesz w procedurze SUB. Wygląda to tak:

Sub JakasNazwa()
...
 Twoj kod
...
End Sub

Procedura Sub to po prostu logiczny zestaw poleceń, który wykonuje jakieś działanie lub zestaw działań. Procedury umieszczasz w modułach (Module) ale to od Ciebie zależy, czy umieścisz wszystkie procedury w jednym module czy rozdzielisz na kilka. Na razie nie musisz się tym przejmować.

Nie będziemy robić nic skomplikowanego i wystarczy nam jedna procedura i jeden moduł. Nasza procedura ma za zadanie skopiować zawartość komórki A1 i wkleić ją jako wartość (bez formuły) do komórki B1.

Co musisz zrobić, aby to osiągnąć?

• utwórz nowy plik, a następnie w komórce wpisz dowolną formułę. Może być =2+2 😉
• uruchom edytor VBA (skrót ALT + F11),
• po lewej stronie, w oknie Project – VBA Project odnajdź nazwę swojego pliku (zapewne Zeszyt1),
• kliknij prawym przyciskiem myszy na tę nazwę (VBA Project – Zeszyt1) i wybierz INSERT > MODULE,
• w środkowej części ekranu pojawi się puste okno, gdzie możesz zacząć pisać kod.

Podstawy VBA: procedura SUB (przykład)

Sub KopiujWartosci()
 Range("A1").Copy
 Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub

Po wpisaniu kodu, uruchamiasz go skrótem F5. Odpowiednią opcję znajdziesz również na pasku narzędzi.

Jeżeli będziesz chciał zapisać plik z napisanym właśnie kodem, musisz to zrobić wybierając odpowiedni format pliku – skoroszyt programu Excel z obsługą makr (rozszerzenie pliku .xlsm)

Prześledźmy wspólnie kod linijka po linijce.

 Sub KopiujWartosci()

Zaczynamy od zdefiniowania procedury – słowo kluczowe Sub oraz nazwy KopiujWartosci. Całość kończy się nawiasami. Na marginesie, pomiędzy nawiasami będziesz mógł umieszczać argumenty i przekazywać wyniki z / do innych procedur. Ale póki co – po prostu zostawiamy otwarty i zamknięty nawias. To konieczne dla poprawności składni.

 Range("A1").Copy

Dalej mamy zdefiniowany obiekt Range(„A1”), czyli po prostu komórka A1. Skąd wiadomo z którego arkusza / pliku to będzie komórka? Ponieważ nie definiujemy tego wcześniej (przed Range(„A1”)) będzie to komórka A1 aktywnego arkusza – w momencie uruchomienia kodu.

Następnie, po kropce definiujemy metodę – Copy, czyli po prostu skopiowanie zawartości komórki.

  Range("B1").PasteSpecial Paste:=xlPasteValues

Kolejna linijka, to znowu obiekt – Range(„B1”) oraz metoda PasteSpecial (wklej specjalnie) Dalej korzystamy z parametru xlPasteValues, aby wkleić tylko wartości.

End Sub

Całą procedurę musimy zakończyć słowami End Sub, co jest wymaganą składnią dla poprawności działania całej procedury.

---

---

Podsumowanie

Zaryzykuję stwierdzenie, że VBA sprowadza się do manipulowania obiektami: komórkami, zakresami, wykresami, tabelami etc. Przy czym możesz wykorzystywać przy tym elementy charakterystyczne dla „klasycznych” języków programowania takich, jak: pętle, instrukcje warunkowe czy zmienne.

Jeżeli zrozumiesz na czym polega hierarchiczność obiektów (np. komórka w arkuszu, arkusz w skoroszycie) oraz zapamiętasz najważniejsze pojęcia: obiekt, właściwość, metoda to będzie dobry początek aby szybko przyswajać pozostałe elementy VBA.

Mam nadzieję, że wspólnie ze mną 😉 Daj znać w komentarzu, jeżeli masz jakieś pytania albo chciałbyś się podzielić swoimi doświadczeniami w nauce VBA.

---

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