Korzystanie z makr Excela może przyspieszyć pracę i zaoszczędzić sporo czasu.
Jednym ze sposobów uzyskania kodu VBA jest zarejestrowanie makra i pobranie wygenerowanego przez niego kodu. Jednak ten kod rejestratora makr jest często pełen kodu, który nie jest tak naprawdę potrzebny. Również rejestrator makr ma pewne ograniczenia.
Dlatego opłaca się mieć zbiór przydatnych kodów makr VBA, które można mieć w tylnej kieszeni i używać ich w razie potrzeby.
Chociaż pisanie kodu makra Excel VBA może początkowo zająć trochę czasu, po jego zakończeniu możesz zachować go jako odniesienie i używać go, gdy będziesz go potrzebować w następnej kolejności.
W tym obszernym artykule zamierzam wymienić kilka przydatnych przykładów makr Excela, których często potrzebuję i które przechowuję w moim prywatnym skarbcu.
Będę aktualizował ten samouczek o więcej przykładów makr. Jeśli uważasz, że coś powinno znaleźć się na liście, po prostu zostaw komentarz.
Możesz dodać tę stronę do zakładek, aby móc z niej skorzystać w przyszłości.
Teraz, zanim przejdę do przykładu makra i podam kod VBA, pozwól mi najpierw pokazać, jak korzystać z tych przykładowych kodów.
Korzystanie z kodu z przykładów makr programu Excel
Oto kroki, które należy wykonać, aby użyć kodu z dowolnego z przykładów:
- Otwórz skoroszyt, w którym chcesz użyć makra.
- Przytrzymaj klawisz ALT i naciśnij F11. Spowoduje to otwarcie edytora VB.
- Kliknij prawym przyciskiem myszy dowolny obiekt w eksploratorze projektu.
- Przejdź do Wstaw -> Moduł.
- Skopiuj i wklej kod w oknie kodu modułu.
W przypadku, gdy przykład mówi, że musisz wkleić kod w oknie kodu arkusza, kliknij dwukrotnie obiekt arkusza i skopiuj i wklej kod w oknie kodu.
Po wstawieniu kodu do skoroszytu należy go zapisać z rozszerzeniem .XLSM lub .XLS.
Jak uruchomić makro
Po skopiowaniu kodu w edytorze VB, oto kroki, aby uruchomić makro:
- Przejdź do zakładki Deweloper.
- Kliknij Makra.
- W oknie dialogowym Makro wybierz makro, które chcesz uruchomić.
- Kliknij przycisk Uruchom.
Jeśli nie możesz znaleźć karty dewelopera na wstążce, przeczytaj ten samouczek, aby dowiedzieć się, jak ją zdobyć.
Powiązany samouczek: Różne sposoby uruchamiania makra w programie Excel.
Jeśli kod zostanie wklejony w oknie kodu arkusza roboczego, nie musisz się martwić o uruchomienie kodu. Uruchomi się automatycznie, gdy nastąpi określona akcja.
Przejdźmy teraz do przydatnych przykładów makr, które pomogą zautomatyzować pracę i zaoszczędzić czas.
Uwaga: znajdziesz wiele wystąpień apostrofu (‘), po którym następuje jedna lub dwie linie. Są to komentarze, które są ignorowane podczas uruchamiania kodu i są umieszczane jako uwagi dla siebie/czytelnika.
Jeśli znajdziesz jakiś błąd w artykule lub kodzie, bądź super i daj mi znać.
Przykłady makr Excel
Poniższe przykłady makr zostały omówione w tym artykule:
Odkryj wszystkie arkusze za jednym zamachem
Jeśli pracujesz w skoroszycie zawierającym wiele ukrytych arkuszy, musisz odkrywać te arkusze jeden po drugim. Może to zająć trochę czasu, jeśli jest wiele ukrytych arkuszy.
Oto kod, który odkryje wszystkie arkusze w skoroszycie.
'Ten kod odkryje wszystkie arkusze w skoroszycie Sub UnhideAllWoksheets() Dim ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Powyższy kod używa pętli VBA (For Each) do przechodzenia przez każdy arkusz w skoroszycie. Następnie zmienia widzialną właściwość arkusza na widoczny.
Oto szczegółowy samouczek dotyczący korzystania z różnych metod odkrywania arkuszy w programie Excel.
Ukryj wszystkie arkusze oprócz aktywnego arkusza
Jeśli pracujesz nad raportem lub pulpitem nawigacyjnym i chcesz ukryć cały arkusz oprócz tego, który zawiera raport/pulpit nawigacyjny, możesz użyć tego kodu makra.
'To makro ukryje cały arkusz z wyjątkiem aktywnego arkusza Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub
Sortuj arkusze alfabetycznie za pomocą VBA
Jeśli masz skoroszyt z wieloma arkuszami i chcesz je posortować alfabetycznie, ten kod makra może się bardzo przydać. Może tak być, jeśli masz nazwy arkuszy jako lata lub nazwiska pracowników lub nazwy produktów.
'Ten kod sortuje arkusze alfabetycznie. Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Arkusze.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Przenieś przed:=Arkusze(i) End If Next j Next i Application.ScreenUpdating = True End Sub
Chroń wszystkie arkusze za jednym razem
Jeśli masz dużo arkuszy w skoroszycie i chcesz chronić wszystkie arkusze, możesz użyć tego kodu makra.
Pozwala określić hasło w kodzie. To hasło będzie potrzebne do usunięcia ochrony arkusza roboczego.
'Ten kod ochroni wszystkie arkusze za jednym razem. Sub ProtectAllSheets() Dim ws As Worksheet Dim hasło As String password = "Test123" 'zastąp Test123 hasłem, które chcesz Napis końcowy
Odblokuj wszystkie arkusze robocze za jednym razem
Jeśli masz chronione niektóre lub wszystkie arkusze, możesz po prostu użyć niewielkiej modyfikacji kodu używanego do ochrony arkuszy, aby je usunąć.
'Ten kod będzie chronił wszystkie arkusze za jednym razem. Sub ProtectAllSheets() Dim ws As Worksheet Dim hasło As String password = "Test123" 'zastąp Test123 hasłem, które chcesz Napis końcowy
Zwróć uwagę, że hasło musi być takie samo, które zostało użyte do zablokowania arkuszy. Jeśli tak nie jest, zobaczysz błąd.
Odkryj wszystkie wiersze i kolumny
Ten kod makra odkryje wszystkie ukryte wiersze i kolumny.
Może to być bardzo pomocne, jeśli otrzymasz plik od kogoś innego i chcesz mieć pewność, że nie ma ukrytych wierszy/kolumn.
'Ten kod odkryje wszystkie wiersze i kolumny w arkuszu roboczym UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Rozłącz wszystkie scalone komórki
Powszechną praktyką jest łączenie komórek w jedną. Chociaż to działa, po połączeniu komórek nie będzie można sortować danych.
Jeśli pracujesz z arkuszem roboczym ze scalonymi komórkami, użyj poniższego kodu, aby rozłączyć wszystkie scalone komórki za jednym razem.
'Ten kod rozłączy wszystkie scalone komórki Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub
Zwróć uwagę, że zamiast Scal i wyśrodkuj, zalecam użycie opcji Wyśrodkuj w całym zaznaczeniu.
Zapisz skoroszyt ze znacznikiem czasu w jego nazwie
Dużo czasu może być konieczne utworzenie wersji swojej pracy. Są one bardzo przydatne w długich projektach, w których pracujesz z plikiem w czasie.
Dobrą praktyką jest zapisanie pliku ze znacznikami czasu.
Korzystanie ze znaczników czasu pozwoli Ci wrócić do określonego pliku, aby zobaczyć, jakie zmiany zostały wprowadzone lub jakie dane zostały użyte.
Oto kod, który automatycznie zapisze skoroszyt w określonym folderze i doda znacznik czasu za każdym razem, gdy zostanie zapisany.
'Ten kod zapisze plik ze znacznikiem czasu w jego nazwie Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-rrrr") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" i znacznik czasu End Sub
Musisz określić lokalizację folderu i nazwę pliku.
W powyższym kodzie „C: UsersUsernameDesktop to lokalizacja folderu, z której korzystałem. Musisz określić lokalizację folderu, w którym chcesz zapisać plik. Użyłem również ogólnej nazwy „WorkbookName” jako prefiksu nazwy pliku. Możesz określić coś związanego z Twoim projektem lub firmą.
Zapisz każdy arkusz jako oddzielny plik PDF
Jeśli pracujesz z danymi z różnych lat, działów lub produktów, może być konieczne zapisanie różnych arkuszy jako plików PDF.
Chociaż może to być czasochłonny proces, jeśli zostanie wykonany ręcznie, VBA może go naprawdę przyspieszyć.
Oto kod VBA, który zapisze każdy arkusz jako osobny plik PDF.
'Ten kod zapisze każdy arkusz jako oddzielny plik PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet dla każdego ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
W powyższym kodzie podałem adres lokalizacji folderu, w którym chcę zapisać pliki PDF. Ponadto każdy plik PDF otrzyma taką samą nazwę jak arkusz roboczy. Będziesz musiał zmodyfikować tę lokalizację folderu (chyba że masz na imię Sumit i zapisujesz go w folderze testowym na pulpicie).
Zauważ, że ten kod działa tylko dla arkuszy roboczych (a nie arkuszy wykresów).
Zapisz każdy arkusz jako oddzielny plik PDF
Oto kod, który zapisze cały skoroszyt jako plik PDF w określonym folderze.
'Ten kod zapisze cały skoroszyt jako PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Będziesz musiał zmienić lokalizację folderu, aby użyć tego kodu.
Konwertuj wszystkie formuły na wartości
Użyj tego kodu, jeśli masz arkusz zawierający wiele formuł i chcesz przekonwertować te formuły na wartości.
'Ten kod przekonwertuje wszystkie formuły na wartości Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub
Ten kod automatycznie identyfikuje używane komórki i konwertuje je na wartości.
Chroń/blokuj komórki za pomocą formuł
Możesz zablokować komórki za pomocą formuł, gdy masz dużo obliczeń i nie chcesz ich przypadkowo usunąć ani zmienić.
Oto kod, który zablokuje wszystkie komórki zawierające formuły, podczas gdy wszystkie inne komórki nie są zablokowane.
'Ten kod makra zablokuje wszystkie komórki za pomocą formuł Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub
Powiązany samouczek: Jak zablokować komórki w programie Excel.
Chroń wszystkie arkusze w skoroszycie
Użyj poniższego kodu, aby chronić wszystkie arkusze w skoroszycie za jednym razem.
'Ten kod chroni wszystkie arkusze w skoroszycie Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Next ws End Sub
Ten kod przejdzie przez wszystkie arkusze jeden po drugim i zabezpieczy go.
Jeśli chcesz wyłączyć ochronę wszystkich arkuszy, użyj ws.Unprotect zamiast ws.Protect w kodzie.
Wstaw wiersz po każdym innym wierszu w zaznaczeniu
Użyj tego kodu, jeśli chcesz wstawić pusty wiersz po każdym wierszu w wybranym zakresie.
'Ten kod wstawi wiersz po każdym wierszu w zaznaczeniu Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Wybór CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Wstaw ActiveCell.Offset(2, 0). Wybierz Next i End Sub
Podobnie możesz zmodyfikować ten kod, aby wstawić pustą kolumnę po każdej kolumnie w wybranym zakresie.
Automatycznie wstawiaj datę i znacznik czasu w sąsiedniej komórce
Sygnatura czasowa to coś, czego używasz, gdy chcesz śledzić działania.
Na przykład możesz chcieć śledzić działania, takie jak kiedy poniesiono konkretny wydatek, o której godzinie utworzono fakturę sprzedaży, kiedy dokonano wpisu danych w komórce, kiedy ostatnia aktualizacja raportu itp.
Użyj tego kodu, aby wstawić sygnaturę daty i godziny w sąsiedniej komórce, gdy dokonywany jest wpis lub edytowana jest istniejąca zawartość.
'Ten kod wstawi znacznik czasu w sąsiedniej komórce Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value "" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-rrrr gg:mm:ss") Application.EnableEvents = True End If Handler: End Sub
Zauważ, że musisz wstawić ten kod w oknie kodu arkusza roboczego (a nie w oknie kodu modułu, jak to zrobiliśmy w innych przykładach makr Excela do tej pory). Aby to zrobić, w Edytorze VB kliknij dwukrotnie nazwę arkusza, na którym chcesz uzyskać tę funkcjonalność. Następnie skopiuj i wklej ten kod w oknie kodu tego arkusza.
Ponadto ten kod ma działać, gdy wprowadzanie danych odbywa się w kolumnie A (należy zauważyć, że kod ma wiersz Target.Column = 1). Możesz to odpowiednio zmienić.
Wyróżnij alternatywne wiersze w zaznaczeniu
Wyróżnianie alternatywnych wierszy może znacznie zwiększyć czytelność danych. Może to być przydatne, gdy trzeba wykonać wydruk i przejrzeć dane.
Oto kod, który natychmiast podświetli alternatywne wiersze w zaznaczeniu.
'Ten kod podświetli alternatywne wiersze w zaznaczeniu Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Zaznaczenie dla każdego Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Jeśli następny Myrow Koniec Sub
Zauważ, że w kodzie określiłem kolor jako vbCyan. Możesz również określić inne kolory (takie jak vbRed, vbGreen, vbBlue).
Zaznacz komórki z błędnie napisanymi słowami
Program Excel nie ma funkcji sprawdzania pisowni, jak ma to miejsce w programach Word lub PowerPoint. Chociaż możesz uruchomić sprawdzanie pisowni, naciskając klawisz F7, nie ma wizualnej wskazówki, gdy wystąpi błąd w pisowni.
Użyj tego kodu, aby natychmiast podświetlić wszystkie komórki, w których występuje błąd ortograficzny.
'Ten kod podświetli komórki, które zawierają błędnie napisane słowa Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl Koniec Sub
Zwróć uwagę, że podświetlone komórki to te, które zawierają tekst, który program Excel uznaje za błąd pisowni. W wielu przypadkach podkreślałby również nazwy lub terminy związane z marką, których nie rozumie.
Odśwież wszystkie tabele przestawne w skoroszycie
Jeśli masz więcej niż jedną tabelę przestawną w skoroszycie, możesz użyć tego kodu, aby odświeżyć wszystkie te tabele przestawne jednocześnie.
'Ten kod odświeży całą tabelę przestawną w skoroszycie Sub RefreshAllPivotTables() Dim PT As PivotTable dla każdego PT w ActiveSheet.PivotTables PT.RefreshTable Następny PT End Sub
Więcej o odświeżaniu tabel przestawnych przeczytasz tutaj.
Zmień wielkość liter w wybranych komórkach na wielkie litery
Chociaż program Excel ma formuły do zmiany wielkości liter w tekście, sprawia, że robisz to w innym zestawie komórek.
Użyj tego kodu, aby natychmiast zmienić wielkość liter w wybranym tekście.
'Ten kod zmieni zaznaczenie na wielkie litery Sub ChangeCase() Dim Rng As Range dla każdego Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub
Zwróć uwagę, że w tym przypadku użyłem UCase, aby ustawić górną wielkość liter. Możesz użyć LCase dla małych liter.
Zaznacz wszystkie komórki komentarzami
Użyj poniższego kodu, aby podświetlić wszystkie komórki, które mają w sobie komentarze.
'Ten kod podświetli komórki, które mają komentarze' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub
W tym przypadku użyłem vbBlue, aby nadać komórkom niebieski kolor. Możesz zmienić to na inne kolory, jeśli chcesz.
Wyróżnij puste komórki za pomocą VBA
Chociaż możesz podświetlić pustą komórkę za pomocą formatowania warunkowego lub używając okna dialogowego Przejdź do specjalnego, jeśli musisz to robić dość często, lepiej użyć makra.
Po utworzeniu możesz mieć to makro na pasku narzędzi Szybki dostęp lub zapisać je w osobistym skoroszycie makr.
Oto kod makra VBA:
'Ten kod podświetli wszystkie puste komórki w zestawie danych Sub HighlightBlankCells() Dim Dataset jako Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub
W tym kodzie określiłem puste komórki, które mają być podświetlone na czerwono. Możesz wybrać inne kolory, takie jak niebieski, żółty, cyjan itp.
Jak sortować dane według pojedynczej kolumny
Możesz użyć poniższego kodu, aby posortować dane według określonej kolumny.
Sub SortDataHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes End Sub
Zauważ, że utworzyłem nazwany zakres o nazwie „DataRange” i użyłem go zamiast odwołań do komórek.
Istnieją również trzy kluczowe parametry, które są tutaj używane:
- Key1 - To jest ten, na którym chcesz posortować zestaw danych. W powyższym przykładowym kodzie dane zostaną posortowane na podstawie wartości w kolumnie A.
- Kolejność- Tutaj musisz określić, czy chcesz sortować dane w kolejności rosnącej czy malejącej.
- Nagłówek - w tym miejscu musisz określić, czy Twoje dane mają nagłówki, czy nie.
Przeczytaj więcej o tym, jak sortować dane w Excelu za pomocą VBA.
Jak sortować dane według wielu kolumn
Załóżmy, że masz zbiór danych, jak pokazano poniżej:
Poniżej znajduje się kod, który posortuje dane na podstawie wielu kolumn:
Sub SortMultipleColumns() Z ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlTak .Apply End With End Sub
Zauważ, że tutaj określiłem najpierw sortowanie na podstawie kolumny A, a następnie na podstawie kolumny B.
Wynik będzie taki, jak pokazano poniżej:
Jak uzyskać tylko część numeryczną z ciągu w Excelu?
Jeśli chcesz wyodrębnić tylko część numeryczną lub tylko część tekstową z ciągu, możesz utworzyć funkcję niestandardową w VBA.
Następnie możesz użyć tej funkcji VBA w arkuszu (podobnie jak zwykłe funkcje programu Excel) i wyodrębni ona tylko część numeryczną lub tekstową z ciągu.
Coś, jak pokazano poniżej:
Poniżej znajduje się kod VBA, który utworzy funkcję wyodrębniania części numerycznej z ciągu:
'Ten kod VBA utworzy funkcję pobierającą część numeryczną z ciągu Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Następnie Wynik = Wynik & Środek(CellRef, i, 1) Dalej i GetNumeric = Wynik Koniec funkcji
Potrzebujesz umieścić w kodzie w module, a następnie możesz użyć funkcji =GetNumeric w arkuszu.
Ta funkcja przyjmie tylko jeden argument, który jest odwołaniem do komórki, z której chcesz pobrać część numeryczną.
Podobnie poniżej znajduje się funkcja, która pobierze tylko część tekstową z ciągu w programie Excel:
'Ten kod VBA utworzy funkcję pobierającą część tekstową z ciągu Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Następnie Wynik = Wynik & Środek(CellRef, i, 1) Dalej i GetText = Wynik Koniec funkcji
Oto niektóre z przydatnych kodów makr programu Excel, których można używać w codziennej pracy, aby zautomatyzować zadania i zwiększyć produktywność.
