Tworzenie funkcji w Excel VBA – Funkcje użytkownika 9


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:

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

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, 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:

 

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

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

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.

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

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

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

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

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

Twój adres email nie zostanie opublikowany.

9 komentarzy do “Tworzenie funkcji w Excel VBA – Funkcje użytkownika

  • GreG

    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?

    • Tomasz Kenig Autor wpisu

      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

  • Hubert

    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?

  • Pieknywojciech

    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 😉

  • Krog

    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

  • Łukasz

    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

     

     

  • kaczan

    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