Samouczek dla początkujących na temat pisania makr VBA w programie Excel (i dlaczego warto się uczyć)

Makra programu Excel mogą zaoszczędzić mnóstwo czasu, automatyzując często używane procesy programu Excel. Ale makra są w rzeczywistości dość ograniczone. Łatwo jest pomylić się z narzędziem do nagrywania, a proces nagrywania jest niewygodny.

Używanie VBA do tworzenia makr daje znacznie więcej mocy. Możesz dokładnie powiedzieć Excelowi, co ma robić i jak to zrobić. Otrzymasz również dostęp do znacznie większej liczby funkcji i możliwości. Jeśli regularnie korzystasz z programu Excel, warto nauczyć się tworzyć makra VBA.

Zaczniemy od podstaw.

Co to jest VBA?

VBA jest Visual Basic for Applications, język programowania, którego można używać w wielu aplikacjach Microsoft. Visual Basic to język programowania, a VBA jest wersją aplikacji. (Microsoft wycofał Visual Basic w 2008 roku, ale VBA nadal działa dobrze).

Na szczęście dla nie-programistów VBA jest bardzo prosty, a interfejs, którego używasz do edycji, oferuje wiele pomocy. Wiele poleceń, których będziesz używać, to wyskakujące sugestie i automatyczne uzupełnianie, pomagając szybko uruchomić skrypt.

Mimo to VBA wymaga czasu, aby się przyzwyczaić.

Zalety makr VBA w programie Excel

Jeśli VBA jest trudniejsze niż nagrywanie makra, dlaczego miałbyś go używać? Krótka odpowiedź jest taka, że ​​zyskujesz o wiele więcej mocy dzięki makrom VBA.

Zamiast klikać wokół arkusza kalkulacyjnego i rejestrować te kliknięcia, możesz uzyskać dostęp do pełnego zakresu funkcji i możliwości programu Excel. Musisz tylko wiedzieć, jak z nich korzystać.

A kiedy poczujesz się lepiej z VBA, możesz robić wszystko, co możesz zrobić w zwykłym makrze w znacznie krótszym czasie. Wyniki będą również bardziej przewidywalne, jak mówisz Excelowi dokładnie co robić. W ogóle nie ma dwuznaczności.

Po utworzeniu makra VBA można go łatwo zapisać i udostępnić, aby każdy mógł z niego skorzystać. Jest to szczególnie przydatne, gdy pracujesz z wieloma osobami, które muszą robić te same rzeczy w programie Excel.

Spójrzmy na proste makro VBA, aby zobaczyć, jak to działa.

Przykład makra VBA w programie Excel

Spójrzmy na proste makro. Nasz arkusz kalkulacyjny zawiera nazwiska pracowników, numer sklepu, w którym pracują pracownicy, oraz ich kwartalną sprzedaż.

To makro doda kwartalną sprzedaż z każdego sklepu i zapisze te sumy do komórek w arkuszu kalkulacyjnym (jeśli nie masz pewności, jak uzyskać dostęp do okna dialogowego VBA, zapoznaj się z naszym przewodnikiem po VBA tutaj):

Sub StoreSales () Dim Sum1 As Currency Dim Sum2 As Currency Dim Sum3 As Currency Dim Sum4 As Currency For Each Cell In Range („C2: C51”) Cell.Activate If IsEmpty (Cell) Następnie Exit For If ActiveCell.Offset (0, -1) = 1 Następnie Sum1 = Sum1 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 2 Następnie Sum2 = Sum2 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 3 Następnie Sum3 = Sum3 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 4 Następnie Sum4 = Sum4 + Cell.Value End If Next Cell Range („F2”). Value = Sum1 Range („F3”). Value = Sum2 Range („ F4 ”). Wartość = Sum3 Zakres („ F5 ”). Wartość = Sum4 End Sub

Może to wyglądać na długie i skomplikowane, ale podzielimy to, abyś mógł zobaczyć poszczególne elementy i dowiedzieć się trochę o podstawach VBA.

Deklaracja Sub

Na początku modułu mamy “Sub StoreSales ()”. To definiuje nowy sub o nazwie StoreSales.

Możesz także zdefiniować funkcje - różnica polega na tym, że funkcje mogą zwracać wartości, a SUB nie mogą (jeśli znasz inne języki programowania, SUB są odpowiednikiem metod). W takim przypadku nie musimy zwracać wartości, dlatego używamy sub.

Na końcu modułu mamy “Napis końcowy,” co mówi Excelowi, że skończyliśmy z tym makrem VBA.

Deklarowanie zmiennych

Pierwsze wiersze kodu w naszym skrypcie zaczynają się od “Ciemny.” Dim jest poleceniem VBA do deklarowania zmiennej.

Więc “Dim Sum 1” tworzy nową zmienną o nazwie “Suma 1.” Musimy jednak powiedzieć Excelowi, jaka to zmienna. Musimy wybrać typ danych. W VBA istnieje wiele różnych typów danych - pełną listę można znaleźć w dokumentach pomocy Microsoftu.

Ponieważ nasze makro VBA będzie obsługiwać waluty, używamy typu danych Waluta.

Wyrok “Dim Sum1 jako waluta” mówi Excelowi, aby utworzył nową zmienną walutową o nazwie Sum1. Każda deklarowana zmienna musi mieć “Tak jak” instrukcja informująca program Excel o jego typie.

Uruchamianie pętli For

Pętle to jedne z najpotężniejszych rzeczy, które można tworzyć w dowolnym języku programowania. Jeśli nie jesteś zaznajomiony z pętlami, zapoznaj się z tym objaśnieniem pętli Do-While Jak pętle Do-While działają w programowaniu komputerowym Jak pętle Do-While działają w programowaniu komputerowym Pętle są jednym z pierwszych rodzajów sterowania, których nauczysz się w programowaniu . Prawdopodobnie wiesz o pętlach while i for, ale co osiąga pętla do-while? . W tym przykładzie używamy pętli For, która jest również opisana w artykule.

Oto jak wygląda pętla:

For Each Cell in Range („C2: C51”) [kilka rzeczy] Next Cell

To mówi Excelowi, aby iterował przez komórki w określonym przez nas zakresie. Użyliśmy obiektu Range Zrozumienie komórek Excela a funkcje zakresu w VBA Zrozumienie komórek Excela a funkcje zakresu w VBA Korzystanie z funkcji zakresu i komórek w programie Excel może być bardzo mylące. Oto, w jaki sposób możesz z nich korzystać w sposób, którego prawdopodobnie nigdy sobie nie wyobrażałeś, za pomocą VBA, który jest specyficznym typem obiektu w VBA. Gdy używamy go w ten sposób Range-Range (“C2: C51”) - mówi Excelowi, że jesteśmy zainteresowani tymi 50 komórkami.

“Dla każdego” mówi Excelowi, że zrobimy coś z każdą komórką w zakresie. “Następna komórka” pojawia się po wszystkim, co chcemy zrobić, i mówi Excelowi, aby zaczął pętlę od początku (zaczynając od następnej komórki).

Mamy również następujące oświadczenie: “Jeśli IsEmpty (komórka), to wyjdź dla.”

Czy możesz zgadnąć, co to robi?

Uwaga: Ściśle mówiąc, użycie pętli While mogło być lepszym wyborem. 4 Błędy, których można uniknąć podczas programowania makr programu Excel za pomocą VBA 4 Błędy, których można uniknąć, programując makra programu Excel za pomocą VBA Prosty kod i makra są kluczem do supermoce programu Microsoft Excel. Nawet osoby niebędące programistami mogą łatwo dodać imponującą funkcjonalność do swoich arkuszy kalkulacyjnych dzięki Virtual Basics for Applications (VBA). Po prostu unikaj błędów w programowaniu dla początkujących! . Jednak ze względu na nauczanie postanowiłem użyć pętli For z wyjściem.

Oświadczenia „Jeśli-to-jeszcze”

Rdzeń tego konkretnego makra znajduje się w instrukcjach If-Then-Else. Oto nasza sekwencja instrukcji warunkowych:

Jeśli ActiveCell.Offset (0, -1) = 1, to Sum1 = Sum1 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 2, a następnie Sum2 = Sum2 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 3 Następnie Sum3 = Sum3 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 4 Następnie Sum4 = Sum4 + Cell.Value End If

W większości przypadków prawdopodobnie zgadniesz, co robią te instrukcje. Jednak możesz nie znać ActiveCell.Offset. “ActiveCell.Offset (0, -1)” mówi Excelowi, aby sprawdził komórkę, która jest jedną kolumną na lewo od aktywnej komórki.

W naszym przypadku mówi to Excelowi, aby sprawdził kolumnę z numerem sklepu. Jeśli Excel znajdzie 1 w tej kolumnie, pobiera zawartość aktywnej komórki i dodaje ją do Sum1. Jeśli znajdzie 2, dodaje zawartość aktywnej komórki do Sum2. I tak dalej.

Excel przegląda wszystkie te instrukcje w kolejności. Jeśli instrukcja warunkowa Jak korzystać z instrukcji IF w Microsoft Excel Jak korzystać z instrukcji IF w Microsoft Excel Niezależnie od tego, czy jesteś doświadczonym ekspertem czy początkującym arkuszem kalkulacyjnym, zapoznaj się z tym przewodnikiem po instrukcjach IF w programie Excel. jest usatysfakcjonowany, uzupełnia instrukcję Then. Jeśli nie, przechodzi do następnego ElseIf. Jeśli dojdzie do końca i żaden z warunków nie zostanie spełniony, nie podejmie żadnych działań.

Połączenie pętli i warunków jest tym, co napędza to makro. Pętla mówi Excelowi, aby przejrzał każdą komórkę w zaznaczeniu, a warunki warunkowe mówią mu, co zrobić z tą komórką.

Zapisywanie wartości komórek

Wreszcie możemy zapisać wyniki naszych obliczeń w komórkach. Oto linie, których używamy do tego:

Zakres („F2”). Wartość = Zakres Sum1 („F3”). Wartość = Zakres Sum2 („F4”). Wartość = Zakres Sum3 („F5”). Wartość = Sum4

Z “.Wartość” i znakiem równości ustawiamy każdą z tych komórek na wartość jednej z naszych zmiennych.

I to wszystko! Mówimy Excelowi, że skończyliśmy pisać to Sub “Napis końcowy,” a makro VBA jest kompletne.

Kiedy uruchamiamy makro za pomocą Makra przycisk w Deweloper zakładkę, otrzymujemy nasze sumy:

Łączenie elementów składowych języka VBA w programie Excel

Kiedy po raz pierwszy spojrzysz na makro VBA powyżej, wygląda ono dość skomplikowane. Ale po rozbiciu go na części składowe logika staje się jasna. Jak każdy język skryptowy, przyzwyczajenie się do składni VBA zajmuje trochę czasu.

Ale dzięki praktyce rozwiniesz swoje słownictwo w języku VBA i będziesz w stanie pisać makra szybciej, dokładniej i przy znacznie większej mocy, niż kiedykolwiek mógłbyś je nagrać.

Gdy utkniesz, uruchomienie wyszukiwania Google to szybki sposób na uzyskanie odpowiedzi na pytania w języku VBA. Odwołanie Microsoft VBA do Excela może być pomocne, jeśli chcesz się nim przekopać w celu uzyskania odpowiedzi technicznej.

Po opanowaniu podstaw możesz zacząć używać VBA do wysyłania wiadomości e-mail z Excela, eksportowania zadań programu Outlook Jak eksportować zadania Outlooka do Excela za pomocą VBA Jak eksportować zadania Outlooka do Excela za pomocą VBA Niezależnie od tego, czy jesteś fan firmy Microsoft, jedną dobrą rzeczą, którą można powiedzieć o produktach MS Office, jest to, jak łatwa jest integracja każdego z nich ... i wyświetlanie informacji o komputerze Jak wyświetlić wszystkie informacje o komputerze za pomocą prostego Excel VBA Script Jak wyświetlić wszystkie informacje o komputerze za pomocą prostego skryptu Excel VBA Excel i 10 minut pracy dostarczy Ci bardziej szczegółowych informacji na temat komputera, niż kiedykolwiek myślałeś, że to możliwe. Brzmi zbyt dobrze, aby mogło być prawdziwe? To Windows, jeśli wiesz, jak go używać. .