Pchełki VBA – odcinek 3: własna funkcja

https://xpil.eu/ZOeWq 📋

Dziś pokażę jak napisać własną, niestandardową funkcję, której można następnie używać w arkuszu.

Po pierwsze jednak, zanim kiedykolwiek zaczniemy pisać własną funkcję arkusza, upewnijmy się, że to, co chcemy uzyskać, nie istnieje już w zestawie funkcji standardowych (wbudowanych). Małomiękki z wersji na wersję dorzuca do Excela mnóstwo nowych funkcji i nie warto odkrywać koła ani wyważać otwartych drzwi.

Czasami jednak zdarzają się sytuacje, w których własna funkcja okazuje się nieodzowna. Wyobraźmy sobie na przykład następujący scenariusz: arkusz z listą plików (jedna kolumna), w którym chcemy dołożyć drugą kolumnę wyliczaną pokazującą rozmiar każdego z plików, oraz trzecią z informacją o dacie utworzenia pliku. Po pobieżnym przejrzeniu listy funkcji standardowych szybko orientujemy się, że nie ma tu żadnych rzeczy związanych z operacjami na plikach - trzeba więc napisać coś własnego.

Inny przykład: mamy kolumnę z datami, chcemy w osobnej kolumnie wyświetlić odpowiadające im polskie nazwy dni tygodnia. Oczywiście w polskiej wersji Excela (bądź też na komputerze z polskimi ustawieniami regionalnymi) da się to uzyskać prostym sformatowaniem komórki. Jednak jeżeli chcemy, żeby nazwy dni tygodnia nie były w żaden sposób zależne od ustawień regionalnych, najprościej będzie napisać własną funkcję arkusza. Rzecz jasna można zamiast tego zagnieździć 6 IF-ów, ale rozwiązanie z VBA jest o wiele bardziej eleganckie.

A więc - do boju.

Uruchamiamy Excela, otwieramy edytor VBA (Alt-F11). Wstawiamy nowy moduł (czyli prawomysz na drzewku po lewej, Insert => Module).

Następnie wpisujemy (bądź też kopiujemy stąd) następujący kod:

Public Function PolskiDzienTygodnia(data As Date) As String
Dim dzien As Integer
dzien = Weekday(data, vbMonday)
PolskiDzienTygodnia = Switch(dzien = 7, "Niedziela", dzien = 1, "Poniedziałek", dzien = 2, "Wtorek", dzien = 3, "Środa", dzien = 4, "Czwartek", dzien = 5, "Piątek", dzien = 6, "Sobota", True, "???")
End Function

Sprawdzamy czy działa: idziemy do arkusza, wpisujemy w dowolnej komórce formułę:

=PolskiDzienTygodnia(TODAY())

Jeżeli w wyniku dostaniemy polską nazwę bieżącego dnia tygodnia, mamy sukces.

Teraz - tradycyjnie - objaśnienie kodu:

Public Function PolskiDzienTygodnia(data As Date) As String

Function to - podobnie jak Sub - blok kodu VBA do późniejszego wykorzystania. W odróżnieniu od bloku Sub, który po prostu wykonuje podany kod, blok Function, po wykonaniu kodu dodatkowo zwraca jakąś wartość. Typ zwracanej wartości podajemy na końcu linii, po słówku As. W naszym przypadku zwracanym typem jest String, czyli tekst (nazwa dnia tygodnia jest tekstem).

A skąd funkcja ma "wiedzieć", z jakiej daty ma ten dzień tygodnia wyznaczyć? Ano, z parametru data (nazwę parametru ustalamy dowolnie - dobrze, żeby reprezentowała sens przekazywanej wartości). Tak samo jak w bloku Sub.

Ponadto, parametr możemy przekazać bezpośrednio lub przez referencję, ale to kiedy indziej, żeby nie namieszać.

Dim dzien As Integer

Tutaj deklarujemy zmienną o nazwie dzien, typu całkowitoliczbowego. Dlaczego? Za chwilę się okaże.

dzien = Weekday(data, vbMonday)

Tu odbywa się właściwa magia, czyli wyciągnięcie numeru dnia tygodnia z podanej daty. Funkcja Weekday jest wbudowana w VBA. Działa ona tak, że zwraca liczbę z zakresu 1-7, reprezentującą kolejny dzień tygodnia. Jednak, ponieważ w niektórych krajach tydzień zaczyna się w niedzielę (a więc poniedziałek ma numer 2, wtorek 3 itd. aż do soboty, która ma numer 7), a w innych krajach w poniedziałek (czyli sobota ma numer 6 a niedziela 7), funkcję Weekday wyposażono w drugi parametr mówiący od którego dnia tygodnia liczymy. Stała vbMonday podana jako drugi parametr funkcji Weekday mówi, że poniedziałek ma numer 1, wtorek 2 itd. Gdybyśmy tego vbMonday nie podali, zostałaby użyta wartość domyślna (czyli chyba niedziela).

PolskiDzienTygodnia = Switch(dzien = 1, "Poniedziałek", dzien = 2, "Wtorek", dzien = 3, "Środa", dzien = 4, "Czwartek", dzien = 5, "Piątek", dzien = 6, "Sobota", dzien = 7, "Niedziela", True, "???")

Tu proszę o chwilę skupienia. W tej jednej linii kodu wydarzają się bowiem dwie bardzo ważne rzeczy.

Po pierwsze, użyliśmy funkcji Switch. Funkcja Switch - często niedoceniana przez programistów - to bardzo potężne narzędzie do wyliczania wartości warunkowych.

Jak to działa?

Każdy argument funkcji Switch znajdujący się na pozycji nieparzystej to warunek. Następujący zaraz za nim argument na pozycji parzystej to wartość zwracana w przypadku, kiedy dany warunek jest prawdziwy. Sprawdzanie warunków odbywa się do napotkania pierwszego prawdziwego, a następnie funkcja Switch zwraca występującą po tym warunku wartość. Przykład:

Switch(2 = 3, 4, 5 = 6, 7, 8 = 8, 9, 12 = 12, 10)

W powyższym przykładzie przekazujemy funkcji Switch osiem argumentów. Cztery z nich to warunki (2=3, 5=6, 8=8 oraz 12=12), cztery pozostałe to wartości (4, 7, 9, 10). Funkcja zacznie od sprawdzenia pierwszego warunku. Ponieważ warunek 2=3 jest fałszywy, argument numer 2 (czwórka) zostanie zignorowany i funkcja sprawdzi drugi warunek. Ponieważ 5=6 jest również fałszem, argument numer 4 (siódemka) zostanie zignorowany i nastąpi sprawdzenie trzeciego warunku 8=8. Ponieważ ten warunek jest prawdziwy, funkcja Switch zwróci występującą po nim wartość 9 i zakończy działanie.

Uwaga: w rzeczywistości, pomimo iż warunek 12=12 następuje po warunku 8=8, będzie on również sprawdzony. Ponieważ jednak nie jest on pierwszym prawdziwym warunkiem na liście, wartość 10 będzie zignorowana. Ogólnie rzecz biorąc, wszystkie warunki będą sprawdzone - bez względu na to, który z nich jest prawdziwy. Warto o tym pamiętać pisząc bardziej skomplikowane wyrażenia.

Załóżmy teraz, że parametr data jest równy #7 listopada 2011# (dzisiaj, czyli poniedziałek). Funkcja Weekday zwróci jedynkę (bo poniedziałek jest pierwszym dniem tygodnia, licząc od poniedziałku). A więc, pierwszy warunek przekazany funkcji Switch (dzien = 1) jest prawdziwy i funkcja zwróci wartość "Poniedziałek".

Nadmieniłem wcześniej, że ostatnia linia naszej funkcji zawiera dwa ważne elementy. Jednym z nich jest wywołanie funkcji Switch. A drugi?

Drugi to sposób, w jaki funkcja zwraca wartość. Odbywa się to poprzez podstawienie tej wartości do nieistniejącej zmiennej o nazwie identycznej z nazwą funkcji. Jak widać, wykonuje się:

PolskiDzienTygodnia = ...

Cokolwiek pojawi się w miejscu trzech kropek, o ile tylko jest wartością tesktową, będzie potraktowane jako wartość do zwrócenia przez funkcję. W naszym przypadku będzie to wynik funkcji Switch. W tym momencie funkcja już "wie" co "wypluć" na wyjściu.

W odróżnieniu od większości języków programowania, w których zwrócenie wartości przez funkcję jest równoznaczne z zakończeniem działa funkcji i przekazaniem sterowania do kodu, który ją wywołał, w VBA możemy przekazać funkcji wartość a następnie kontynuować wykonywanie funkcji. Zdania programistów na temat przydatności / elegancji takiego podejścia są podzielone.

Miłego kodowania!

https://xpil.eu/ZOeWq 📋