Programowanie makr w programie Excel: kompletny przewodnik krok po kroku

Informatec Digital » Programowanie » Programowanie makr w programie Excel: kompletny przewodnik krok po kroku

Jeśli często wykonujesz te same zadania w Excelu, makra będą Twoim najlepszym sprzymierzeńcem w oszczędzaniu czasu. Dzięki nim możesz zautomatyzować całe sekwencje działań (dowiedz się więcej o programowaniu VBA ), od formatowania, przez importowanie danych, po wykonywanie obliczeń, i uruchamiać je w dowolnym momencie za pomocą kliknięcia lub skrótu klawiaturowego.

Podczas tworzenia makra program Excel rejestruje kliknięcia i naciśnięcia klawiszy i wiernie je ponownie wykonuje. Następnie można modyfikować kod, aby dostroić wykonywanie i korzystać z zasobów programu Excel . Często zaczyna się od rejestratora makr, a następnie udoskonala go w języku VBA, co zapewnia większą kontrolę, elastyczność i eliminuje błędy wynikające z nieuwagi.

Czym właściwie jest makro w programie Excel?

Makro to lista instrukcji wykonywanych przez program Excel w określonej kolejności . Może to być proste, jak zastosowanie stylu procentowego, lub złożone, jak przygotowanie raportu z tabelami przestawnymi , sformatowanie go, utworzenie arkuszy kalkulacyjnych i wysłanie wiadomości e-mail w programie Outlook. Makra są zapisywane w skoroszycie i można je uruchamiać w dowolnym momencie.

Nie musisz znać się na programowaniu, aby zacząć: rejestrator tłumaczy Twoje działania na kod VBA. Mimo to, pewna znajomość VBA pozwoli Ci tworzyć niestandardowe funkcje, formularze, a nawet mini-aplikacje w Excelu.

Dlaczego warto używać makr? Rzeczywiste korzyści

Głównym celem makra jest unikanie powtarzających się zadań i minimalizowanie błędów . Jeśli co miesiąc przygotowujesz ten sam raport, makro oszczędzi Ci zbędnych, mechanicznych kroków i pozwoli Ci skupić się na tym, co ważne.

Jego zalety obejmują oszczędność czasu, spójne wykonywanie zadań (bez pomijania kroków) oraz możliwość tworzenia skrótów i przycisków przyspieszających procesy . Jeśli korzystasz z Excela tylko okazjonalnie, może to nie być warte zachodu; ale jeśli używasz go codziennie, zauważysz różnicę już od pierwszego dnia.

Włącz kartę Deweloper (Windows i Mac)

Karta Deweloper jest domyślnie ukryta. W systemie Windows kliknij prawym przyciskiem myszy wstążkę i wybierz opcję „Dostosuj wstążkę”. Zaznacz „Deweloper” i kliknij OK. Następnie zobaczysz kartę Deweloper obok widoku.

Na komputerze Mac przejdź do Excel > Preferencje > Paski narzędzi i wstążka. W sekcji „Główne karty” wybierz „Deweloper” i zapisz. Uzyskasz dostęp do rejestratora, języka VBA i kontrolek . Jeśli dopiero zaczynasz korzystać z programu Excel, zapoznaj się z podstawowymi instrukcjami dotyczącymi programu Excel , aby zapoznać się ze wstążką i jej opcjami.

Jak tworzyć makra za pomocą Rejestratora

Rejestrator idealnie nadaje się na początek. W menu Deweloper > Nagraj makro (lub Alt+T+M+R w systemie Windows) nadaj opisową nazwę. Pamiętaj: nazwa musi zaczynać się od litery, bez spacji i symboli.

Wybierz miejsce zapisu: „Ten skoroszyt” sprawdzi się w większości przypadków. Jeśli chcesz, aby był zawsze dostępny, wybierz „Skoroszyt makr osobistych”, a Excel utworzy/zapisze go w pliku Personal.xlsb , który otwiera się w trybie ukrytym w programie Excel.

Opcjonalnie dodaj skrót. Aby uniknąć konfliktów (na przykład utraty Ctrl+Z), zaleca się używanie skrótów Ctrl+Shift. Możesz również napisać krótki , jasny opis (bardzo przydatny, gdy masz wiele makr).

Kliknij OK i wykonaj czynności, które mają zostać zarejestrowane. Na przykład: zaznacz A1 i zastosuj format „Procentowy”. Unikaj niepotrzebnych kliknięć lub zmian w zaznaczeniu, które nie są częścią zamierzonego procesu, ponieważ rejestrator rejestruje niemal wszystko.

Po zakończeniu zatrzymaj nagrywanie. Aby je uruchomić, przejdź do Deweloper > Makra (lub naciśnij Alt+F8), wybierz makro i kliknij Uruchom. Zobaczysz, że Excel powtórzy kroki w dokładnie tej samej kolejności.

Uruchamiaj, edytuj i zarządzaj swoimi makrami

W menu Deweloper > Makra (Alt+F8) możesz uruchamiać, edytować i usuwać makra. Po wybraniu opcji Edycja otworzy się Edytor Visual Basic z zarejestrowanym kodem, który możesz oczyścić i zoptymalizować (często zawiera on powtarzające się kroki).

Przypisanie makra do przycisku lub kształtu jest bardzo wygodne: wstaw kształt, kliknij prawym przyciskiem myszy > Przypisz makro, wybierz makro i gotowe. Po kliknięciu kształtu makro zostanie uruchomione natychmiast.

Aby mieć go zawsze pod ręką na pasku narzędzi Szybki dostęp, otwórz menu paska narzędzi (strzałka w dół) > Więcej poleceń > w sekcji „Polecenia dostępne w”, wybierz „Makra”, dodaj swoje i zmień ikonę, jeśli chcesz. Teraz będziesz mieć stały przycisk, nawet gdy nie jesteś w trybie programisty.

Jeśli chcesz ponownie użyć makra w innym skoroszycie, możesz skopiować moduł z Edytora Visual Basic. Otwórz VBE (Alt+F11), przeciągnij moduł do innego otwartego skoroszytu lub użyj opcji Eksportuj/Importuj plik .bas.

Programowanie makr w programie Excel za pomocą edytora Visual Basic (VBA)

Aby wyjść poza rejestrator, otwórz Edytor za pomocą Alt+F11. Wstaw moduł (Wstaw > Moduł) i utwórz pierwszą podprocedurę. Podstawowy schemat to Sub Name() … End Sub.

Na przykład, aby wyświetlić komunikat i zapisać wartość w komórce A1, możesz zacząć od czegoś prostego. W ten sposób zrozumiesz strukturę i sposób wywołania makra z Excela (Alt+F8) lub przypisania go do przycisku, przy użyciu zaledwie kilku linijek kodu :

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Kolejny szybki przykład zastosowania stylu procentowego do bieżącego zaznaczenia. To makro powtarza to, co zrobiłeś z rejestratorem , ale wpisane bezpośrednio:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

Idealna kombinacja to: nagrywanie w celu przyspieszenia, a następnie otwieranie VBE w celu usunięcia zbędnych kroków, dodania zmiennych, warunków lub pętli. W ten sposób przekształcasz „dosłowne” nagranie w solidne i wielokrotnego użytku makro.

Odwołania absolutne i względne

Domyślnie rejestrator tworzy makra w trybie bezwzględnym: zawsze działa na tych samych komórkach (na przykład B3), niezależnie od miejsca, w którym się znajdujesz podczas jego uruchamiania. Jeśli dane zmieniają położenie w procesie, może to nie być to, czego potrzebujesz.

Dzięki opcji „Użyj odwołań względnych” makro działa względem aktywnej komórki w momencie rozpoczęcia wykonywania. Na przykład, jeśli rozpocząłeś rejestrowanie w komórce B3 i wpisałeś „Imię”, „Nazwisko” itd., przesuwając się w prawo, to po uruchomieniu od komórki C4, makro wpisze te same informacje względem komórek C4, D4, E4 itd.

Typowy przykład: dodawanie tytułów do tabeli, która czasami zaczyna się w komórce B3, a czasami w komórce C5. W trybie bezwzględnym tytuł zawsze będzie zapisywany w komórce B3. W trybie względnym dostosuje się do wybranej komórki początkowej.

Typowe błędy przy rejestrowaniu procesów

Częstą pułapką jest rejestrowanie formuły „przeciągnij” dla, powiedzmy, trzech wierszy i myślenie, że zadziała ona również w przypadku dłuższych tabel. Rejestrator zarejestruje coś równoważnego do „ wypełnij trzy komórki w dół ”, a nie „do końca tabeli”.

Rozwiązaniem jest zmiana podejścia: użyj zakresów dynamicznych (na przykład wykrywających ostatni wiersz), pętli lub metody CurrentRegion, albo dostosuj makro w VBA po jego nagraniu tak, aby można je było skalować do dowolnego rozmiaru.

Dobre praktyki przy nagrywaniu

Przed nagraniem przeprowadź proces ręcznie kilka razy, aby go dopracować. Twórz krótkie, szczegółowe nagrania zamiast jednego obszernego; dzięki temu łatwiej będzie je scalać i łączyć.

Unikaj niepotrzebnych kliknięć, przewijania wstążki i wybierania opcji, które nic nie wnoszą. Zachowaj jasną nazwę i opis makra, aby Ty i każdy współpracownik mogli natychmiast zidentyfikować jego przeznaczenie.

Uruchamiaj makra na wiele sposobów

Oprócz okna dialogowego Makra (Alt+F8) możesz uruchamiać makra za pomocą kształtu, przycisku na pasku narzędzi Szybki dostęp lub otwierając skoroszyt. Możesz nawet uruchomić makro w programie Excel z poziomu innych aplikacji pakietu Office, co pozwala zautomatyzować zadania międzyaplikacyjne (na przykład aktualizację tabeli i wysłanie wiadomości e-mail za pośrednictwem programu Outlook).

Zastanów się dokładnie nad skrótami klawiaturowymi: jeśli przypiszesz kombinację klawiszy Ctrl+Z do makra, utracisz możliwość cofnięcia operacji, gdy skoroszyt jest otwarty. Dlatego zaleca się używanie kombinacji klawiszy Ctrl+Shift , aby uniknąć konfliktów.

Zapisywanie skoroszytów za pomocą makr

Jeśli plik zawiera kod VBA, zapisz go jako plik .xlsm, aby zachować kod i móc go uruchomić. Zapisanie pliku .xlsx spowoduje usunięcie projektu VBA . Jeśli potrzebujesz makr globalnych, użyj skoroszytu makr osobistych (Personal.xlsb).

Bezpieczeństwo i włączanie makr

Makra mogą być blokowane w zależności od ustawień bezpieczeństwa. Sprawdź Centrum zaufania, aby włączać je selektywnie i nigdy nie włączaj makr z nieznanych źródeł. Znajomość poziomu bezpieczeństwa pomoże Ci zrównoważyć ochronę i produktywność.

Przypisz makra do przycisków, kształtów i elementów sterujących

Aby umożliwić użytkownikom automatyczne uruchamianie makr, przypisz makro do przycisku, kształtu lub kontrolki. Kliknij obiekt prawym przyciskiem myszy > Przypisz makro > wybierz. Możesz również umieścić ikonę na wstążce lub pasku narzędzi Szybki dostęp i połączyć ją z makrem.

Jeśli korzystasz z formularzy lub kontrolek ActiveX, możesz przypisać do nich makra lub procedury zdarzeń (na przykład po kliknięciu). Pozwala to na tworzenie bardziej przyjaznych dla użytkownika interfejsów i procesów z przewodnikiem.

Przydatne przykłady makr (VBA)

Poniższe przykłady ilustrują typowe zadania. Możesz je wkleić do modułu (Alt+F11 > Wstaw > Moduł) i dostosować. Pamiętaj o podpisaniu skoroszytów lub dostosowaniu ustawień zabezpieczeń, jeśli wymaga tego Twoja organizacja, aby móc uruchamiać je bez ostrzeżeń.

Skopiuj wybraną zawartość do innej lokalizacji na tym samym arkuszu: Selection.Copy Destination

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Wydrukuj aktywny arkusz: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Zapisz plik jako skoroszyt z włączoną obsługą makr (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Zastosuj formatowanie do zakresu (dostosuj zakres do swoich potrzeb): Za pomocą Range(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Znajdź wartość w zakresie i wyświetl pierwsze dopasowanie: Zakres(…).Znajdź

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Przypadek praktyczny: formatowanie procentowe

Nagrywanie: Rozpocznij nagrywanie, nadaj mu nazwę, wybierz miejsce zapisu i opcjonalnie przypisz skrót klawiaturowy i opis. Zaznacz komórki i zastosuj „Procent” z karty Narzędzia główne. Zatrzymaj nagrywanie i uruchom je ponownie, naciskając Alt+F8. Aby odtworzyć je z minimalistycznym kodem, użyj polecenia Selection.Style = "Percent" w VBA.

Wskazówka: Podczas nagrywania unikaj niepotrzebnego przełączania komórek. Te ruchy w przód i w tył są rejestrowane i mogą spowolnić i uszkodzić makro.

Automatyzacja długich zadań: lepsze są małe makra

Jeśli masz proces składający się z wielu kroków, rozważ podzielenie go na kilka mniejszych makr i wywoływanie ich sekwencyjnie. Ułatwia to konserwację, umożliwia ponowne wykorzystanie bloków i upraszcza debugowanie.

Na przykład jedno makro do przygotowania danych, drugie do formatowania, trzecie do generowania wydruku, a trzecie do eksportu do pliku PDF lub wysłania e-mailem . Możesz wywołać je pojedynczo lub wszystkie naraz.

Edytuj i wyczyść zarejestrowany kod

Rejestrator rejestruje „wszystko”: zaznaczenia, kliknięcia i zmiany tabulacji. Po otwarciu VBE usuwa niepotrzebne zaznaczenia (Select/Selection), zastępując je bezpośrednimi odwołaniami (na przykład Range("A1").Value = ... ) i grupuje formaty w bloki With/End With.

Dodatkowo dodaje zmienne i podstawową obsługę błędów. Kilka instrukcji if i prostych kontroli zapobiega błędom, gdy zakres jest pusty lub wybór nie jest zgodny z oczekiwaniami.

Najczęściej zadawane pytania

Jak utworzyć formularz użytkownika w programie Excel? Otwórz Edytor Visual Basic (Alt+F11), przejdź do Wstawianie > Formularz użytkownika i dodaj kontrolki (pola tekstowe, przyciski itp.). Możesz je wyświetlić za pomocą makra.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Następnie przypisz procedury do zdarzeń elementów sterujących (na przykład kliknięcia przycisku), aby wykonać potrzebną logikę.

Jak zabezpieczyć/odbezpieczyć arkusz za pomocą makr? Użyj tych procedur, zmieniając hasło według potrzeb. Pozwalają one aktywować lub dezaktywować ochronę jednym kliknięciem.

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

Jak usunąć makro? Przejdź do Deweloper > Makra, zaznacz makro i naciśnij Delete. Jeśli chcesz usunąć cały kod z modułu, otwórz VBE (Alt+F11), znajdź moduł w projekcie, kliknij prawym przyciskiem myszy i wybierz Usuń moduł (możesz go najpierw wyeksportować, jeśli chcesz go zapisać).

Ostatnia praktyczna wskazówka: jeśli formatujesz raport miesięczny, w którym klienci z zaległymi saldami powinni być wyróżnieni na czerwono i pogrubieni, zapisz tę sekwencję raz, dostosuj kod VBA, aby zastosować ją do prawidłowego zakresu (używając wykrywania ostatniego wiersza lub filtrów) i zapisz makro w swoim osobistym skoroszycie makr. Następnym razem wystarczy prosty skrót lub przycisk, aby zastosować formatowanie natychmiast, bezbłędnie i w kilka sekund.