Ten samouczek nauczy Cię tworzyć i używać funkcji z parametrami i bez parametrów w VBA
VBA zawiera wiele wbudowanych funkcji, z których możesz korzystać, ale możesz także pisać własne. Kiedy piszesz kod w VBA, możesz napisać go w procedurze podrzędnej lub procedurze funkcji. Procedura funkcji może zwrócić wartość do twojego kodu. Jest to niezwykle przydatne, jeśli chcesz, aby VBA wykonał zadanie, aby zwrócić wynik. Funkcje VBA można również wywoływać z poziomu Excela, podobnie jak wbudowane funkcje Excela.
Tworzenie funkcji bez argumentów
Aby utworzyć funkcję, musisz ją zdefiniować, nadając jej nazwę. Funkcję można następnie zdefiniować jako typ danych wskazujący typ danych, które funkcja ma zwracać.
Możesz chcieć stworzyć funkcję, która zwraca wartość statyczną za każdym razem, gdy zostanie wywołana - trochę jak stała.
| 123 | Funkcja GetValue() jako liczba całkowitaUzyskajWartość = 50Koniec funkcji |
Gdybyś miał uruchomić funkcję, funkcja zawsze zwracałaby wartość 50.
Możesz także tworzyć funkcje, które odwołują się do obiektów w VBA, ale musisz użyć słowa kluczowego Set, aby zwrócić wartość z funkcji.
| 123 | Funkcja GetRange() jako zakresUstaw GetRange = Zakres("A1:G4")Koniec funkcji |
Jeśli miałbyś użyć powyższej funkcji w swoim kodzie VBA, funkcja zawsze zwracałaby zakres komórek od A1 do G4 w dowolnym arkuszu, w którym pracujesz.
Wywołanie funkcji z procedury podrzędnej
Po utworzeniu funkcji możesz wywołać ją z dowolnego miejsca w kodzie, używając procedury Sub do wywołania funkcji.
Wartość 50 zawsze zostanie zwrócona.
Możesz również wywołać funkcję GetRange z procedury Sub.
W powyższym przykładzie funkcja GetRange jest wywoływana przez procedurę Sub w celu pogrubienia komórek w obiekcie zakresu.
Tworzenie funkcji
Pojedynczy argument
Możesz również przypisać parametr lub parametry do swojej funkcji. Parametry te można nazwać argumentami.
| 123 | Funkcja ConvertKilosToPounds (dblKilo jako Double) jako DoublePrzeliczKiloDoFuntów = dblKilo*2,2Koniec funkcji |
Następnie możemy wywołać powyższą funkcję z Procedury Sub, aby obliczyć, ile funtów to określona ilość kilogramów.
W razie potrzeby funkcja może być wywołana z wielu procedur w kodzie VBA. Jest to bardzo przydatne, ponieważ zapobiega ciągłemu pisaniu tego samego kodu. Umożliwia także dzielenie długich procedur na małe, łatwe do zarządzania funkcje.
W powyższym przykładzie mamy 2 procedury - każda z nich używa Funkcji do obliczenia wartości funta przekazanych im kilogramów w dblKilo Argument funkcji.
Wiele argumentów
Możesz utworzyć funkcję z wieloma argumentami i przekazać wartości do funkcji za pomocą procedury Sub.
| 123 | Funkcja CalculateDayDiff(Data1 jako data, Date2 jako data) jako DoubleOblicz różnicę dnia = data2-data1Koniec funkcji |
Następnie możemy wywołać funkcję, aby obliczyć ilość dni między 2 datami.
Argumenty opcjonalne
Możesz również przekazać argumenty opcjonalne do funkcji. Innymi słowy, czasami możesz potrzebować argumentu, a czasami nie - w zależności od tego, z jakim kodem używasz Function z .
| 123456 | Funkcja CalculateDayDiff(Data1 jako data, opcjonalna Date2 jako data) jako Double'sprawdź drugą datę, a jeśli jej nie ma, ustaw Date2 równą dzisiejszej dacie.Jeśli Data2=0 to Data2 = Data'oblicz różnicę'ObliczRoznDzień = Data2-Data1Koniec funkcji |
Domyślna wartość argumentu
Możesz również ustawić domyślną wartość argumentów opcjonalnych podczas tworzenia funkcji, aby jeśli użytkownik pominął argument, zamiast tego zostanie użyta wartość ustawiona jako domyślna.
| 1234 | Function CalculateDayDiff(Date1 as Date, opcjonalnie Date2 as Date="06/02/2020") jako Double'oblicz różnicę'Oblicz różnicę dnia = data2-data1Koniec funkcji |
ByVal i ByRef
Kiedy przekazujesz wartości do funkcji, możesz użyć ByVal lub ByRef słowa kluczowe. Jeśli pominiesz którekolwiek z tych, ByRef jest używany domyślnie.
ByVal oznacza, że przekazujesz kopię zmiennej do funkcji, podczas gdy ByRef oznacza, że odwołujesz się do oryginalnej wartości zmiennej. Kiedy przekazujesz kopię zmiennej (ByVal), pierwotna wartość zmiennej to NIE zmieniona, ale kiedy odwołujesz się do zmiennej, pierwotna wartość zmiennej jest zmieniana przez funkcję.
| 1234 | Funkcja GetValue(ByRef intA As Integer) As IntegerintA = intA * 4PobierzWartość = intAKoniec funkcji |
W powyższej funkcji ByRef można pominąć, a funkcja działałaby w ten sam sposób.
| 1234 | Funkcja GetValue(intA As Integer) As IntegerintA = intA * 4PobierzWartość = intAKoniec funkcji |
Aby wywołać tę funkcję, możemy uruchomić podprocedurę.
| 123456789 | Podrzędne wartości testowe()Dim intVal As Integer'wypełnij zmienną wartością 10wart.wewn = 10'uruchom funkcję GetValue i pokaż wartość w bezpośrednim oknieDebug.Print GetValue(intVal)'pokaż wartość zmiennej intVal w bezpośrednim oknieDebug.Print intValNapis końcowy |
Zauważ, że okna debugowania pokazują wartość 40 za każdym razem. Gdy przekazujesz zmienną IntVal do funkcji - wartość 10 jest przekazywana do funkcji i pomnożona przez 4. Użycie słowa kluczowego ByRef (lub jego całkowite pominięcie) ZMIENI wartość zmiennej IntVal. Jest to widoczne, gdy najpierw pokazujesz wynik funkcji w oknie bezpośrednim (40), a następnie wartość zmiennej IntVal w oknie debugowania (również 40).
Jeśli NIE chcemy zmieniać wartości oryginalnej zmiennej, musimy w funkcji użyć ByVal.
| 1234 | Funkcja GetValue(ByVal intA As Integer) As IntegerintA = intA * 4PobierzWartość = intAKoniec funkcji |
Teraz, jeśli wywołamy funkcję z podprocedury, wartość zmiennej IntVal pozostanie na 10.
Wyjdź z funkcji
Jeśli tworzysz funkcję, która testuje określony warunek, a gdy warunek zostanie uznany za spełniony, chcesz zwrócić wartość z funkcji, może być konieczne dodanie instrukcji Exit Function w funkcji, aby wyjść z funkcji przed przejrzałeś cały kod w tej funkcji.
| 12345678910111213 | Funkcja FindNumber(strSearch As String) As IntegerDim i jako liczba całkowita'przejdź przez każdą literę w łańcuchuDla i = 1 To Len(strSearch)'jeśli litera jest liczbowa, zwróć wartość do funkcjiIf IsNumeric(Mid(strSearch, i, 1)) ThenFindNumber= Mid(strSearch, i, 1)'następnie wyjdź z funkcjiWyjdź z funkcjiZakończ, jeśliNastępnyZnajdźNumer= 0Koniec funkcji |
Powyższa funkcja będzie przechodzić przez dostarczony ciąg, aż znajdzie liczbę, a następnie zwróci tę liczbę z ciągu. Znajdzie tylko pierwszą liczbę w ciągu, tak jak wtedy Wyjście funkcja.
Powyższa funkcja może być wywołana przez procedurę Sub, taką jak ta poniżej.
| 1234567 | Sub SprawdźNumer()Dim NumIs jako liczba całkowita'przekaż ciąg tekstowy do funkcji wyszukiwania liczbNumIs = FindNumber("Górne piętro, 8 Oak Lane, Teksas")'pokaż wynik w bezpośrednim oknieDebug.Print NumIsNapis końcowy |
Korzystanie z funkcji z arkusza Excel
Oprócz wywoływania funkcji z kodu VBA przy użyciu procedury podrzędnej, możesz również wywołać funkcję z poziomu arkusza programu Excel. Utworzone funkcje powinny domyślnie pojawiać się na liście funkcji w sekcji Zdefiniowane przez użytkownika listy funkcji.
Kliknij na fx aby wyświetlić okno dialogowe Wstaw funkcję.
Wybierz Określony przez użytkownika z listy kategorii
Wybierz żądaną funkcję z dostępnych Funkcje zdefiniowane przez użytkownika (UDF).
Alternatywnie, gdy zaczniesz pisać swoją funkcję w programie Excel, funkcja powinna pojawić się na rozwijanej liście funkcji.
Jeśli nie chcesz, aby funkcja była dostępna w arkuszu Excel, musisz umieścić słowo prywatne przed słowem Funkcja podczas tworzenia funkcji w kodzie VBA.
| 123 | Funkcja prywatna CalculateDayDiff(Data1 jako Date, Date2 jako Date) jako DoubleOblicz różnicę dnia = data2-data1Koniec funkcji |
Teraz nie pojawi się na liście rozwijanej, pokazującej dostępne funkcje programu Excel.
Co ciekawe, nadal można jednak z tej funkcji korzystać - po prostu nie pojawi się ona na liście, gdy jej szukamy!
Jeśli zadeklarowałeś drugi argument jako Opcjonalny, możesz go pominąć w arkuszu Excela, a także w kodzie VBA.
Możesz także użyć funkcji a utworzonej bez argumentów w arkuszu Excela.
