Excel - kurs online - oferta dla każdego

Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.


Excel - kurs online. Dlaczego warto?

Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.

Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.

Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.

Excel — Jak wyciągnąć kod VBA z add-in do Excela — Lista z opcją wyszukiwania — porada #380

W dzisiejszym poście nauczymy się jak skopiować kod add-inn (kod dodatku do Excela), żeby wkleić go do konkretnego pliku Excela. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.

Rys. nr 1 – przykładowe dane
Rys. nr 1 – przykładowe dane

W poprzednim poście  (porada 379) pokazaliśmy jak zainstalować add-inn, dodatek do Excela na przykładzie Jona Acampory. Problem z dodatkami jest taki, że są instalowane na konkretnym urządzeniu (komputerze) i przy kopiowaniu pliku, przenoszeniu pliku na inne urządzenie dodatek nie będzie działał. Podsumowując na innym urządzeniu mimo, że korzystamy z tego samego pliku nie mamy dodatkowych opcji wynikających z dodatku, z którego korzystaliśmy, nie ma również dodatkowej karty XL Campus na pasku narzędzi.

Możemy jednak skopiować kod tego dodatku i podpiąć go pod konkretny plik Excela. Wykonamy to zadanie z kodem Jona Acampory ponieważ jest on darmowy i możemy sobie pozwolić na jego kopiowanie.

Załóżmy że mamy otwarty nasz przykładowy plik Excela (dane z rys. nr 1) i mamy pobrany dodatek Add-inn Jona Acampory na dysk (rys. nr 2).

Rys. nr 2 – dodatek Jona Acampora pobrany na dysk
Rys. nr 2 – dodatek Jona Acampora pobrany na dysk

Istotne jest, że jeśli wykonałeś czynności z poprzedniego postu i masz już zainstalowany dodatek add-in to nie musisz na tym etapie otwierać pliku dodatku. My dla przykładu otworzymy sobie ten plik w Excelu (nie zainstalujemy tylko otworzymy), czyli klikamy na jego nazwę prawym przyciskiem myszy (plik z rozszerzeniem .xlam) i z podręcznego menu rozwijamy polecenie Otwórz za pomocą, a następnie wybieramy program Excel (rys. nr 3).

Rys. nr 3 – Otwieranie dodatku add-in za pomocą programu Excel
Rys. nr 3 – Otwieranie dodatku add-in za pomocą programu Excel

Po uruchomieniu Excela kod Jona Acampory powinien się uruchomić, czyli powinniśmy widzieć kartę XL Campus. Jest tp uruchomienie jednorazowe, czyli jak ponownie uruchomimy Excela to już tego dodatku nie będzie (w przeciwieństwie do porady nr 379, gdzie instalowaliśmy dodatek na stałe na to urządzenie).

Istotne dla nas jest teraz wyciągnięcie kodu z tego dodatku i podpięcie do na stałe do tego pliku. Korzystając ze skrótu klawiszowego Alt+F11, przechodzimy do Edytora zaawansowanego VBA (rys. nr 4).

Rys. nr 4 – edytor zaawansowany VBA
Rys. nr 4 – edytor zaawansowany VBA

Przede wszystkim musimy sobie uruchomić edytor Projektu, a możemy to zrobić wybierając polecenie Project Explorer z karty View (rys. nr 5).

Rys. nr 5 – polecenie Project Explorer
Rys. nr 5 – polecenie Project Explorer

W edytorze projektu widzimy dodatek Add-in oraz nasz plik, który nie posiada pod sobą żadnych modułów, formularzy czy klas. Musimy skopiować elementy zaznaczone na zielono na rys. nr 6 do naszego pliku. Możemy je kopiować dzięki temu, że dodatek Jona Acampora jest darmowy.

Rys. nr 6 – elementy dodatku add-in, które musimy skopiować
Rys. nr 6 – elementy dodatku add-in, które musimy skopiować

Moduły z dodatku kopiujemy na zasadzie przeciągania, czyli przeciągamy wszystkie moduły dodatku add-in do naszego pliku. Po skopiowaniu nasz plik powinien zawierać wszystkie elementy kodu dodatku (przedstawione na rys. nr 7).

Rys. nr 7 – elementy kodu skopiowane do naszego pliku
Rys. nr 7 – elementy kodu skopiowane do naszego pliku

Kiedy już skopiujemy ten kod, musimy poznać na tyle ten dodatek, żeby wiedzieć które makro jest tym głównym, które uruchamia cały proces. W przypadku tego dodatku jest to formularz. Aby uruchomić ten formularz potrzebujemy kodu, który przygotowałem sobie wcześniej. Kod ten powinien wyglądać następująco:

Sub ListaWyszukaj ()

            F_ListSearch.Show

End sub

Zaznaczamy ten kod (rys. nr 8) i kopiujemy go za pomocą skrótu klawiszowego Ctrl+C.

Rys. nr 8 – kod dodatku Add-in (makro)
Rys. nr 8 – kod dodatku Add-in (makro)

Aby wkleić ten kod do naszego pliku klikamy prawym przyciskiem myszy na Modules (moduły w naszym pliku) i z podręcznego menu rozwijamy polecenie Insert, a następnie wybieramy polecenie Module (rys. nr 9).

Rys. nr 9 – polecenie wstawiania modułu (makra)
Rys. nr 9 – polecenie wstawiania modułu (makra)

Otworzy nam się okno, gdzie za pomocą skrótu klawiszowego Ctrl+V możemy wkleić wcześniej skopiowane makro (rys. nr 10).

Rys. nr 10 – miejsce wklejenia skopiowanego makra
Rys. nr 10 – miejsce wklejenia skopiowanego makra

Musimy pamiętać o zapisaniu pliku za pomocą skrótu klawiszowego Ctrl+S. Powyższe działania sprawiły, że mamy działające uruchamiające makro podpięte do naszego pliku. Możemy teraz w Excelu podejrzeć makra klikając polecenie Makra na karcie Deweloper (rys. nr 11). Możemy również skorzystać ze skrótu klawiszowego Alt+F8.

Rys. nr 11 – polecenie Makra na karcie Deweloper
Rys. nr 11 – polecenie Makra na karcie Deweloper

Otworzy nam się okno ze wszystkimi makrami w kodzie przedstawione na rys. nr 12. Nasze makro uruchamiające formularz nazywa się ListaWyszukaj. Musimy je znaleźć na liście wszystkich makr i zaznaczyć. Ważne jest, aby to makro przypiąć do jakiegoś przycisku lub podpiąć pod skrót klawiszowy. My podepniemy je pod skrót klawiszowy ponieważ będziemy go często używać. W tym celu zaznaczamy nasze makro i przechodzimy do jego edycji za pomocą przycisku Opcje (rys. nr 12).

Rys. nr 12 – Nazwa naszego makra na liście wszystkich makr
Rys. nr 12 – Nazwa naszego makra na liście wszystkich makr

Otworzy nam się okno Opcji makra, gdzie możemy sobie wybrać jakiś skrót uruchamiający nasze makro. W polu Klawisz skrótu wpisujemy np. literkę L i otrzymamy skrót klawiszowy uruchamiający makro w postaci Ctrl+L. Wybrany skrót klawiszowy zatwierdzamy przyciskiem OK (rys. nr 13).

Rys. nr 13 – Opcje makra
Rys. nr 13 – Opcje makra

Excel po zatwierdzeniu skrótu klawiszowego powróci do okna Makro, które zamykamy za pomocą znaku x w prawym górnym rogu okna. Teraz możemy sprawdzić działanie makra uruchamiającego nasz dodatek. Wciskamy skrót klawiszowy Ctrl+L aby uruchomić makro ListSearch (rys. nr 14).

Rys. nr 14 – makro ListSearch uruchomione za pomocą skrótu klawiszowego Ctrl+L
Rys. nr 14 – makro ListSearch uruchomione za pomocą skrótu klawiszowego Ctrl+L

W polu w tym oknie możemy zacząć wpisywać nazwę artykułu i funkcja ta ograniczy liste wyszukiwania do słów zawierających dane znaki (rys. nr 15).

Rys. nr 15 – lista rozwijana z wyszukiwaniem
Rys. nr 15 – lista rozwijana z wyszukiwaniem

Kod Jona jest dobrze dopracowany, mianowicie jeśli wpiszemy jakąś wartość i nie wybierzemy czegoś z listy a będziemy chcieli zatwierdzić wpisane znaki przyciskiem Enter, to wyskoczy nam komunikat, że tej wartości nie możemy wpisać bo nie ma jej na liście (rys. nr 16).

Rys. nr 16 – komunikat, że wpisanej wartości nie ma na liście
Rys. nr 16 – komunikat, że wpisanej wartości nie ma na liście

Musimy mieć pewność, że wpisaliśmy pełną nazwę z listy, ponieważ w przeciwnym razie dodatek add-in nie pozwoli nam jej zatwierdzić Enterem.

Analogicznie dodatek zadziała dla listy elementów – kolumny z danymi (bez listy rozwijanej). Uruchamiamy ponownie kod dodatku za pomocą ustawionego przez nas skrótu klawiszowego Ctrl+L. Pojawi nam się okienko ListSearch, które automatycznie wykryje wszystkie elementy z listy w kolumnie (rys. nr 17).

Rys. nr 17 – ListSearch wykrywające elementy z listy w kolumnie (nie listy rozwijanej)
Rys. nr 17 – ListSearch wykrywające elementy z listy w kolumnie (nie listy rozwijanej)

Wszystko działa poprawnie, mamy podpięty kod pod nasz plik i mamy pewność, że po uruchomieniu na innym komputerze zadziała on poprawnie.

Potrzebna nam jest jeszcze drobna modyfikacja tego kodu ponieważ została ona napisana pod język angielski, gdzie znakiem (ogranicznikiem) rozdzielającym elementy jest przecinek (,) a nie jak w polskiej wersji średnik (;). Zaznaczamy dowolną pojedynczą komórkę, a następnie wybieramy polecenie Poprawność danych z karty Dane (rys. nr 18).

Rys. nr 18 – polecenie Poprawność danych
Rys. nr 18 – polecenie Poprawność danych

Otworzy nam się okno Sprawdzania poprawności danych, gdzie w karcie Ustawienia, w polu Dozwolone (Kryteria poprawności) wybieramy z listy rozwijanej opcję Lista, a następnie w polu Źródło dodajemy przykładowe elementy oddzielone odpowiednim znakiem (średnikiem) jak widać na rys. nr 19.

Rys. nr 19 – okno Sprawdzania poprawności danych
Rys. nr 19 – okno Sprawdzania poprawności danych

Otrzymamy prostą, poprawnie działającą listę przedstawioną na rys. nr 20.

Rys. nr 20 – lista rozwijana stworzona poprzez sprawdzanie poprawności danych
Rys. nr 20 – lista rozwijana stworzona poprzez sprawdzanie poprawności danych

Teraz jeśli użyjemy skrótu klawiszowego Ctrl+L, aby uruchomić nasz kod i spróbujemy w okienku ListSearch rozwinąć tą listę otrzymamy tylko jeden element składający się ze wszystkich elementów naszej listy (rys. nr 21).

Rys. nr 21 – elementy z listy rozpoznane przez dodatek jako jeden element
Rys. nr 21 – elementy z listy rozpoznane przez dodatek jako jeden element

Podsumowując kod naszego dodatku nie rozpoznał tej listy, ponieważ jej elementy zostały rozdzielone średnikami, a nie jak w angielskiej wersji przecinkami. Kod ten ma zapisane w pamięci, że znakiem rozdzielającym elementy z listy jest przecinek. Naszym zadaniem jest skorygowanie tego tak, aby kod poprawnie rozpoznawał elementy listy.

Za pomocą skrótu klawiszowego Alt+F11 przechodzimy do kodu w edytorze VBA. Musimy znaleźć w elementach naszego pliku formularz (Forms), żeby wyświetliło się nam odpowiednie okno, w którym wprowadzimy zmiany (rys. nr 22).

Rys. nr 22 – zmiany w kodzie dodatku (formularz)
Rys. nr 22 – zmiany w kodzie dodatku (formularz)

W okienku List Search – ExcelCampus.com musimy kliknąć prawym przyciskiem myszy na szare tło, a następnie wybrać z podręcznego menu polecenie View Code (rys. nr 23).

Rys. nr 23 – polecenie View code
Rys. nr 23 – polecenie View code

Otworzy nam się okno z kodem, który zmodyfikujemy (rys. nr 24).

Rys. nr 24 – okno z kodem
Rys. nr 24 – okno z kodem

Aby wyszukać interesujący nas fragment możemy użyć skrótu klawiszowego Ctrl+F, otworzy nam się okienko Find, gdzie w polu Find What w znakach cudzysłowu wpisujemy szukany znak (przecinek). Wpisany znak zatwierdzamy przyciskiem Find Next (rys. nr 25).

Rys. nr 25 – szukanie w kodzie znaku przecinka
Rys. nr 25 – szukanie w kodzie znaku przecinka

Wyskoczy nam komunikat, że został znaleziony taki element. Zatwierdzamy ten komunikat przyciskiem OK i zamykamy okienko Find. W oknie z kodem mamy podświetlony szukany element kodu co widać na rys. nr 26.

Rys. nr 26 – podświetlony szukany fragment kodu
Rys. nr 26 – podświetlony szukany fragment kodu

Jest to linijka kodu, która wyznacza znak rozdzielający elementy listy. Pamiętamy, że w polskiej wersji oprogramowania podziała elementów odbywa się za pomocą znaku średnika. Udało mi się znaleźć fragment kodu, który należy wstawić w miejsce linijki ze znakiem przecinka. Kopiujemy ten fragment z Notepada ++ za pomocą skrótu klawiszowego Ctrl+C (rys. nr 27).

Rys. nr 27 – fragment kodu, który wkleimy do VBA
Rys. nr 27 – fragment kodu, który wkleimy do VBA

Teraz w VBA wstawiamy fragment kodu ze znakiem przecinka w pojedynczy cudzysłów (robimy z niego komentarz), a następnie wklejamy skopiowany wcześniej kod za pomocą skrótu klawiszowego Ctrl+V. Po drobnych estetycznych poprawkach otrzymamy kod przedstawiony na rys. nr 28. Zostawiam dużo komentarzy, żeby potencjalny użytkownik zrozumiał co tutaj zmieniliśmy.

Rys. nr 28 – Zmieniony kod (teraz średnik będzie znakiem rozdzielającym elementy listy)
Rys. nr 28 – Zmieniony kod (teraz średnik będzie znakiem rozdzielającym elementy listy)

W miejsce przecinka wpisujemy zmienną, która odczyta z wersji oprogramowania Windows odczyta, jaki jest poprawny znak rozdzielający elementy listy. Musimy pamiętać o zapisaniu zmian w kodzie za pomocą skrótu klawiszowego Ctrl+S i wracamy do Excela. Teraz kiedy za pomocą skrótu klawiszowego Ctrl+L wywołamy nasz dodatek ListSearch, otrzymamy trzy elementy listy (rys. nr 29).

Rys. nr 29 – poprawnie rozpoznane elementy listy
Rys. nr 29 – poprawnie rozpoznane elementy listy

Naprawiliśmy tą niedogodność, która nie zawsze występuje, ponieważ jeśli lista rozwijana korzysta ze źródła, którym był zakres komórek, problem ten się nie pojawi. Najczęściej pojawia się taki problem kiedy sami wpisujemy elementy takich list.

Aby sprawdzić czy wszystko działa poprawnie, zamykamy wszystkie okna, arkusze i pliki, a przede wszystkim Excela. Następnie ponownie uruchamiamy nasz plik i korzystając ze skrótu klawiszowego Ctrl+L uruchamiamy nasz dodatek. Podsumowując dzięki naszym modyfikacjom kod działa, ponieważ jest podpięty do pliku. Korzystając ze skrótu klawiszowego Ctrl+F11, uruchamiamy ponownie edytor VBA, żeby pokazać, że mamy uruchomiony tylko nasz plik, nie został uruchomiony plik kodu z rozszerzeniem .xlam, co widać na rys. nr 30 

Rys. nr 30 – edytor VBA
Rys. nr 30 – edytor VBA

Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Jak cofnąć makro — odcinek #20

Ogólnie jeśli chcesz Twoje makro ruszy arkusz, czy też dane w komórkach, to również wyczyści bufor cofania i nie będziesz mógł cofnąć, ani czynności, które wykonało makro, ani czynności, które wcześniej wykonywałeś — Ctrl + Z nie zadziała.

Jeśli Twoje makro nie rusza arkusza, np: wyświetla komunikat za pomocą funkcji Msgbox, to bufor cofania nie jest czyszczony i dalej możesz cofnąć swoje wcześniejsze czynności za pomocą skrótu klawiszowe.

Niektórzy chcieliby móc cofać swoje makra, dlatego został stworzony kod, który wykorzystuje pewną sztuczkę, a mianowicie w swoim makrze na samym jego początku dodajesz linijkę kodu, która zapisuje Twój plik:
ActiveWorkbook.Save

Następnie jeśli Twoje makro zadziałało niepoprawnie uruchamiasz makro, które powinieneś przechowywać w innym pliku np: skoroszycie makr osobistych, które pobiera nazwę Twojego pliku (aktywnego pliku), zamyka go bez zapisywania, a następnie ponownie uruchamia:

Sub Cofanie()
Dim AktualnyArkusz As String
'Zapisujemy nazwę aktywnego skoroszytu/pliku
AktualnyArkusz = ActiveWorkbook.FullName
'zamykamy go bez zapisywania zmian
ActiveWorkbook.Close SaveChanges:=False
'ponownie go otwieramy
Workbooks.Open Filename:=AktualnyArkusz
End Sub

Czyli symuluje to co musiałbyś zrobić ręcznie, żeby odzyskać swoje dane 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak dodać makro do paska narzędzi szybkiego dostępu — widzowie #114

Jeśli chcesz dodać makro jako przycisk w pasku narzędzi szybkiego dostępu to wystarczy, żebyś kliknął prawym przyciskiem myszy i z podręcznego menu musisz wybrać polecenie Dostosuj pasek narzędzi Szybki dostęp. 

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 01

Następnie w oknie, które się pojawi potrzebujesz wybrać u góry z listy rozwijanej pozycję makra.

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 02
Zobaczysz wtedy wszystkie makra z otwartych plików (przykładowo na rysunku poniżej są to makra ze skoroszytu makr osobisty – PERSONAL.XLSB). Wystarczy, że naciśniesz przycisk Dodaj, żeby dodać je do poleceń paska narzędzi szybkiego dostępu.

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 03

Ponieważ wszystkie makra mają domyślnie takie same ikony, to warto zmodyfikować ją dla makra, z którego korzystamy, żebyśmy szybko je rozpoznawali – wystarczy, że zaznaczysz polecenie/makro, a następnie klikniesz przycisk Modyfikuj. Pokaże się wtedy okno, gdzie możesz wybrać ikonę dla polecenia i ewentualnie zmienić nazwę dla niego.

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 04

Wystarczy pozatwierdzać wybór ikony i dodane makro przyciskami OK i już na pasku narzędzi zobaczysz wybrane makro (na obrazie poniżej ikona klepsydry).

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 05

Analogicznie możesz dodać makro do kart wstążki, ale dopiero od Excela 2010, bo dopiero ta wersja udostępnia opcję modyfikacji wstążki.

Link do strony z makrami wykorzystanymi w filmie:
https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx

Pozdrawiam
Adam Kopeć
Miłośnik Excela

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.

Sub Raport()
'Zaznaczamy komórkę A1 - pamiętaj mieliśmy tutaj wyłączone odwołania względne
Range("A1").Select
'Przechodzimy do ostatniej wypełnionej komórki w prawo (xlToRight)
'czyli strót klawiszowy Ctrl + stzrałka w prawo
'Nie ma znaczenia, czy miałeś włączone odwołania względne czy nie
'kod zapisze się identycznie.
Selection.End(xlToRight).Select
'Przechodzimy na odwołania względne przy rejestracji makr
'i przesuwamy się jedną kolumnę w prawo
ActiveCell.Offset(0, 1).Range("A1").Select
'W aktywną komórkę wpisujemy Zysk i zatwierdzamy Enterem
Selection.FormulaR1C1 = "Zysk"
'dzięki temu od razu przesuwamy się też jedną komórkę w dół
ActiveCell.Offset(1, 0).Range("A1").Select
'W aktywną komórkę wpisujemy formułę, która odejmuje od Zysku Koszt,
'czyli od komórki o dwie kolumny w lewo (RC[-2]) odejmujemy
'wartość z komórki o jedną kolumnę w lewo (RC[-1])
'Formułę zatwierdzamy kombinacją klawiszy Ctrl + Enter
'dzięki czemu nie przesuwamy się po zatwierdzeniu formuł
Selection.FormulaR1C1 = "=RC[-2]-RC[-1]"
'Kopiujemy zaznaczenie (komórkę z formułą)
Selection.Copy
'Tutaj musimy trochę pokombinować, żeby wkleić skopiowaną formułę
'do końca danych w dół.
'Najpierw przechodzimy jedną komórkę w prawo
ActiveCell.Offset(1, -1).Range("A1").Select
'Później idziemy na koniec danych.
'Do ostatniej wypełnionej komórki skrót: Ctrl + strzałka w dół
Selection.End(xlDown).Select
'Przechodzimy komórkę w prawo
ActiveCell.Offset(0, 1).Range("A1").Select
'Ponieważ jesteśmy w pustej komórce na końcu danych
'zaznaczamy wszystkie komórki w górę do pierwszej wypełnionej komórki
'skrót: Ctrl + Shift + strzałka w górę
Range(Selection, Selection.End(xlUp)).Select
'Wklejamy skopiowaną formułę we wszystkie zaznaczone komórki
ActiveSheet.Paste
'Wyłącza się tryb kopiowania przy okazji kolejnej czynności
Application.CutCopyMode = False
'Zmieniamy formatowanie zaznacznie na walutowe
'Zapis angielski jest tłumaczony na polskie złotówki
'przez ustawienia regionalne
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
'Zaznaczamy komórkę A1 przy włączonych odwołaniach względnych
'za pomocą skrótu klawiszowego Ctrl + Home
Range("A1").Select
'Zaznaczamy wszystkie nagłówki skrót: Ctrl + Shift + strzałka w prawo
Range(Selection, Selection.End(xlToRight)).Select
'Pogrubiamy nagłówki skrót: Ctrl + B
Selection.Font.Bold = True
'Przesuwamy się jedną komórkę w dół od aktywnej komórki (powinna być to komórka A1)
ActiveCell.Offset(1, 0).Range("A1").Select
'Zaznaczamy wszystkie dane w dół skrót: Ctrl + Shift + strzałka w dół
Range(Selection, Selection.End(xlDown)).Select
'Ustawiamy formatowanie daty, które w kodzie VBA ma zapis angielski
'ale ustawienia regionalne tłumaczą na standardowy polski zapis daty
Selection.NumberFormat = "m/d/yyyy"
'Na podstawie aktualnego zaznaczenia zaznaczamy aktualny zakres danych (CurrentRegion)
Selection.CurrentRegion.Select
'Reszta to już obramowania komórek na zaznaczomy zakresie.
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub

Chcesz ze mną poznać możliwości VBA? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online:
https://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:
https://excelw30dni.pl/kategoria-produktu/szkolenia-vba/

Pozdrawiam
Adam Kopeć
Miłośnik Excela