VBA i Makra ·

Excel VBA – pierwsze kroki, które pozwolą ci napisać własny, działający kod

Arkusz Excela z listą zdarzeń i fragmentami kodu VBA

Czym VBA różni się od formuł

Formuła w komórce zawsze zwraca wartość do tej komórki i tylko do niej. VBA (Visual Basic for Applications) to język programowania wbudowany w Excela, który może robić znacznie więcej: otwierać pliki, przenosić dane między arkuszami, formatować komórki warunkowo na podstawie logiki, której nie da się zapisać formułą, albo wyświetlać własne okna dialogowe. Jeśli formuła odpowiada na pytanie "jaka jest wartość tej komórki", VBA odpowiada na pytanie "co ma się stać, krok po kroku".

Jak włączyć edytor VBA

Edytor VBA jest wbudowany w Excela, ale domyślnie schowany. Dwie drogi, żeby go otworzyć:

  1. Skrót klawiszowy Alt+F11 – działa zawsze, niezależnie od ustawień wstążki.
  2. Karta Deweloper na wstążce – jeśli jej nie widzisz, włącz ją w Plik > Opcje > Dostosowywanie wstążki i zaznacz "Deweloper" w prawej kolumnie.

Po otwarciu edytora (Alt+F11) zobaczysz drzewo projektu po lewej stronie z nazwami arkuszy i skoroszytu. Żeby dodać własny kod, klikasz prawym przyciskiem na nazwę skoroszytu, wybierasz Insert > Module – w nowym module piszesz swój kod.

Pierwszy, naprawdę prosty kod

Sub PierwszeMakro()
    MsgBox "Działa!"
End Sub

Sub i End Sub to początek i koniec procedury – wszystko, co napiszesz między nimi, wykona się po uruchomieniu. MsgBox wyświetla okno z komunikatem. Żeby uruchomić ten kod, ustaw kursor gdziekolwiek w jego treści i naciśnij F5.

Kod, który faktycznie coś robi w arkuszu

Załóżmy, że masz tabelę zamówień i chcesz, żeby makro wpisywało dzisiejszą datę do kolumny D, w pierwszym wolnym wierszu:

Sub WpiszDate()
    Dim ostatniWiersz As Long
    ostatniWiersz = Cells(Rows.Count, "A").End(xlUp).Row + 1
    Cells(ostatniWiersz, "D").Value = Date
End Sub

Rozbijmy to na części:

Pętle – dlaczego VBA jest potężniejsze niż kopiowanie formuł

Załóżmy, że chcesz przejść przez 500 wierszy i oznaczyć kolorem te, w których kwota przekracza 1000:

Sub OznaczDuzeZamowienia()
    Dim i As Long
    Dim ostatniWiersz As Long
    ostatniWiersz = Cells(Rows.Count, "C").End(xlUp).Row

    For i = 2 To ostatniWiersz
        If Cells(i, "C").Value > 1000 Then
            Cells(i, "C").Interior.Color = RGB(255, 199, 132)
        End If
    Next i
End Sub

Ten kod robi dokładnie to, co moglibyście zrobić formatowaniem warunkowym – ale pętla For w VBA pozwala dodać dowolnie skomplikowaną logikę, której formatowanie warunkowe po prostu nie obsłuży (np. porównanie z wartościami z innego arkusza, logowanie zmian do osobnej listy, wysłanie komunikatu po przekroczeniu progu).

Zapisywanie pliku z makrem

To częsty błąd początkujących: napisany kod przepada, bo plik zostaje zapisany jako zwykły .xlsx. Pliki z makrami VBA muszą być zapisane w formacie .xlsm (Skoroszyt programu Excel z obsługą makr) – przy zapisie wybierz ten typ pliku z listy rozwijanej w oknie "Zapisz jako".

Dlaczego Excel blokuje uruchomienie makra

Domyślne ustawienia zabezpieczeń Excela blokują makra w plikach pobranych z internetu albo otrzymanych mailem – to ochrona przed złośliwym kodem. Jeśli otwierasz własny plik .xlsm i widzisz żółty pasek "Zabezpieczenia uruchamiania makr", klikasz Włącz zawartość. Jeśli makra są zablokowane na poziomie ustawień całego Excela, sprawdzasz Plik > Opcje > Centrum zaufania > Ustawienia Centrum zaufania > Ustawienia makr.

Rejestrator makr jako most między formułami a pisaniem kodu

Jeśli pisanie kodu od zera wydaje się zbyt dużym skokiem, dobrym punktem startowym jest rejestrator makr (karta Deweloper > Zarejestruj makro) – nagrywasz swoje kliknięcia, a Excel generuje za ciebie kod VBA. Wygenerowany kod rzadko jest elegancki, ale pokazuje, jak w VBA nazywają się konkretne akcje (np. Range("A1:B10").Sort), co jest świetną ściągawką przy pisaniu własnego kodu.

Typowe pierwsze zastosowania VBA, które mają sens

Najczęstszy błąd początkujących w VBA

Największym błędem nie jest literówka w kodzie (edytor VBA i tak ją podkreśli) – jest nim pisanie makra, które odwołuje się do konkretnego arkusza albo zakresu "na sztywno" (np. Sheets("Arkusz1")), a potem zmiana nazwy arkusza w pliku. Kod przestaje działać, a komunikat błędu (Run-time error '9': Subscript out of range) nie mówi od razu, co się stało. Dobra praktyka to nazywanie arkuszy w kodzie tak samo, jak w interfejsie, i sprawdzanie tego przy każdej zmianie struktury pliku.

Drugi częsty problem to brak zapisanych kopii zapasowych przed testowaniem nowego makra na prawdziwych danych – pętla, która ma "posprzątać" dane, przy błędzie w logice równie łatwo je zniszczy. Zanim uruchomisz nowy kod na oryginalnym pliku, przetestuj go na kopii.

Przykład bardziej zaawansowany: filtrowanie listy na formularzu w czasie pisania

Jedno z klasycznych zadań w VBA to lista (np. ListBox na formularzu UserForm), która zawęża się w czasie rzeczywistym, gdy użytkownik wpisuje tekst w polu wyszukiwania – podobnie jak podpowiedzi w przeglądarce. Załóżmy, że masz formularz z polem tekstowym TextBox1 i listą ListBox1, do której chcesz wczytywać tylko nazwiska zawierające wpisywany fragment tekstu.

Najprostszym sposobem jest obsłużenie zdarzenia Change pola tekstowego i przefiltrowanie tablicy nazwisk funkcją Filter:

Private Sub TextBox1_Change()
    Dim listaNazwisk() As String
    Dim wynik() As String
    Dim i As Long

    listaNazwisk = Split("Kowalski,Nowak,Wiśniewski,Kowalczyk,Zieliński", ",")
    wynik = Filter(listaNazwisk, Me.TextBox1.Value)

    Me.ListBox1.Clear
    For i = LBound(wynik) To UBound(wynik)
        Me.ListBox1.AddItem wynik(i)
    Next i
End Sub

Funkcja Filter przyjmuje tablicę jednowymiarową i wzorzec tekstowy, zwracając nową tablicę zawierającą tylko elementy, które go zawierają. Ważne ograniczenie: Filter nie obsługuje znaków specjalnych ("wildcards" typu * czy ?) w sposób, w jaki działają one np. w filtrze zaawansowanym Excela – szukając "s" dostaniesz dopasowanie do dosłownego ciągu "s", nie do wszystkich słów zaczynających się na "s".

Wariant z pętlą For – gdy potrzebujesz więcej kontroli niż daje Filter

Jeśli potrzebujesz bardziej precyzyjnego dopasowania niż to, co oferuje Filter (np. dopasowania tylko na początku nazwiska, a nie w dowolnym miejscu tekstu), lepiej napisać własną pętlę z operatorem Like:

Private Sub TextBox1_Change()
    Dim listaNazwisk() As String
    Dim i As Long

    listaNazwisk = Split("Kowalski,Nowak,Wiśniewski,Kowalczyk,Zieliński", ",")
    Me.ListBox1.Clear

    For i = LBound(listaNazwisk) To UBound(listaNazwisk)
        If UCase(listaNazwisk(i)) Like UCase(Me.TextBox1.Value) & "*" Then
            Me.ListBox1.AddItem listaNazwisk(i)
        End If
    Next i
End Sub

Operator Like w połączeniu z * na końcu wzorca dopasowuje tylko nazwiska zaczynające się od wpisanego tekstu, a UCase po obu stronach porównania sprawia, że filtrowanie nie rozróżnia wielkości liter. To rozwiązanie jest wolniejsze na bardzo długich listach (kilka tysięcy elementów) niż wstępne przefiltrowanie funkcją Filter, a dopiero potem doprecyzowanie wyniku pętlą – dla większości praktycznych zastosowań (listy do kilkuset pozycji) różnica jest jednak niezauważalna.

Częste błędy przy pracy z formularzami (UserForm) i ListBox