Samouczek funkcji Excel VBA: Return, Call, Przykłady

Co to jest funkcja?

Funkcja to fragment kodu, który wykonuje określone zadanie i zwraca wynik. Funkcje są najczęściej używane do wykonywania powtarzalnych zadań, takich jak formatowanie danych wyjściowych, wykonywanie obliczeń itp.

Załóżmy, że jesteś rozwijanyping Program obliczający odsetki od pożyczki. Możesz utworzyć funkcję, która akceptuje kwotę pożyczki i okres spłaty. Funkcja może następnie wykorzystać kwotę pożyczki i okres spłaty do obliczenia odsetek i zwrócenia ich wartości.

Po co używać funkcji

Zalety korzystania z funkcji są takie same, jak w przypadku podprogramów: dzielą długi program na łatwe do opanowania części, można je ponownie wykorzystać w dowolnym miejscu projektu, a opisowa nazwa dokumentuje działanie kodu. Samouczek podprogramów VBA w programie Excel pokrywa te świadczenia w całości.

Zasady nazewnictwa funkcji

Zasady nazewnictwa są identyczne jak w przypadku podprogramów. Nazwa funkcji nie może zawierać spacji, musi zaczynać się od litery lub znaku podkreślenia i nie może być zarezerwowana. VBA słowa kluczowego, takiego jak Funkcja, Prywatne lub Koniec.

Składnia VBA do deklarowania funkcji

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
    myFunction = arg1 + arg2
End Function

TUTAJ w składni,

Jak zwrócić wartość i ustawić typ danych funkcji

Funkcja ma jedno zadanie, którego nie ma podprogram: zwraca wartość. Kontrolują ją dwa szczegóły, które łatwo przeoczyć.

Pierwszym jest przypisanie. VBA nie ma instrukcji Return. Zamiast tego przypisujesz wynik do nazwy funkcji, dlatego linia brzmi: mojaFunkcja = arg1 + arg2Jeśli to zadanie nigdy nie zostanie wykonane, funkcja po cichu zwróci pustą wartość zamiast zgłosić błąd, więc każda gałąź kodu musi ją ustawić.

Drugim jest typ zwracany. Powyższa deklaracja kończy się na nawiasie zamykającym, więc funkcja zwraca typ Variant. Dodanie klauzuli As po nawiasach ustala typ, co jest szybsze, zużywa mniej pamięci i pozwala kompilatorowi wykryć niezgodność.

💡 Wskazówka: Użyj funkcji Exit Function, aby wyjść wcześniej po ustawieniu wartości zwracanej, w taki sam sposób, w jaki Exit Sub wychodzi z podprogramu.

Funkcja zademonstrowana na przykładzie:

Funkcje są bardzo podobne do podprogramu. Główną różnicą między podprogramem a funkcją jest to, że funkcja zwraca wartość, gdy jest wywoływana. Podczas gdy podprogram nie zwraca wartości, gdy jest wywoływany. Powiedzmy, że chcesz dodać dwie liczby. Możesz utworzyć funkcję, która akceptuje dwie liczby i zwraca sumę liczb.

1. Utwórz interfejs użytkownika
2. Dodaj funkcję
3. Napisz kod przycisku polecenia
4. Przetestuj kod

Krok 1) Interfejs użytkownika

Dodaj przycisk polecenia do arkusza, jak pokazano poniżej

Ustaw następujące właściwości CommandButton1 na następujące.

Twój interfejs powinien teraz wyglądać następująco

Krok 2) Kod funkcji.

1. Naciśnij Alt + F11, aby otworzyć okno kodu
2. Dodaj następujący kod

Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)
    addNumbers = firstNumber + secondNumber
End Function

TUTAJ w kodzie,

Krok 3) Napisz Code który wywołuje funkcję

1. Kliknij prawym przyciskiem myszy przycisk DodajNumbers przycisk polecenia
2. Wybierz Widok Code
3. Dodaj następujący kod

Private Sub btnAddNumbers_Click()
    MsgBox addNumbers(2, 3)
End Sub

TUTAJ w kodzie,

Krok 4) Uruchom program, otrzymasz następujące wyniki

Pobierz Excel zawierający powyższy kod

Pobierz powyższy plik Excel Code

Przycisk powyżej wywołuje funkcję z kodu VBA. Funkcję można również wywołać z samego arkusza kalkulacyjnego, bez użycia żadnego przycisku.

Jak używać funkcji VBA w komórce arkusza kalkulacyjnego

Funkcję napisaną w VBA można wpisać do komórki dokładnie tak samo, jak SUMA lub WYSZUKAJ.PIONOWO. W Excelu nazywa się to funkcją zdefiniowaną przez użytkownika, czyli UDF, i to właśnie dlatego wiele osób uczy się funkcji przed podprogramami. Muszą być spełnione trzy warunki.

• Umieść go w standardowym module: Wstaw, Moduł w edytorze. Funkcja zapisana za arkuszem kalkulacyjnym lub w ThisWorkbook nie jest widoczna na pasku formuł.
• Ogłoś to jako publiczne: W powyższym przykładzie użyto słowa kluczowego „Prywatne”, które ukrywa je w programie Excel. Domyślnie jest to „Publiczne”, więc wystarczy usunąć słowo kluczowe.
• Zwróć wartość, niczego nie zmieniaj: Funkcja UDF nie może formatować komórek, usuwać wierszy ani zapisywać danych w innej komórce. Program Excel blokuje te działania, a komórka wyświetla komunikat #VALUE!.

Poniższa funkcja konwertuje temperaturę i można jej używać w dowolnym miejscu arkusza.

Public Function CelsiusToF(ByVal Celsius As Double) As Double
    CelsiusToF = (Celsius * 9 / 5) + 32
End Function

Zapisz skoroszyt jako plik .xlsm z włączoną obsługą makr, a następnie wpisz =CelsjuszaDoF(A1) do dowolnej komórki. Wynik jest aktualizowany przy każdej zmianie komórki A1, a nazwa pojawia się na liście autouzupełniania formuł w kategorii Zdefiniowane przez użytkownika. Ponieważ skoroszyt zawiera teraz makra, każdy, kto go otwiera, musi włączyć zawartość, zanim formuła zwróci wartość zamiast #NAME?.

Typowe błędy funkcji VBA i jak je naprawić

Cztery problemy wyjaśniają większość sytuacji, w których funkcje kompilują się, ale zwracają błędną odpowiedź.

• Funkcja zwraca wartość Empty lub 0: Wynik nigdy nie został przypisany do nazwy funkcji lub jedna z gałęzi instrukcji If pomija przypisanie. Ustaw wartość zwracaną dla każdej ścieżki.
• #NAZWA? w komórce arkusza kalkulacyjnego: Funkcja jest prywatna, znajduje się w module arkusza, a nie w module standardowym, lub skoroszyt został zapisany bez włączonych makr.
• Przepełnienie argumentami całkowitymi: W przykładzie użyto As Integer, który zatrzymuje się na 32 767. Zmień oba parametry i typ zwracany na Long dla dowolnych danych rzeczywistych.
• Zmieniony argument zaskakuje dzwoniącego: Pominięcie ByVal powoduje, że VBA przekazuje samą zmienną, dzięki czemu funkcja może zmienić wartość wywołującego. Użyj ByVal, chyba że chcesz uzyskać taki efekt.

FAQ