Tworzenie funkcji w Excel VBA – Funkcje użytkownika

przez Tomasz Kenig|Updated 2017-06-23|12 komentarzy

Spis treści

1. Wprowadzenie do funkcji użytkownika VBA

Zarówno Excel jak i VBA oferują nam szereg użytecznych funkcji. Co jednak, jeśli chcielibyśmy samodzielnie stworzyć funkcję, z której będziemy korzystali w Excelu? Z pomocą przychodzi nam możliwość tworzenia tzw. funkcji użytkownika. Są to funkcje, których kod źródłowy piszemy sami. Możemy z nich korzystać zarówno w kodzie VBA jak i w arkuszu Excela.

2. Najprostsza funkcja użytkownika – Jak napisać funkcję w VBA Excel?

Stwórzmy prostą funkcję użytkownika. Utwórzmy nowy moduł w Edytorze VisualBasic. Funkcja ma zwracać tekst „To jest wynik funkcji” i nie wymaga argumentów. Najprostszy przykład takiej funkcji zamieszczam poniżej:

Function MojaFunkcja()
MojaFunkcja = "To jest wynik funkcji"
End Function

Jak widzisz najprostsza postać funkcji użytkownika wygląda następująco:

Function NazwaFunkcji([agrumenty])
instrukcje
End Function

Zauważ, że obecność argumentów nie jest obowiązkowa. Dla przykładu funkcja arkuszowa DZIŚ() także nie posiada argumentów, które moglibyśmy wpisać z poziomu arkusza danych. Najważniejsze, co należy zapamiętać z naszego schematu, to odniesienie do zmiennej „MojaFunkcja” w instrukcjach. Jest to wynik zwracany przez naszą funkcję. Nazwa zmiennej odpowiada nazwie funkcji. Możemy to porównać do instrukcji Return w innych językach programowania. Z funkcji użytkownika korzystamy analogicznie jak w przypadku każdej innej funkcji. Możemy więc z niej skorzystać zarówno w kodzie VBA jak i w Arkuszu po wprowadzeniu nazwy naszej funkcji.

Kurs Excel VBA - Tworzenie własnych funkcji VBA - Funkcje Użytkownika — Kurs Excel VBA – Tworzenie własnych funkcji VBA – Funkcje Użytkownika

Kurs Excel VBA - Tworzenie własnych funkcji - Funkcje Użytkownika — Kurs Excel VBA – Tworzenie własnych funkcji – Funkcje Użytkownika

3. Argumenty i zaawansowany schemat funkcji

Powyższy schemat funkcji nie określa argumentów jak i typów danych. Jak wiemy zarówno argumenty, jak i wynik zwracany przez funkcje posiadają swoje typy danych. Jeśli ich nie określimy, przyjmą one typ danych Variant. Więcej o typach danych możesz przeczytać w tej części kursu. Bardziej zaawansowany schemat budowy własnych funkcji w Excel VBA wygląda następująco:

 [Poziom deklaracji] Function NazwaFunkcji([argument1 as typ_danych, argument2 as typ_danych...]) as zwracany_typ_danych 
[blok instrukcji] 
End Function

Poziom deklaracji, a więc zadeklarowania w obrębie jakiego zakresu projektu VBA nasza funkcja działa. Jeśli chcemy, by funkcja mogła być wykorzystywana zarówno w arkuszu, jak i w każdym napisanym przez nas kodzie VBA zalecany jest tutaj poziom Public. Jeśli chcemy, by funkcja działała w arkuszu i w obrębie modułu – stosujemy poziom Private. Więcej na temat poziomów deklaracji możesz dowiedzieć się z tego artykułu.
Określamy nazwą argumenty, a więc wartości, które funkcja będzie przerabiała. Określamy także typy danych argumentów, w przeciwnym wypadku argumenty przyjmą typ Variant.
Na koniec powinniśmy określić typ danych dla wartości zwracanych przez funkcję. Jeśli tego nie zrobimy automatycznie będzie to typ Variant. Przy tworzeniu dużych funkcji, które eksploatują znacząco zasoby naszego komputera zalecane jest określenie typów danych. Więcej na temat typów danych pisałem w tej części kursu.

Stwórzmy teraz nową funkcję. Nasza funkcja powinna przyjmować 2 argumenty tekstowe: imię i nazwisko. Wynikiem funkcji powinny być inicjały pisane wielkimi literami stworzone z argumentów. Sam wynik powinien zwracać wartość także w typie danych tekstowych String. Naszą funkcję nazwijmy „INICJALY„. Funkcję zadeklarujmy na poziomie Public:

Public Function INICJALY(imie As String, nazwisko As String) As String
INICJALY = UCase(Left(imie, 1) & Left(nazwisko, 1))
End Function

W naszej nowej funkcji wyciągnęliśmy pierwsze litery imienia i nazwiska za pomocą funkcji LEFT(). Teksty łączymy ze sobą za pomocą operatora „&”. Następnie za pomocą funkcji VBA UCase zamieniamy litery inicjałów na litery wielkie.

4. Debugowanie funkcji – Jak debugować funkcje w VBA?

By sprawdzić, czy nasza funkcja działa w sposób poprawny powinniśmy ją przetestować. Można oczywiście zrobić to po napisaniu funkcji. Jeśli jednak chcemy sprawdzić na poziomie kodu VBA, czy kod naszej funkcji nie zawiera błędów programistycznych powinniśmy skorzystać z debugowania. W Edytorze VisualBasic wybieramy zakładkę Debug, a następnie Complie VBAProject. Nieprawidłowo napisana funkcja zwróci nam błąd, jak na poniższym screenie.

5. Tworzenie własnych funkcji VBA – Zadania (Rozwiązanie możesz wpisać w komentarzu)

5.1. Napisz funkcję zamieniającą numer PESEL na datę urodzenia. Warunki takiej funkcji możesz sprawdzić w tym artykule.

5.2. Utwórz funkcję zliczającą dni pozostałe do końca bieżącego roku.

Dodaj komentarz Anuluj pisanie odpowiedzi

12 komentarzy “Tworzenie funkcji w Excel VBA – Funkcje użytkownika”

12 komentarzy

PawelW
2017-06-23, 21:54

Solidny artykuł. Wszystko napisane, co powinno być
Odpowiedz
GreG
2017-09-14, 11:42

Zadanie 1:
Public Function pesel(NrPesel As String) As String
If IsNumeric(Mid(NrPesel, 3, 2)) <= 12 Then
pesel = "19" & Mid(NrPesel, 1, 2) & " " & Mid(NrPesel, 3, 2) & " " & Mid(NrPesel, 5, 2)
Else: pesel = "20" & Mid(NrPesel, 1, 2) & " " & Mid(NrPesel, 3, 2) – 20 & " " & Mid(NrPesel, 5, 2)
End If
End Function
I tutaj występują dwa problemy:
1.funkcja przy napisaniu peselu zaczynającego się od 0 – pomija 0 (nie wiem czemu gdyż zadeklarowałem zmienna pesel jako tekst, a nie jako liczbę)
2.funkcja nie sprawdza warunku IsNumeric(Mid(NrPesel, 3, 2)) <= 12
Jakieś sugestie?
Odpowiedz
- Tomasz Kenig Autor wpisu
  2017-09-15, 19:47
  
  Cześć,
  Jeśli chodzi o funkcję IsNumeric, to zwróci wartość True także w przypadku liczby pisanej jako tekst. W zasadzie, to zwraca True, gdy zmienna jest liczbą, lub może być zamieniona na liczbę. Można to sprawdzić na tym przykładzie:
  Sub IsNumericCheck() MsgBox IsNumeric("10") MsgBox IsNumeric(10) MsgBox IsNumeric(Str(10)) MsgBox IsNumeric("010") End Sub
  Co do funkcji, przerobiłem na szybko. Funkcja CINT zamienia typ string na Integer.
  Nie jest potrzebna, ale mamy pewność, że wszystko zadziała.
  Public Function pesel(NrPesel As String) As String
  If CInt(Mid(NrPesel, 3, 2)) <= 12 Then
  pesel = DateSerial(1900 + CInt(Mid(NrPesel, 1, 2)), CInt(Mid(NrPesel, 3, 2)), CInt(Mid(NrPesel, 5, 2)))
  Else: pesel = DateSerial(2000 + CInt(Mid(NrPesel, 1, 2)), CInt(Mid(NrPesel, 3, 2)) - 20, CInt(Mid(NrPesel, 5, 2)))
  End If
  End Function
GreG
2017-09-14, 11:51

zadanie 2:
Public Function IleJeszczeDni()
IleJeszczeDni = DateDiff(„d”, Date, „2017-12-31”)
End Function
Odpowiedz
Hubert
2018-02-23, 16:30

Mam pytanie, gdzie robię błąd?
`Public Function PDATA(PESEL As String) As Date
Dim Dzien As Integer
Dzien = Mid(PESEL, 5, 2)
Range(„B2”) = Dzien
Dim Miesiac As Integer
If Mid(PESEL, 3, 2) > 20 Then
Miesiac = Mid(PESEL, 3, 2) – 20
Else: Miesiac = Mid(PESEL, 3, 2)
End If
Range(„B3”) = Miesiac
Dim Rok1 As Integer
If Mid(PESEL, 1, 2) > 18 Then
Rok1 = 19
Else: Rok1 = 20
End If
Range(„B4”) = Rok1
Dim Rok2 As Integer
Rok2 = Mid(PESEL, 1, 2)
Range(„B5”) = Rok2
PDATA = DateSerial(Rok1 & Rok2, Miesiac, Dzien)
End Function
Sub PDATAMAKRO()
Range(„C2”) = PDATA(96012306276#)
End Sub
Makro działa bez problemu ale funkcja nie chce działać w arkuszu, wyskakuje błąd „#NAZWA”
Może ktoś pomóc?
Odpowiedz
Pieknywojciech
2018-03-23, 21:51

Zadanie 5.2
Option Explicit
Sub ile_dni_do_konca_roku()
Dim bierzacy_rok As Integer
Dim dni_do_konca_roku As Integer
Dim ostatni_dzien_roku As Date
bierzacy_rok = DatePart(„yyyy”, Date)
ostatni_dzien_roku = DateSerial(bierzacy_rok, 12, 31)
dni_do_konca_roku = DateDiff(„d”, Date, ostatni_dzien_roku)
End Sub
O tyle lepiej, że będzie działać też w przyszłym roku 😉
Odpowiedz
Krog
2018-06-12, 13:32

Zadanie 5.1
Function DataPesel(PESEL As String) As Date
If Mid(PESEL, 3, 1) < 2 Then
DataPesel = DateSerial(1900 + Mid(PESEL, 1, 2), Mid(PESEL, 3, 2), Mid(PESEL, 5, 2))
ElseIf Mid(PESEL, 3, 1) = 2 Then
DataPesel = DateSerial(2000 + Mid(PESEL, 1, 2), Mid(PESEL, 3, 2), Mid(PESEL, 5, 2))
End If
End Function
Zadanie 5.2
Function IleDoKonca(Data As Date) As String
IleDoKonca = DateDiff(„d”, Data, DateSerial(Year(Data), 12, 31))
End Function
Odpowiedz
Łukasz
2018-09-13, 10:52

5.2
komórki zawierające numery PESEL formatuję jako „Tekst” albo jako „Niestandardowe” i wpisuję 11 zer: 00000000000.
komórki zawierające funkcję formatuję jako „Data”.

Public Function PESEL_data(PESEL As String) As Date
PESEL_data = DateSerial(Left(PESEL, 2), Mid(PESEL, 3, 2), Mid(PESEL, 5, 2))
End Function

Odpowiedz
kaczan
2018-12-07, 13:29

Public Function IleDniDoKoncaRoku(datData As Date) As Integer
Dim datKoniecRoku As Date
If datData = 0 Then
datData = Date
Else
End If
datKoniecRoku = DateSerial(Year(datData), 12, 31)
IleDniDoKoncaRoku = DateDiff(„d”, datData, datKoniecRoku)
End Function
Odpowiedz

Weronika
2021-08-29, 15:04

Public Function Urodziny(pesel As String) As String
Dim Dzien As String
Dim Miesiac As String
Dim Rok As String
'if sprawdzamy, czy liczba dzień mieści się w przedziale 1-31 oraz przypisujemy wartość do zmniennej dzien
If Mid(pesel, 5, 2) >= 1 & Mid(pesel, 5, 2) <= 31 Then
Dzien = Mid(pesel, 5, 2)
Else
MsgBox („Podaj właściwy nr pesel”)
End If
'if sprawdzamy, czy miesiąc dotyczy osób urodzonych po ’00 roku czy nie
If Mid(pesel, 3, 2) >= 21 Then
Miesiac = Val(Mid(pesel, 3, 2)) – 20
Else
Miesiac = Mid(pesel, 3, 2)
End If
'if sprawdzamy, czy miesiąc dotyczy osób urodzonych po ’00 roku czy nie
If Mid(pesel, 3, 2) >= 21 Then
Rok = „20” & Left(pesel, 2)
Else
Rok = „19” & Left(pesel, 2)
End If
Urodziny = Dzien & „.” & Miesiac & „.” & Rok
End Function

Public Function DniDoKoncaRoku() As Integer
Dim Dzisiaj As Date
Dim Rok As String
Dim KoniecRoku As String
'wybranie bieżącego roku do obliczenia pozostałych dni do końca roku
Dzisiaj = Now()
Rok = Year(Now)
KoniecRoku = „31/12/” & Rok
'obliczenie różnicy (dni) między dzisiaj a 31.12
DniDoKoncaRoku = DateDiff(„d”, Dzisiaj, DateValue(KoniecRoku))
End Function
Odpowiedz