Procedury „funkcyjne” i „podrzędne” w VBA

Wbudowane funkcje VBA

Zanim zaczniesz tworzyć własne funkcje VBA, warto wiedzieć, że Excel VBA ma bogatą kolekcję gotowych wbudowanych funkcji, których możesz używać podczas pisania kodu.

Listę tych funkcji można wyświetlić w edytorze VBA:

• Otwórz skoroszyt programu Excel i uruchom edytor VBA (kliknij, aby to zrobić Alt + F11), a następnie naciśnij F2.
• Wybierz bibliotekę z listy rozwijanej w lewym górnym rogu ekranu VBA.
• Pojawi się lista wbudowanych klas i funkcji VBA. Kliknij nazwę funkcji, aby wyświetlić jej krótki opis na dole okna. pilny F1 otworzy stronę pomocy online dla tej funkcji.

Ponadto pełną listę wbudowanych funkcji VBA wraz z przykładami można znaleźć w Visual Basic Developer Center.

Procedury niestandardowe „Funkcja” i „Sub” w VBA

W programie Excel Visual Basic zestaw poleceń wykonujących określone zadanie jest umieszczany w procedurze. Funkcjonować (funkcja) lub Podłoże (podprogram). Główna różnica między procedurami Funkcjonować и Podłoże czy ta procedura? Funkcjonować zwraca wynik, procedura Podłoże - nie.

Dlatego jeśli potrzebujesz wykonać czynności i uzyskać jakiś wynik (na przykład zsumować kilka liczb), zwykle stosuje się procedurę Funkcjonować, a aby po prostu wykonać pewne czynności (na przykład zmienić formatowanie grupy komórek), należy wybrać procedurę Podłoże.

Argumenty

Do procedur VBA można przekazywać różne dane za pomocą argumentów. Lista argumentów jest określana podczas deklarowania procedury. Na przykład procedura Podłoże w VBA dodaje podaną liczbę całkowitą (Integer) do każdej komórki w wybranym zakresie. Możesz przekazać tę liczbę do procedury za pomocą argumentu takiego:

Sub AddToCells(i As Integer) ... End Sub

Pamiętaj, że mając argumenty za procedurami Funkcjonować и Podłoże w VBA jest opcjonalne. Niektóre procedury nie wymagają argumentów.

Argumenty opcjonalne

Procedury VBA mogą mieć opcjonalne argumenty. Są to argumenty, które użytkownik może określić, jeśli chce, a jeśli zostaną pominięte, procedura wykorzystuje dla nich wartości domyślne.

Wracając do poprzedniego przykładu, aby argument będący liczbą całkowitą dla funkcji był opcjonalny, zostałby zadeklarowany w następujący sposób:

Sub AddToCells (opcjonalnie i As Integer = 0)

W tym przypadku argument całkowity i wartość domyślna to 0.

W procedurze może być kilka opcjonalnych argumentów, z których wszystkie są wymienione na końcu listy argumentów.

Przekazywanie argumentów według wartości i przez odwołanie

Argumenty w VBA można przekazać do procedury na dwa sposoby:

• ByVal – przekazywanie argumentu przez wartość. Oznacza to, że tylko wartość (tj. kopia argumentu) jest przekazywana do procedury, a zatem wszelkie zmiany wprowadzone w argumencie wewnątrz procedury zostaną utracone po zakończeniu procedury.
• wg ref – przekazywanie argumentu przez odwołanie. Oznacza to, że do procedury przekazywany jest rzeczywisty adres lokalizacji argumentu w pamięci. Wszelkie zmiany wprowadzone w argumencie wewnątrz procedury zostaną zapisane po zakończeniu procedury.

Korzystanie ze słów kluczowych ByVal or wg ref w deklaracji procedury można określić, w jaki sposób argument jest przekazywany do procedury. Pokazują to poniższe przykłady:

Sub AddToCells(ByVal i As Integer) ... End Sub

Sub AddToCells(ByRef i As Integer) ... End Sub

Pamiętaj, że argumenty w VBA są domyślnie przekazywane przez referencję. Innymi słowy, jeśli słowa kluczowe nie są używane ByVal or wg ref, argument zostanie przekazany przez odwołanie.

Przed przystąpieniem do procedur Funkcjonować и Podłoże bardziej szczegółowo, przydatne będzie ponowne przyjrzenie się cechom i różnicom między tymi dwoma rodzajami procedur. Poniżej znajdują się krótkie omówienia procedur VBA Funkcjonować и Podłoże i proste przykłady są pokazane.

Procedura VBA «Funkcja»

Edytor VBA rozpoznaje procedurę Funkcjonowaćgdy napotka grupę poleceń umieszczoną między następującymi instrukcjami otwierającymi i zamykającymi:

Funkcja ... Zakończ funkcję

Jak wspomniano wcześniej, procedura Funkcjonować w VBA (w przeciwieństwie do Podłoże) zwraca wartość. Do zwracanych wartości obowiązują następujące zasady:

• Typ danych zwracanej wartości musi być zadeklarowany w nagłówku procedury Funkcjonować.
• Zmienna zawierająca zwracaną wartość musi mieć taką samą nazwę jak procedura Funkcjonować. Zmienna ta nie musi być osobno deklarowana, ponieważ zawsze istnieje jako integralna część procedury. Funkcjonować.

Dobrze ilustruje to poniższy przykład.

Przykład funkcji VBA: wykonywanie operacji matematycznych na 3 liczbach

Poniżej znajduje się przykład kodu procedury VBA Funkcjonować, który przyjmuje trzy argumenty typu Podwójne Geometryczne (liczby zmiennoprzecinkowe podwójnej precyzji). W rezultacie procedura zwraca kolejną liczbę typu Podwójne Geometrycznerówna sumie dwóch pierwszych argumentów minus trzeci argument:

Funkcja SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double SumMinus = dNum1 + dNum2 - dNum3 Funkcja zakończenia

Ta bardzo prosta procedura VBA Funkcjonować ilustruje sposób przekazywania danych do procedury za pomocą argumentów. Widać, że typ danych zwracany przez procedurę jest zdefiniowany jako Podwójne Geometryczne (słowa mówią jako podwójne po liście argumentów). Ten przykład pokazuje również, jak wynik procedury Funkcjonować przechowywane w zmiennej o tej samej nazwie co nazwa procedury.

Wywołanie procedury VBA „Funkcja”

Jeśli powyższa prosta procedura Funkcjonować wstawiony do modułu w edytorze Visual Basic, można go wywołać z innych procedur VBA lub użyć w arkuszu w skoroszycie programu Excel.

Wywołaj procedurę VBA „Funkcja” z innej procedury

Procedura Funkcjonować można wywołać z innej procedury VBA, po prostu przypisując tę ​​procedurę do zmiennej. Poniższy przykład pokazuje wywołanie procedury Suminus, który został zdefiniowany powyżej.

Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub

Wywołaj procedurę VBA „Funkcja” z arkusza roboczego

Procedura VBA Funkcjonować można wywołać z arkusza programu Excel w taki sam sposób, jak każdą inną wbudowaną funkcję programu Excel. Dlatego procedura utworzona w poprzednim przykładzie Funkcjonować - Suminus można wywołać, wprowadzając do komórki arkusza następujące wyrażenie:

=SumMinus(10, 5, 2)

Procedura VBA «Sub»

Edytor VBA rozumie, że przed nim jest procedura Podłożegdy napotka grupę poleceń umieszczoną między następującymi instrukcjami otwierającymi i zamykającymi:

Sub ... Koniec Sub

Procedura VBA „Sub”: Przykład 1. Wyśrodkowanie i zmiana rozmiaru czcionki w wybranym zakresie komórek

Rozważ przykład prostej procedury VBA Podłoże, którego zadaniem jest zmiana formatowania wybranego zakresu komórek. Komórki są wyśrodkowane (zarówno w pionie, jak iw poziomie), a rozmiar czcionki jest zmieniany na określony przez użytkownika:

Sub Format_Centered_And_Sized(Opcjonalnie iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Tej procedury Podłoże wykonuje akcje, ale nie zwraca wyniku.

W tym przykładzie zastosowano również argument opcjonalny Rozmiar czcionki. Jeśli argument Rozmiar czcionki nie przeszedł do procedury Podłoże, to jego domyślna wartość to 10. Jeśli jednak argument Rozmiar czcionki przeszedł do procedury Podłoże, to wybrany zakres komórek zostanie ustawiony na rozmiar czcionki określony przez użytkownika.

Procedura podrzędna VBA: Przykład 2: Wyrównaj do środka i pogrubioną czcionkę w wybranym zakresie komórek

Poniższa procedura jest podobna do omówionej przed chwilą, ale tym razem zamiast zmiany rozmiaru stosuje pogrubiony styl czcionki do wybranego zakresu komórek. To jest przykładowa procedura Podłoże, który nie przyjmuje argumentów:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Wywołanie procedury „Sub” w Excel VBA

Wywołaj procedurę VBA „Sub” z innej procedury

Aby wywołać procedurę VBA Podłoże z innej procedury VBA, musisz wpisać słowo kluczowe Numer Telefonu, nazwa procedury Podłoże a dalej w nawiasach argumenty procedury. Pokazuje to poniższy przykład:

Sub main() Wywołaj Format_Centered_And_Sized(20) End Sub

Jeśli procedura Format_Centered_And_Sized ma więcej niż jeden argument, muszą być oddzielone przecinkami. Lubię to:

Sub main() Wywołaj Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Wywołaj procedurę VBA „Sub” z arkusza roboczego

Procedura Podłoże nie można wprowadzić bezpośrednio do komórki arkusza Excel, jak można to zrobić za pomocą procedury Funkcjonowaćponieważ procedura Podłoże nie zwraca wartości. Jednak procedury Podłoże, które nie mają argumentów i są zadeklarowane jako Publiczne (jak pokazano poniżej) będą dostępne dla użytkowników arkusza. Tak więc, jeśli proste procedury omówione powyżej Podłoże wstawiona do modułu w Edytorze Visual Basic, procedura Format_Centered_And_Bold będą dostępne do wykorzystania w arkuszu programu Excel, a procedura Format_Centered_And_Sized – nie będzie dostępny, ponieważ ma argumenty.

Oto prosty sposób na uruchomienie (lub wykonanie) procedury Podłoże, dostępny z arkusza roboczego:

• Naciśnij przycisk Alt + F8 (naciśnij klawisz inny i trzymając go wciśnięty, naciśnij klawisz F8).
• Z wyświetlonej listy makr wybierz to, które chcesz uruchomić.
• Naciśnij przycisk run (biegać)

Aby wykonać procedurę Podłoże szybko i łatwo możesz przypisać do niego skrót klawiaturowy. Dla tego:

• Naciśnij przycisk Alt + F8.
• Z wyświetlonej listy makr wybierz to, do którego chcesz przypisać skrót klawiaturowy.
• Naciśnij przycisk parametry (Opcje) i w wyświetlonym oknie dialogowym wprowadź skrót klawiaturowy.
• Naciśnij przycisk OK i zamknij okno dialogowe Macro (Makro).

Uwaga: Przypisując skrót klawiaturowy do makra, upewnij się, że nie jest on standardowo używany w programie Excel (na przykład Ctrl + C). Jeśli wybierzesz już istniejący skrót klawiaturowy, zostanie on ponownie przypisany do makra, w wyniku czego użytkownik może przypadkowo uruchomić makro.

Zakres procedury VBA

W drugiej części tego samouczka omówiono zakres zmiennych i stałych oraz rolę słów kluczowych. Publiczne и Sprawy Prywatne. Te słowa kluczowe mogą być również używane z procedurami VBA:

Public Sub AddToCells(i As Integer) ... End Sub

Private Sub AddToCells(i As Integer) ... End Sub

Pamiętaj, że jeśli przed zadeklarowaniem procedury VBA Funkcjonować or Podłoże słowo kluczowe nie jest wstawione, domyślna właściwość jest ustawiona dla procedury Publiczne (oznacza to, że będzie dostępny wszędzie w tym projekcie VBA). Jest to w przeciwieństwie do deklaracji zmiennych, które domyślnie są Sprawy Prywatne.

Wczesne wyjście z procedur VBA „Funkcja” i „Sub”

Jeśli musisz przerwać wykonywanie procedury VBA Funkcjonować or Podłoże, nie czekając na jego naturalne zakończenie, to do tego są operatorzy Funkcja wyjścia и Exit Sub. Użycie tych operatorów pokazano poniżej na przykładzie prostej procedury. FunkcjonowaćA, który oczekuje na otrzymanie pozytywnego argumentu w celu wykonania dalszych operacji. Jeśli do procedury zostanie przekazana wartość nie-dodatnia, dalsze operacje nie będą mogły być wykonane, więc użytkownikowi powinien zostać wyświetlony komunikat o błędzie i procedura powinna zakończyć się natychmiast:

Funkcja VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 Jeśli sVAT_Rate <= 0 Następnie MsgBox „Oczekiwano dodatniej wartości sVAT_Rate, ale otrzymano” & sVAT_Rate Wyjdź z funkcji End If ... End Function

Należy pamiętać, że przed zakończeniem procedury Funkcjonować - VAT_Kwota, do kodu wstawiana jest wbudowana funkcja VBA MsgBox, który wyświetla użytkownikowi wyskakujące okienko z ostrzeżeniem.