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

Jak zamienić kropkę na przecinek w VBA – odcinek #16

Jeśli chcesz zamienić kropkę na przecinek za pomocą VBA natkniesz się na pewien problem w polskojęzycznym Excelu.
Często powtarzam, że jeśli nie wiesz jak coś zrobić/napisać w VBA, ale wiesz jak to zrobić w Excelu, to zarejestruj sobie makro, tego co robisz w Excelu, a później analizuj kod. Sprawdza się to w większości sytuacji. Tak też zrobimy teraz, czyli najpierw zarejestrujemy makro, gdzie w Excelu za pomocą opcji Zamień zmienimy kropkę na przecinek, a dopiero później sprawdzimy jak działa zarejestrowany kod VBA.
Do opcji Zamień najszybciej dojdziesz naciskając skrót klawiszowy Ctrl + H, potem wypełniasz odpowiednio pola (Znajdź – kropka, Zamień na – przecinek) i klikasz przycisk Zamień wszystko.

vba-16-jak-zamienic-kropke-na-przecinek-01

Teraz trzeba sprawdzić czy uruchomienie zarejestrowanego kodu zadziała.

vba-16-jak-zamienic-kropke-na-przecinek-02

Okazuje się, że nie do końca, bo w niektórych przypadkach może w ogóle nie dokonać zmiany, a w innych znaki się zmienią, ale liczby nie zostaną poprawnie rozpoznane przez Excela (są wyrównane do lewej).

Musisz pamiętać, że edytor VBA jest angielskojęzyczny, co się wiąże również z tym, że część całkowita jest oddzielona od części dziesiętnej liczby kropką, a nie przecinkiem jak w polskim Excelu. Dlatego w kodzie VBA musisz zrobić dziwną rzecz – zamienić kropkę na kropkę. Resztę zrobią („przetłumaczą”) ustawienia regionalne.

Tym razem zmiana dokonała się poprawnie.

vba-16-jak-zamienic-kropke-na-przecinek-03

Dlatego pamiętaj o tym, że edytor VBA zawsze „mówi” po angielsku, a Excel różnie w zależności od wersji językowej. Z tych różnic wynikają podobne różne dziwne kody.

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

Pokaż tylko te liczby ID zaczynające się na konkretną cyfrę – porada #279

Jeśli zależy Ci, żeby wyciągnąć wszystkie liczby (numery ID) rozpoczynające się od konkretnej cyfry, to jeśli Twoje ID Excel traktuje jako tekst, to wystarczy, że z menu filtrowanie wybierzesz zaczyna się od

porada-279-pokaz-tylko-liczby-zaczynajace-sie-na-cyfre-2-01

A następnie w oknie autofiltrów niestandardowych, które się otworzy wpiszesz tą cyfrę i zatwierdzisz wybór przyciskiem OK.

porada-279-pokaz-tylko-liczby-zaczynajace-sie-na-cyfre-2-02

Potem już będą widoczne tylko numery ID rozpoczynające się na wybraną przez Ciebie cyfrę.

porada-279-pokaz-tylko-liczby-zaczynajace-sie-na-cyfre-2-03

Natomiast jeśli Twój numer ID jest zapisany jako liczba, to powyższa ścieżka nie zadziała. Na szczęście jest jeszcze inna, nawet chyba prostsza 😀 Wystarczy, że w polu wyszukiwania filtra wpiszesz wybraną cyfrę i znak wieloznaczny gwiazdki.

porada-279-pokaz-tylko-liczby-zaczynajace-sie-na-cyfre-2-04

Wtedy wpisałbyś samą liczbę, to pokazałyby się wszystkie numery ID, gdzie ta cyfra występuje, czy to na początku, czy w środku, czy na końcu.

porada-279-pokaz-tylko-liczby-zaczynajace-sie-na-cyfre-2-05

Wystarczy, że zatwierdzisz powyższe przeszukiwanie filtra i ponownie będą widoczne tylko numery ID rozpoczynające się od wybranej przez Ciebie cyfry.

Chcesz ze mną poznać możliwości Excela? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online: http://excelw30dni.pl/produkt/szkolenie-excel/
Możesz się też podzielić tym postem z innymi, może przydać im się w pracy 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela