Niestandardowa funkcja użytkownika Excel w VBA

Excel posiada ponad 400 wbudowanych funkcji, z których możemy korzystać do woli i w zależności od naszych potrzeb. Ta ilość wydaje się być spora i rośnie z każdą kolejną wersją programu Excel. Jednak tak naprawdę, najczęściej korzystamy zaledwie z kilkudziesięciu najbardziej popularnych formuł, które stosujemy w różnych kombinacjach. Możemy tworzyć formuły zagnieżdżone, w których łączymy ze sobą kilka funkcji. Możemy również stosować formuły tablicowe.

Gdyby jednak okazało się, że na nasze potrzeby chcemy stworzyć jakąś nietypową, niestandardową funkcję to możemy z powodzeniem napisać ją w języku VBA. W wielu przypadkach pozawala to przyspieszyć codzienna pracę i zoptymalizować przeliczenia wewnątrz naszych arkuszy. Napisanie kilku linijek kodu wcale nie zajmuje dużo czasu. Takie niestandardowe funkcje nazywane są również funkcjami użytkownika. We wpisie zademonstruję krok po kroku jak napisać jedną z nich na pewnym konkretnym przykładzie.

Tworzenie funkcji „GenerujListe”

Załóżmy, że potrzebujemy by w jednej komórce zwracana była lista liczb rozdzielona jakimś określonym separatorem. W naszym przypadku będą to kolejne lata ułożone narastająco (np. od 2005 do 2012), ale funkcję z powodzeniem będziemy mogli zastosować dla dowolnych ciągów liczbowych (np. 123 do 223). W Excelu taka funkcja domyślnie nie występuje.

Nasza niestandardowa funkcja będzie przyjmować 3 argumenty i będą to odpowiednio:

1. Start – liczba początkowa na naszej liście;
2. Koniec – liczba końcowa na naszej liście;
3. Separator – znak rozdzielający poszczególne liczby.

Gdy już wiemy jak ma działać nasza formuła to nie pozostaje nic innego jak wejść do edytora kodu VBA i napisać kilka linijek. W przypadku standardowych procedur kod zaczynamy od słowa „Sub” natomiast pisząc funkcję rozpoczynamy od „Function”. W taj samej linii wpisujemy nazwę funkcji, a w nawiasach jej argumenty razem z określeniem rodzaju zmiennych. Dla naszych kolejnych argumentów będą to odpowiednio Start as Integer (liczba), Koniec as Integer (liczba), Separator as String (tekst). Po zamknięciu nawiasu dopisujemy typ zmiennej jaki ma być przez funkcję zwracany. W naszym przypadku będzie to ciąg liczb przechowywany jako tekst (link przeczytaj więcej o zamianie liczby na tekst w Excel), czyli dopisujemy „As String”. Kod od razu zamykamy wpisując kilka linijek niżej „End Function”. Treść będzie znajdować się pomiędzy pierwszą i ostatnią linią kodu.

Oparcie funkcji o pętlę „For i, Next”

Teraz przechodzimy do meritum. Mechanizm działania funkcji będzie oparty na pętli „For i, Next”. Z jej pomocą będziemy przechodzić przez wszystkie wartości liczbowe znajdujące się pomiędzy liczbą startową, a końcową. Pętlę zamykamy z użyciem „Next i”.

Wewnątrz pętli będziemy tworzyć listę lat powiększając ją za każdym przejściem pętli o kolejną wartość. W tym miejscu musimy pamiętać o dodaniu separatora oraz spacji występującej po separatorze, a przed kolejną liczbą. Dodajemy dwie zmienne: a i b. Zadaniem pierwszej (a= ) będzie pobranie kolejnej liczby przetwarzanej przez pętlę (i), uwzględnienie separatora oraz spacji oznaczanej jako „ „. Druga zmienna wewnątrz pętli (b= ) będzie dodawała do dotychczasowego ciągu znaków ostatnią liczbę z separatorem wygenerowaną w pierwszej zmiennej (fragment & a). Pierwsze przejście nada zmiennej b wartość a, każde kolejne będzie sukcesywnie rozbudowywało listę o kolejne a. Pętla będzie pracować narastająco aż dojedzie do ostatniego argumentu przechowywanego w zmiennej „Koniec”.

Co po wyjściu z pętli?

W momencie gdy wartość zmiennej i będzie równa argumentowi „Koniec” nastąpi ostatnie przejście przez pętle oraz wyjście z niej. Aby nasza funkcja niestandardowa zwróciła do arkusza Excel oczekiwaną przez nas wartość musimy określić czego dokładnie oczekujemy. W tym przypadku wynikiem powinien być ciąg liczb wygenerowany przez ostatnia zmienną b. Zapisujemy wiec za pętlą „GenerujListe = b”. W oknie Immediate podglądamy wynik dla przykładowych lat i widzimy poprawny ciąg. Jest jedno małe „ale”. Po ostatniej liczbie w liście również występuje separator ze spacją „; ” a wolelibyśmy żeby go nie było.

W tym celu pobieramy długość naszej listy za pomocą funkcji VBA „Len(b)” i odejmujemy od niej 2. Następnie wynik tego obliczenia zagnieżdżamy w funkcji „Left”, która pobierze z naszego b ilość znaków pomniejszoną właśnie o nadmiarowe 2. W oknie Immediate widzimy teraz, że dwa ostatnie znaki zostały usunięte.

Sprawdzenie funkcji niestandardowej w arkuszu Excel

Nasza niestandardowa funkcja „GenerujListe” jest gotowa do użycia. Nie pozostaje nic innego jak przetestowanie jej bezpośrednio w arkuszu Excel połączonym z modułem zawierającym kod funkcji. W wybranej komórce wstawiamy znak „=” a następnie zaczynamy wpisywać nazwę naszej formuły. Excel na podstawie pierwszych liter podpowie pełną nazwę funkcji.

Ustawiamy odpowiednie argumenty odpowiadające wartości początkowej i końcowej oraz w cudzysłowu określamy typ separatora –  alternatywnie jego również możemy zaczytać z jakiejś komórki arkusza.

Naszą niestandardową funkcję możemy uruchomić również w inny sposób – poprzez wstawienie jej z listy wszystkich formuł dostępnych w skoroszycie. W tym celu wybieramy kategorię „Zdefiniowane przez użytkownika” i kolejnym oknie określamy argumenty. Tak jak w standardowych formułach możemy tutaj również podejrzeć spodziewany wynik, a przynajmniej pierwszą jego cześć.

Kod funkcji „GenerujListe”

Function GenerujListe(Start As Integer, Koniec As Integer, Separator As String) As String
For i = Start To Koniec
a = i & Separator & " "
b = b & a
Next i
GenerujListe = Left(b, Len(b) - 2)
End Function

Modyfikacje funkcji „GenerujListe”

W zależności od naszych potrzeb napisaną funkcję możemy dowolnie modyfikować. Jeśli chcemy aby lista liczb narastała co 10 to dodajemy opcjonalny argument pętli „Step 10”

Kod funkcji „GenerujListePlus10”

Function GenerujListePlus10(Start As Integer, Koniec As Integer, Separator As String) As String
For i = Start To Koniec Step 10
a = i & Separator & " "
b = b & a
Next i
GenerujListePlus10 = Left(b, Len(b) - 2)
End Function<>

Gdybyśmy chcieli utworzyć listę ale z malejącymi wartościami to dopisujemy „Step -1” i zmieniamy w pętli miejsce argumentów „Start” i „Koniec”. Teraz lista będzie tworzona od końca i zamiast narastać będzie maleć o 1 rok.

Kod funkcji „GenerujListeMinus1”

Function GenerujListeMinus1(Start As Integer, Koniec As Integer, Separator As String) As String
For i = Koniec To Start Step -1
a = i & Separator & " "
b = b & a
Next i
GenerujListeMinus1 = Left(b, Len(b) - 2)
End Function

Podsumowanie

Stworzenie własnej funkcji nie jest takie trudne jak by się mogło wydawać. Wcale nie trzeba być programistą Microsoftu żeby to uczynić. Wystarczy samodzielnie napisać kilka linijek kodu VBA i osiągnąć zamierzony rezultat. W opisywanym przypadku funkcja zapisana jest w module jednego skoroszytu więc jest dostępne tylko w jego obrębie (nie zadziała w innych plikach). Gdybyśmy chcieli używać funkcję niestandardową we wszystkich skoroszytach na naszym komputerze to należy umieścić kod w jednym z modułów tzw. skoroszytu makr osobistych PERSONAL. Ale to już temat na osobny wpis. Podobnie jak opisanie możliwość bardziej profesjonalnego zaimplementowania naszej funkcji do Excela – razem z opisem argumentów czy obsługą błędów.

Potrzebujesz przygotować podobną funkcję aby przyspieszyć swoją pracę? A może sam już taką funkcję napisałeś? Skontaktuj się ze ze mną bezpośrednio lub podziel się w komentarzu swoim doświadczeniem 🙂