Rejestrator makr Excela ma dużą moc, ale ma swoje ograniczenia. Jak opisano w innym artykule, rejestrator makr często rejestruje niepotrzebny kod i nie może rejestrować takich rzeczy, jak logika lub interakcje z innymi programami. Może to być również trudne w użyciu w przypadku dłuższych makr – może się okazać, że wcześniej tworzysz storyboardy, aby uniknąć kosztownych błędów.
Ten artykuł ma na celu pomóc Ci rozpocząć kodowanie makr od podstaw w VBA. Dowiesz się, gdzie są przechowywane makra, napiszesz podstawowe makro i nauczysz się podstaw programowania w VBA przy użyciu zmiennych, logiki i pętli.
Pierwsze kroki
VBA i Edytor Visual Basic
VBA lub Visual Basic for Applications to język, w którym pisane są makra. Wszystkie makra są przechowywane jako kod VBA, niezależnie od tego, czy zostały napisane ręcznie, czy utworzone za pomocą rejestratora makr.
Możesz uzyskać dostęp do całego kodu VBA w skoroszycie za pomocą Edytora Visual Basic. Jest to specjalny edytor tekstu i debuger wbudowany we wszystkie aplikacje biurowe, w tym Excel. Zazwyczaj otwierasz ten edytor za pomocą ALT+F11 skrót klawiaturowy w programie Excel, ale można również uzyskać do niego dostęp z programu Excel Deweloper zakładka, jeśli masz ją włączoną.
Eksplorator projektów
ten Eksplorator projektów to okno w edytorze VB, które pokazuje wszystkie elementy, które mogą zawierać kod VBA. Jeśli nie widzisz tego okna, naciśnij F5 aby się pojawił lub wybierz Eksplorator projektów od Pogląd menu.
Dwukrotne kliknięcie elementu w Eksploratorze projektów spowoduje wyświetlenie kodu tego elementu. Istnieje kilka typów elementów, które mogą pojawić się w Eksploratorze projektów:
- Zeszyty ćwiczeń
- Arkusze robocze
- Formularze użytkownika
- Moduły klas
- Moduły (makra są przechowywane w tych elementach)
Chociaż wszystkie te typy elementów mogą zawierać kod VBA, najlepszym rozwiązaniem jest kodowanie makr w modułach.
Tworzenie pierwszego makra
Korzystanie z listy makr
Lista makr zawiera wszystkie makra w skoroszycie. Z tej listy możesz edytować istniejące makro lub utworzyć nowe.
Aby utworzyć nowe makro za pomocą listy Makra:
- Wybierz kartę Deweloper i kliknij Makra (lub naciśnij ALT+F8)
- Wpisz nową nazwę makra, a następnie kliknij „Utwórz”
Po kliknięciu „Utwórz” pojawi się edytor VB, pokazujący nowo utworzone makro. W razie potrzeby Excel utworzy nowy moduł dla makra.
Ręcznie w edytorze VB
Nowe makro można dodać ręcznie bez listy Makra. Jest to lepsza opcja, jeśli chcesz określić moduł, w którym zapisywane jest makro.
Aby ręcznie dodać makro:
- Otwórz edytor VB (ALT+F11)
- Albo:
- Dodaj nowy moduł, klikając Wstaw > Moduł w menu (moduł otworzy się automatycznie)
-
- LUB kliknij dwukrotnie istniejący moduł w Eksploratorze projektów, aby go otworzyć
- W module wpisz kod nowego makra
Sub MojeMakro() Koniec Sub
Te dwie linie wskazują początek i koniec makra o nazwie „MojeMakro” (zwróć uwagę na nawiasy, które są wymagane). Pojawi się to w oknie dialogowym "Wyświetl makra" w programie Excel i można je przypisać do przycisku (nawet jeśli jeszcze nic nie robi).
Dodaj trochę kodu do makra
Teraz dodajmy trochę kodu między wierszami „Sub” i „End Sub”, aby to makro faktycznie coś zrobiło:
Sub MojeMakro() Zakres („A1”). Wartość = „Witaj świecie!” Napis końcowy
Podstawowe struktury kodu
Obiekt zakresu
Excel VBA używa obiektu zakresu do reprezentowania komórek w arkuszu. W powyższym przykładzie tworzony jest obiekt Range z kodem Zakres („A1”) aby uzyskać dostęp do wartości komórki A1.
Obiekty zakresu są używane głównie do ustawiania wartości komórek:
Zakres („A1”). Wartość = 1
Zakres(„A1”).Wartość = „Pierwsza komórka”
Zauważ, że definiując wartości komórek jako liczby, po prostu wpisujesz liczbę, ale podczas wprowadzania tekstu musisz otoczyć tekst cytatami.
Zakresy mogą być również używane do uzyskiwania dostępu do wielu właściwości komórek, takich jak czcionka, obramowania, formuły i nie tylko.
Na przykład możesz ustawić czcionkę komórki na pogrubioną w ten sposób:
Zakres („A1”). Czcionka pogrubiona = Prawda
Możesz także ustawić formułę komórki:
Zakres („A1”). Formuła = „=Suma(A2:A10)”
W Excelu możesz zaznaczyć blok komórek kursorem (powiedzmy od A1 do D10) i ustawić je wszystkie na pogrubienie. Obiekty zakresu mogą uzyskiwać dostęp do bloków komórek w następujący sposób:
Zakres („A1:D10”). Czcionka Pogrubiona = Prawda
Możesz także odwoływać się do kilku komórek/bloków jednocześnie:
Zakres(„A1:D10,A12:D12,G1”).Czcionka.Pogrubienie = Prawda
Format tego jest taki sam, jak format, którego używasz podczas wybierania komórek dla formuły SUMA() w programie Excel. Każdy blok jest oddzielony przecinkiem, a bloki są oznaczone lewą górną i prawą dolną komórką oddzielone dwukropkiem.
Wreszcie obiekty Range mają wbudowane metody wykonywania typowych operacji na arkuszu. Na przykład możesz chcieć skopiować niektóre dane z jednego miejsca do drugiego. Oto przykład:
Zakres („A1:D10”). Kopiuj zakres („F1”). PasteSpecial xlPasteValues Range(„F1”).
Spowoduje to skopiowanie komórek A1: D10 do schowka, a następnie wykonanie PasteSpecial() rozpoczynającej się w komórce C1 - tak jak robisz to ręcznie w programie Excel. Zauważ, że ten przykład pokazuje, jak używać PasteSpecial() do wklejania tylko wartości i formatów - istnieją parametry dla wszystkich opcji, które zobaczysz w oknie dialogowym Wklej specjalnie.
Oto przykład wklejania „Wszystko” do innego arkusza roboczego:
Zakres („A1:D10”). Kopiuj arkusze („Arkusz2”). Zakres („A1”).
Jeśli Oświadczenia
Z Jeśli oświadczenie, możesz sprawić, że sekcja kodu będzie uruchamiana tylko „jeśli” określone stwierdzenie jest prawdziwe.
Na przykład możesz chcieć pogrubić komórkę i pokolorować ją na czerwono, ale tylko „jeśli” wartość w komórce jest mniejsza niż 100.
If Range(„A4”).Wartość < 100 Then Range(„A4”).Font.Bold = True Range(„A4”).Interior.Color = vbRed End If
Właściwa struktura instrukcji If jest następująca (nawiasy kwadratowe wskazują opcjonalne komponenty):
Jeśli następnie
[W przeciwnym razie]
[W przeciwnym razie]
Zakończ, jeśli
Możesz dołączyć tyle W przeciwnym razie bloki, jak chcesz przetestować wiele warunków. Możesz również dodać W przeciwnym razie blok, który jest uruchamiany tylko wtedy, gdy żaden z pozostałych warunków w instrukcji If nie jest spełniony.
Oto kolejny przykład oparty na poprzednim, w którym komórka jest formatowana na kilka różnych sposobów w zależności od wartości:
If Range("A4").Value < 100 Then Range("A4").Font.Bold = True Range("A4").Interior.Color = vbRed ElseIf Range("A4").Value < 200 Then Range( "A4").Font.Bold = False Range("A4").Interior.Color = vbYellow Else Range("A4").Font.Bold = False Range("A4").Interior.Color = vbGreen End If
W powyższym przykładzie komórka nie jest pogrubiona w blokach ElseIf, gdzie wartość nie jest mniejsza niż 100. Możesz gniazdo Instrukcje If, aby uniknąć duplikowania kodu, takie jak:
If Range("A4").Value < 100 Then Range("A4").Font.Bold = True Range("A4").Interior.Color = vbRed Else Range("A4").Font.Bold = False ' odtłuszczenie czcionki tylko raz If Range("A4").Value < 200 Then Range("A4").Interior.Color = vbYellow Else Range("A4").Interior.Color = vbGreen End If End If
Zmienne
A Zmienny to fragment pamięci używany do przechowywania informacji tymczasowych podczas działania makra. Są często używane w pętlach jako iteratory lub do przechowywania wyniku operacji, której chcesz użyć kilka razy w makrze.
Oto przykład zmiennej i sposób jej użycia:
Sub ExtractSerialNumber() Dim strSerial As String ' to jest deklaracja zmiennej ' 'As String' oznacza, że ta zmienna jest przeznaczona do przechowywania tekstu ' konfigurując udawany numer seryjny: Range("A4").Value = "serial# 804567-88 ” ' przeanalizuj numer seryjny z komórki A4 i przypisz go do zmiennej strSerial = Mid(Range(„A4”).Value, 9) ' teraz użyj zmiennej dwukrotnie, zamiast dwukrotnie analizować numer seryjny Range(„ B4”). Wartość = strSerial MsgBox strSerial End Sub
W tym podstawowym przykładzie zmienna „strSerial” służy do wyodrębnienia numeru seryjnego z komórki A4 za pomocą funkcji Mid(), a następnie jest używana w dwóch innych miejscach.
Standardowy sposób na ogłosić zmienna ma następującą postać:
Ciemny jakakolwiek nazwa [Jak rodzaj]
- jakakolwiek nazwa to nazwa, którą zdecydujesz się nadać swojej zmiennej
- rodzaj to typ danych zmiennej
„[As rodzaj]” można pominąć - jeśli tak, to zmienna jest zadeklarowana jako typ Variant, który może przechowywać dowolne dane. Chociaż całkowicie poprawne, typy wariantów należy unikać, ponieważ mogą prowadzić do nieoczekiwanych wyników, jeśli nie będziesz ostrożny.
Są zasady dla nazw zmiennych. Muszą zaczynać się literą lub znakiem podkreślenia, nie mogą zawierać spacji, kropek, przecinków, cudzysłowów ani znaków „! @ i $ #”.
Oto kilka przykładów deklaracji zmiennych:
Dim strFilename As String ' styl dobrej nazwy - opisowy i używa prefiksu Dim i As Long ' styl złej nazwy - akceptowalny tylko dla niektórych iteratorów Dim SalePrice As Double ' poprawny styl nazwy - opisowy, ale nie używa przedrostka Dim iCounter ' dobra nazwa - niezbyt opisowy, używa prefiksu, brak typu danych
Wszystkie te przykłady używają nieco innych schematów nazewnictwa, ale wszystkie są poprawne. Nie jest złym pomysłem, aby poprzedzić nazwę zmiennej krótką formą jej typu danych (jak w niektórych z tych przykładów), ponieważ sprawia, że kod jest bardziej czytelny na pierwszy rzut oka.
VBA zawiera wiele podstawowych typy danych. Najpopularniejsze z nich to:
- Strunowy (używany do przechowywania danych tekstowych)
- Długie (używane do przechowywania liczb całkowitych, tj. bez miejsc po przecinku)
- Podwójnie (używany do przechowywania liczb zmiennoprzecinkowych, tj. miejsc dziesiętnych)
Pełną listę wewnętrznych typów danych VBA można znaleźć tutaj: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Zmienne obiektu zakresu
Możliwe jest również tworzenie zmiennych, które odwołują się również do obiektów zakresu. Jest to przydatne, jeśli chcesz odwoływać się do określonego zakresu w swoim kodzie w kilku miejscach - w ten sposób, jeśli chcesz zmienić zakres, wystarczy go zmienić w jednym miejscu.
Kiedy tworzysz zmienną obiektu Range, musisz „ustawić” ją na instancję Range. Na przykład:
Dim rMyRange Jako Zakres Ustaw rMyRange = Zakres(“A1:A10;D1:J10”)
Pozostawienie instrukcji „Set” podczas przypisywania zmiennej Range spowoduje błąd.
Pętle
Pętle to bloki, które powtarzają zawarty w nich kod określoną liczbę razy. Przydają się do zmniejszenia ilości kodu, który trzeba napisać, i pozwalają napisać jeden fragment kodu, który wykonuje te same czynności na wielu różnych powiązanych elementach.
W następnym
A W następnym blok to pętla, która powtarza się określoną liczbę razy. Używa zmiennej jako an iterator aby policzyć, ile razy został uruchomiony, a ta zmienna iteratora może być używana wewnątrz pętli. To sprawia, że pętle For-Next są bardzo przydatne do iteracji przez komórki lub tablice.
Oto przykład, który przechodzi przez komórki w wierszach od 1 do 100, kolumnie 1 i ustawia ich wartości na wartość zmiennej iteratora:
Dim i As Long For i = 1 do 100 Cells(i, 1)).Value = i Next i
Wiersz „For i = 1 to 100” oznacza, że pętla zaczyna się od 1 i kończy po 100. Możesz ustawić dowolne liczby początkowe i końcowe; możesz również użyć zmiennych dla tych liczb.
Domyślnie pętle For-Next liczą się jako 1. Jeśli chcesz liczyć inną liczbą, możesz napisać pętlę z wyraźnym Krok klauzula:
Dla i = 5 do 100 Krok 5
Ta pętla rozpocznie się od 5, a następnie doda 5 do „i” za każdym razem, gdy pętla się powtarza (więc „i” będzie wynosić 10 przy drugim powtórzeniu, 15 przy trzecim itd.).
Za pomocą Krok, możesz też zrobić pętlę odliczającą wstecz:
Dla i = 100 do 1 Krok -1
Możesz także gniazdo Pętle For-Next. Każdy blok wymaga własnej zmiennej do zliczania, ale możesz używać tych zmiennych w dowolnym miejscu. Oto przykład tego, jak jest to przydatne w Excel VBA:
Dim i As Long, j As Long For i = 1 To 100 For j = 1 To 100 Cells(i, j).Value = i * j Next j Next i
Pozwala to na przeglądanie zarówno wierszy, jak i kolumn.
OSTRZEŻENIE: chociaż jest to dozwolone, NIGDY nie należy MODYFIKOWAĆ zmiennej iteratora wewnątrz bloku For-Next, ponieważ używa tego iteratora do śledzenia pętli. Modyfikacja iteratora może spowodować nieskończoną pętlę i zawiesić makro. Na przykład:
Dla i = 1 Do 100 i = 1 Dalej i
W tej pętli „I” nigdy nie przekroczy 2 przed zresetowaniem do 1, a pętla będzie się powtarzać w nieskończoność.
Dla każdego
Dla każdego bloki są bardzo podobne do bloków For-Next, z wyjątkiem tego, że nie używają licznika do określenia, ile razy zapętlają się. Zamiast tego blok For-Each pobiera „kolekcję” obiektów (takich jak zakres komórek) i uruchamia się tyle razy, ile jest obiektów w tej kolekcji.
Oto przykład:
Dim r As Range For Each r In Range("A15:J54") If r.Value > 0 Then r.Font.Bold = True End If Next r
Zwróć uwagę na użycie zmiennej obiektu Range „r”. Jest to zmienna iteratora używana w pętli For-Each - za każdym razem, gdy przechodzi ona przez pętlę, „r” otrzymuje odwołanie do następnej komórki w zakresie.
Zaletą korzystania z pętli For-Each w Excel VBA jest to, że można przechodzić przez wszystkie komórki w zakresie bez zagnieżdżania pętli. Może to być przydatne, jeśli musisz przejść przez wszystkie komórki w złożonym zakresie, takim jak Zasięg („A1:D12,J13, M1:Y12”).
Jedną z wad pętli For-Each jest brak kontroli nad kolejnością przetwarzania komórek. Mimo że w praktyce Excel przejdzie przez komórki w kolejności, W teorii może przetwarzać komórki w całkowicie losowej kolejności. Jeśli musisz przetwarzać komórki w określonej kolejności, zamiast tego użyj pętli For-Next.
Wykonaj pętlę
Podczas gdy bloki For-Next używają liczników, aby wiedzieć, kiedy przestać, Wykonaj pętlę bloki działają, dopóki warunek nie zostanie spełniony. Aby to zrobić, użyj an Dopóki klauzula na początku lub na końcu bloku, która sprawdza warunek i powoduje zatrzymanie pętli po spełnieniu tego warunku.
Przykład:
Dim str As String str = "Buffalo" Do until str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & " " & "Buffalo" Loop Range("A1").Value = str
W tej pętli „Buffalo” jest łączony z „str” za każdym razem w pętli, dopóki nie zostanie dopasowany do oczekiwanego zdania. W tym przypadku test jest wykonywany na początku pętli - gdyby 'str' było już oczekiwanym zdaniem (co nie jest, ponieważ nie zaczęliśmy go w ten sposób, ale jeśli) pętla nawet by się nie uruchomiła .
Możesz sprawić, by pętla działała przynajmniej raz, przenosząc klauzulę Until na koniec, w ten sposób:
Wykonaj str = str & " " & "Buffalo" Pętla Do str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Możesz użyć dowolnej wersji, która ma sens w Twoim makrze.
OSTRZEŻENIE: możesz wywołać nieskończoną pętlę z blokiem Do-Loop, jeśli warunek Until nigdy nie zostanie spełniony. Zawsze pisz swój kod tak, aby warunek Do na pewno został spełniony, gdy użyjesz tego typu pętli.
Co dalej?
Kiedy już opanujesz podstawy, dlaczego nie spróbować nauczyć się bardziej zaawansowanych technik? Nasz samouczek pod adresem https://easyexcel.net/excel/learn-vba-tutorial/ będzie bazował na wszystkim, czego się tutaj nauczyłeś i poszerzy Twoje umiejętności dzięki wydarzeniom, formularzom użytkownika, optymalizacji kodu i wielu innym!
