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
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.
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:
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.
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ć.
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.
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.
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.
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.
Teraz trzeba sprawdzić czy uruchomienie zarejestrowanego kodu zadziała.
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.
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.
Sub KropkaPrzecinek()
Selection.Replace What:=".", Replacement:=".", LookAt:=xlPart, _
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.
Sub Transponowanie()
'Zapis skorzystania ze skrótu klawiszowego Ctrl + Shift + strzałka
'wykorzystuje funkcję Range, która odwołuje się do zakresu korzystając z dwóch komórek
'z lewego górnego rogu i prawego dolnego rogu
'górny róg w tym przykładzie to zaznaczony zakres, natomiast dolny róg,
'to przejście na koniec (End) – do ostatniej wypełnionej komórki, w danym kierunku (xlDown)
Range(Selection, Selection.End(xlDown)).Select
'Zwykłe włączenie kopiowania zaznaczonego obszaru
Selection.Copy
'Przejście od oktywnej komórki do komórki o 1 wiersz w górę
'Funkcja Range("A1") w poniższym zapisie odnosi się tylko do rozmiaru zaznaczanego zakresu
'po dokonaniu przesunięcia (Offset)
ActiveCell.Offset(-1, 0).Range("A1").Select
'wklejanie skopiowanego obszaru, najważniejsze w poniższym kodzie jest włączona
'opcja transponowania (Tranpose:=True)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
'Przesunięcie o 1 komórkę w dół (1 wiersz w dół) od aktywnej komórki (ActiveCell)
ActiveCell.Offset(1, 0).Range("A1").Select
'Ponowne skorzystanie ze skrótu Ctrl + Shift + strzałka w dół
Range(Selection, Selection.End(xlDown)).Select
'Dodatkowy kawałek kodu, który wyłącza tryb kopiowania
'przestaje być aktywny zakres do kopiowania
Application.CutCopyMode = False
'Usuwamy zaznaczone komórki z przesunięciem pozostałych danych (Shift) w górę (xlUp)
Selection.Delete Shift:=xlUp
'Zaznaczamy komórkę o 1 wiersz poniżej aktywnej komórki
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
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
A następnie w oknie autofiltrów niestandardowych, które się otworzy wpiszesz tą cyfrę i zatwierdzisz wybór przyciskiem OK.
Potem już będą widoczne tylko numery ID rozpoczynające się na wybraną przez Ciebie cyfrę.
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.
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.
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: https://excelw30dni.pl/produkt/szkolenie-excel/
Możesz się też podzielić tym postem z innymi, może przydać im się w pracy 😉
