Tworzenie funkcji zdefiniowanej przez użytkownika (UDF) w Excel VBA (Ultimate Guide)

Dzięki VBA możesz utworzyć niestandardową funkcję (nazywaną również funkcją zdefiniowaną przez użytkownika), której można używać w arkuszach roboczych tak jak zwykłych funkcji.

Są one pomocne, gdy istniejące funkcje programu Excel nie wystarczają. W takich przypadkach można utworzyć własną niestandardową funkcję definiowaną przez użytkownika (UDF), aby zaspokoić określone potrzeby.

W tym samouczku omówię wszystko na temat tworzenia i używania funkcji niestandardowych w VBA.

Jeśli interesuje Cię nauka VBA w prosty sposób, zajrzyj na mój Szkolenie Excel VBA online.

Co to jest procedura funkcji w VBA?

Procedura Function to kod VBA, który wykonuje obliczenia i zwraca wartość (lub tablicę wartości).

Korzystając z procedury funkcji, możesz utworzyć funkcję, której możesz używać w arkuszu (tak jak każda zwykła funkcja programu Excel, taka jak SUMA lub WYSZUKAJ.PIONOWO).

Po utworzeniu procedury Function przy użyciu języka VBA można jej używać na trzy sposoby:

1. Jako formuła w arkuszu, w której może przyjmować argumenty jako dane wejściowe i zwracać wartość lub tablicę wartości.
2. Jako część kodu podprogramu VBA lub innego kodu funkcji.
3. W formatowaniu warunkowym.

Chociaż w arkuszu jest już ponad 450 wbudowanych funkcji Excela, możesz potrzebować funkcji niestandardowej, jeśli:

• Wbudowane funkcje nie mogą zrobić tego, co chcesz zrobić. W takim przypadku możesz utworzyć funkcję niestandardową w oparciu o swoje wymagania.
• Wbudowane funkcje mogą wykonać pracę, ale formuła jest długa i skomplikowana. W takim przypadku możesz utworzyć funkcję niestandardową, która jest łatwa do odczytania i użycia.

Zwróć uwagę, że niestandardowe funkcje utworzone za pomocą VBA mogą być znacznie wolniejsze niż funkcje wbudowane. Dlatego najlepiej nadają się do sytuacji, w których nie można uzyskać wyniku za pomocą wbudowanych funkcji.

Funkcja vs. Podprogram w VBA

„Podprogram” umożliwia wykonanie zestawu kodu, podczas gdy „Funkcja” zwraca wartość (lub tablicę wartości).

Na przykład, jeśli masz listę liczb (zarówno dodatnich, jak i ujemnych) i chcesz zidentyfikować liczby ujemne, oto, co możesz zrobić z funkcją i podprogramem.

Podprogram może przechodzić przez każdą komórkę w zakresie i podświetlać wszystkie komórki, które mają w niej wartość ujemną. W takim przypadku podprogram kończy się zmianą właściwości obiektu zakresu (poprzez zmianę koloru komórek).

Dzięki funkcji niestandardowej można jej użyć w osobnej kolumnie i zwrócić TRUE, jeśli wartość w komórce jest ujemna, a FALSE, jeśli jest dodatnia. Za pomocą funkcji nie można zmienić właściwości obiektu. Oznacza to, że nie możesz zmienić koloru komórki za pomocą samej funkcji (jednak możesz to zrobić za pomocą formatowania warunkowego za pomocą funkcji niestandardowej).

Podczas tworzenia funkcji zdefiniowanej przez użytkownika (UDF) przy użyciu języka VBA możesz używać tej funkcji w arkuszu, tak jak każdej innej funkcji. Więcej na ten temat omówię w sekcji „Różne sposoby korzystania z funkcji zdefiniowanej przez użytkownika w programie Excel”.

Tworzenie prostej funkcji zdefiniowanej przez użytkownika w VBA

Pozwólcie, że stworzę prostą funkcję zdefiniowaną przez użytkownika w VBA i pokażę, jak to działa.

Poniższy kod tworzy funkcję, która wyodrębni części numeryczne z ciągu alfanumerycznego.

Funkcja GetNumeric(CellRef As String) as Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Dalej i GetNumeric = Funkcja zakończenia wyniku

Gdy masz powyższy kod w module, możesz użyć tej funkcji w skoroszycie.

Poniżej jest jak ta funkcja - Pobierz numeryczne - może być używany w programie Excel.

Teraz zanim powiem ci, jak ta funkcja jest tworzona w VBA i jak działa, jest kilka rzeczy, które powinieneś wiedzieć:

• Gdy tworzysz funkcję w VBA, staje się ona dostępna w całym skoroszycie, tak jak każda inna zwykła funkcja.
• Po wpisaniu nazwy funkcji, po której następuje znak równości, program Excel wyświetli nazwę funkcji na liście pasujących funkcji. W powyższym przykładzie, kiedy wprowadziłem =Get, Excel pokazał mi listę, która zawierała moją niestandardową funkcję.

Uważam, że jest to dobry przykład, kiedy możesz użyć VBA do stworzenia prostej w użyciu funkcji w Excelu. Możesz zrobić to samo z formułą (jak pokazano w tym samouczku), ale staje się to skomplikowane i trudne do zrozumienia. W przypadku tego UDF wystarczy przekazać tylko jeden argument, aby uzyskać wynik.

Anatomia funkcji zdefiniowanej przez użytkownika w VBA

W powyższej sekcji podałem kod i pokazałem, jak działa funkcja UDF w arkuszu.

Teraz zanurkujmy głęboko i zobaczmy, jak powstaje ta funkcja. Musisz umieścić poniższy kod w module w edytorze VB. Omówię ten temat w sekcji „Gdzie umieścić kod VBA dla funkcji zdefiniowanej przez użytkownika”.

Function GetNumeric(CellRef As String) as Long ' Ta funkcja wyodrębnia część numeryczną z ciągu Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Wynik & Mid(CellRef, i, 1) Dalej i GetNumeric = Wynik Koniec funkcji

Pierwsza linia kodu zaczyna się od słowa - Function.

To słowo mówi VBA, że nasz kod jest funkcją (a nie podprogramem). Po słowie Function następuje nazwa funkcji - GetNumeric. Jest to nazwa, której będziemy używać w arkuszu, aby korzystać z tej funkcji.

• Nazwa funkcji nie może zawierać spacji. Ponadto nie możesz nazwać funkcji, jeśli koliduje ona z nazwą odwołania do komórki. Na przykład nie można nazwać funkcji ABC123, ponieważ odnosi się ona również do komórki w arkuszu programu Excel.
• Nie powinieneś nadawać swojej funkcji takiej samej nazwy, jak nazwa istniejącej funkcji. Jeśli to zrobisz, Excel da pierwszeństwo wbudowanej funkcji.
• Możesz użyć podkreślenia, jeśli chcesz oddzielić słowa. Na przykład akceptowalną nazwą jest Get_Numeric.

Po nazwie funkcji następuje kilka argumentów w nawiasach. To są argumenty, których nasza funkcja potrzebuje od użytkownika. To tak jak argumenty, które musimy dostarczyć wbudowanym funkcjom Excela. Na przykład w funkcji LICZ.JEŻELI występują dwa argumenty (zakres i kryteria)

W nawiasie musisz podać argumenty.

W naszym przykładzie jest tylko jeden argument - CellRef.

Dobrą praktyką jest również określenie, jakiego rodzaju argumentu oczekuje funkcja. W tym przykładzie, ponieważ będziemy podawać funkcji odwołanie do komórki, możemy określić argument jako typ „Zakres”. Jeśli nie określisz typu danych, VBA uzna go za wariant (co oznacza, że ​​możesz użyć dowolnego typu danych).

Jeśli masz więcej niż jeden argument, możesz podać je w tym samym nawiasie - oddzielone przecinkiem. W dalszej części tego samouczka zobaczymy, jak używać wielu argumentów w funkcji zdefiniowanej przez użytkownika.

Zauważ, że funkcja jest określona jako typ danych „String”. To powie VBA, że wynik formuły będzie typu danych String.

Chociaż mogę tutaj użyć liczbowego typu danych (takiego jak Long lub Double), to ograniczyłoby to zakres liczb, które może zwrócić. Jeśli mam ciąg o długości 20 liczb, który muszę wyodrębnić z całego ciągu, zadeklarowanie funkcji jako Long lub Double dałoby błąd (ponieważ liczba byłaby poza jej zakresem). Dlatego zachowałem typ danych wyjściowych funkcji jako String.

Drugi wiersz kodu - ten w kolorze zielonym, rozpoczynający się apostrofem - to komentarz. Podczas czytania kodu VBA ignoruje ten wiersz. Możesz użyć tego, aby dodać opis lub szczegół dotyczący kodu.

Trzeci wiersz kodu deklaruje zmienną „StringLength” jako typ danych Integer. Jest to zmienna, w której przechowujemy wartość długości ciągu analizowanego przez formułę.

Czwarty wiersz deklaruje zmienną Result jako typ danych String. To jest zmienna, w której wydobędziemy liczby z ciągu alfanumerycznego.

Piąty wiersz przypisuje długość ciągu w argumencie wejściowym do zmiennej „StringLength”. Zauważ, że „CellRef” odnosi się do argumentu, który zostanie podany przez użytkownika podczas używania formuły w arkuszu (lub używania jej w VBA – co zobaczymy w dalszej części tego samouczka).

Szósta, siódma i ósma linia są częścią pętli For Next. Pętla działa tyle razy, ile znaków znajduje się w argumencie wejściowym. Numer ten jest nadawany przez funkcję LEN i przypisywany do zmiennej „StringLength”.

Tak więc pętla biegnie od „1 do Stringlength”.

W pętli instrukcja IF analizuje każdy znak ciągu i jeśli jest liczbowy, dodaje ten znak liczbowy do zmiennej Result. W tym celu wykorzystuje funkcję MID w VBA.

W przedostatnim wierszu kodu przypisuje się wartość wyniku do funkcji. To właśnie ten wiersz kodu zapewnia, że ​​funkcja zwróci wartość „Wynik” z powrotem w komórce (z której jest wywołana).

Ostatni wiersz kodu to End Function. Jest to obowiązkowy wiersz kodu, który informuje VBA, że kod funkcji kończy się tutaj.

Powyższy kod wyjaśnia różne części typowej funkcji niestandardowej utworzonej w VBA. W kolejnych sekcjach zagłębimy się w te elementy, a także zobaczymy różne sposoby wykonywania funkcji VBA w programie Excel.

Argumenty w funkcji zdefiniowanej przez użytkownika w VBA

W powyższych przykładach, w których utworzyliśmy funkcję zdefiniowaną przez użytkownika, aby pobrać część numeryczną z ciągu alfanumerycznego (GetNumeric), funkcja została zaprojektowana tak, aby pobierała jeden pojedynczy argument.

W tej sekcji omówię tworzenie funkcji, które nie przyjmują argumentów do tych, które przyjmują wiele argumentów (wymaganych, a także opcjonalnych).

Tworzenie funkcji w VBA bez żadnych argumentów

W arkuszu programu Excel mamy kilka funkcji, które nie przyjmują argumentów (takich jak RAND, DZIŚ, TERAZ).

Te funkcje nie są zależne od żadnych argumentów wejściowych. Na przykład funkcja DZIŚ zwróci bieżącą datę, a funkcja RAND zwróci losową liczbę z zakresu od 0 do 1.

Możesz stworzyć podobną funkcję również w VBA.

Poniżej znajduje się kod, który poda nazwę pliku. Nie przyjmuje żadnych argumentów, ponieważ wynik, który musi zwrócić, nie jest zależny od żadnego argumentu.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name Funkcja zakończenia

Powyższy kod określa wynik funkcji jako typ danych String (ponieważ pożądanym wynikiem jest nazwa pliku - będąca ciągiem znaków).

Ta funkcja przypisuje wartość „ThisWorkbook.Name” do funkcji, która jest zwracana, gdy funkcja jest używana w arkuszu.

Jeśli plik został zapisany, zwraca nazwę z rozszerzeniem pliku, w przeciwnym razie po prostu podaje nazwę.

Powyższe ma jednak jeden problem.

Jeśli nazwa pliku się zmieni, nie zaktualizuje się automatycznie. Zwykle funkcja odświeża się za każdym razem, gdy następuje zmiana w argumentach wejściowych. Ale ponieważ w tej funkcji nie ma argumentów, funkcja nie przelicza się ponownie (nawet jeśli zmienisz nazwę skoroszytu, zamknij go, a następnie ponownie otwórz).

Jeśli chcesz, możesz wymusić ponowne obliczenie za pomocą skrótu klawiaturowego - Control + Alt + F9.

Aby formuła była ponownie obliczana po każdej zmianie w arkuszu, potrzebujesz do niej wiersza kodu.

Poniższy kod powoduje, że funkcja przelicza się ponownie za każdym razem, gdy nastąpi zmiana w arkuszu (podobnie jak inne podobne funkcje arkusza, takie jak funkcja DZIŚ lub RAND).

Function WorkbookName() As String Application.Volatile True WorkbookName = ThisWorkbook.Name Funkcja zakończenia

Teraz, jeśli zmienisz nazwę skoroszytu, ta funkcja będzie aktualizowana za każdym razem, gdy nastąpi jakakolwiek zmiana w arkuszu lub po ponownym otwarciu tego skoroszytu.

Tworzenie funkcji w VBA z jednym argumentem

W jednej z powyższych sekcji widzieliśmy już, jak utworzyć funkcję, która przyjmuje tylko jeden argument (funkcja GetNumeric opisana powyżej).

Stwórzmy kolejną prostą funkcję, która przyjmuje tylko jeden argument.

Funkcja utworzona za pomocą poniższego kodu przekonwertowałaby tekst odniesienia na wielkie litery. Teraz mamy już do tego funkcję w Excelu, a ta funkcja ma tylko pokazać, jak to działa. Jeśli musisz to zrobić, lepiej skorzystać z wbudowanej funkcji UPPER.

Funkcja ConvertToUpperCase(CellRef jako zakres) ConvertToUpperCase = UCase(CellRef) Funkcja zakończenia

Ta funkcja używa funkcji UCase w języku VBA do zmiany wartości zmiennej CellRef. Następnie przypisuje wartość do funkcji ConvertToUpperCase.

Ponieważ ta funkcja przyjmuje argument, nie musimy tutaj używać części Application.Volatile. Gdy tylko argument się zmieni, funkcja zostanie automatycznie zaktualizowana.

Tworzenie funkcji w VBA z wieloma argumentami

Podobnie jak funkcje arkusza, możesz tworzyć funkcje w VBA, które przyjmują wiele argumentów.

Poniższy kod utworzy funkcję, która wyodrębni tekst przed określonym ogranicznikiem. Przyjmuje dwa argumenty - odwołanie do komórki zawierającej ciąg tekstowy i ogranicznik.

Funkcja GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Wynik As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Wynik = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Wynik Koniec funkcji

Gdy musisz użyć więcej niż jednego argumentu w funkcji zdefiniowanej przez użytkownika, możesz umieścić wszystkie argumenty w nawiasach oddzielone przecinkiem.

Zauważ, że dla każdego argumentu możesz określić typ danych. W powyższym przykładzie „CellRef” został zadeklarowany jako typ danych zakresu, a „Delim” został zadeklarowany jako typ danych String. Jeśli nie określisz żadnego typu danych, VBA uzna, że ​​są to warianty typu danych.

Gdy korzystasz z powyższej funkcji w arkuszu, musisz podać odwołanie do komórki zawierające tekst jako pierwszy argument i znaki ogranicznika w podwójnych cudzysłowach jako drugi argument.

Następnie sprawdza pozycję ogranicznika za pomocą funkcji INSTR w VBA. Ta pozycja jest następnie używana do wyodrębnienia wszystkich znaków przed ogranicznikiem (za pomocą funkcji LEWO).

Na koniec przypisuje wynik do funkcji.

Ta formuła jest daleka od ideału. Na przykład, jeśli wprowadzisz ogranicznik, którego nie ma w tekście, spowoduje to błąd. Teraz możesz użyć funkcji JEŻELI.BŁĄD w arkuszu, aby pozbyć się błędów, lub możesz użyć poniższego kodu, który zwraca cały tekst, gdy nie może znaleźć ogranicznika.

Funkcja GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Wynik As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Jeśli DelimPosition < 0 Następnie DelimPosition = Len(CellRef) Wynik = Left( CellRef, DelimPosition) GetDataBeforeDelimiter = Funkcja zakończenia wyniku

Możemy jeszcze bardziej zoptymalizować tę funkcję.

Jeśli wprowadzisz tekst (z którego chcesz wyodrębnić część przed ogranicznikiem) bezpośrednio w funkcji, dałoby to błąd. Śmiało… spróbuj!

Dzieje się tak, ponieważ określiliśmy „CellRef” jako typ danych zakresu.

Lub, jeśli chcesz, aby ogranicznik znajdował się w komórce i użyj odwołania do komórki zamiast twardego kodowania go w formule, nie możesz tego zrobić za pomocą powyższego kodu. To dlatego, że Delim został zadeklarowany jako typ danych łańcuchowych.

Jeśli chcesz, aby funkcja miała elastyczność w zakresie akceptowania bezpośredniego wprowadzania tekstu lub odwołań do komórek od użytkownika, musisz usunąć deklarację typu danych. Spowodowałoby to utworzenie argumentu jako wariantu typu danych, który może przyjąć dowolny typ argumentu i go przetworzyć.

Poniższy kod zrobi to:

Funkcja GetDataBeforeDelimiter(CellRef, Delim) As String Dim Wynik As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Jeśli DelimPosition < 0 Wtedy DelimPosition = Len(CellRef) Wynik = Left(CellRef), Delim GetDataBeforeDelimiter = Funkcja zakończenia wyniku

Tworzenie funkcji w VBA z opcjonalnymi argumentami

W programie Excel istnieje wiele funkcji, w których niektóre argumenty są opcjonalne.

Na przykład legendarna funkcja WYSZUKAJ.PIONOWO ma 3 argumenty obowiązkowe i jeden argument opcjonalny.

Opcjonalny argument, jak sama nazwa wskazuje, jest opcjonalny do określenia. Jeśli nie podasz jednego z obowiązkowych argumentów, twoja funkcja wygeneruje błąd, ale jeśli nie podasz opcjonalnego argumentu, twoja funkcja będzie działać.

Ale opcjonalne argumenty nie są bezużyteczne. Pozwalają wybierać spośród wielu opcji.

Na przykład w funkcji WYSZUKAJ.PIONOWO, jeśli nie określisz czwartego argumentu, funkcja WYSZUKAJ.PIONOWO wykona przybliżone wyszukiwanie, a jeśli określisz ostatni argument jako FAŁSZ (lub 0), to dopasuje dokładne.

Pamiętaj, że opcjonalne argumenty muszą zawsze znajdować się po wszystkich wymaganych argumentach. Na początku nie możesz mieć opcjonalnych argumentów.

Zobaczmy teraz, jak utworzyć funkcję w VBA z opcjonalnymi argumentami.

Funkcja z tylko opcjonalnym argumentem

O ile mi wiadomo, nie ma wbudowanej funkcji, która przyjmuje tylko opcjonalne argumenty (tutaj mogę się mylić, ale nie przychodzi mi do głowy żadna taka funkcja).

Ale możemy go stworzyć za pomocą VBA.

Poniżej znajduje się kod funkcji, która poda bieżącą datę w formacie dd-mm-rrrr, jeśli nie wprowadzisz żadnego argumentu (tzn. zostaw to pole puste), oraz w formacie „dd mmmm, rrrr”, jeśli coś wpiszesz jako argument (tj. cokolwiek, aby argument nie był pusty).

Funkcja CurrDate(Opcjonalne fmt As Variant) Dim Wynik If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-rrrr") Else CurrDate = Format(Date, "dd mmmm, rrrr") End If End Function

Zauważ, że powyższa funkcja używa „IsMissing”, aby sprawdzić, czy brakuje argumentu, czy nie. Aby użyć funkcji IsMissing, opcjonalny argument musi być typu danych wariantu.

Powyższa funkcja działa bez względu na to, co wpiszesz jako argument. W kodzie sprawdzamy tylko, czy podano opcjonalny argument, czy nie.

Możesz uczynić to bardziej niezawodnym, przyjmując tylko określone wartości jako argumenty i wyświetlając błąd w pozostałych przypadkach (jak pokazano w poniższym kodzie).

Funkcja CurrDate(Opcjonalne fmt As Variant) Wynik Dim If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-rrrr") ElseIf fmt = 1 Then CurrDate = Format(Date, "dd mmmm, rrrr") Else CurrDate = CVErr(xlErrValue) End If End Function

Powyższy kod tworzy funkcję, która wyświetla datę w formacie „dd-mm-rrrr”, jeśli nie podano argumentu, oraz w formacie „dd mmmm,rrrr”, gdy argument ma wartość 1. We wszystkich innych przypadkach daje błąd.

Funkcja z argumentami wymaganymi i opcjonalnymi

Widzieliśmy już kod, który wyodrębnia część numeryczną z ciągu.

Przyjrzyjmy się teraz podobnemu przykładowi, który przyjmuje zarówno wymagane, jak i opcjonalne argumenty.

Poniższy kod tworzy funkcję, która wyodrębnia część tekstową z ciągu. Jeśli opcjonalny argument ma wartość PRAWDA, wynik jest pisany wielkimi literami, a jeśli opcjonalny argument ma wartość FAŁSZ lub jest pominięty, wynik jest taki, jaki jest.

Funkcja GetText(CellRef As Range, opcjonalnie TextCase = False) As String Dim StringLength As Integer Dim Wynik As String StringLength = Len(CellRef) For i = 1 To StringLength Jeśli nie (IsNumeric(Mid(CellRef, i, 1))) Wtedy Wynik = Wynik i Mid(CellRef, i, 1) Next i If TextCase = True Then Result = UCase(Result) GetText = Wynik End Function

Zauważ, że w powyższym kodzie zainicjowaliśmy wartość „TextCase” jako False (spójrz w nawias w pierwszym wierszu).

Dzięki temu upewniliśmy się, że opcjonalny argument zaczyna się od wartości domyślnej, którą jest FALSE. Jeśli użytkownik określi wartość jako TRUE, funkcja zwróci tekst wielkimi literami, a jeśli użytkownik określi opcjonalny argument jako FALSE lub go pominie, to zwrócony tekst jest taki, jak jest.

Tworzenie funkcji w VBA z tablicą jako argumentem

Do tej pory widzieliśmy przykłady tworzenia funkcji z argumentami opcjonalnymi/wymaganymi - gdzie te argumenty były pojedynczą wartością.

Możesz także utworzyć funkcję, która jako argument może przyjąć tablicę. W funkcjach arkusza programu Excel istnieje wiele funkcji, które przyjmują argumenty tablicowe, takie jak SUMA, WYSZUKAJ.PIONOWO, SUMA.JEŻELI, LICZ.JEŻELI itp.

Poniżej znajduje się kod, który tworzy funkcję dającą sumę wszystkich liczb parzystych w określonym zakresie komórek.

Function AddEven(CellRef as Range) Dim Cell As Range dla każdej komórki w CellRef If IsNumeric(Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Wynik + Cell.Value End If End If Next Cell AddEven = Wynik End Funkcjonować

Możesz użyć tej funkcji w arkuszu i podać zakres komórek, których argumentem są liczby. Funkcja zwróci pojedynczą wartość - sumę wszystkich parzystych liczb (jak pokazano poniżej).

W powyższej funkcji zamiast pojedynczej wartości podaliśmy tablicę (A1:A10). Aby to zadziałało, musisz upewnić się, że twój typ danych argumentu może akceptować tablicę.

W powyższym kodzie określiłem argument CellRef jako Range (który może przyjmować tablicę jako dane wejściowe). Możesz również użyć tutaj typu danych wariantu.

W kodzie znajduje się pętla For Each, która przechodzi przez każdą komórkę i sprawdza, czy jest to liczba nie. Jeśli tak nie jest, nic się nie dzieje i przechodzi do następnej komórki. Jeśli jest to liczba, sprawdza, czy jest parzysta, czy nie (za pomocą funkcji MOD).

Na koniec wszystkie liczby parzyste są dodawane i suma jest przypisywana z powrotem do funkcji.

Tworzenie funkcji z nieskończoną liczbą argumentów

Tworząc niektóre funkcje w VBA, możesz nie znać dokładnej liczby argumentów, które użytkownik chce podać. Tak więc potrzebne jest stworzenie funkcji, która może przyjąć tyle argumentów, ile zostało dostarczonych i użyć ich do zwrócenia wyniku.

Przykładem takiej funkcji arkusza jest funkcja SUMA. Możesz podać do niego wiele argumentów (takich jak ten):

=SUMA(A1,A2:A4,B1:B20)

Powyższa funkcja doda wartości we wszystkich tych argumentach. Pamiętaj też, że może to być pojedyncza komórka lub tablica komórek.

Możesz utworzyć taką funkcję w VBA, podając ostatni argument (lub może to być jedyny argument) jako opcjonalny. Ponadto ten opcjonalny argument powinien być poprzedzony słowem kluczowym „ParamArray”.

„ParamArray” to modyfikator, który pozwala akceptować dowolną liczbę argumentów. Zauważ, że użycie słowa ParamArray przed argumentem sprawia, że ​​argument jest opcjonalny. Jednak nie musisz tutaj używać słowa Opcjonalne.

Teraz stwórzmy funkcję, która akceptuje dowolną liczbę argumentów i dodaje wszystkie liczby w podanych argumentach:

Funkcja AddArguments(ParamArray arglist() As Variant) Dla każdego argumentu In arglist AddArguments = AddArguments + arg Następny argument Zakończ funkcję

Powyższa funkcja może przyjąć dowolną liczbę argumentów i dodać te argumenty, aby uzyskać wynik.

Zauważ, że jako argumentu możesz użyć tylko pojedynczej wartości, odwołania do komórki, wartości logicznej lub wyrażenia. Nie możesz podać tablicy jako argumentu. Na przykład, jeśli jednym z twoich argumentów jest D8:D10, ta formuła da ci błąd.

Jeśli chcesz mieć możliwość użycia obu argumentów wielokomórkowych, musisz użyć poniższego kodu:

Funkcja AddArguments(ParamArray arglist() As Variant) Dla każdego argumentu w liście argumentów Dla każdej komórki w arg AddArguments = AddArguments + komórka Następna komórka Następna komórka Zakończ funkcję

Należy zauważyć, że ta formuła działa z wieloma komórkami i odwołaniami do tablic, jednak nie może przetwarzać zakodowanych na stałe wartości ani wyrażeń. Możesz stworzyć bardziej niezawodną funkcję, sprawdzając i lecząc te warunki, ale nie o to tutaj chodzi.

Celem tego jest pokazanie, jak działa ParamArray, dzięki czemu można zezwolić na nieskończoną liczbę argumentów w funkcji. Jeśli chcesz mieć lepszą funkcję niż ta stworzona przez powyższy kod, użyj funkcji SUMA w arkuszu.

Tworzenie funkcji zwracającej tablicę

Do tej pory widzieliśmy funkcje, które zwracają pojedynczą wartość.

Dzięki VBA możesz utworzyć funkcję, która zwraca wariant, który może zawierać całą tablicę wartości.

Formuły tablicowe są również dostępne jako wbudowane funkcje w arkuszach programu Excel. Jeśli znasz formuły tablicowe w programie Excel, wiesz, że są one wprowadzane za pomocą klawiszy Control + Shift + Enter (zamiast tylko Enter). Możesz przeczytać więcej o formułach tablicowych tutaj. Jeśli nie wiesz o formułach tablicowych, nie martw się, czytaj dalej.

Utwórzmy formułę, która zwraca tablicę trzech liczb (1,2,3).

Poniższy kod zrobi to.

Funkcja ThreeNumbers() jako wariant Dim NumberValue(1 do 3) NumberValue(1) = 1 NumberValue(2) = 2 NumberValue(3) = 3 ThreeNumbers = NumberValue Funkcja końcowa

W powyższym kodzie jako wariant określiliśmy funkcję „TrzyLiczby”. Pozwala to na przechowywanie tablicy wartości.

Zmienna „NumberValue” jest zadeklarowana jako tablica składająca się z 3 elementów. Przechowuje trzy wartości i przypisuje je do funkcji „TrzyLiczby”.

Możesz użyć tej funkcji w arkuszu, wprowadzając funkcję i naciskając klawisz Control + Shift + Enter (przytrzymaj klawisze Control i Shift, a następnie naciśnij klawisz Enter).

Kiedy to zrobisz, zwróci 1 w komórce, ale w rzeczywistości zawiera wszystkie trzy wartości. Aby to sprawdzić, użyj poniższego wzoru:

=MAX(TrzyLiczby())

Użyj powyższej funkcji z Control + Shift + Enter. Zauważysz, że wynikiem jest teraz 3, ponieważ są to największe wartości w tablicy zwrócone przez funkcję Max, która otrzymuje trzy liczby jako wynik naszej funkcji zdefiniowanej przez użytkownika - TrzyLiczby.

Możesz użyć tej samej techniki, aby utworzyć funkcję, która zwraca tablicę nazw miesięcy, jak pokazano w poniższym kodzie:

Funkcja Months() As Variant Dim MonthName(1 do 12) MonthName(1) = "January" MonthName(2) = "February" MonthName(3) = "March" MonthName(4) = "April" MonthName(5) = "May" MonthName(6) = "June" MonthName(7) = "July" MonthName(8) = "Sierpień" MonthName(9) = "September" MonthName(10) = "Październik" MonthName(11) = "Listopad " MonthName(12) = "December" Months = MonthName Funkcja zakończenia

Teraz, gdy wprowadzisz funkcję =Months() w arkuszu programu Excel i użyjesz Control + Shift + Enter, zwróci całą tablicę nazw miesięcy. Zauważ, że w komórce widzisz tylko styczeń, ponieważ jest to pierwsza wartość w tablicy. Nie oznacza to, że tablica zwraca tylko jedną wartość.

Aby pokazać, że zwraca wszystkie wartości, zrób tak - zaznacz komórkę z formułą, przejdź do paska formuły, zaznacz całą formułę i naciśnij F9. Spowoduje to wyświetlenie wszystkich wartości zwracanych przez funkcję.

Możesz tego użyć, korzystając z poniższej formuły INDEKS, aby uzyskać listę wszystkich nazw miesięcy za jednym razem.

=INDEKS(Miesiące();WIERSZ())

Teraz, jeśli masz dużo wartości, nie jest dobrą praktyką przypisywanie tych wartości jedna po drugiej (jak zrobiliśmy powyżej). Zamiast tego możesz użyć funkcji Array w VBA.

Tak więc ten sam kod, w którym tworzymy funkcję „Miesiące”, stałby się krótszy, jak pokazano poniżej:

Function Months() As Variant Months = Array("styczeń", "luty", "marzec", "kwiecień", "maj", "czerwiec", _ "lipiec", "sierpień", "wrzesień", "październik" , "listopad", "grudzień") Funkcja zakończenia

Powyższa funkcja używa funkcji Array do przypisywania wartości bezpośrednio do funkcji.

Zauważ, że wszystkie funkcje utworzone powyżej zwracają poziomą tablicę wartości. Oznacza to, że jeśli wybierzesz 12 poziomych komórek (powiedzmy A1:L1) i wpiszesz formułę =Miesiące() w komórce A1, otrzymasz nazwy wszystkich miesięcy.

Ale co, jeśli chcesz, aby te wartości znajdowały się w pionowym zakresie komórek.

Możesz to zrobić za pomocą formuły TRANSPONUJ w arkuszu.

Po prostu wybierz 12 pionowych komórek (sąsiadujących) i wprowadź poniższą formułę.

Zrozumienie zakresu funkcji zdefiniowanej przez użytkownika w programie Excel

Funkcja może mieć dwa zakresy - Publiczny lub Prywatny.

• A Zakres publiczny oznacza, że ​​funkcja jest dostępna dla wszystkich arkuszy w skoroszycie, a także dla wszystkich procedur (Sub i Function) we wszystkich modułach w skoroszycie. Jest to przydatne, gdy chcesz wywołać funkcję z podprogramu (zobaczymy, jak to się robi w następnej sekcji).
• A Zakres prywatny oznacza, że ​​funkcja jest dostępna tylko w module, w którym istnieje. Nie możesz go używać w innych modułach. Nie zobaczysz go również na liście funkcji w arkuszu. Na przykład, jeśli nazwa funkcji to „Miesiące()” i wpiszesz funkcję w programie Excel (po znaku =), nazwa funkcji nie zostanie wyświetlona. Możesz jednak nadal z niego korzystać, jeśli wpiszesz nazwę formuły.

Jeśli nic nie określisz, funkcja jest domyślnie funkcją publiczną.

Poniżej znajduje się funkcja, która jest funkcją prywatną:

Funkcja prywatna WorkbookName() As String WorkbookName = ThisWorkbook.Name Funkcja zakończenia

Możesz używać tej funkcji w podprogramach i procedurach w tych samych modułach, ale nie możesz jej używać w innych modułach. Ta funkcja również nie pojawiłaby się w arkuszu.

Poniższy kod uczyniłby tę funkcję publiczną. Pojawi się również w arkuszu.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name Funkcja zakończenia

Różne sposoby korzystania z funkcji zdefiniowanej przez użytkownika w programie Excel

Po utworzeniu funkcji zdefiniowanej przez użytkownika w VBA możesz jej używać na wiele różnych sposobów.

Omówmy najpierw, jak korzystać z funkcji w arkuszu.

Korzystanie z UDF w arkuszach roboczych

Widzieliśmy już przykłady użycia funkcji utworzonej w VBA w arkuszu.

Wystarczy wpisać nazwę funkcji, a pojawi się ona w Intellisense.

Zauważ, że aby funkcja pojawiła się w arkuszu, musi to być funkcja publiczna (jak wyjaśniono w powyższej sekcji).

Możesz również użyć okna dialogowego Wstaw funkcję, aby wstawić funkcję zdefiniowaną przez użytkownika (postępując zgodnie z poniższymi krokami). Działałoby to tylko w przypadku funkcji publicznych.

• Przejdź do zakładki Dane.
• Kliknij opcję „Wstaw funkcję”.
• W oknie dialogowym Wstaw funkcję wybierz kategorię Zdefiniowane przez użytkownika. Ta opcja pojawia się tylko wtedy, gdy masz funkcję w edytorze VB (a funkcja jest publiczna).
• Wybierz funkcję z listy wszystkich funkcji zdefiniowanych przez użytkownika publicznego.
• Kliknij przycisk OK.

Powyższe kroki spowodowały wstawienie funkcji do arkusza. Wyświetla również okno dialogowe Argumenty funkcji, które zawiera szczegółowe informacje na temat argumentów i wyniku.

Funkcji zdefiniowanej przez użytkownika można używać tak samo, jak każdej innej funkcji w programie Excel. Oznacza to również, że możesz go używać z innymi wbudowanymi funkcjami Excela. Na przykład. poniższa formuła podałaby nazwę skoroszytu wielkimi literami:

= GÓRNE(Nazwa Skoroszytu())

Korzystanie z funkcji zdefiniowanych przez użytkownika w procedurach i funkcjach VBA

Po utworzeniu funkcji możesz jej używać również w innych procedurach podrzędnych.

Jeśli funkcja jest publiczna, można jej użyć w dowolnej procedurze w tym samym lub innym module. Jeśli jest prywatny, można go używać tylko w tym samym module.

Poniżej znajduje się funkcja, która zwraca nazwę skoroszytu.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name Funkcja zakończenia

Poniższa procedura wywołuje funkcję, a następnie wyświetla nazwę w oknie komunikatu.

Sub ShowWorkbookName() MsgBox WorkbookName End Sub

Możesz także wywołać funkcję z innej funkcji.

W poniższych kodach pierwszy kod zwraca nazwę skoroszytu, a drugi zwraca nazwę pisaną wielkimi literami, wywołując pierwszą funkcję.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name Funkcja zakończenia

Funkcja WorkbookNameinUpper() WorkbookNameinUpper = UCase(WorkbookName) Zakończ funkcję

Wywoływanie funkcji zdefiniowanej przez użytkownika z innych skoroszytów

Jeśli masz funkcję w skoroszycie, możesz wywołać tę funkcję również w innych skoroszytach.

Można to zrobić na wiele sposobów:

1. Tworzenie dodatku
2. Funkcja zapisywania w skoroszycie makr osobistych
3. Odwoływanie się do funkcji z innego skoroszytu.

Tworzenie dodatku

Tworząc i instalując dodatek, będziesz mieć w nim niestandardową funkcję dostępną we wszystkich skoroszytach.

Załóżmy, że utworzyłeś funkcję niestandardową - „GetNumeric” i chcesz ją znaleźć we wszystkich skoroszytach. Aby to zrobić, utwórz nowy skoroszyt i umieść kod funkcji w module w tym nowym skoroszycie.

Teraz wykonaj poniższe czynności, aby zapisać go jako dodatek, a następnie zainstalować go w programie Excel.

• Przejdź do zakładki Plik i kliknij Zapisz jako.
• W oknie dialogowym Zapisz jako zmień typ „Zapisz jako” na .xlam. Nazwa przypisana do pliku będzie nazwą dodatku. W tym przykładzie plik jest zapisywany pod nazwą GetNumeric.
  • Zauważysz, że ścieżka pliku, w którym został zapisany, zmienia się automatycznie. Możesz użyć domyślnego lub zmienić go, jeśli chcesz.
• Otwórz nowy skoroszyt programu Excel i przejdź do karty Deweloper.
• Kliknij opcję Dodatki programu Excel.
• W oknie dialogowym Dodatki przejrzyj i zlokalizuj zapisany plik, a następnie kliknij przycisk OK.

Teraz dodatek został aktywowany.

Teraz możesz używać funkcji niestandardowej we wszystkich skoroszytach.

Zapisywanie funkcji w skoroszycie makr osobistych

Skoroszyt makr osobistych to ukryty skoroszyt w systemie, który otwiera się przy każdym otwarciu aplikacji Excel.

Jest to miejsce, w którym możesz przechowywać kody makr, a następnie uzyskiwać do nich dostęp z dowolnego skoroszytu. To świetne miejsce do przechowywania makr, których chcesz często używać.

Domyślnie w programie Excel nie ma osobistego skoroszytu makr. Musisz je utworzyć, rejestrując makro i zapisując je w skoroszycie makr osobistych.

Szczegółowe instrukcje tworzenia i zapisywania makr w skoroszycie makr osobistych można znaleźć tutaj.

Odwoływanie się do funkcji z innego skoroszytu

Chociaż dwie pierwsze metody (tworzenie dodatku i używanie skoroszytu makr osobistych) działają we wszystkich sytuacjach, jeśli chcesz odwołać się do funkcji z innego skoroszytu, skoroszyt ten musi być otwarty.

Załóżmy, że masz skoroszyt o nazwie „Skoroszyt z formułą”i ma funkcję o nazwie ‘Pobierz numeryczne”.

Aby użyć tej funkcji w innym skoroszycie (gdy Skoroszyt z formułą jest otwarty), możesz skorzystać z poniższego wzoru:

= „Skoroszyt z formułą”! GetNumeric(A1)

Powyższy wzór użyje funkcji zdefiniowanej przez użytkownika w Skoroszyt z formułą plik i podać wynik.

Zauważ, że ponieważ nazwa skoroszytu zawiera spacje, musisz ująć ją w pojedyncze cudzysłowy.

Korzystanie z instrukcji wyjścia funkcji VBA

Jeśli chcesz wyjść z funkcji podczas działania kodu, możesz to zrobić za pomocą instrukcji „Exit Function”.

Poniższy kod wyodrębni pierwsze trzy znaki numeryczne z alfanumerycznego ciągu tekstowego. Jak tylko otrzyma trzy znaki, funkcja kończy się i zwraca wynik.

Funkcja GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Jeśli J = 3 Następnie wyjdź z funkcji If IsNumeric(Mid(CellRef, i, 1)) Wtedy J = J + 1 Wynik = Wynik i środek(CellRef, i, 1) GetNumericFirstThree = Wynik Koniec Jeśli dalej i Zakończ funkcję

Powyższa funkcja sprawdza liczbę znaków, które są numeryczne, a gdy otrzyma 3 znaki numeryczne, Wychodzi z funkcji w następnej pętli.

Debugowanie funkcji zdefiniowanej przez użytkownika

Istnieje kilka technik, których możesz użyć podczas debugowania funkcji zdefiniowanej przez użytkownika w VBA:

Debugowanie funkcji niestandardowej za pomocą okna wiadomości

Użyj funkcji MsgBox, aby wyświetlić okno komunikatu z określoną wartością.

Wyświetlana wartość może być oparta na tym, co chcesz przetestować. Na przykład, jeśli chcesz sprawdzić, czy kod jest wykonywany, czy nie, każdy komunikat zadziała, a jeśli chcesz sprawdzić, czy pętle działają, czy nie, możesz wyświetlić określoną wartość lub licznik pętli.

Debugowanie funkcji niestandardowej przez ustawienie punktu przerwania

Ustaw punkt przerwania, aby móc przejść przez każdą linię pojedynczo. Aby ustawić punkt przerwania, wybierz linię, w której chcesz, i naciśnij F9 lub kliknij szary pionowy obszar, który jest pozostawiony po liniach kodu. Każda z tych metod spowoduje wstawienie punktu przerwania (zobaczysz czerwoną kropkę w szarym obszarze).

Po ustawieniu punktu przerwania i wykonaniu funkcji, przechodzi ona do linii punktu przerwania, a następnie zatrzymuje się. Teraz możesz przejść przez kod za pomocą klawisza F8. Jednokrotne naciśnięcie klawisza F8 powoduje przejście do następnej linii kodu.

Debugowanie funkcji niestandardowej za pomocą Debug.Print w kodzie

Możesz użyć instrukcji Debug.Print w kodzie, aby uzyskać wartości określonych zmiennych/argumentów w bezpośrednim oknie.

Na przykład w poniższym kodzie użyłem Debug.Print, aby uzyskać wartość dwóch zmiennych - „j” i „Wynik”

Funkcja GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Jeśli J = 3 Następnie wyjdź z funkcji If IsNumeric(Mid(CellRef, i, 1)) Then J = J + 1 Wynik = Wynik i Mid(CellRef, i, 1) Debug.Print J, Wynik GetNumericFirstThree = Wynik Koniec Jeśli dalej i Zakończ funkcję

Gdy ten kod jest wykonywany, w bezpośrednim oknie widać następujące elementy.

Wbudowane funkcje programu Excel Vs. Funkcja zdefiniowana przez użytkownika VBA

Istnieje kilka silnych zalet korzystania z wbudowanych funkcji programu Excel w porównaniu z funkcjami niestandardowymi utworzonymi w VBA.

• Funkcje wbudowane są znacznie szybsze niż funkcje VBA.
• Kiedy tworzysz raport / pulpit nawigacyjny za pomocą funkcji VBA i wysyłasz go do klienta / współpracownika, nie musiałby się martwić, czy makra są włączone, czy nie. W niektórych przypadkach klienci/klienci boją się, widząc ostrzeżenie na żółtym pasku (który po prostu prosi ich o włączenie makr).
• Dzięki wbudowanym funkcjom Excela nie musisz się martwić o rozszerzenia plików. Jeśli w skoroszycie znajdują się makra lub funkcje zdefiniowane przez użytkownika, należy je zapisać w formacie xlsm.

Chociaż istnieje wiele silnych powodów, aby używać wbudowanych funkcji programu Excel, w kilku przypadkach lepiej jest użyć funkcji zdefiniowanej przez użytkownika.

• Lepiej użyć funkcji zdefiniowanej przez użytkownika, jeśli wbudowana formuła jest duża i skomplikowana. Staje się to jeszcze bardziej istotne, gdy potrzebujesz kogoś innego do zaktualizowania formuł. Na przykład, jeśli masz ogromną formułę składającą się z wielu różnych funkcji, nawet zmiana odwołania do komórki może być żmudna i podatna na błędy. Zamiast tego możesz utworzyć niestandardową funkcję, która przyjmuje tylko jeden lub dwa argumenty i wykonuje wszystkie ciężkie prace związane z backendem.
• Kiedy musisz zrobić coś, czego nie mogą zrobić wbudowane funkcje Excela. Przykładem może być, gdy chcesz wyodrębnić wszystkie znaki numeryczne z ciągu. W takich przypadkach korzyści wynikające z użycia funkcji zdefiniowanej przez użytkownika gar przewyższają jej negatywy.

Gdzie umieścić kod VBA dla funkcji zdefiniowanej przez użytkownika

Podczas tworzenia funkcji niestandardowej musisz umieścić kod w oknie kodu dla skoroszytu, w którym chcesz tę funkcję.

Poniżej znajdują się kroki, aby umieścić kod funkcji „GetNumeric” w skoroszycie.

1. Przejdź do zakładki Deweloper.
2. Kliknij opcję Visual Basic. Spowoduje to otwarcie edytora VB w zapleczu.
3. W okienku Eksplorator projektu w edytorze VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu, do którego chcesz wstawić kod. Jeśli nie widzisz Eksploratora projektów, przejdź do karty Widok i kliknij Eksplorator projektów.
4. Przejdź do Wstaw i kliknij Moduł. Spowoduje to wstawienie obiektu modułu do skoroszytu.
5. Skopiuj i wklej kod w oknie modułu.

Możesz również polubić następujące samouczki Excel VBA:

• Praca z komórkami i zakresami w Excel VBA.
• Praca z Arkuszami w Excel VBA.
• Praca ze skoroszytami przy użyciu VBA.
• Jak korzystać z pętli w Excel VBA.
• Wydarzenia Excel VBA - łatwy (i kompletny) przewodnik
• Korzystanie z instrukcji IF Then Else w VBA.
• Jak nagrać makro w programie Excel.
• Jak uruchomić makro w programie Excel.
• Jak sortować dane w programie Excel za pomocą VBA (przewodnik krok po kroku).
• Funkcja Excel VBA InStr - wyjaśniona z przykładami.