Tworzenie Funkcji

Przykłady opisany w tej lekcji jest dostępny w pliku Excela: VBA.xlsm tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Przykład 1.

(Arkusz: ‘Tworzenie Funkcji 1’, Moduł: ‘Tworzenie_Funkcji_1’)

W poniższym przykładzie użytkownik na podstawie przekazywanych mu danych musi wyliczyć datę ważności dla dużej liczby produktów.

Otrzymywane dane to data produkcji i okres ważności, który może wynosić 1M, 3M, 6M, 1R, 3L (M- miesiąc, R – rok, L –lata).

Mógłby w tym celu skorzystać z funkcji Jeżeli a dokładniej 5 zapętlonych takich funkcji aby obliczyć datę ważności. Jednak wprowadzanie za każdym razem tak skomplikowanej formuły jest czasochłonne. Dodatkowo w przypadku gdyby ilość okresów ważności znacząco wzrosła wyczerpałby się limit ilości funkcji wewnętrznych.

Ponieważ operacje takie wykonywane są często wygodnie będzie utworzyć funkcję VBA obliczającą datę ważności.

Przechodzimy do edytora VBA (Alt+F11) i w jednym z modułów pliku Personal.xls wpisujemy następującą funkcję.

Function Data_Waznosci(Data_Produkcji, Okres_waznosci)

Data_Waznosci = "Nieznana"

Select Case Okres_waznosci

Case Is = "1M": Data_Waznosci = Data_Produkcji + 30

Case Is = "3M": Data_Waznosci = Data_Produkcji + 90

Case Is = "6M": Data_Waznosci = Data_Produkcji + 180

Case Is = "1R": Data_Waznosci = Data_Produkcji + 365

Case Is = "3L": Data_Waznosci = Data_Produkcji + 1095

End Select

End Function

Funkcje zawsze zaczynają się określeniem ‘Function’ a kończą ‘End Function’.

Po ‘Function’ następuje nazwa funkcji, która jednocześnie jest nazwą zmiennej wyjściowej. W nawiasie podawane są nazwy argumentów wejściowych.

W kolejnej linii przypisujemy argumentowi Data_Wazności wartość „Nieznana”, zabezpieczy to nas przed sytuacjami w których argument Okres_Waznosci nie został podany lub jest podany błędnie, w takich przypadkach wynikiem działania funkcji będzie tekst „Nieznana”.

Potem następuje konstrukcja ‘Select Case’, w której w zależności od argumentu Okres_Waznosci, do Data produkcji dodawana jest odpowiednia ilość dni i przypisywane jest to do wyniku funkcji.

Tak utworzoną funkcję możemy znaleźć w kategorii funkcji ‘Zdefiniowane przez użytkownika’ w standardowym oknie ‘Wstawianie funkcji’.

Po jej wybraniu i kliknięciu przycisku ‘OK’, wyświetli się ekran, na którym podajemy argumenty funkcji.

Na ekranie tym, w tym przypadku Excel nie formatuje właściwie dat, dlatego też podaje je jako liczby (39721, 39751).

Wynik formuły pokazywany jest we właściwym formacie w lewym dolnym rogu okna.

Funkcję możemy przegrać do pozostałych komórek w kolumnie ‘Data ważności’

Daty ważności zostały obliczone zgodnie z zasadami przyjętymi w naszej funkcji, zadziałał również mechanizm chroniący przed brakiem lub niewłaściwym argumentem ‘Okres_ważności’, pod koniec tabeli widać 3 takie przypadki.

Gdybyśmy nie wprowadzili do naszej funkcji tego mechanizmu, jej wynik wynosiłby zero, które w sformatowane jako data zostałoby wyświetlone jako: ‘1900-01-00’

Utworzoną funkcję możemy też wstawiać w arkusz wpisując jej nazwę bezpośrednio w komórce, dokładnie tak samo jak robimy to ze standardowymi funkcjami Excela.

Przykład 2. Funkcja Słownie

(Arkusz: ‘Tworzenie Funkcji 2’, Moduł: ‘Tworzenie_Funkcji_2’)

Poniżej przedstawiłem zdecydowanie bardziej złożoną funkcję, która zamienia kwoty zapisane liczbami na zapis słowny.

Funkcja będzie szczególnie przydatna dla osób które często przygotowują faktury lub inne dokumenty wymagające oprócz zapisu liczbowego także kwoty zapisanej słownie.

Nawet początkujący użytkownik VBA będzie potrafił skopiować poniższy kod lub moduł do swojego pliku i użyć go zgodnie z opisem znajdującym się w przykładzie pierwszym.

Funkcja radzi sobie dobrze nawet z dużymi liczbami a także z podawaniem właściwych zaokrągleń dla groszy.

Funkcja nie jest wcale tak skomplikowana jak by się to mogło wydawać, składa się głównie z dużej ilości konstrukcji If Then, kilku select case i operacji na tekście, który jest jej wynikiem.

Teoretycznie można by taką funkcję utworzyć nawet z połączenia (wielu) funkcji Excela bez VBA, ale ponieważ zajęłaby nie jedną a co najmniej kilka komórek arkusza, jej używanie byłoby skomplikowane i niepraktyczne.

Function Słownie(x As Variant) As String 'dla liczb od -99 999.99 do 99 999.99

If x < 0 Then w = w & "minus "

x = Format(Abs(x), "000 000 000.00"): m = Left(x, 3): t = Mid(x, 5, 3): j = Mid(x, 9, 3): g = "0" & Right(x, 2)

Select Case m

Case 0

Case 1

w = "jeden milion "

Case Else

w = w & trzy(m)

If Mid(m, 2, 1) <> 1 And (Right(m, 1) = 2 Or Right(m, 1) = 3 Or Right(m, 1) = 4) Then w = w & "miliony " Else w = w & "milionów "

End Select

Select Case t

Case 0

Case 1

w = w & "jeden tysiąc "

Case Else

w = w & trzy(t)

If Mid(t, 2, 1) <> 1 And (Right(t, 1) = 2 Or Right(t, 1) = 3 Or Right(t, 1) = 4) Then w = w & "tysiące " Else w = w & "tysięcy "

End Select

Select Case j

Case 0

If m = 0 And t = 0 Then w = w & "zero złotych " Else w = w & "złotych "

Case 1

If m = 0 And t = 0 Then w = w & "jeden złoty " Else w = w & "jeden złotych "

Case Else

w = w & trzy(j)

If Mid(j, 2, 1) <> 1 And (Right(j, 1) = 2 Or Right(j, 1) = 3 Or Right(j, 1) = 4) Then w = w & "złote " Else w = w & "złotych "

End Select

Select Case g

Case 0

w = w & "zero groszy"

Case 1

w = w & "jeden grosz"

Case Else

w = w & trzy(g)

If Mid(g, 2, 1) <> 1 And (Right(g, 1) = 2 Or Right(g, 1) = 3 Or Right(g, 1) = 4) Then w = w & "grosze" Else w = w & "groszy"

End Select

Słownie = w

End Function

Function trzy(x As Variant) As String

x3 = Val(Left(x, 1)): x2 = Val(Mid(x, 2, 1)): x1 = Val(Right(x, 1))

If x3 = 9 Then w = w & "dziewięćset "

If x3 = 8 Then w = w & "osiemset "

If x3 = 7 Then w = w & "siedemset "

If x3 = 6 Then w = w & "sześćset "

If x3 = 5 Then w = w & "pięćset "

If x3 = 4 Then w = w & "czterysta "

If x3 = 3 Then w = w & "trzysta "

If x3 = 2 Then w = w & "dwieście "

If x3 = 1 Then w = w & "sto "

If x2 = 9 Then w = w & "dziewięćdziesiąt "

If x2 = 8 Then w = w & "osiemdziesiąt "

If x2 = 7 Then w = w & "siedemdziesiąt "

If x2 = 6 Then w = w & "sześćdziesiąt "

If x2 = 5 Then w = w & "pięćdziesiąt "

If x2 = 4 Then w = w & "czterdzieści "

If x2 = 3 Then w = w & "trzydzieści "

If x2 = 2 Then w = w & "dwadzieścia "

If x2 = 1 Then

If x1 = 9 Then w = w & "dziewiętnaście "

If x1 = 8 Then w = w & "osiemnaście "

If x1 = 7 Then w = w & "siedemnaście "

If x1 = 6 Then w = w & "szesnaście "

If x1 = 5 Then w = w & "piętnaście "

If x1 = 4 Then w = w & "czternaście "

If x1 = 3 Then w = w & "trzynaście "

If x1 = 2 Then w = w & "dwanaście "

If x1 = 1 Then w = w & "jedenaście "

If x1 = 0 Then w = w & "dziesięć "

End If

If x2 <> 1 Then

If x1 = 9 Then w = w & "dziewięć "

If x1 = 8 Then w = w & "osiem "

If x1 = 7 Then w = w & "siedem "

If x1 = 6 Then w = w & "sześć "

If x1 = 5 Then w = w & "pięć "

If x1 = 4 Then w = w & "cztery "

If x1 = 3 Then w = w & "trzy "

If x1 = 2 Then w = w & "dwa "

If x1 = 1 Then w = w & "jeden "

End If

trzy = w

End Function

Nie jestem autorem tej funkcji, znalazłem ją w Internecie, strona z której ją zaczerpnąłem niestety nie podawała autora.