12 – Tłumaczenie makra formatującego raport

Poniżej masz opisany dokładnie kod, który się nagrał podczas nagrywania makra, którego zadaniem było formatowanie prawidłowe raportu nawet przy różnej ilości kolumn i wierszy.

Przechodzić pomiędzy Excelem, a edytorem VBA możesz szybko korzystając ze skrótu klawiszowego Alt + F11.

Dużo prościej jest zrozumieć kod makra, jeśli pamiętasz dokładnie, co krok po kroku robiłeś w Excelu.

Chcesz ze mną poznać możliwości VBA? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online:
http://excelw30dni.pl/kategoria-produktu/szkolenia-vba/

Pozdrawiam
Adam Kopeć
Miłośnik Excela

11 – Makro formatujące raport i skróty klawiszowe

W tym przykładzie zakładamy, że systematycznie dostajesz dane z systemu do Excela i potrzebujesz je odpowiednio sformatować (m.in. dodać kolumnę obliczającą Zysk). Główne założenia są takie, że ilość wierszy i kolumn w raporcie może się zmieniać, ale zawsze dwie ostatnie kolumny to Sprzedaż i Koszt własny sprzedaży.

W tym przykładzie będzie bardzo istotne wykorzystanie odpowiednich skrótów klawiszowych i pamiętanie o rodzaju odwołań z jakich korzysta rejestrator makr. Chcemy też być mniej zależni od tego jaką komórkę, lub obszar zaznaczy użytkownik. Najłatwiej to tutaj uzyskać wykonując operację zaznaczenia komórki A1 na początku makra. Odwołania względne muszą być tutaj wyłączone.

Wystarczy, że przed włączeniem nagrywania makra zaznaczysz dowolny obszar, a jako pierwszy krok zaznaczysz komórkę A1. Załóżmy, że nadamy makru nazwę Raport i nie przypiszemy do niego skrótu klawiszowego.

wstep-do-makr-11-nagrywanie-makra-formatujacego-raport-i-skroty-klawiszowe-01

Następnie chcemy dołożyć kolumnę obliczającą Zysk. Musimy pamiętać o tym, że ilość kolumn może się zmieniać dlatego nie możemy od razu przeskoczyć do komórki H1, ponieważ to nie zawsze będzie pierwsza pusta kolumna za naszym raportem. Na szczęście możemy wykorzystać skrót klawiszowy Ctrl + strzałka w prawo. Przy tym i podobnych skrótach nie ma znaczenia, czy masz włączoną rejestrację odwołań względnych, czy bezwzględnych, ponieważ te skróty klawiszowe rejestrują się jako specyficzna komenda (kod) VBA.

Korzystając ze skrótu Ctrl + strzałka w prawo przeniesie nas do ostatniej wypełnionej komórki w prawo, a my potrzebujemy przesunąć się jeszcze jedną kolumnę w prawo i zanim to zrobimy już musimy włączyć rejestrację odwołań względnych.
Teraz nie będzie mieć znaczenia czy naciśniesz strzałkę w prawo, czy klikniesz na właściwą komórkę myszką – edytor VBA zarejestruje tą operację tak samo.

Jak już masz zaznaczoną komórkę możesz wpisać do niej tekst Zysk. Po prostu zacznij go pisać i zatwierdź przyciskiem Enter. Enter zadziała na dwa sposoby. Po pierwsze zatwierdzi wpisany tekst, a po drugie przejdzie do komórki poniżej, gdzie będziemy chcieli wpisać formułę obliczającą Zysk:

wstep-do-makr-11-nagrywanie-makra-formatujacego-raport-i-skroty-klawiszowe-02

Przykładowa formuła =F2-G2, wykorzystuje odwołania względne do komórek i dzięki temu też nie jest istotna ilość kolumn w raporcie. Przede wszystkim musisz wiedzieć, że to nie będzie odejmowanie od siebie wartości z kolumny F i G, ale z kolumn o dwie kolumny na lewo od aktywnej kolumny (tej z formułą) i z kolumny o jeden na lewo. Formułę potrzebujemy zatwierdzić skrótem klawiszowym Ctrl + Enter, żeby rejestrator nie dograł nam niepotrzebnych kroków.

Po wpisaniu formuły chcielibyśmy ją skopiować do końca danych, ale niestety standardowa operacja podwójnego kliknięcia w prawy dolny róg komórki z formułą nie zostanie dla nas dobrze zarejestrowana, ponieważ edytor VBA zarejestruje tą operacją jako wpisanie formuły na dokładnie takim obszarze jaki wynika z aktualnych danych aktualnego raportu, a nam zależy, żeby ten obszar się zmieniał w zależności od ilości danych. Dlatego musimy trochę oszukać edytor VBA.

Najpierw skopiujemy formułę (Ctrl + C), następnie przechodzimy o jedną komórkę w lewo i stąd możemy skorzystać ze skrótu klawiszowego Ctrl + strzałka w dół, żeby przejść do ostatniej komórki danych (zakładamy, że wszystkie komórki są wypełnione). Następnie możemy wrócić w prawo jedną kolumnę.

Cały czas powinny być włączone odwołania względne przy rejestrowaniu makra.

Teraz skrót klawiszowy Ctrl + Shift + strzałka w górę, który zaznaczy nam zakres od ostatniego wiersza danych do komórki z formułą. Teraz możemy nacisnąć Ctrl + V, żeby wkleić formułę. Dzięki temu przechodzeniu zakres, w którym wklejamy formułę będzie się zmieniał w zależności od ilości danych.

Możemy też od razu skorzystać, ze skrótu klawiszowego Ctrl + Shift + 4, żeby nałożyć formatowanie walutowe na obszarze naszej formuły.

wstep-do-makr-11-nagrywanie-makra-formatujacego-raport-i-skroty-klawiszowe-03

W naszym raporcie potrzebujemy jeszcze pogrubić nagłówki (jako przykładowe ich formatowanie). Żeby to zrobić możemy nacisnąć skrót klawiszowy Ctrl + Home. Przeniesie on nas dokładnie do komórki A1, mimo, że wciąż mamy włączoną rejestrację odwołań względnych. Następnie wystarczy nacisnąć Ctrl + Shift + strzałkę w prawo, by zaznaczyć wszystkie nagłówki i Ctrl + B, żeby je pogrubić.

wstep-do-makr-11-nagrywanie-makra-formatujacego-raport-i-skroty-klawiszowe-04

Chcemy jeszcze naprawić daty, żeby wyświetlały się jako daty, a nie liczby. Wystarczy, że naciśniemy strzałkę w dół (przejdziemy wtedy względnie z komórki A1 do komórki A2), a następnie Ctrl + Shift + strzałka w dół, żeby zaznaczyć wszystkie daty do końca danych i musimy im zmienić formatowanie liczbowe z ogólnego na formatowanie dat – np. wykorzystując listę rozwijaną z karty Narzędzia Główne.

wstep-do-makr-11-nagrywanie-makra-formatujacego-raport-i-skroty-klawiszowe-05

Została nam ostatnia rzecz przy tym raporcie – na całym obszarze chcemy mieć nałożone obramowanie. Teraz bardzo ważne jest z jakiego skrótu klawiszowego skorzystamy (nie ma znaczenie sposób rejestrowania odwołań). Jeśli skorzystasz ze skrótu klawiszowego Ctrl + A, to edytor VBA zapisze zaznaczenie dokładnie takiego obszaru jaki ma rozmiar aktualny raport, a jeśli skorzystasz ze skrótu klawiszowego Ctrl + Shift + 8, to zostanie zapisane jako zaznaczenie aktualnego zakresu danych, czyli procedury, która będzie zmieniać zaznaczony zakres w zależności od ilości danych.

Po zaznaczeniu danych wystarczy, że nałożysz obramowanie np. wykorzystując polecenie z karty Narzędzia Główne.
wstep-do-makr-11-nagrywanie-makra-formatujacego-raport-i-skroty-klawiszowe-06

Nareszcie nagrywanie naszego makra jest skończone. Dokładną analizą kodu zajmiemy się w następnym odcinku.
Jeśli chcesz sprawdzić, czy działa możesz je uruchomić na następnym arkuszu naciskając skrót Ctrl + F8 i wybierając nasze makro.

Chcesz ze mną poznać możliwości VBA? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online:
http://excelw30dni.pl/kategoria-produktu/szkolenia-vba/

Pozdrawiam
Adam Kopeć
Miłośnik Excela

10 – Wyjaśnienie kodu z transponowaniem danych

Poniżej masz opisany dokładnie kod, który się nagrał podczas nagrywania makra, którego zadaniem było transponowanie danych.
Przechodzić pomiędzy Excelem, a edytorem VBA możesz szybko korzystając ze skrótu klawiszowego Alt + F11.
Dużo prościej jest zrozumieć kod makra, jeśli pamiętasz dokładnie, co krok po kroku robiłeś w Excelu.

Chcesz ze mną poznać możliwości VBA? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online:
http://excelw30dni.pl/kategoria-produktu/szkolenia-vba/

Pozdrawiam
Adam Kopeć
Miłośnik Excela

09 – Nagrywanie makra z transponowaniem danych i run time error

Tym razem będziemy nagrywać makro, którego zadaniem będzie transponowanie danych, które zostały zaimportowane w małych blokach w jednej kolumnie. Na raz chcemy transponować pojedynczy blok danych.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-01

Musimy pamiętać, żeby nasze makro było na tyle uniwersalne, żeby uwzględniało różną długość bloków danych (ilość wierszy danych w jednym bloku). Najważniejsze jednak w tym przykładzie jest zastanowienie się nad tym, gdzie nasze makro powinno się zaczynać, a gdzie kończyć, żebyśmy je mogli wykonywać cyklicznie. Czyli zaraz po skończeniu makra chcemy je uruchomić ponownie, by transponować kolejny blok.

Zwróć tu uwagę, że w niektórych makrach musisz założyć, że kod sam powinien przejść do konkretnej komórki, ale w tych najprostszych – pisanych dla siebie, możesz założyć, że użytkownik (TY ;)) zaznaczy poprawnie pierwszą komórkę. W naszej sytuacji powinien po być pierwszy numer ID, a żeby nasze makro można było uruchamiać wielokrotnie po sobie, powinniśmy skończyć na kolejnym numerze ID.

Przy tym makrze praktycznie cały czas możemy mieć włączone odwołania względne w makrach, bo gdy będziemy korzystać ze skrótów klawiszowych nie będzie to miało znaczenia, ponieważ w większości sytuacji, skrót klawiszowy uruchamia konkretny mechanizm – zapisuje się jako konkretna „funkcja” w VBA.

Zacznijmy w końcu nagrywać nasze makro. Nazwijmy je sobie Transponowanie i przypiszmy do niego skrót klawiszowy Ctrl + Shift + T, żebyśmy mogli je szybko uruchamiać.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-02

Teraz możemy przejść przez kolejne kroki naszego makra. Ponieważ rozpoczynamy od numeru ID, to żeby zaznaczyć cały blok danych wystarczy, że skorzystamy ze skrótu klawiszowego Ctrl + Shift + strzałka w dół.

Teraz wystarczy go skopiować (Ctrl + C) i przejść do komórki nad numerem ID. Ponieważ zaczynaliśmy od numeru ID, to jego komórka jest aktywną, więc możemy albo nacisnąć strzałkę w górę, albo od razu kliknąć na komórkę powyżej prawym przyciskiem myszy (przypominam, że mają być włączone odwołania względne).

Jeśli klikniemy prawym przyciskiem myszy, to od Excela 2010, z podręcznego menu możemy wybrać opcję transponowania.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-03

W Excelu 2007 najbezpieczniejszą drogą jest zaznaczenie komórki nad numerem ID, a potem rozwinięcie polecenia Wklej na karcie Narzędzia dane i wybranie z listy opcji Transpozycji.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-04

Po wklejeniu przetransponowanych danych, będziemy musieli usunąć wklejony blok danych.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-05

Ponieważ będzie teraz aktywna komórka nad ID, to wystarczy strzałka w dół lub kliknięcie na komórkę poniżej myszką (edytor VBA zarejestruje te operacje tak samo). Następnie znów skrót Ctrl + Shift + strzałka w dół, by zaznaczyć cały blok danych. Teraz będziemy chcieli go usunąć. Możesz albo skorzystać ze skrótu klawiszowego Ctrl + – (Ctrl i minus), albo kliknąć prawym przyciskiem myszy na zaznaczenie i z podręcznego menu wybrać polecenie usuń.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-06

Pojawi się okno usuwanie, gdzie chcemy się upewnić, że jest zaznaczona opcja: Przesuń komórki do góry.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-07

Po usunięciu danych będzie aktywna komórka o jeden wiersz wyżej niż chcieliśmy, dlatego musimy jeszcze nacisnąć strzałkę w dół i możemy zatrzymać rejestrowanie naszego makra.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-08

Teraz chcemy sprawdzić, czy zarejestrowane przez nas makro działa, czyli naciskamy skrót klawiszowy, który do niego przypisaliśmy Ctrl + Shift + T.

Dane powinny się odpowiednio transponować. Przytrzymaj teraz tą kombinację klawiszy, aż pokaże się komunikat o błędzie run-time error ‘1004’.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-09

Komunikat ten daje nam możliwość zakończenia makra (przycisk End), albo przejścia do linijki kodu, która spowodowała błąd (przycisk Debug).

Przejdźmy sobie do debugowania kodu. Linijka podświetlona na żółto, to ta wywołująca błąd.

wstep-do-makr-09-nagrywanie-makra-z-transponowaniem-danych-i-run-time-error-10

Po słowie PasteSpecial można się zorientować, że wklejamy tutaj dane, ale dlaczego nasze makro, które działało poprawnie, aż do tego momentu, teraz powoduje błąd?

Problemu nie rozwiążemy patrząc na kod VBA, bo tutaj się nic nie zmienia, ale jeśli spojrzysz na arkusz Excela, to zobaczyć, że do kopiowania został zaznaczony bardzo duży zakres pustych komórek. Tak zadziałała skrót klawiszowy Ctrl + Shift + strzałka w dół, kiedy zaczynasz od pustej komórki i nie ma żadnych danych pod spodem.

Czyli kod VBA chce teraz transponować ponad milion wierszy. To jest nie możliwe, bo kolumn jest znacznie mniej. Dlatego też pojawia się błąd przy uruchomieniu kodu, bo dopiero pewna kombinacja danych, czy też ich braku, powoduje zaistnienie takiej sytuacji, a w większości sytuacji ten kod zadziała poprawnie.

Ponieważ zaczynamy dopiero naukę makr, nie będziemy tutaj próbować obsługiwać tego błędu, po prostu zakończmy makro, bo przecież transponowaliśmy już wszystkie bloki danych, czyli wykonaliśmy naszą pracę 🙂
Zapamiętaj sobie w tym momencie jedno:

Najważniejsze, żeby Twój kod działał! Nie musi być idealny!

Czasem nawet może powodować błąd jeśli TY go obsługujesz, bo szkoda tracić czasu na jego dopieszczanie. Bo jak to w życiu – kolejne zadania czekają za rogiem.

W tym przykładzie moglibyśmy dopisać pętlę i obsługę błędu, żeby makro somo się wykonywało do końca danych poprawnie, ale nie jest nam to potrzebne na tym etapie znajomości makr.

To co zrobiliśmy działa i z tego powinniśmy się cieszyć :D. Dopiero z czasem, jeśli będzie to Ci potrzebne, nauczysz się dodawać odpowiednie kawałki kodu, a w następnym odcinku zobaczysz dokładny opis kodu.

Chcesz ze mną poznać możliwości VBA? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online:
http://excelw30dni.pl/kategoria-produktu/szkolenia-vba/

Pozdrawiam
Adam Kopeć
Miłośnik Excela

08 – Opcje, które warto zmienić w edytorze VBA

Jest kilka opcji, które warto, żebyś zmienił w opcjach edytora VBA. Rozwijasz menu Tools, a następnie Options. Pojawi się okno, w którym na pierwszej zakładce (Editor), moim zdaniem warto zmienić dwie opcje.
wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-01
Pierwszą jest zaznaczenie pola wyboru Require Variable Declaration. Po jej zaznaczeniu w nowo stworzonych modułach będzie dodawać się na samej górze fraza: Option Explicit. Fraza ta wymusza deklarowanie zmiennych, co oznacza, że nie będziesz mógł przypisać wartości do zmiennej która wcześniej nie została zadeklarowane. Jeśli nie zadeklarujesz zmiennej, do której przypiszesz wartość, to w trakcie kompilacji (próby uruchomienia kodu) pojawi się odpowiedni komunikat:

wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-02
Dzięki temu unikniesz problemu z ewentualnymi literówkami oraz oszczędzisz trochę pamięci przez deklarowanie zmiennych konkretnych typów.
Drugą zmianą jest odznaczenie pola wyboru Auto Syntax Check. Ta opcja automatycznie sprawdza składnie kodu i wyświetla odpowiedni komunikat podczas pisania (przykładowy komunikat poniżej).

wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-03
Ten komunikat jest całkiem zbędny, ponieważ błędnie składniowo napisany kod jest podświetlany na czerwono i to jest wystarczające wyróżnienie. Natomiast wyskakujące okienko z komunikatem, może irytować i rozpraszać w trakcie pisania kodu.

Na tej zakładce możesz ewentualnie jeszcze zmienić wielkość wcięć (Tab Width).

Domyślną kolorystykę błędów składni (Syntax Error Text) oraz słów kluczowych itp., znajdziesz na drugiej zakładce opcji edytora VBA (Editor Format).

wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-04
Tam co najwyżej warto zmienić rozmiar czcionki, a kolory zostawić domyślne, bo właśnie takie znajdziesz w tutorialach i pomocach w sieci.
Dodatkowa możliwość, która może Ci się przydać, to komentowanie (i odkomentowywanie) całych bloków kodu. Możesz to uzyskać dodać sobie pasek narzędzi Edit (Menu View -> Toolbars -> Edit).

wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-05Po dodaniu możesz go przypiąć z boku lub na górze okna edytora VBA. Tu najbardziej przydatna jest opcja komentowania bloków kodu, bo do pozostałych ważnych narzędzi przypisane są skróty klawiszowe.

Chcesz ze mną poznać możliwości VBA? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online:
http://excelw30dni.pl/kategoria-produktu/szkolenia-vba/

Pozdrawiam
Adam Kopeć
Miłośnik Excela

07 – Tłumaczenie makra formatującego komórki i jego modyfikacja

Na samym dole znajdziesz kod z komentarzami, tutaj zajmiemy się możliwością uruchamiania kodu krok po kroku, czyli linijka po linijce.
Kod krok po kroku możesz uruchomić za pomocą menu Debug i polecenia Step Into (skórt klawiszowy – klawisz F8).

Wstęp do makr 07 - - Tłumaczenie makra formatującego komórki i jego modyfikacja 01

Inny rozwiązaniem, jeśli masz trudności z odnalezieniem, gdzie został zapisany Twój kod naciśnięcie skrótu klawiszowego Alt + F8, żeby pokazała się lista makr w otwartych plikach wybranie tego, które Cię interesuje, a następnie kliknięcie przycisku Wkrocz.

Wstęp do makr 07 - - Tłumaczenie makra formatującego komórki i jego modyfikacja 02

Wtedy zaczniesz uruchamiać dany kod krok po kroku, dlatego lepiej upewnij się, że byłeś w odpowiednim miejscu arkusza przed uruchomieniem kodu. Jeśli chcesz zatrzymać makro wystarczy, że naciśniesz przycisk stop pod menu edytora VBA.

Linijka, która ma zostać uruchomiona jest przez edytor VBA podświetlana na żółto.

Wstęp do makr 07 - - Tłumaczenie makra formatującego komórki i jego modyfikacja 03

Najszybciej uruchamiasz ją naciskając klawisz F8. Czasami możesz zobaczyć, że edytor VBA podświetla kilka linijek kodu, ale wynika to z zastosowania specjalnego połączenia spacji i znaku pokreślenia (” _”), która informuje edytor, że kod ciągnie się dalej w następnej linijce.

W trakcie uruchamiania krok po kroku makra możesz przeskakiwać do Excela i obserwować zmiany jakie wprowadził kod VBA, ale pamiętaj, żeby nie zmieniać zaznaczenia i wartości, bo kolejne linijki kodu VBA mogą zadziałać nieprawidłowo.

Ważną informacją może być to, że w trakcie uruchomienia kodu krok po kroku możesz podglądać wartości zmiennych i stałych wystarczy, że najedziesz na nie myszką.

Wstęp do makr 07 - - Tłumaczenie makra formatującego komórki i jego modyfikacja 04

Chcesz ze mną poznać możliwości VBA? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online:
http://excelw30dni.pl/kategoria-produktu/szkolenia-vba/

Pozdrawiam
Adam Kopeć
Miłośnik Excela