Jeśli czytałeś poprzednie artykuły z cyklu „Podstawy VBA”, to teraz nadeszła chwila prawdy! Instrukcja VBA IF…THEN w połączeniu z wcześniejszą wiedzą pozwoli stworzyć Ci pierwszy użyteczny kod.
VBA IF…THEN (dla jasności JEŻELI…WÓWCZAS…) pozwoli Ci sterować, jaką drogą powinien „iść” Twój program. Jeśli znasz funkcję JEŻELI, to pójdzie Ci naprawdę szybko 🙂
Tak naprawdę, tego typu instrukcja warunkowa jest chyba podstawą każdego języka programowania. Piszę „chyba”, bo nie jestem programistą. Ale na tyle na ile rozumiem pojęcie algorytmu, to w każdym języku potrzebna jest instrukcja, która pozwoli programiście sterować „zachowaniem” programu.
Podobnie jest w języku VBA, a służy do tego między innymi instrukcja IF…THEN. Idea jest taka: jeżeli w trakcie działania programu trafiasz na dwie lub więcej możliwości to instrukcja pozwala zdecydować co ma się wydarzyć dalej. Graficznie można to pokazać jak na poniższym obrazku.
Dla przykładu, weźmy taki fragment Twojego kodu, gdzie dalsze działanie programu uzależnione jest od wartości wpisanej do komórki A1. Jeżeli użytkownik wpisze coś do komórki (PRAWDA), to ma nastąpić dalsze działanie, sprawdzające czy wartość jest liczbą czy tekstem. Jeżeli komórka A1 pozostaje pusta – nic się nie dzieje, a w zasadzie program kończy działanie nie wykonując żadnej operacji.
VBA IF: 1 czy 2 linie kodu
Składnia kodu VBA w podstawowej postaci, wygląda następująco:
IF Range("A1")<>"" Then Range("A2").Value = Range("A1").Value
W powyższym przykładzie, program sprawdza zawartość komórki A1. Jeśli komórka nie jest pusta (<>dwa znaki cudzysłowu), to komórka A2 przyjmie wartość taką samą jak komórka A1. W przeciwnym razie – nic się stanie i program zakończy swoje działanie.
Opisaną wyżej instrukcję możesz również umieścić w dwóch liniach kodu. W takim przypadku na końcu musisz dodać wyrażenie End If, które zamyka działanie całej instrukcji.
IF Range("A1")<>""
Then Range("A2").Value = Range("A1").Value
End If
VBA IF…ELSE…ELSEIF…
No dobrze, ale do tej pory nie mieliśmy okazji do rzeczywistego sterowania programem. Czyli nie pisaliśmy co ma się wydarzyć, jeżeli pierwszy warunek jest niespełniony. Program po prostu kończył działanie. Teraz to nadrobimy.
Weźmy taki przykład. Program ma sprawdzić zawartość komórki A1, do której powinien być wprowadzana litera oznaczająca region (N oznacza Północ, S – Południe, W – Zachód, E – Wschód) Program ma zamienić literę na jeden z czterech regionów. Jeśli komórka nie będzie miała oznaczenia: N, S, W, E to powinien pojawić się napis „Brak”.
Jak widzisz mamy tutaj do czynienia z więcej niż jednym warunkiem. Musimy bowiem sprawdzić, czy A1 = „N”, a jeżeli nie, to czy równa się „S”, a jeżeli nie…itd. Aby wprowadzić takie wielowarunkowe sprawdzenie, w wyrażeniu trzeba użyć instrukcji ELSEIF oraz ELSE. ELSEIF pozwala wprowadzić kolejny warunek, a po ELSE wskazujesz co ma się wydarzyć w każdym innym przypadku (poza opisanymi warunkami)
Kod będzie wyglądał tak:
Sub Regiony()
If Range("A1") = "N" Then
Range("A1") = "Północ"
ElseIf Range("A1") = "S" Then
Range("A1") = "Południe"
ElseIf Range("A1") = "W" Then
Range("A1") = "Zachód"
ElseIf Range("A1") = "E" Then
Range("A1") = "Wschód"
Else
Range("A1") = "Brak"
End If
End Sub
Program zadziała jak na poniższym filmie.
Chcesz lepiej poznać Excela? Zapisz się na Excelness News
Dziękuję!
Proszę, sprawdź swoją skrzynkę pocztową.
Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
Podsumowanie
Znajomość instrukcji IF-THEN to kolejny krok, aby opanować podstawy VBA. W większości przypadków, będziesz mógł sterować działaniem swoich programów wykorzystując to rozwiązanie. Dla bardziej złożonych przypadków (większej ilości warunków) użyjesz wyrażenia SELECT CASE. Ale o tym w jednym z kolejnych artykułów. Nie przegap i zapisz się na newsletter, bądź dołącz do grupy na Facebooku.
Jestem pewny, że już używasz zmiennych w Excelu. Nie muszą to być zmienne VBA. Wystarczy, że przynajmniej raz, napisałeś najprostszą formułę. Użyłeś w niej komórki z jakąś wartością. A wartości w komórkach mogą się zmieniać.
A może używasz nazw? Nazwa reprezentuje wartość, zakres albo pojedynczą komórkę. Nazwa pozostaje stała, ale wartości jakie reprezentuje zmieniają się. Widzisz, że to nic skomplikowanego. Zatem do dzieła!
Zmienna w VBA to nic innego jak nazwa, która służy do „przechowywania” wartości. Nazwy są przypisywane z wykorzystaniem znaku równości. Na przykład:
mojaZmienna = Range("A1").Value
Dlaczego używamy zmiennych? Po pierwsze, zmienne ułatwiają czytanie kodu VBA. Po drugie ułatwiają utrzymanie bardziej złożonych programów oraz pozwalają na bardziej zaawansowane kodowanie. I w końcu – oszczędzają czas!
Wyobraź sobie sytuację, że w swoim kodzie użyłbyś 10 razy odwołania do komórki B1 w taki sposób:
....Range("B1").Value
Jeżeli po jakimś czasie będziesz zmuszony zmienić to odwołanie, to czeka Cię aktualizacja wszystkich przypadków. Gdybyś użył zmiennej, na przykład tak:
mojaDrugaZmienna = Range("B1").Value
to w opisanej sytuacji – zmienisz tylko tę zmienną.
Na koniec być może najważniejszy powód, aby używać zmiennych VBA. Niektórych zadań w VBA, po prostu nie zrobisz efektywnie bez ich użycia. Mam na myśli na przykład wszelkiego rodzaju pętle. Są to fragmenty kodu gdzie przetwarzasz, jeden po drugim jakieś obiekty (np. komórki w zakresie)
Nadając nazwy swoim zmiennym, pamiętaj o tych zasadach:
Pierwszy znak nazwy zmiennej musi być literą
Nie używaj słów zastrzeżonych, takich jak: if, for, with, select etc.
Używaj podkreślenia i/lub wielkich liter w środku nazwy w celu zwiększenia czytelności
Nie używaj znaków: #$&!
Nazwa zmiennej nie może mieć więcej niż 255 znaków
Rodzaje zmiennych VBA
Wiesz już czym są zmienne VBA i dlaczego warto ich używać. Porozmawiajmy zatem o ich rodzajach oraz kiedy ich używać.
Generalnie rzecz biorąc, wskazując typ zmiennej mówisz ile pamięci ma zarezerwować na dane jakie przypiszesz do tej zmiennej. To przyspiesza kod, zmniejsza ryzyko popełnienia błędów i jest dobrą praktyką kodowania. Ale to jest cykl dla początkujących nie-programistów, więc na tym poprzestańmy. Nie będziemy liczyć ile to bajtów i ile milisekund szybciej będzie działał Twój kod 🙂
Najważniejsze typy zmiennych znajdziesz w poniższej tabeli.
TYP ZMIENNEJ
ZAKRES
Byte
Liczby całkowite w zakresie od 0 do 255
Integer
Liczby w zakresie od -32 768 do 32 768
Long
Liczby w zakresie od -2 147 483 648 do 2 147 483 648
Boolean
Prawda / Fałsz
Double
Bardzo duże liczby (ujemne do dodatnich) z wysoką precyzją miejsc po przecinku. Używamy również w przypadku %.
String
Tekst. Może być bardzo długi.
Object
Wszelkiego rodzaju obiekty, np. wykresy.
Date
Data w zakresie od 1 stycznia 0001 do 31 grudnia 9999
Variant
Dowolna wartość. Może przechowywać również takie wartości jak: „Empty” czy „Null”. Jeśli nie przypiszesz innego typu, to zmienna przyjmie właśnie typ Variant.
Jak deklarować zmienne VBA?
Aby zadeklarować jakąś zmienną musisz użyć słowa kluczowego Dim. Po słowie Dim wpisujesz wybraną przez siebie nazwę (zgodną z zasadami opisanymi na początku artykułu) Następnie używasz słowa „as” oraz wskazujesz na typ zmiennej.
Pojedynczą zmienną deklarujesz w pojedynczym wierszu, tak:
Dim MojTekst as String
lub kilka zmiennych w ramach jednego wiersza kodu, w taki sposób:
Dim PierwszyWiersz as Long, OstatniWiersz as Long
Jeżeli potrzebujesz użyć w kodzie stałej wartości, która nie będzie się zmieniała użyj słowa kluczowego Const.
Const StawkaVat as Double = 0,23
Deklarując jednocześnie wiele zmiennych tego samego typu, w ramach tej samej nazwy użyjesz tablicy Array, w taki sposób:
Dim Miesiace(1 to 12) as String
O deklarowaniu zmiennych dość łatwo zapomnieć. Ale możesz na samym sobie wymóc taki obowiązek. Wystarczy jeżeli użyjesz Option Explicit, które pojawiając się na początku każdego kodu wymusi definiowanie każdej zmiennej. Zachęcam Cię gorąco do włączenia tej opcji. W edytorze VBA, przejdź do TOOLS > OPTIONS i zaznacz REQUIRE VARIABLE DECLARATION.
Jedną z typów zmiennych jest Object. Dlatego, że zmienne mogą przechowywać różne obiekty Excela. Mogą nimi być na przykład: arkusz, wykres, kształt czy zakres komórek.
Nie wystarczy, że zadeklarujesz zmienną w ten sposób:
Dim MojZakres as Object
Musisz następnie przypisać dany obiekt do tej zmiennej używając słowa Set.
Set MojZakres = Range("A1:C1000")
W przypadku innych typów zmiennych, używasz znaku równości aby przypisać wartość do zmiennej. Co prawda w składni VBA występuje w takich przypadkach słowo Let, ale jego użycie jest opcjonalne. Taki zapis:
OstatniWiersz = Rows.Count
będzie równoznaczny z tym:
Let OstatniWiersz = Rows.Count
Zakres obowiązywania zmiennych
Zmienne VBA możesz wykorzystywać na kilku poziomach. Oznacza to, że zmienna może istnieć w ramach danej: procedury, pojedynczego modułu, czy też wszystkich procedur i modułów. Jeśli wydaje Ci się to skomplikowane, to spójrz na poniższe przykłady.
Option Explicit
Sub MojaProcedura()
Dim MojaZmienna as String
---jakiś kod---
End Sub
W powyższym przykładzie, zmienna MojaZmienna, będzie działała tylko w ramach procedury MojaProcedura. Oznacza to że zmienna istnieje tak długo, jak długo trwa procedura. Następnie pamięć jest zwalniana. Wartości przechowywanej przez zmienną, nie będziesz w stanie wykorzystywać w innych procedurach.
Option Explicit
Dim MojaZmienna as String
Sub MojaProcedura()
---jakiś kod---
End Sub
Umieszczając deklarację zmiennej przed procedurą sprawisz, że będzie ona dostępna również dla innych procedur (w ramach danego modułu). Co więcej, po zakończeniu działania danej procedury, wartość przechowywana przez zmienną nie znika z pamięci – możesz jej użyć w innym kodzie.
Option Explicit
Public MojaZmienna as String
Sub MojaProcedura()
---jakiś kod---
End Sub
Jeśli użyjesz słowa Public, to zdefiniowana zmienna będzie dostępna dla wszystkich modułów i procedur w ramach danego pliku Excel. Taką zmienną możesz zadeklarować w dowolnym module. Oczywiście, nie będzie też znikała z pamięci po zakończeniu działania danego kodu.
Dziękuję!
Proszę, sprawdź swoją skrzynkę pocztową.
Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
Podsumowanie
Wiesz już wszystko co powinieneś wiedzieć (na dobry początek) o zmiennych w VBA. Używaj ich, bo to nie tylko dobra praktyka i ułatwienie w czytaniu kodu. Przede wszystkim, dzięki zmiennym, możesz robić naprawdę ciekawe rzeczy w VBA. Automatyzacja pracy, przetwarzanie wielu plików, arkuszy i wierszy – to wszystko jest możliwe między innymi, dzięki zmiennym.
Nadal masz wątpliwości lub pytania? Zadaj je w komentarzu, lub dołącz do naszej grupy na Facebooku.
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:
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.
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.
Dziękuję!
Proszę, sprawdź swoją skrzynkę pocztową.
Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
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!
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:
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:
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.
Dziękuję!
Proszę, sprawdź swoją skrzynkę pocztową.
Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
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.
Kiedyś musi być ten pierwszy raz, prawda? Pierwsza praca. Pierwsza jazda samochodem. Pierwsze pieniądze. Po lekturze artykułu będziesz wiedział jak nagrać makro. Twoje pierwsze makro! Potem już tylko: sława, pieniądze… A nie, to nie ten temat 😉
Przede wszystkim na temat makr krąży wiele mitów. Wielu widzi w nich remedium na wszystkie excelowe problemy. „Gdybym tylko znał makra, rozwiązałbym ten problem w pracy” – myśli niejeden z nas. Tymczasem to nieprawda. No, nie całkiem prawda.
Czym jest makro?
Mówiąc w skrócie, jest to zestaw poleceń, który pozwala na zautomatyzowanie często powtarzanych czynności. Makro możesz zarejestrować, tak jak nagrywasz film. Klikasz wirtualną migawkę w Excelu i zaczynasz działać. Makro „nagrywa” kolejne kroki, które wykonujesz w postaci tajemniczo wyglądających poleceń.
Na przykład jeżeli włączę rejestrowanie makra, wykonując czynności które w efekcie dadzą taki efekt:
program zarejestruje taki kod:
Sub Makro1()
'Makro1 Makro
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Nagłówek"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Firma A"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Firma B"
End Sub
Obłęd! Wpisałem trzy słowa. Wyboldowałem nagłówek. Pokolorowałem tło na żółto. A w efekcie mam tyle linijek jakiś „chińskich krzaczków” 😉
Na szczęście w ogóle nie musisz patrzeć na te polecenia. Jeśli będziesz chciał uruchomić makro, po prostu wybierzesz odpowiednie polecenie. Program błyskawicznie wykona zestaw czynności, które zarejestrowałeś. I nie musisz nic więcej robić.
Nagrywane makra są „tłumaczone” na język poleceń zrozumiałych dla Excela. Tym językiem jest VBA (Visual Basic for Application) Jeśli będziesz chciał zapisać zeszyt z nagranym makrem, musisz to zrobić wybierając odpowiedni format pliku – skoroszyt programu Excel z obsługą makr (rozszerzenie pliku .xlsm)
Znając VBA wkraczasz w zupełnie nowy świat możliwości Excela. Ale nie od razu Kraków zbudowano 😉 Od czegoś trzeba zacząć.
Powiedzmy, że chcesz stworzyć makro, które zaznaczy wszystkie komórki, które zawierają formuły. Taka dodatkowa wizualizacja na pewno pomoże, w codziennej pracy z wieloma arkuszami.
Otwórz dowolny plik, który zawiera jakieś formuły. Możesz skorzystać z tego. Następnie postępuj wg poniższych kroków:
Ustaw się w dowolnej komórce, w zakresie danych (w zakresie jakieś tabeli)
Kliknij kartę DEWELOPER i wybierz ZAREJESTRUJ NOWE MAKRO.
Jeżeli nie widzisz karty DEWELOPER, wybierz PLIK > OPCJE > DOSTOSOWYWANIE WSTĄŻKI i po prawej stronie zaznacz kartę DEWELOPER.
Kliknij skrót CTRL + A na klawiaturze, tym samym zaznaczysz cały obszar tabeli
Teraz użyj skrótu CTRL + G, aby przejść do komórek specjalnych.
Wybieramy FORMUŁY i oczywiście zatwierdzamy, klikając OK.
Excel zaznaczy tylko te komórki, które zawierają formuły.
W dowolny sposób sformatuj te komórki. Ja wybrałem czerwoną czcionkę.
Gratulacje! Nagrałeś makro. Jeżeli robiłeś to na moim pliku, powinieneś otrzymać poniższy kod.
Sub Makro2()
'Makro2 Makro
Range("B2:G12").Select
Range("E11").Activate
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
End Sub
Aby zobaczyć kod, wybierz skrót ALT + F11, dzięki czemu przejdziesz do edytora Visual Basic. W tym artykule nie będziemy opisywać całego edytora. Skupimy się tylko na najważniejszej dla nas kwestii.
Wybierz Module1 po lewej stronie, a po prawej ukaże się okno edytora z kodem makr które nagrywałeś.
W interesującym nas Makrze nr2 mamy taką linijkę kodu:
Range("B2:G12").Select
Co oznacza, że nasze makro będzie zawsze działało na zakresie B2:G12. No zaraz! Ale przecież chcieliśmy stworzyć uniwersalne narzędzie. Ma kolorować komórki z formułami niezależnie od tego, gdzie znajduje się nasz zakres danych. Co zrobić? Musisz ręcznie zmodyfikować kod. W miejsce tych dwóch linijek:
Range("B2:G12").Select
Range("E11").Activate
wpisz:
Selection.CurrentRegion.Select
Ta linijka kodu powoduje, że zaznaczany jest cały obszar komórek przylegających do aktualnie zaznaczonej. Następnie cały ten obszar jest zaznaczany. Dalszą część kodu pomijamy.
Teraz możesz już śmiało przetestować makro:
stwórz nowy arkusz z tabelką i formułami,
skopiuj do niego swoje makro (możesz po prostu skopiować kod),
następnie uruchom makro i sprawdź czy wszystkie komórki z formułami zostały pokolorowane na czerwono.
Jak widzisz samo nagrywanie makra to nic trudnego. Ale podobno w życiu istnieje zasada: jak coś przychodzi nam zbyt łatwo to najczęściej nie jest tak samo wartościowe, jak to co zdobywamy z trudem 😉
W przypadku makr zasada sprawdza się znakomicie: możesz w prosty sposób zarejestrować czynności, które wykonujesz w Excelu, a potem odtworzyć je…w identyczny sposób. Problem w tym, że pomimo wykonywania tego samego zestawu czynności, najczęściej działamy: na różnych plikach, arkuszach, obszarach danych. Tu zwykły rejestrator makr nie pomoże. Trzeba poznać VBA.
Wg mnie główną zaletą rejestratora makr, jest właśnie możliwość nauki. Rejestrujesz makro, zmieniasz wielkość czcionki. Wchodzisz w edytor.
– Acha…więc takie polecenie zmieniło mi wielkość czcionki…noooo ciekawe…
Niestety rejestrator nie da Ci najbardziej czystego i optymalnego kodu. Ale na początek to nie jest najważniejsze. Niemniej jednak, będziesz mógł posmakować świata automatycznego Excela. Skoro już wiesz jak nagrać makro, to do dzieła! Próbuj, ucz się, zadawaj pytania – działaj!
