Używanie makr Excela może przyspieszyć pracę i zaoszczędzić sporo czasu.
Jednym ze sposobów uzyskania kodu VBA jest nagranie makra i pobranie kodu, który generuje. Jednakże, kod generowany przez rejestrator makr jest często pełen kodu, który nie jest naprawdę potrzebny. Ponadto rejestrator makr ma pewne ograniczenia.
Więc opłaca się mieć kolekcję użytecznych kodów makr VBA, które można mieć w tylnej kieszeni i używać ich w razie potrzeby.
Pomimo że pisanie kodu makra VBA w Excelu może początkowo zająć trochę czasu, po jego wykonaniu można go zachować jako odniesienie i używać, kiedy tylko będzie potrzebny.
W tym artykule 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 poradnik, dodając więcej przykładów makr. Jeśli uważasz, że coś powinno znaleźć się na liście, po prostu zostaw komentarz.
Możesz zachować tę stronę jako zakładkę na przyszłość.
Zanim przejdę do przykładu makra i podam ci kod VBA, pozwól, że najpierw pokażę ci, jak używać tych przykładów.
Używanie kodu z przykładów makr Excela
Oto kroki, które należy wykonać, aby użyć kodu z któregokolwiek z przykładów:
- Otwórz skoroszyt, w którym chcesz użyć makra.
- Przytrzymaj klawisz ALT i naciśnij klawisz F11. Spowoduje to otwarcie Edytora VB.
- Kliknij prawym przyciskiem myszy na dowolny obiekt w eksploratorze projektu.
- Przejdź do Wstaw -> Moduł.
- Kopiuj i wklej kod w oknie Kod modułu.
W przypadku, gdy przykład mówi, że należy wkleić kod w oknie kodu arkusza, należy dwukrotnie kliknąć na obiekt arkusza i skopiować kod w oknie kodu.
Po wstawieniu kodu do skoroszytu należy zapisać go z rozszerzeniem .XLSM lub .XLS.
Jak uruchomić makro
Po skopiowaniu kodu w Edytorze VB, oto kroki, które należy wykonać, aby uruchomić makro:
- Przejdź do zakładki Deweloper.
- Kliknij na Makra.
- W oknie dialogowym Makro wybierz makro, które chcesz uruchomić.
- Kliknij przycisk Uruchom.
W przypadku, gdy nie możesz znaleźć zakładki Deweloper na wstążce, przeczytaj ten samouczek, aby dowiedzieć się, jak ją uzyskać.
Powiązany samouczek: Różne sposoby uruchamiania makra w Excelu.
W przypadku, gdy kod jest wklejony w oknie kodu arkusza, nie musisz się martwić o jego uruchomienie. Zostanie on automatycznie uruchomiony w momencie wystąpienia określonej akcji.
A teraz przejdźmy do przykładów użytecznych makr, które pomogą Ci zautomatyzować pracę i zaoszczędzić czas.
Uwaga: W wielu przypadkach znajdziesz apostrof ('), po którym następuje linia lub dwie. Są to komentarze, które są ignorowane podczas uruchamiania kodu i są umieszczone jako notatki dla siebie/czytelnika.
W przypadku znalezienia jakiegokolwiek błędu w artykule lub kodzie, proszę być wspaniałym i dać mi znać.
Przykłady makr w Excelu
Niżej wymienione przykłady makr zostały omówione w tym artykule:
This Tutorial Covers:
Ukrywanie wszystkich arkuszy za jednym zamachem
Jeśli pracujesz w skoroszycie, który ma wiele ukrytych arkuszy, musisz odkleić te arkusze jeden po drugim. Może to zająć trochę czasu w przypadku, gdy istnieje wiele ukrytych arkuszy.
Tutaj znajduje się kod, który ukryje wszystkie arkusze w skoroszycie.
'This code will unhide all sheets in the workbookSub UnhideAllWoksheets()Dim ws As WorksheetFor Each ws In ActiveWorkbook.Worksheetsws.Visible = xlSheetVisibleNext wsEnd Sub
Powyższy kod używa pętli VBA (For Each), aby przejść przez każdy arkusz w skoroszycie. Następnie zmienia właściwość visible arkusza na visible.
Tutaj znajduje się szczegółowy samouczek, jak korzystać z różnych metod, aby ukryć arkusze w programie Excel.
Ukryj wszystkie arkusze oprócz aktywnego arkusza
Jeśli pracujesz nad raportem lub pulpitem i chcesz ukryć wszystkie arkusze oprócz tego, który ma raport/pulpit, możesz użyć tego kodu makro.
'This macro will hide all the worksheet except the active sheetSub HideAllExceptActiveSheet()Dim ws As WorksheetFor Each ws In ThisWorkbook.WorksheetsIf ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHiddenNext wsEnd Sub
Sortowanie arkuszy alfabetycznie przy użyciu VBA
Jeśli masz skoroszyt z wieloma arkuszami i chcesz je posortować alfabetycznie, ten kod makra może się bardzo przydać. Może to być przypadek, gdy masz nazwy arkuszy jako lata lub nazwy pracowników lub nazwy produktów.
'This code will sort the worksheets alphabeticallySub SortSheetsTabName()Application.ScreenUpdating = FalseDim ShCount As Integer, i As Integer, j As IntegerShCount = Sheets.CountFor i = 1 To ShCount - 1For j = i + 1 To ShCountIf Sheets(j).Name < Sheets(i).Name ThenSheets(j).Move before:=Sheets(i)End IfNext jNext iApplication.ScreenUpdating = TrueEnd Sub
Chroń wszystkie arkusze za jednym zamachem
Jeśli masz wiele arkuszy w skoroszycie i chcesz chronić wszystkie arkusze, możesz użyć tego makra.
Pozwala ono określić hasło w kodzie. Będziesz potrzebował tego hasła, aby odbezpieczyć arkusz.
'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword = "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheets ws.Protect password:=passwordNext wsEnd Sub
Unprotect All Worksheets At One Go
Jeśli masz niektóre lub wszystkie arkusze chronione, możesz po prostu użyć niewielkiej modyfikacji kodu używanego do ochrony arkuszy, aby je odbezpieczyć.
'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword = "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheetsws.Unprotect password:=passwordNext wsEnd Sub
Zauważ, że hasło musi być takie samo jak to, które zostało użyte do zablokowania arkuszy. Jeśli tak nie jest, pojawi się błąd.
Ukryj wszystkie wiersze i kolumny
Ten kod makro usunie wszystkie ukryte wiersze i kolumny.
To może być bardzo pomocne, jeśli dostajesz plik od kogoś innego i chcesz mieć pewność, że nie ma w nim ukrytych wierszy/kolumn.
'This code will unhide all the rows and columns in the WorksheetSub UnhideRowsColumns()Columns.EntireColumn.Hidden = FalseRows.EntireRow.Hidden = FalseEnd Sub
Unmerge All Merged Cells
Powszechną praktyką jest łączenie komórek w celu utworzenia jednej. W przypadku, gdy pracujesz z arkuszem zawierającym scalone komórki, użyj poniższego kodu, aby usunąć wszystkie scalone komórki za jednym zamachem.
'This code will unmerge all the merged cellsSub UnmergeAllCells()ActiveSheet.Cells.UnMergeEnd Sub
Zauważ, że zamiast opcji Scal i Wyśrodkuj, zalecam użycie opcji Wyśrodkuj przez zaznaczenie.
Zapisywanie skoroszytu ze znacznikiem czasu w nazwie
Często zdarza się, że musisz tworzyć wersje swojej pracy. Są one bardzo pomocne w długich projektach, gdzie pracujesz z plikiem przez dłuższy czas.
Dobrą praktyką jest zapisywanie plików ze znacznikami czasu.
Użycie znaczników czasu pozwoli Ci wrócić do danego pliku, aby zobaczyć jakie zmiany zostały wprowadzone lub jakie dane zostały użyte.
Tutaj znajduje się kod, który automatycznie zapisze skoroszyt w określonym folderze i doda znacznik czasu za każdym razem, gdy zostanie zapisany.
'This code will Save the File With a Timestamp in its nameSub SaveWorkbookWithTimeStamp()Dim timestamp As Stringtimestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss")ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestampEnd Sub
Musisz określić lokalizację folderu i nazwę pliku.
W powyższym kodzie „C:UsersUsernameDesktop jest lokalizacją folderu, której użyłem. Musisz określić lokalizację folderu, w którym chcesz zapisać plik. Ponadto, użyłem nazwy ogólnej „WorkbookName” jako prefiksu nazwy pliku. Możesz określić coś związanego z Twoim projektem lub firmą.
Zapisz każdy arkusz jako osobny plik PDF
Jeśli pracujesz z danymi dla różnych lat, działów lub produktów, możesz mieć potrzebę zapisania różnych arkuszy jako plików PDF.
Pomimo, że może to być czasochłonny proces, jeśli jest wykonywany ręcznie, VBA może go naprawdę przyspieszyć.
Oto kod VBA, który zapisze każdy arkusz jako oddzielny plik PDF.
'This code will save each worsheet as a separate PDFSub SaveWorkshetAsPDF()Dim ws As WorksheetFor Each ws In Worksheetsws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf"Next wsEnd Sub
W powyższym kodzie podałem adres lokalizacji folderu, w którym chcę zapisać pliki PDF. Ponadto, każdy PDF otrzyma taką samą nazwę jak arkusz. 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 dla arkuszy wykresów).
Zapisz każdy arkusz roboczy jako osobny plik PDF
Oto kod, który zapisze cały skoroszyt jako plik PDF we wskazanym folderze.
'This code will save the entire workbook as PDFSub 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, gdy masz arkusz, który zawiera wiele formuł i chcesz przekonwertować te formuły na wartości.
'This code will convert all formulas into valuesSub ConvertToValues()With ActiveSheet.UsedRange.Value = .ValueEnd WithEnd Sub
Ten kod automatycznie identyfikuje komórki, które są używane i konwertuje je na wartości.
Chroń/zablokuj komórki z formułami
Możesz chcieć zablokować komórki z formułami, gdy masz dużo obliczeń i nie chcesz ich przypadkowo usunąć lub zmienić.
Tutaj jest kod, który zablokuje wszystkie komórki, które mają formuły, podczas gdy wszystkie inne komórki nie są zablokowane.
'This macro code will lock all the cells with formulasSub LockCellsWithFormulas()With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=TrueEnd WithEnd Sub
Powiązany samouczek: How to Lock Cells in Excel.
Chroń wszystkie arkusze w skoroszycie
Użyj poniższego kodu, aby chronić wszystkie arkusze w skoroszycie za jednym zamachem.
'This code will protect all sheets in the workbookSub ProtectAllSheets()Dim ws As WorksheetFor Each ws In Worksheetsws.ProtectNext wsEnd Sub
Ten kod przejdzie przez wszystkie arkusze jeden po drugim i ochroni je.
W przypadku, gdy chcesz odbezpieczyć wszystkie arkusze, użyj ws.Unprotect zamiast ws.Protect w kodzie.
Wstaw wiersz po każdym innym wierszu w zaznaczeniu
Użyj tego kodu, gdy chcesz wstawić pusty wiersz po każdym wierszu w zaznaczonym zakresie.
'This code will insert a row after every row in the selectionSub InsertAlternateRows()Dim rng As RangeDim CountRow As IntegerDim i As IntegerSet rng = SelectionCountRow = rng.EntireRow.CountFor i = 1 To CountRowActiveCell.EntireRow.InsertActiveCell.Offset(2, 0).SelectNext iEnd Sub
Podobnie, możesz zmodyfikować ten kod, aby wstawić pustą kolumnę po każdej kolumnie w zaznaczonym zakresie.
Automatycznie wstawiaj datę & Znacznik czasowy w sąsiedniej komórce
Znacznik czasowy jest czymś, czego używasz, gdy chcesz śledzić działania.
Na przykład, możesz chcieć śledzić działania, takie jak kiedy został poniesiony konkretny wydatek, o której godzinie została utworzona faktura sprzedaży, kiedy wprowadzono dane do komórki, kiedy raport został ostatnio zaktualizowany itp.
Użyj tego kodu, aby wstawić znacznik daty i godziny do sąsiedniej komórki, gdy dokonano wpisu lub edytowano istniejącą zawartość.
'This code will insert a timestamp in the adjacent cellPrivate Sub Worksheet_Change(ByVal Target As Range)On Error GoTo HandlerIf Target.Column = 1 And Target.Value <> "" ThenApplication.EnableEvents = FalseTarget.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")Application.EnableEvents = TrueEnd IfHandler:End Sub
Zauważ, że musisz wstawić ten kod w oknie kodu arkusza (a nie w oknie kodu modułu, jak to robiliśmy w innych dotychczasowych przykładach makr Excela). Aby to zrobić, w edytorze VB kliknij dwukrotnie na nazwę arkusza, na którym chcesz mieć tę funkcjonalność. Następnie skopiuj i wklej ten kod w oknie kodu tego arkusza.
Ponadto, ten kod jest tak skonstruowany, że działa, gdy dane są wprowadzane w kolumnie A (zauważ, że w kodzie jest linia Target.Column = 1). Możesz to odpowiednio zmienić.
Wyświetlanie alternatywnych wierszy w zaznaczeniu
Wyświetlanie alternatywnych wierszy może znacznie zwiększyć czytelność danych. Może to być przydatne, gdy musisz zrobić wydruk i przejrzeć dane.
Tutaj znajduje się kod, który natychmiast podświetli alternatywne wiersze w zaznaczeniu.
'This code would highlight alternate rows in the selectionSub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End IfNext MyrowEnd Sub
Zauważ, że w kodzie określiłem kolor jako vbCyan. Możesz określić również inne kolory (takie jak vbRed, vbGreen, vbBlue).
Podświetl komórki z błędnie napisanymi słowami
Excel nie posiada sprawdzania pisowni jak to ma miejsce w Word lub PowerPoint. Chociaż możesz uruchomić sprawdzanie pisowni, naciskając klawisz F7, nie ma wizualnej wskazówki, kiedy jest błąd ortograficzny.
Użyj tego kodu, aby natychmiast podświetlić wszystkie komórki, które mają błąd ortograficzny.
'This code will highlight the cells that have misspelled wordsSub HighlightMisspelledCells()Dim cl As RangeFor Each cl In ActiveSheet.UsedRangeIf Not Application.CheckSpelling(word:=cl.Text) Thencl.Interior.Color = vbRedEnd IfNext clEnd Sub
Zauważ, że komórki, które są podświetlone, to te, które mają tekst, który Excel uważa za błąd ortograficzny. W wielu przypadkach podświetli również nazwy lub terminy marek, 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 naraz.
'This code will refresh all the Pivot Table in the WorkbookSub RefreshAllPivotTables()Dim PT As PivotTableFor Each PT In ActiveSheet.PivotTablesPT.RefreshTableNext PTEnd Sub
Więcej o odświeżaniu tabel przestawnych możesz przeczytać tutaj.
Zmień wielkość liter w wybranych komórkach na duże
Mimo że Excel posiada formuły do zmiany wielkości liter w tekście, każe Ci to robić w innym zestawie komórek.
Użyj tego kodu, aby natychmiast zmienić wielkość liter tekstu w zaznaczonym tekście.
'This code will change the Selection to Upper CaseSub ChangeCase()Dim Rng As RangeFor Each Rng In Selection.CellsIf Rng.HasFormula = False ThenRng.Value = UCase(Rng.Value)End IfNext RngEnd Sub
Zauważ, że w tym przypadku użyłem UCase, aby zmienić wielkość liter tekstu na górną. Możesz użyć LCase dla małych liter.
Podświetl wszystkie komórki z komentarzami
Użyj poniższego kodu, aby podświetlić wszystkie komórki, które mają komentarze.
'This code will highlight cells that have comments`Sub HighlightCellsWithComments()ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlueEnd Sub
W tym przypadku użyłem vbBlue, aby nadać niebieski kolor komórkom. Możesz to zmienić na inne kolory, jeśli chcesz.
Wyświetl puste komórki za pomocą VBA
Mimo że możesz wyróżnić 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 jest użyć makra.
Po utworzeniu makra można je umieścić na pasku narzędzi szybkiego dostępu lub zapisać w osobistym skoroszycie z makrami.
Tutaj znajduje się kod makra VBA:
'This code will highlight all the blank cells in the datasetSub HighlightBlankCells()Dim Dataset as RangeSet Dataset = SelectionDataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRedEnd Sub
W tym kodzie puste komórki zostały zaznaczone 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 do sortowania danych według określonej kolumny.
Sub SortDataHeader()Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYesEnd 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 – Jest to parametr, na podstawie którego chcesz posortować zestaw danych. W powyższym przykładzie dane zostaną posortowane na podstawie wartości w kolumnie A.
- Order – Tutaj musisz określić, czy chcesz posortować dane w porządku rosnącym czy malejącym.
- Header – Tutaj musisz określić, czy dane mają nagłówki czy nie.
Przeczytaj więcej o tym, jak sortować dane w Excelu za pomocą VBA.
Jak posortować 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()With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1:C13") .Header = xlYes .ApplyEnd WithEnd Sub
Zauważ, że tutaj określiłem, aby najpierw sortować w oparciu o kolumnę A, a następnie w oparciu o kolumnę B.
Wyjściem byłoby coś, jak pokazano poniżej:
Jak uzyskać tylko część numeryczną z ciągu znaków w Excelu
Jeśli chcesz wyodrębnić tylko część numeryczną lub tylko część tekstową z ciągu znaków, możesz utworzyć funkcję niestandardową w VBA.
Możesz następnie użyć tej funkcji VBA w arkuszu (tak jak zwykłych funkcji Excela) i będzie ona wyodrębniać tylko część numeryczną lub tekstową z łańcucha.
Coś takiego jak poniżej:
Poniżej znajduje się kod VBA, który utworzy funkcję wyodrębniającą część numeryczną z ciągu znaków:
'This VBA code will create a function to get the numeric part from a stringFunction GetNumeric(CellRef As String)Dim StringLength As IntegerStringLength = Len(CellRef)For i = 1 To StringLengthIf IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)Next iGetNumeric = ResultEnd Function
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órym jest odwołanie do komórki, z której chcesz uzyskać część numeryczną.
Podobnie, poniżej znajduje się funkcja, która pozwoli Ci uzyskać tylko część tekstową z ciągu znaków w Excelu:
'This VBA code will create a function to get the text part from a stringFunction GetText(CellRef As String)Dim StringLength As IntegerStringLength = Len(CellRef)For i = 1 To StringLengthIf Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)Next iGetText = ResultEnd Function
Są to niektóre z przydatnych makrokodów Excela, które możesz wykorzystać w codziennej pracy, aby zautomatyzować zadania i być o wiele bardziej produktywnym.
Inne tutoriale Excel, które mogą Ci się spodobać:
- Jak usunąć makra w Excelu