UDF – formuły z funkcjami użytkownika

Za pomocą UDF (user defined functions, funkcje zdefiniowane przez użytkownika) programista może stworzyć nowe funkcje do użycia w formułach. W poniższy sposób używam moją nową funkcję:

Kod znajduje się w module VBA:

Option Explicit

Function SUMUJPARZYSTE(rng As Range)
  Dim lr_cell
  
  For Each lr_cell In rng
    If lr_cell.Value Mod 2 = 0 Then
      SUMUJPARZYSTE = SUMUJPARZYSTE + lr_cell.Value
    End If
  Next lr_cell
End Function

Tworzy je się tak samo jak zwykłe funkcje VBA. Umieszczone w module kodu mogą być używane w tym samym pliku. Można je też dodać do pliku personal  lub dodatku. Funkcje UDF podlegają jednak kilku ograniczeniom.

Ograniczenia

Dzięki UDF programista może rozszerzyć wachlarz funkcji dostarczanych przez Excel. Tak jak zwykłe funkcje mogą jedynie zwracać wartość do komórki w której się znajdują. Nie mogą więc robić poniższych:

  • Dodawać, usuwać i formatować komórki na arkuszu,
  • Edytować komórki w arkuszu,
  • Modyfikować zmienne środowiskowe, takie jak odświerzanie ekranu, czy przeliczanie automatyczne,
  • Ustawiać atrybuty czy wywołuwać większość metod modelu obiektowego Excela,

Dane wejściowe powinny  być zawsze pobierane przez argumenty, czyli bez odwoływania się do obiektu Range. Odwołanie do danych przez Range może doprowadzić do niespodziewanych rezultatów, ponieważ zaburza Excelowi drzewo przeliczania formuł. Nasza formuła może nie być przeliczona kiedy powinna.

Zwracanie błędów

UDF mogą zwracać błędy tak jak standardowe funkcje. Funkcja CVErr tworzy odpowiedni variant.

Function Podziel(A As Double, B As Double) As Variant
  If B = 0 Then
    Podziel = CVErr(xlErrDiv0)
  Else
    Podziel = A / B
  End If
End Function

Poniższe stałe mogą być używane do zwracania innych błędów.

  • xlErrDiv0 – #DIV/0
  • xlErrNA – #N/A
  • xlErrName – #NAME?
  • xlErrNull – #NULL
  • xlErrNum – #NUM
  • xlErrRef – #REF
  • xlErrValue – #VALUE

Argumenty opcjonalne

Poniżej znajduje się przykład w którym drugi argument jest opcjonalny. Funkcją IsMissing sprawdzam czy argument został przekazany.

Function sumuj_co_drugi(ref As Range, Optional jakie As Variant) As Variant Dim lref_cell As Range Dim lint_modulo As Integer If IsMissing(jakie) = True Then jakie = gc_Parzyste ElseIf jakie <> gc_Parzyste And jakie <> gc_NieParzyste Then sumuj_co_drugi = CVErr(xlErrValue) Return End If If jakie = gc_Parzyste Then lint_modulo = 0 Else lint_modulo = 1 For Each lref_cell In ref.Cells If lref_cell.Value Mod 2 = lint_modulo Then sumuj_co_drugi = sumuj_co_drugi + lref_cell.Value End If Next lref_cell End Function Czytaj więcej...

Kurs VBA – Klasy – Atrybuty i właściwości

Zastanawiałem się przez jakiś czas czy atrybuty i właściwości podzielić na dwa osobne posty. Występują one bardzo często razem i poza klasą używa się je tak samo, jednak należy pamiętać, że są to dwie osobne funkcjonalności klas.

Atrybuty określają aktualny stan obiektu. Dla przykładowej klasy Samochód może to być:

  • Prędkość
  • Pojemność
  • Czy drzwi są zamknięte?
  • Czy światła są zapalone?
  • Stan oleju

Spójrzmy na taką (bardziej praktyczną) instrukcję:

If gr_plik.IsOpen = True Then

Mamy tutaj atrybut, zadeklarowany następująco w module klasy.

Public IsOpen As Boolean

Przybiera wartość True, jeżeli plik jest otwarty lub False, jeżeli zamknięty. W metodzie otwierającej plik ustawiamy ten atrybut na True. Mogę więc napisać gdzieś:

If gr_plik.IsOpen = True Then
   MsgBox "Plik jest wciąż otwarty"
End If

Czy ma w takim razie sens poniższa instrukcja?

gr_plik.IsOpen = False

Pozornie ma sens, jest jednak niebezpieczna. Ostatecznie zamykanie pliku nie może się ograniczać do ustawienia wartości jakieś zmiennej, plik należy zapisać. Z pomocą przychodzą właściwości, dzięki którym możemy tworzyć coś pomiędzy atrybutami a metodami. Poza klasą używa się je jak atrybuty. W środku klasy jest to jednak szereg wykonywanych instrukcji. Utworzę więc właściwość:

Property Let isOpen(iv_IsOpen As Boolean)
  If iv_IsOpen = False Then
    CloseFile
  End If
End Property

Dzięki temu powyższe przypisanie wartości False uruchamia instrukcje, które wywołują metodę CloseFile.

Wkrótce wyjaśnię to wszystko dokładniej. Przejdźmy więc do opisu używania atrybutów i właściwości

Atrybuty

Deklaruje się je jak każdą inną zmienną modułową. Mogą być prywatne i publiczne. Zadeklaruję dwie zmienne.

Private Wynagrodzenie As Double
Public Nazwisko As String

Atrybuty publiczne mogę używać poza klasą. Poniżej widać, że intelisense podpowiada mi atrybut publiczny. Prywatne oczywiście mogą być używane tylko w metodach tej klasy.

Właściwości

Tutaj wreszcie pojawia się jakaś nowość w stosunku do zwykłych modułów. Właściwości deklaruje się przy pomocy specjalnych słów kluczowych.

  1. Get – zwraca wartość lub obiekt
  2. Let – ustawia wartość
  3. Set – ustawia obiekt

Public Property Get wlasciwoscNazwa () As Type End Property Public Property Let wlasciwoscNazwa(argumentNazwa As Type ) End Property Public Property Set wlasciwoscNazwa(argumentNazwa As Type ) End Property Czytaj więcej...

Kurs VBA – Klasy – Metody

Metody są różnymi działaniami, które może wykonywać modelowana przez naszą klasę rzecz. Modelując samochód możemy dać mu metody:
  • hamuj
  • przyspieszaj
  • włącz światła
  • wyłącz swiątła
  • otwórz bagażnik
  • itd.

Oczywiście w praktyce nie będziemy modelować samochodu. Bardziej praktyczna klasa może służyć do zapisywania danych do pliku. Metody w takiej klasie mogą wyglądać tak: Czytaj więcej...

Kurs VBA – Klasy – Tworzenie i inicjalizacja

W tej części kursu zobaczymy jak tworzyć i używać klasy. Nie będę się wdawał w szczegóły, które poznacie w kolejnych postach. Chciałbym abyście byli przygotowani do (technicznie) swobodnego tworzenia i używania klas, aby stosować elementy przedstawione później.

Stworzymy klasę, które będzie przechowywać oraz wypisywać do arkusza dane pracownika.

 

Definicja klasy

Każda klasa mieści się w osobnym module w pliku Excel. Moduł klasy tworzymy podobnie jak zwykłe moduły z kodem. Klikam prawym przyciskiem na dowolnej pozycji w oknie projektu i wybieram Insert / Class Module.

Jak widać dodał się nowy katalog z nowym modułem. Cały kod zawarty w tym nowym module będzie dotyczył jednej klasy.

Kiedy moduł jest zaznaczony w drzewie projektu, możemy zmienić nazwę klasy w oknie Properties.

W oknie kodu umieśćmy poniższe instrukcje:

Option Explicit

Public Name As String
Public Surname As String
Private pAge As String

Public Property Get Age() As String
 Age = pAge
End Property

Public Property Let Age(value As String)
 If value < 0 Then
  pAge = 0
 Else
  pAge = value
 End If
End Property

Sub writeData(ir_TargetRange As Range)
 ir_TargetRange = Name
 ir_TargetRange.Offset(0, 1) = Surname
 ir_TargetRange.Offset(0, 2) = Age
End Sub

Klasa zawiera trzy atrybuty.

  • Imię
  • Nazwisko
  • Wiek
Zawiera też dwie specjalne metody do zapisu i odczytu wieku oraz najważniejszą metodę do wypisywania danych we wskazanym miejscu arkusza. Jak widać atrybuty definiuje się jak każdą inną zmienną do tej pory. Definicja metody wygląda jak definicja zwykłej procedury.

Inicjalizacja obiektu

Oczywiście powyższa definicja to dopiero schemat, który nic nie robi. Atrybuty nie mają zarezerwowanego miejsca w pamięci. Musimy więc stworzyć zwykły moduł z kodem i wkleić tam poniższy przykład:

Option Explicit Dim gr_Employee As Person Sub test()     Set gr_Employee = New Person          gr_Employee.Name = "Jan"     gr_Employee.Surname = "Kowalski"     gr_Employee.Age = 34          gr_Employee.writeData Range("A1")      End Sub Czytaj więcej...

Kurs VBA – Klasy – Wstęp, teoria

Poniższy tekst rozpoczyna kilka rozdziałów na temat programowania obiektowego. Pewnie dało by się to wszystko skrócić i umieścić w jednej części, ale cóż, taka ze mnie gaduła. Pisząc kolejne wiersze zakładam, że czytelnik porusza się względnie swobodnie w programowaniu VBA. Nie istotne czy nabył tą wiedzę przy pomocy niniejszego kursu czy nie. Nie można programować w VBA bez kontaktu z programowaniem obiektowym, czyli modelem obiektowym Excela. W tej i kolejnych częściach kursu dowiemy się po prostu jak rozszerzać nasz program o nasze własne klasy. Dzięki temu programy mogą stać się bardziej czytelna a ich projektowanie łatwiejsze. Na temat programowania obiektowego napisano mnóstwo książek, na pewno nie mam zamiaru napisać tutaj kolejnej, tym bardziej, że trudno jest znaleźć język programowania, który by spełniał oczekiwania uczonych głów co do realizacji wszystkich cech programowania obiektowego. Sam nie uważam się za wielkiego specjalistę z tego zakresu. Na szczęście wiedza praktyczna i teoretyczna nie muszą iść w parze. Po poniższe części teoretycznej przejdziemy do różnych przykładów praktycznych. Z pewnością VBA tylko w drobnym stopniu realizuje wymagania programowania obiektowego, ale wciąż możemy to wykorzystać do swoich celów. Programowanie obiektowe możemy realizować poprzez Abstrakcję i Hermetyzację.

Abstrakcja

Każdy obiekt w systemie służy jako model abstrakcyjnego „wykonawcy”, który może wykonywać pracę, opisywać i zmieniać swój stan oraz komunikować się z innymi obiektami w systemie bez ujawniania, w jaki sposób zaimplementowano dane cechy. Cyt. Wikipedia

Klasą nazywamy pewien schemat „realnego” bytu. Sama klasa jest dopiero definicją, która jest realizowana przez obiekty danej klasy. Często przytaczanym przykładem jest przypadek samochodu. Klasa to zbiór papierowych czy komputerowych schematów. Określają one rozmiar, możliwe kolory, budowę silnika, sposób użytkowania itd. Obiektem jest natomiast konkretna sztuka auta zbudowana no podstawie tego schematu. Klasę auto możemy zdefiniować w następujący sposób (w kolejnej części kursu napiszę dokładnie jak „pisać” kod klas):

Option Explicit

Public pubSpeed As Integer

Public Sub SpeedUp()
    pubSpeed = pubSpeed + 10
End Sub

Public Sub SpeedDown()
    pubSpeed = pubSpeed - 10
End Sub

Oczywiście pełna definicja klasy realizującej funkcje samochodu, zajęła by tysiące linii. Powyżej mamy tylko dwie metody, realizujące przyspieszanie i zwalnianie, oraz jeden atrybut przechowujący prędkość.

Wciąż jest to jedynie definicja klasy. Nazwałem ją Samochód. Żeby ją używać muszę zadeklarować obiekt. Tutaj już wchodzimy w znane nam rejony.
Dim MojSamochod As Samochod

Kolejnym krokiem jest inicjalizacja obiektu:

Set MojSamochod = New Samochod

Ostatecznie mogę przyspieszyć moim samochodem.

MojSamochod.SpeedUp

Podsumowując, dzięki klasom możemy w jednym miejscu zgromadzić wszystkie cechy modelowanego obiektu.

Hermetyzacja

Czyli ukrywanie implementacji, enkapsulacja. Zapewnia, że obiekt nie może zmieniać stanu wewnętrznego innych obiektów w nieoczekiwany sposób. Tylko własne metody obiektu są uprawnione do zmiany jego stanu. Cyt. Wikipedia

W jednej z poprzednich części kursu opisałem widoczność zmiennych. Daje ona nam gwarancję, że zmienne będą zmieniane w tylko w określonej części kodu. Programowanie obiektowe idzie krok naprzód. Spójrzmy na poniższy przykład:

Option Explicit Const conMaxSpeed As Integer = 180 Const conMinSpeed As Integer = 0 Const conStepSpeed As Integer = 10 Private prvSpeed As Integer Public Sub PressAccPedal()     prvSpeed = prvSpeed + conStepSpeed          If prvSpeed > conMaxSpeed Then         prvSpeed = conMaxSpeed     End If End Sub Public Sub PressBrakePedal()     prvSpeed = prvSpeed - conStepSpeed          If prvSpeed < conMinSpeed Then         prvSpeed = conMinSpeed     End If End Sub Czytaj więcej...

Kurs VBA – cz. 26 – Formularze – Metody

Czas na ostatnią część tryptyku atrybuty-zdarzenia-metody. Każdy obiekt formularza ma przypisany zestaw metod. Na szczęście jest ich tylko kilka (istotnych). Zdecydowanie w pracy z formularzami najważniejsze są atrybuty i zdarzenia.

Metody wywołujemy przez połączenie nazwy obiektu i nazwy metody, jak w przypadku każdego innego obiektu.

Najważniejsze metody

Show (pokaż)

Pokazuje formularz. Wspominana już we wcześniejszych cześciach kursu.

Hide (ukryj)

Ukrywa formularz przed użytkownikiem. Wszystkie kontrolki, razem ze swoimi wartościami dalej „istnieją” i możemy się do nich odnosić w programie.

Używamy tej metody, jeżeli przewidujemy, że użytkownik będzie chciał wrócić do tego ekranu i zobaczyć te same wartości, które były, kiedy go opuszczał. Na przykład możemy mieć pomocniczy formularz z listą adresową, który może być otwierany kilka razy, żeby wybrać adres do głównego formularza. Lista będzie uzupełniana tylko przy pierwszym otwarciu. Czytaj więcej...

Kurs VBA – cz. 25 – Formularze – Zdarzenia

W tej części kursu opiszę najważniejsze zdarzenia formularza i jego kontrolek. W zdarzeniach możemy umieścić kod, który uruchomi się automatycznie w odpowiedzi na działanie użytkownika.Jest to główna metoda programowania formularzy. Ostatecznie taka jest ich filozofia, program wyświetla pewien szablon przed użytkownikiem i czeka na jego reakcję. Użytkownik może zacząć wypełniać pierwsze pole, może zacznie od drugiego, może kliknie od razu ok albo się rozmyśli i zamknie okno. Wszystkie te działania uruchamiają określone zdarzenia.

Kod zdarzeń umieszczamy w pliku kodu formularza. Możemy go włączyć wybierając View Object po kliknięciu prawym przyciskiem na formularzu w drzewku projektu.

Przełączać się pomiędzy widokiem formularza a kodem formularza możemy używając na zmianę F7 i shift + F7 albo wybierając View Code lub View Object w powyższym menu.

 Dodawanie zdarzeń

Dodawaniem zdarzenia nazywamy wypełnianie go kodem w pliku kodu formularza. Najłatwiej dodać kod do zdarzenia Click. Wystarczy dwa razy kliknąć kontrolkę na formularzu w trybie edycji.

W przypadku pól tekstowych zostanie dodane zdarzenie Change, które jest ważniejsze dla tego typu kontrolki. O tym jednak w dalszej części kursu.

Inne zdarzenia też można bardzo łatwo dodać.

  1. Otwieramy widok kodu danego formularza.
  2. Na górnej liście wybieramy interesującą nas kontrolkę lub sam formularz. Widać na tej liście tylko kontrolki z formularza wybranego w drzewku projektu.
  3. Prawa lista jest wtedy uzupełniana o zdarzenia związane z daną kontrolką. Po wybraniu pozycji z prawej listy, zdarzenie zostaje dodane do pliku.

Jak widać w trakcie zostało dodane zdarzenie Click (jeszcze go nie było). Jest to swego rodzaju „ułatwienie”. Zdarzenie Click występuje prawie zawsze, ponieważ myszka jest podstawową metodą poruszania się po formularzu. Jeżeli nie chcemy programować tego zdarzenia, można je usunąć.

Pomoc do zdarzeń

Dokładny opis każdego zdarzenia otrzymamy po zaznaczeniu jego nazwy i kliknięciu F1.

 

W przypadku zdarzeń bardzo istotne są argumenty. Dzięki nim wiemy np. gdzie była myszka w momencie naciśnięcia przycisku albo jaka litera została wpisana w polu tekstowym. Dodatkowo, modyfikując wartość argumentów możemy wpływać na proces, w którym znajduje się zdarzenie. Na przykład zapobiegać zamknięciu formularza po kliknięciu krzyżyka.

Wszystkie strony pomocy wyglądają podobnie:

Strona pomocy zaczyna się od opisania sytuacji, w których dane zdarzenie występuje.

W części Syntax opisana jest składnia zdarzenia. Znajdziemy tam kolejność argumentów. Po dodaniu zdarzenia, to wszystko wpisywane jest automatycznie.

W części Settings znajdziemy dodatkowe wyjaśnienia dotyczące argumentów. W powyższym przykładzie dowiemy się, że argument button może przyjmować wartości 1,2 lub 4, zależnie, który przycisk myszki został wciśnięty.

Ostatnia część to Remarks, czyli dodatkowe uwagi, opisujące ogólny kontekst zdarzenia.

Najważniejsze zdarzenia

Jak w przypadku atrybutów, zobaczymy teraz najważniejsze, uniwersalne zdarzenia.

Click,  

Zdecydowanie najczęściej używane zdarzenie. Umieszczamy w nim kod, który ma się wykonać po kliknięciu kontrolki myszką. Używamy go więc przede wszystkim dla wszystkich przycisków. Istnieje także kilka innych zdarzeń związanych z myszką, które wykonują się w określonej kolejności.

  1. MouseDown
  2. MouseUp
  3. Click
  4. DblClick
Change

Wywołuje się, kiedy zmienia się atrybut Value. Może to być spowodowane działaniem użytkownika, lub wykonaniem programu. Wydarzenie to może być używane do synchronizacji wartości różnych kontrolek. Poniżej pole combo jest filtrowane wartościami z pola tekstowego w czasie rzeczywistym.

Można to uzyskać w poniższy sposób (na formularzu są dwie kontrolki Txt_Filter i Lbx_MonthList).

Private Sub Txt_Filter_Change()
    AddItems
End Sub

Private Sub UserForm_Initialize()
    AddItems
End Sub

Private Sub AddItems()
Dim lr_Cell As Range
    Lbx_MonthList.Clear
    
    For Each lr_Cell In Worksheets("Sheet2").Range("A1:A12").Cells
        If lr_Cell Like "*" & Txt_Filter & "*" Then
            Lbx_MonthList.AddItem lr_Cell
        End If
    Next lr_Cell
    
End Sub
Initialize

Zdarzenie się uruchamia, kiedy pojawia się formularz. Kod wykona się, zanim użytkownik będzie miał szansę wykonać cokolwiek na formularzu. Możemy więc wykonać tutaj dodatkowe czynności przygotowujące. Na przykład wypełnić pola combo, albo pobrać dane z bazy danych.

QueryClose

Uruchamia się przed zamknięciem formularza. Możemy tutaj wykonać dodatkowe czynności „sprzątające” lub zadać standardowe pytanie „Czy jesteś pewien?”.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)     If MsgBox("Czy na pewno zamknąć", vbYesNo) = vbNo Then         Cancel = vbTrue     End If End Sub Czytaj więcej...

Kurs VBA – cz. 24 – Formularze – Atrybuty

W części kursu dotyczącej modelu obiektowego Excela opisałem, że każdy obiekt jest wyposażony w zestaw atrybutów, metod i zdarzeń. Atrybuty opisują stan obiektu, metody to procedury uruchamiane przez nas, natomiast zdarzenia uruchamiają się automatycznie. Poniżej opisuję kilka najważniejszych atrybutów występujących w formularzach. W kolejnych częściach kursu omówię najczęściej używane metody i zdarzenia. Ostatecznie podpowiem gdzie szukać dokładnych informacji na temat poszczególnych elementów.

AtrybutyKażdy obiekt na formularzu posiada zestaw atrybutów opisujących jego aktualny stan. Jedne występują we wszystkich obiektach formularzy, inne są specyficzne tylko dla kilku z nich. Wszystkie atrybuty zaznaczonego obiektu można zobaczyć w oknie properties. Znajdują się tam atrybuty aktualnie zaznaczonego obiektu. Jeżeli przypadkiem zamkniemy to okno można je otworzyć przyciskiem F4. Po zaznaczeniu atrybutu i naciśnięciu F1 zobaczymy jego wyczerpujący opis. Czytaj więcej...

Kurs VBA – cz. 23 – Formularze – Uruchamianie

=&0=&Przed poważnym wgłębieniem się w świat formularzy zobaczmy jak je uruchamiać. Przygotujemy się do zabawy z przykładami w kolejnych częściach kursu.

Po stworzeniu formularza chcemy go udostępnić użytkownikowi, czyli po prostu pokazać na ekranie. Robimy to poprzez wykonanie jednej z metod formularza.

NazwaFormularza.Show

Oczywiście powyższą instrukcję musimy gdzieś umieścić. W części kursu o uruchamianiu makr podanych jest wiele przykładów:

Jeden formularz może też uruchamiać inny. Wystarczy w jednym ze zdarzeń związanych z jednym formularzem uruchomić procedurę Show drugiego formularza. Czytaj więcej...

Kurs VBA – cz. 22 – Formularze – Pierwszy program

Poniżej napiszemy błyskawicznie pierwszy program używający formularzy. Wiemy już jak używać edytora VBE i pisać kod, skupię się więc na elementach tworzenia formularzy. Napiszemy program dodający jednocześnie kilka arkuszy. Formularz będzie zawierał dwa pola. W pierwszym będę wpisywał ilość arkuszy do dodania. W drugim pierwszy człon nazwy. Kolejne arkusze będę się nazywać: nazwa_1, nazwa_2, nazwa_3 itd. Dodam też przycisk uruchamiający samo dodawanie oraz przycisk anulowania. Przyciski i pola na formularzu są często nazywane kontrolkami. Rozpoczynamy od otworzenia nowego skoroszytu. Otwieram VBE , dodaję nowy formularz i zmieniam jego nazwę oraz nagłówek.
Dodawanie nowego formularza
Chciałbym teraz dodać dwa pola i jeden przycisk. Listę kontrolek do dodania znajdę w przyborniku toolbox. Włączę go w górnym menu view/toobox. 
Przybornik zawierający podstawowe kontrolki
Przy pomocy graficznego edytora dodam dwa pola tekstowe. Nie zapominam o nadaniu im odpowiednich nazw. Tak jak to opisałem w przypadku zmiennych, tutaj też jest bardzo istotne aby każdy element miał odpowiednią nazwę. Każde pole musi być odpowiednio opisane, żeby użytkownik wiedział co powinien wpisać. Dodam dwie kontrolki label. Na koniec dodam dwa przyciski z odpowiednimi nazwami i napisami. Zmienię jeszcze rozmiar formularza i mogę go już uruchomić przy pomocy przycisku Play (F5). Formularz można zamknąć krzyżykiem w prawym, górnym rogu. Naciskanie przycisków nic nie da, ponieważ nie dodaliśmy do nich żadnych zdarzeń. Zostało nam tylko dodać kod do przycisków. Znacznie mniej kodu będzie pod przyciskiem Anuluj. Wystarczy kliknąć dwa razy w przycisk Anuluj. Otwiera się edytor z automatycznie utworzonym zdarzeniem:
Private Sub cmdCancel_Click()
 
End Sub

Dodam tutaj jedną instrukcję. Wywołanie specjalnej instrukcji zamykania formularza. Czytaj więcej...