Tym razem nagramy odrobinę bardziej skomplikowane makro. Bardziej skomplikowany będzie kod (omówiony w następnym odcinku), bo to co zrobimy będzie zwykłym formatowaniem komórki. Ważne dla nas będzie też nowy sposób uruchamiania makra, a mianowicie przypisanie go do „przycisku”, obrazka itp.
Nasze makro ma po prostu formatować komórkę w specyficzny sposób. Ważne na początek zaznaczymy sobie tylko pojedynczą komórkę z liczbą.
Następnie będziemy chcieli zmienić kilka opcji formatowania tej komórki, tylko pamiętaj najpierw włączyć nagrywanie makra. Nazwiemy je sobie po prostu Formatowanie. Po kliknięciu przycisku OK naciśnij od razu Ctrl + 1, żeby przejść do okna formatowania komórek. Na zakładce liczby ustaw formatowanie walutowe:
Na zakładce Wyrównanie zmień tylko wyrównanie w poziomie na Do środka:
Na zakładce czcionka zmniejsz rozmiar czcionki na 9 i styl czcionki na pogrubiony.
Kolejna zakładka to Obramowanie. Tutaj ustaw jakiś styl przerywanej linii i nałóż ją na Kontur.
Zostaje jeszcze zakładka Wypełnienie, gdzie wybierzemy jeden z kolorów motywu.
Teraz zatwierdź zmiany przyciskiem OK i wyłącz rejestrowanie makra. Zaznaczona komórka powinna być już odpowiednio sformatowana teraz chcemy przypisać nasze makro do „przycisku”. Nie będziemy patrzeć w ogóle na jego kod.
Najpierw musimy stworzyć „przycisk” to może być dowolny kształt z karty wstawianie, ale najbardziej przypomina przycisk zaokrąglony prostokąt.
Po tym jak wstawisz kształt możesz mu dodać tekst i zmienić mu formatowanie według swojego uznania. Wystarczy, że zaznaczysz kształt i zobaczysz jakie masz możliwości na karcie Formatowanie.
Teraz najważniejszy dla nas moment – przypisanie makra do „przycisku”. Wystarczy, że klikniesz na niego prawym przyciskiem myszy i z podręcznego menu wybierzemy polecenie Przypisz makro.
Później musisz tylko wybrać makro, które chcesz przypisać i zatwierdzić to przyciskiem OK (w zależności od ilości otwartych plików, możesz zobaczyć różną ilość makr i plików).
Teraz wystarczy, że zaznaczysz kilka komórek powiedzmy zakres B5:B10 i klikniesz w przycisk (jak najedziesz na niego myszką kształt kursora się zmieni).
Możesz teraz zobaczyć, że przerywane obramowanie nie znajduje się pomiędzy komórkami.
Jest to specjalny „błąd”, który będziemy chcieli poprawić w następnym odcinku, na razie możemy się cieszyć J że udało się nam przypisać makro do „przycisku”, czyli kształtu. Możesz też przypisać makro do wstawionego obrazu i większości obiektów, które tworzysz w Excelu.
P.S. Pamiętaj, że makra nie możesz cofnąć – czyści ono całkiem też bufor wszystkich operacji cofania dlatego pamiętaj o tym i twórz kopie zapasowe swoich danych, gdy tworzysz makra.
Nagraliśmy już pierwsze makro i zobaczyliśmy jego kod. Teraz chcemy zobaczyć różnicę w zarejestrowanym kodzie, gdy będziemy zatwierdzać funkcję DZIŚ tylko Enterem. Dodatkowo poznamy różnicę w zapisie rejestrowanego makra, gdy są wyłączone odwołania względny i gdy są włączone.
Wciąż nagrywamy proste makra, bo dalej skupiamy się na funkcjonalnościach J Wciąż wstawiamy funkcję DZIŚ tylko zatwierdzamy ją Enterem (Załóżmy, że wstawimy ją do komórki A4 i mamy wyłączone odwołanie względne). Powiedzmy, że będzie się nazywać Dzisiaj2 i ponownie zapisujemy ją do skoroszytu makr osobisty. Dołożymy nawet ponownie skrót klawiszowy Ctrl + Shift + D, ale tylko po to, żeby zobaczyć komunikat Excela o tym, że taki skrót został już przypisany do innego makra.
Musimy usunąć skrót (skasować literę D) i nagrać opisane wcześniej proste makro.
Jeśli nagrywałeś makro zaraz po poprzednim, to powinno się zapisać do tego samego modułu co poprzednio (Module1), pod wcześniejszym makrem, ale jeśli w między czasie zdążyłeś zamknąć i otworzyć Excela to rejestrator zapisze makro w nowym module.
W kodzie znów jest dużo niepotrzebnego nam komentarzu, więc możemy go usunąć.
Zmienił się też wykonywany kod:
Sub Dzisiaj2()
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("A5").Select
End Sub
Ponownie powtórzę to, że jeśli znasz angielski będzie Ci łatwiej go zrozumieć. Teraz zamiast słowa Selection jest ActiveCell, czyli funkcja TODAY, będzie wstawiana tylko do pojedynczej aktywnej komórki, nawet jeśli zaznaczysz większy zakres danych (możesz sobie to później przetestować).
Pojawiła się też dodatkowa linijka: Range("A5").Select
Łatwo się domyśleć, że chodzi tu o zaznaczenie (Select) komórki A5. Jest wykorzystana funkcja Range, która służy w VBA do zaznaczania pojedynczych komórek, a także zakresów. Ważne jest to, że ponieważ mieliśmy wyłączone odwołania względne, ta linijka zapisała się dokładnie tak, czyli nie ważne, która komórka będzie aktywna, po wpisaniu w nią funkcji DZIŚ, Excel przejdzie zawsze do komórki A5.
Ponieważ nie dołożyliśmy skrótu klawiszowego do naszego makra musimy poznać inny sposób na jego uruchomienie. Kolejną możliwością jest użycie polecenia Wyświetl makra (skrót klawiszowy do niego do Alt + F8).
Po jego kliknięciu otworzy się okno, gdzie zobaczysz wszystkie makra z otwartych plików.
Jak do tej pory nagraliśmy tylko dwa makra i oba je widać w tym oknie. Ponieważ nie są to makra z aktywnego pliku, więc dodatkowo przed swoją nazwą mają również nazwę pliku, w którym się znajdują i wykrzyknik.
Są tu dostępne przyciski:
Uruchom, to z niego za chwilę skorzystamy,
Wkrocz, do uruchamiania makra krok po kroku, o tym w kolejnym wideo,
Edycja, przeskoczysz dzięki niemu od razu do kodu danego makra,
Usuń usuwania kod makra z modułu.
Opcje, pozwala zmieniać podstawowe opcje makra, m.in. skrót klawiszowy do niego przypisany – o tym za chwilę
Teraz możesz przetestować to makro naciskając przycisk Uruchom, gdy masz zaznaczoną pojedynczą komórkę i kilka komórek.
Wniosek jaki powinieneś wyciągnąć z tego makra jest taki, że ma znaczenie jak zatwierdzasz wpisane wartości i funkcje, bo rejestrator będzie zapisywał inny kod.
Teraz potrzebujemy nagrać jeszcze jedno makro, analogiczne jak to przed chwilą (w komórce A6), tylko z włączonymi odwołaniami względnymi — wystarczy, że raz klikniesz w to polecenie na karcie Deweloper.
Tym razem makro nazwiemy Dzisiaj3 i zapiszemy je w aktualnym pliku. Pamiętaj o wyłączeniu makra. Zanim przejdziemy do kodu VBA zerkniemy co wyświetla polecenie Wyświetl Makra (Alt + F8)
Tym razem jedno makro jest bez nazwy pliku, bo znajduje się w aktywnym pliku. Dodatkowo na obrazie powyżej jest rozwinięta lista możliwości skąd mają być wyświetlane makra.
Teraz dla makra Dzisiaj (pierwszego, które nagraliśmy) kliknij przycisk Opcje. Najpierw musisz kliknąć na nazwę makra, a dopiero potem na przycisk Opcje. Otworzy się okno, w którym możesz zmienić skrót klawiszowy do makra i ewentualnie jego Opis, ale to też możesz zrobić jako komentarz w kodzie. Nie jest już nam potrzebny skrót do pierwszego makra, więc usuńmy stąd literę, a następnie sprawdź, że uruchamianie makra Dzisiaj3 skutkuje wpisaniem funkcji DZIŚ w aktywną komórkę i przejście do komórki poniżej. Zobaczmy teraz kod makra. Jeśli będzie Ci trudno odnaleźć moduł w aktywnym pliku, możesz skorzystać z przycisku Edycja w oknie Makr (Alt + F8).
Sub Dzisiaj2()
ActiveCell.FormulaR1C1 = "=TODAY()"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
Pomijając komentarz pierwsza linijka kodu jest taka sama jak w drugim makrze za to zmieniła się druga linijka – to ona odpowiada za przesunięcie zaznaczonej komórki, po wpisaniu funkcji TODAY. Najlepiej czytać ją krok po kroku, czyli przez poszczególne kroki.
Na początek jest aktywna komórka (ActiveCell). Następnie jest funkcja Offset, która zapewnia przesunięcie o ilość wpisanych wierszy (1) I kolumn (0). Kolejny punkt może być mylący, bo poznałeś już funkcję Range i służyła ona do zaznaczenia konkretnej komórki, ale w tej sytuacji nie chodzi o zaznaczenie komórki A1, tylko obszaru równego rozmiarowi komórce A1. Czyli jeśli byłby to większy obszar, to więcej komórek zostałoby zaznaczonych (Select).
Poznamy tutaj jeszcze jeden sposób na uruchomienie makra. Jeśli kursor znajduje się w kodzie makra, to możesz nacisnąć klawisz F5 lub przycisk play (patrz rysunek poniżej), żeby uruchomić makro.
Na koniec pomówimy jeszcze chwilę o strukturze obiektowej w kodzie VBA. W VBA jest wiele obiektów. Przykładowym obiektem może być arkusz i komórka w arkuszu. Każdy taki obiekt, często ma też obiekty pod sobą, np. komórka arkusza jest podobiektem obiektu arkusz J
Jeśli w edytorze VBA zaczniesz pisać np.: funkcję Range("A1"). (ważna jest ta kropka na końcu) to edytor VBA powinien Ci zacząć podpowiadać dalszy kod.
Przy niektórych pozycjach zobaczysz niejako zielone ruchome pudełko (metody obiektu), a przy niektórych palec wskazujący na tekst (właściwości obiektu). Metody są bardziej związane z wykonywaniem czynności, a wartości przeważnie odczytujesz albo zmieniasz. Może trochę Ci pomoże zrozumieć to obrazek na arkuszu Budzik
Dla budzika godzina to wartość, a Nastaw budzi i Nakręć to metody. Spokojnie tu dopiero zaznaczamy temat i na początku wystarczy, że będziesz wiedział, że istnieje taki podział. Że obiekty, często mogę mieć w sobie mniejsze obiekty i że przeważnie edytor VBA będzie Ci podpowiadał co może zrobić z danym obiektem (metody) lub jakie wartości możesz odczytać, jeśli po nim napiszesz kropkę.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Nagraliśmy pierwsze makro i sprawdziliśmy, że możemy je uruchomić za pomocą skrótu klawiszowego, ale gdzie ono jest zapisane? Jak wygląda?
Musimy przejść do kodu VBA. Możesz to zrobić z karty Deweloper wykorzystując polecenie Visual Basic
albo wykorzystując skrót klawiszowy Alt + F11 (ten skrót pozwala Ci przeskakiwać pomiędzy Excelem, a edytorem kodu VBA).
Po kliknięciu w polecenie Visual Basic w najgorszej sytuacji zobaczysz niemal całkiem szare okno.
Przydadzą się w nim przynajmniej jeszcze dwa dodatkowe okna, dlatego rozwiń menu View i wybierz pozycję Project Explorer (ewentualny skrót klawiszowy do jego włączenie to Ctrl + R).
Ważna uwaga – nie ważne jaką wersję językową Excela masz. Edytor VBA będzie zawsze po angielsku, łącznie ze składnią funkcji i liczb, ale o tym w kolejnych filmach.
Teraz w edytorze VBA widzieć też będziesz okno Project Explorera, gdzie zobaczysz wszystkie otwarte projekty (pliki), z ich podziałem na arkusze, moduły i inne obiekty plików. Powinieneś widzieć skoroszyt makr osobistych (PERSONAL.XLSB) i przynajmniej jeden otwarty plik na, którym pracujesz. U mnie jest to Wstęp do makr 04 – Gdzie…
Kolejnym oknem, które Ci się przyda jest Properties Window — znów otwierasz menu View. Możesz również użyć skrótu klawiszowego F4 do jego włączenia.
Edytor VBA wizualnie nie zmienił się prawie wcale od Excela 2003, a może i wcześniej, dlatego jest w nim zachowane "klasyczne" menu.
Properties Window może bardzo najść na Project Explorer. W takiej sytuacji będziesz musiał zmodyfikować rozmiar poszczególnych elementów edytora VBA, żeby odpowiadał Twoim potrzebom.
Teraz możemy odszukać moduł (tak nazywa się obiekt, w którym rejestrator makr zapisuje kod VBA), w którym odnajdziemy zarejestrowany przez nas kod VBA. Znajduje się on w skoroszycie makr osobistych w katalogu Modules. Możliwe, że będziesz musiał go rozwinąć klikając w znak plusa obok niego, żeby rozwinąć całą jego zawartość. Ponieważ powinno to być Twoje pierwsze nagrane makro, więc powinieneś mieć tylko pojedynczy moduł – Module1. Jeśli klikniesz w niego dwukrotnie otworzy się.
Nareszcie możesz zobaczyć kod, który zarejestrowałeś J. Nie jest go dużo, ale tak jak wspominałem – chodzi nam w tym momencie przede wszystkim o poznanie funkcjonalności makr i kodu VBA, a dopiero później zaczniemy nagrywać trudniejsze makra.
Standardowo możesz poszczególne elementy edytora VBA włączać, wyłączać, maksymalizować, minimalizować i zmieniać rozmiary. Wszystko po to, żeby wygodniej pracowało Ci się kodem VBA.
Przyszła pora na szybką analizę kodu. Sub Dzisiaj()
'
' Dzisiaj Makro
'
' Klawisz skrótu: Ctrl+Shift+D
'
Selection.FormulaR1C1 = "=TODAY()"
End Sub
Słowo kluczowe Sub oznacza początek makra. Później masz nazwę makra, którą nadałeś (moje nazywa się Dzisiaj). Następnie jest dużo linijek na zielono – to jest komentarz. W kodzie VBA komentarz rozpoczyna się od pojedynczego znaku cudzysłowu i ciągnie się do końca linii, czyli możesz na raz zakomentować tylko jedną linijkę kodu, ale możesz to nawet zrobić po kodzie, który ma zostać uruchomiony.
Mimo, że w komentarzu znajduje się skrót klawiszowy, to zmiana go nie zmieni faktycznego sposobu uruchamiania makra. Jest on tu tylko podany informacyjnie.
Na końcu kodu są słowa kluczowe kończące makro End Sub, a jedyną linijką faktycznie uruchamianego kodu jest: Selection.FormulaR1C1 = "=TODAY()".
Jak już wspominałem w edytorze VBA wszystko jest w nim po angielsku – nazwy funkcji też. Dlatego zamiast wpisywanej przez nas funkcji DZIŚ została zapisana funkcja TODAY.
Ważna jest też tutaj struktura obiektowa: Selection.FormulaR1C1, czyli czytając po angielsku na zaznaczeniu (Selection) przechodzimy do (znak kropki) do formuły (FormulaR1C1) na tym zaznaczeniu. Spokojnie odrobina wprawy i taka składnia stanie się dla Ciebie naturalna. Po prostu VBA jest językiem obiektowym, a każdy obiekt ma swoje właściwości itp. Na razie wystarczy o tym informacji.
Jeśli znasz angielski zrozumienie tego kodu (i przyszłych), będzie dla Ciebie dużo prostsze. To, że edytor VBA zapisał wpisywanie wartości na zaznaczeniu, a nie konkretnej, czy aktywnej komórce zawdzięczamy temu, że wykorzystaliśmy skrót klawiszowy Ctrl + Enter.
Jak wyglądałby kod, gdybyś funkcję DZIŚ zatwierdził zwykłym Enterem? Odpowiedź w następnym wideo 😉
P.S. Ponieważ komentarze w tym kodzie są nam zbędne możesz je usunąć.
Czasami chcesz, żeby Twoje dane były zapisane tylko wielkimi literami, czasami małymi, a czasami chcesz, żeby były zapisywane jako nazwy własne, czyli każdy wyraz rozpoczynał się z wielkiej litery.
Excel nie może wymusić, żeby użytkownik tak wpisywał dane, ale łatwo może wpisane dane do takiej postaci doprowadził – wystarczy, że skorzystasz z odpowiedniej funkcji. Jeśli chcesz, żeby tekst był pisany małymi literami to korzystasz z funkcji:
=LITERY.MAŁE(A2)
Jeśli ma to być tekst pisany tylko wielkimi literami, to korzystasz z funkcji:
=LITERY.WIELKIE(A2)
Jeśli natomiast chcesz, żeby każdy wyraz był pisany z wielkiej litery to korzystasz z funkcji:
Miałem okazji przeprowadzić szkolenie indywidualne dla Pana Piotra z firmy XPOLogistics w Rzeszowie. Podczas szkolenia natrafiliśmy na problem przy imporcie danych – dane dla poszczególnych podróży były importowane w dwóch wierszach i wyglądały podobnie jak na obrazku poniżej.
Czyli część informacji była w obu wierszach, część tylko w górnym wierszu, a część tylko w wierszu poniżej. Dodatkowo w kolumnie Pusty informacja pojawia się tylko czasami w górnym wierszu. Takie dane bardzo trudno analizować, dlatego trzeba je odpowiednio naprawić wypełniając odpowiednio dane w dół lub w górę i zostawić tylko jeden z wierszy dotyczących unikalnego numeru podróży.
Ułatwiliśmy sobie trochę zadanie, ponieważ dane są zapisane w postaci tabeli Excela – dzięki temu możemy łatwo zaznaczyć całą kolumnę danych (a nie całą kolumnę arkusza). Załóżmy, że zaczniemy od kolumny z datą. Po tym jak ją zaznaczysz musisz rozwinąć polecenie Znajdź i zaznacz na karcie Narzędzia Główne i z rozwiniętej listy wybrać pozycję Przejdź do – specjalnie.
Otworzy się okno, w którym musisz zaznaczyć pole opcji Puste i zatwierdzić wybór przyciskiem OK.
Excel zaznaczy Ci tylko puste komórki i teraz powinieneś napisać znak równa się i nacisnąć strzałkę w dół.
Ważne żebyś wynik tej operacji zatwierdził kombinacją klawiszy Ctrl + Enter. Wtedy formuła wstawi się do każdej pustej komórki i ta komórka będzie pobierała wartość z komórki poniżej nią.
Po zatwierdzeniu wyniku musisz ponownie zaznaczyć dane, skopiować je i wkleić jako wartości
Dzięki temu będziesz miał pewność, że wartości w komórkach się nie zmienią w wyniku np.: sortowania.
Analogiczną operację możesz wykonać, gdy chcesz skopiować dane z wiersza powyżej, tylko musisz napisać formułę patrzącą komórkę powyżej. Możesz w ten sposób od razu zaznaczyć kilka wierszy.
Ponieważ w kolumnie Pusty pojawia się wartość tylko czasem, to zostawimy ją w spokoju i będziemy chcieli zostawić tylko dolny wiersz. Pozostanie jeszcze w analogiczny sposób, jak poprzednio, wypełnić kolumnę Zysk €.
Ponieważ chcemy usunąć zbędne dane, to przefiltrujemy kolumnę Razem tak, żeby pojawiały się tylko puste wiersze.
Następnie zaznaczamy zakres danych tabeli (wystarczy, że zaznaczysz dowolną komórkę i naciśniesz Ctrl + A). Kolejnym krokiem będzie naciśnięcie skrótu klawiszowe Ctrl + -, który usunie wszystkie widoczne wiersze. Wystarczy teraz zdjąć filtr i widać tylko pojedyncze wiersze, które dużo prościej będzie analizować.
