w tym artykule pokażę Ci niektóre z najbardziej niesamowitych kodów VBA Excel, których możesz użyć do optymalizacji pracy. VBA to język programowania, który można wykorzystać do rozszerzenia możliwości MS Excel i innych aplikacji MS Office. Jest niezwykle pomocny dla użytkowników MS Excel, ponieważ może być używany do automatyzacji pracy i znaczącej poprawy wydajności. Ten artykuł wprowadzi cię w VBA i pokaże Ci niektóre z najbardziej przydatnych, gotowych do użycia kodów VBA. Możesz użyć tych przykładów makr do tworzenia własnych skryptów, które odpowiadają twoim potrzebom.

nie potrzebujesz doświadczenia w programowaniu, aby skorzystać z informacji zawartych w tym artykule, ale oczekuje się, że będziesz miał podstawową wiedzę z zakresu Excela. Jeśli jesteś początkującym użytkownikiem, polecam przeczytanie artykułu 20 Formuły Excel, które powinieneś zacząć używać teraz, aby dowiedzieć się więcej o podstawowych funkcjach programu Excel.

przygotowałam dla Ciebie szereg gotowych do użycia makr VBA Excel z dużą funkcjonalnością, które możesz wykorzystać do optymalizacji swojej pracy. Aby z nich korzystać, musisz je „zainstalować” w pliku Excel. Następny akapit dotyczy instalacji makr programu Excel. Pomiń tę część, jeśli jesteś już zaznajomiony z tym.

jak zainstalować makro

w programie Excel, naciśnij kombinację klawiszy alt + F11. Spowoduje to przejście do edytora VBA w MS Excel. Następnie kliknij prawym przyciskiem myszy folder Microsoft Excel Objects po lewej stronie i wybierz Insert=> moduł. Jest to miejsce, w którym przechowywane są makra. Aby skorzystać z makra, musisz zapisać dokument programu Excel jako włączone makro. Z zakładki file = > Zapisz jako, Wybierz Zapisz jako skoroszyt z obsługą makr (the .rozszerzenie xlsm) teraz czas napisać swoje pierwsze makro!

1. Kopiuj dane z jednego pliku do drugiego.

bardzo przydatne makro, ponieważ pokazuje, jak skopiować zakres danych z wnętrza vba oraz jak utworzyć i nazwać nowy skoroszyt. Można go łatwo uaktualnić do własnych wymagań:

 Sub CopyFiletoAnotherWorkbook () 'kopiuje Arkusze danych ("przykład 1").Zakres("B4:C15").Kopiuj ' Utwórz nowy skoroszyt skoroszyty.Add ' Wklej Arkusz danych.Wklej ' Wyłącz aplikację alerty aplikacji.DisplayAlerts = False ' Zapisz nowy plik. Zmień nazwę katalogu. ActiveWorkbook.SaveAs Filename:="C:\Temp\MyNewBook.XLSX "' Włącz alerty aplikacji z powrotem w aplikacji.DisplayAlerts = TrueEnd Sub

2. Pokaż ukryte wiersze

czasami duże pliki Excela zawierają ukryte linie dla lepszej przejrzystości. Oto makro, które odkryje wszystkie wiersze z aktywnego arkusza roboczego:

 Sub ShowHiddenRows() kolumny.Cała kolumna.Hidden = False Rows.EntireRow.Hidden = FalseEnd Sub

3. Usuń puste wiersze i kolumny

puste wiersze w programie Excel to problem z przetwarzaniem danych. Oto jak się ich pozbyć:

 Sub DeleteEmptyRowsAndColumns() 'Zadeklaruj swoje zmienne. Dim MyRange as Range Dim iCounter As Long 'Zdefiniuj zakres docelowy. Set MyRange = ActiveSheet.UsedRange ' początek odwrotnej pętli przez zakres wierszy. For iCounter = MyRange.Rzędów.Policz do 1 kroku -1 ' jeśli cały wiersz jest pusty, usuń go. Jeśli Zastosowanie.CountA (Rows (Icounter).EntireRow) = 0 Then Rows(Icounter).Usuń 'Usuń komentarz, aby zobaczyć, które są puste wiersze 'MsgBox " row"& iCounter &" jest pusty " koniec, jeśli 'zwiększ licznik w dół następny iCounter 'Krok 6: Rozpocznij odwrotną pętlę przez zakres kolumn. For iCounter = MyRange.Kolumny.Policz do 1 kroku -1 ' Krok 7: Jeśli cała kolumna jest pusta, usuń ją. Jeśli Zastosowanie.CountA (kolumny (Icounter).EntireColumn) = 0 Then Columns(Icounter).Usuń Koniec If ' Krok 8: Zwiększ licznik w dół następny Icounter End Sub

4. Znajdź pustą komórkę

Sub FindEmptyCell() ActiveCell.Offset (1, 0).Wybierz Opcję Do While Not IsEmpty (ActiveCell) ActiveCell.Offset (1, 0).Wybierz LoopEnd Sub

5. Zastąp puste komórki wartością.

jak wcześniej wspomniano, puste komórki zakłócają przetwarzanie danych i tworzenie tabel przestawnych. Oto kod, który zastępuje wszystkie puste komórki z 0. To makro ma bardzo dużą aplikację, ponieważ można go użyć do znalezienia i zastąpienia wyników N / A, a także innych znaków, takich jak kropki, przecinki lub zduplikowane wartości:

Sub FindAndReplace() 'Declare your variables Dim MyRange As Range Dim MyCell As Range 'Zapisz skoroszyt przed zmianą komórek? Wybierz MsgBox Case ("nie można cofnąć tej akcji. "& _ " Zapisz skoroszyt pierwszy?", Vbyesnocancel) Case Is = Vbyes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select ' Zdefiniuj zakres docelowy. Set MyRange = Selection ' Start looping through the range. Dla każdego MYCELLA w Myrange 'Sprawdź zerową Długość, a następnie dodaj 0. If Len(MyCell.Wartość) = 0 Then MyCell = 0 End If ' Get The next cell in the range Next Mycellend Sub

6. Sortuj liczby

poniższe makro sortuje w porządku rosnącym wszystkie liczby z kolumny aktywnej komórki. Wystarczy dwukrotnie kliknąć dowolną komórkę z kolumny, którą chcesz posortować.UWAGA: Musisz umieścić kod w arkuszu 1, a nie w module, aby działał:

 prywatny arkusz Podrzędny_beforedoubleclick (ByVal Target as Range, Cancel As Boolean) 'Declare your Variables Dim LastRow As Long 'Find last row Lastrow = Cells (Rows.Hrabia, 1) .End (xlUp) .Row ' Sortuj rosnąco na podwójnie klikniętych wierszach kolumn ("6:"& LastRow).Sort _ Key1: = Cells (6, ActiveCell.Column), _ Order1: = Xlascendingend Sub

7. Usuń puste spacje

czasami dane w skoroszycie zawierają dodatkowe spacje (spacje), które mogą zakłócać analizę danych i uszkodzić formuły. Oto makro, które usunie wszystkie spacje z wstępnie wybranego zakresu komórek:

 Sub TrimTheSpaces() 'Declare your variables Dim MyRange As Range Dim MyCell As Range 'Save the Workbook before changing cells Select Case MsgBox("Can' t Undo this action. "& _ " Zapisz skoroszyt pierwszy?", Vbyesnocancel) Case Is = Vbyes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select ' Zdefiniuj zakres docelowy. Set MyRange = Selection ' Start looping through the range. Dla każdego MyCell w MyRange ' przyciąć przestrzenie. If Not IsEmpty(MyCell) Then MyCell = Trim (MyCell) End If 'Get The next cell in the range Next Mycellend Sub

8. Podświetl wartości dublikowane

czasami są zduplikowane wartości w kilku kolumnach, które chcielibyśmy oświetlić. Oto makro, które właśnie to robi:

 Sub HighlightDuplicates() 'Declare your variables Dim MyRange As Range Dim MyCell As Range 'Zdefiniuj zakres docelowy. Set MyRange = Selection ' Start looping through the range. Dla każdego MyCell w MyRange 'upewnij się, że komórka ma formatowanie tekstu. If WorksheetFunction.CountIf (MyRange, MyCell.Value) > 1 Then MyCell.Wnętrze.ColorIndex = 36 End If ' Get The next cell in the range Next Mycellend Sub

9. Zaznacz dziesięć najlepszych wartości

ten kod podświetli dziesięć najlepszych wartości z wybranych komórek:

 wybór Sub TopTen ().Warunki formatowania.Wybór AddTop10.FormatConditions(Wybór.Warunki formatowania.Liczyć).Ustaw Pierwszy Priorytet Z Wyborem.FormatConditions (1)TopBottom = xlTop10Top ' Zmień rangę tutaj, aby podświetlić inną liczbę wartości .Pozycja = 10 .Procent = False Kończy Się Z Zaznaczeniem.FormatConditions (1)Czcionka .Color = -16752384Tintandshade = 0 Kończy Się Zaznaczeniem Z.FormatConditions (1)Wnętrze .PatternColorIndex = xlAutomatic ./ Colspan = 13561798 /Tintandshade = 0 Kończy Się Zaznaczeniem.FormatConditions (1)StopIfTrue = FalseEnd Sub 

możesz łatwo dostosować kod, aby podświetlić różną liczbę wartości.

10. Podświetlenie większe niż wartości

Po uruchomieniu tego kodu pojawi się okno. Poprosi Cię o wartość, którą chcesz porównać wybrane komórki.

 Sub HighlightGreaterThanValues() Dim i As Integer i = InputBox("wprowadź większą niż wartość", "Wprowadź wartość") wybór.Warunki formatowania.Usuń ' Zmień operatora na xlower, aby podświetlić zaznaczenie niższe niż wartości.Warunki formatowania.Dodaj typ: = xlCellValue, Operator: = xlGreater, Formula1: = i wybór.FormatConditions(Wybór.Warunki formatowania.Liczyć).Ustaw Pierwszy Priorytet Z Wyborem.FormatConditions (1)Czcionka.Color = RGB (0, 0, 0) .Wnętrze.Color = RGB (31, 218, 154) End With End Sub

możesz dostosować ten kod, aby podświetlić również niższe wartości.

proste makro, które podświetla wszystkie komórki zawierające komentarze:

wybór Sub HighlightCommentCells ().SpecialCells (xlCellTypeComments).Wybierz Zaznaczenie.Style="Note" End Sub

12. Zaznacz komórki z błędnymi słowami

jest to niezwykle przydatne podczas pracy z funkcjami, które pobierają ciągi, jednak ktoś wprowadził ciąg z błędem i twoje formuły nie działają. Oto jak rozwiązać ten problem:

 Sub ColorMispelledCells() dla każdego cl w ActiveSheet.UsedRange Jeśli Nie Aplikacja.Szachownica (Słowo:= cl.Tekst) Następnie _ cl.Wnętrze.ColorIndex = 28 Next Clend Sub

13. Tworzenie tabeli przestawnej

oto jak utworzyć tabelę przestawną z MS Excel (Wersja 2007). Szczególnie przydatne, gdy codziennie tworzysz niestandardowy raport. Możesz zoptymalizować Tworzenie tabeli przestawnej w następujący sposób:

 Sub PivotTableForExcel2007() Dim SourceRange As range Set SourceRange = Sheets("Sheet1").Zakres ("A3:N86") ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=SourceRange, _ Version: = xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion:=xlPivotTableVersion12End Sub

14. Załącz aktywny skoroszyt w wiadomości e-mail

Mój ulubiony kod VBA. Pozwala załączyć i wysłać plik, nad którym pracujesz, z predefiniowanym adresem e-mail, tytułem wiadomości i treścią wiadomości! Najpierw musisz ustawić odniesienie do Microsoft Outlook (w edytorze VBA kliknij tools = > referencje i wybierz Microsoft Outlook).

 Sub Sendfileasattachment() 'Declare your variables 'Ustawia odniesienie do Microsoft Outlook Object library Dim OLApp jako Outlook.Application Dim OLMail As Object ' Open Outlook start a new mail Item Set OLApp = New Outlook.Zestaw Aplikacji OLMail = OLApp.CreateItem (0) OLApp.Sesja.Logon ' Build your mail item and send With OLMail. to = "[email protected]; [email protected]". CC ="".BCC ="".Subject = "this is the Subject line".Body = "cześć".Załączniki.Dodaj ActiveWorkbook.Imię i nazwisko .Wyświetl 'Zmień na .Send to send without reviewing End With ' Memory cleanup Set OLMail = Nothing Set OLApp = NothingEnd Sub

15. Wyślij wszystkie wykresy Excel do prezentacji PowerPoint

bardzo przydatne makro, które pozwala dodać wszystkie wykresy Excel do prezentacji Powerpoint za pomocą jednego kliknięcia:

 Sub SendExcelFiguresToPowerPoint() 'Set reference to Microsoft Powerpoint Object Library 'Declare your variables DIM PP As PowerPoint.Aplikacja Dim PPPres Jako PowerPoint.Prezentacja Dim PPSlide Jako PowerPoint.Slide Dim i As Integer ' sprawdzanie Wykresów; Zamknij, jeśli wykresy nie istnieją Arkusze("dane slajdów").Wybierz, Czy ActiveSheet.ChartObjects.Count < 1 Then MsgBox "No charts existing the active sheet" Exit Sub End If 'Open PowerPoint and create new presentation Set PP = New PowerPoint.Zestaw aplikacji PPPres = PP.Prezentacje.Dodaj PP.Visible = True ' Uruchom pętlę na podstawie liczby wykresów dla i = 1 do ActiveSheet.ChartObjects.Count ' skopiuj Wykres Jako obraz ActiveSheet.ChartObjects(i).Chart.CopyPicture _ Size: = Xlscreen, Format:=Xlpicture Application.Wait (Now + TimeValue ("0:00:1")) 'Policz slajdy i dodaj nowy slajd jako następny dostępny numer slajdu ppSlideCount = PPPres.Slajdy.Count Set PPSlide = PPPres.Slajdy.Dodaj (SlideCount + 1, ppLayoutBlank) PPSlide.Wybierz ' Wklej obrazek i dostosuj jego pozycję; Przejdź do następnego wykresu PPSlide.Kształty.Wklej.Wybierz PP.Aktywuj okno.Selekcja.ShapeRange./ Align = "left" / msoAlignCentersAktywuj okno.Selekcja.ShapeRange.Align msoAlignMiddles, True Następny i ' zestaw czyszczenia pamięci PPSlide = Nothing Set PPPres = Nothing Set PP = NothingEnd Sub

16. Wyślij tabelę Excel w MS Word

tabele Excel są zwykle umieszczane wewnątrz dokumentów tekstowych. Oto automatyczny sposób eksportu tabeli Excel do MS Word:

 Sub Exceltablinword() 'Set reference to Microsoft Word Object library 'Declare your variables Dim MyRange As Excel.Zakres Dim WD jako słowo.Aplikacja Dim wdDoc jako Word.Dokument Dim WdRange Jako Word.Range ' kopiuje zdefiniowane Arkusze zakresu("tabela przychodów").Zakres("B4:F10").Cop ' Otwórz docelowy zestaw dokumentów Word wd = New Word . Zestaw aplikacji wdDoc = WD.Dokumenty.Open _ (ThisWorkbook.Ścieżka && " PasteTable.docx") wd.Visible = True ' Ustawia fokus na zakładce docelowej Set WdRange = wdDoc.Zakładki ("DataTableHere").Range ' Usuń starą tabelę i wklej nową W przypadku błędu Wznów następne WdRange.Stoły(1)Usuń WdRange.Wklej 'wklej w tabeli' Dostosuj szerokość kolumn WdRange.Stoły(1)Kolumny.SetWidth _ (MyRange.Szerokość / MyRange.Kolumny.Count), wdAdjustSameWidth ' włóż ponownie zakładkę wdDoc.Zakładki.Dodaj "DataTableHere", WdRange 'zestaw czyszczenia pamięci wd = Nothing Set wdDoc = Nothing Set WdRange = NothingEnd Sub

17. Wyodrębnij określone słowo z komórki

możemy użyć formuł, jeśli chcemy wyodrębnić określoną liczbę symboli. Ale co, jeśli chcemy wyodrębnić tylko drugie słowo ze zdania lub zakresu słów w komórce? Aby to zrobić, możemy utworzyć niestandardową funkcję Excela za pomocą VBA. Jest to jedna z najbardziej ważnych funkcji VBA, ponieważ pozwala tworzyć własne funkcje, które nie istnieją w MS Excel. Przejdźmy dalej i stwórzmy dwie funkcje: findword () i findwordrev(). Oto kod vba dla tego:

 funkcja FindWord (Źródło jako ciąg, pozycja jako liczba całkowita) jako ciąg po błędzie Wznów następny FindWord = Split(WorksheetFunction.Trim(Source),"") (Position - 1) On Error GoTo 0end Function FindWordRev (Source As String, Position As Integer)As String Dim Arr () As String Arr = VBA.Split (WorksheetFunction.Trim(Source), " ") On Error CV Next FindWordRev = Arr(UBound (Arr) - Position + 1) On Error GoTo 0end Function

bardzo ładne, stworzyliśmy dwie funkcje Cstom Excel. Teraz spróbuj użyć ich w programie Excel. Funkcja = FindWordRev (A1,1) pobiera ostatnie słowo z komórki A1. Funkcja = FindWord (A1, 3) bierze trzecie słowo z komórki A1, itd.

18. Chroń skoroszyt

czasami chcemy chronić dane w naszym pliku, aby tylko my mogli je zmienić. Oto jak to zrobić z VBA:

 Arkusze ochrony Sub() 'Zadeklaruj swoje zmienne Dim ws jako arkusz roboczy 'rozpocznij zapętlanie wszystkich arkuszy roboczych dla każdego ws w ActiveWorkbook.Arkusze robocze chronią i zapętlają do następnego arkusza roboczego ws.Protect Password: = "1234" Next wsEnd Sub

Gratulacje! Ponieważ nadal to czytasz, jesteś naprawdę chętny do nauki VBA. Jak już widzieliście, język programowania VBA jest niezwykle przydatny i może zaoszczędzić nam dużo czasu. Mam nadzieję, że te informacje okazały się pomocne i wykorzystać je, aby stać się mistrzem w MS Excel, VBA i oprogramowania komputerowego w ogóle.