Funkcje własne w Excelu.

Funkcje własne w Excelu.

Funkcje własne w Excelu tworzy się za pomocą skryptów pisanych w języku VBA.

Język VBA (Visual Basic for Applications) jest implementacją języka Visual Basic w aplikacjach, słu- żącą do automatyzacji pracy w dokumentach za pomocą skryptów.

Obecnie edytor skryptów VBA jest dołączany do wszystkich aplikacji Microsoft Office, a także apli- kacji innych producentów (np. AutoCAD od wersji R14).

Kod źródłowy (skrypt) VBA jest przechowywany w module VBA, a ten przechowywany jest w skoro- szycie (zeszycie) Excela.

Wprowadzanie, edycja i przeglądanie kodu źródłowego możliwe są za pomocą edytora Visual Basic, który uruchamiamy w pasku Deweloper, w polu Kod, za pomocą przycisku Visual Basic albo skrótu klawiaturowego: Alt+F11.

Po uruchomieniu edytora VBA pojawia się jego okno. Wprowadzenie nowego modułu (w praktyce nowego skryptu) następuje poprzez polecenie menu okna edytora Insert => Module:

Jeśli mamy już jakiś skrypt zapisany, a chcemy go zmodyfikować lub podejrzeć, to korzystamy z po- lecenia z menu podręcznego (prawy klawisz na interesującym nas module): View Code. Zamknię- cie edytora i powrót do Excela realizujemy poleceniem File => Close and Return to Microsoft Excel albo skrótem klawiaturowym Alt+Q.

Funkcje umieszczone w module są traktowane jak makra, więc przy zapisie dokumentu mnależy wybrać format Skoroszyt programu Excel z obsługą makr.

Pojedynczy moduł może zawierać kilka procedur i funkcji. W tym pliku będę omawiał tylko funkcje, gdyż szerzej składnię języka VBA poznali Państwo w ramach zajęć Grafika Inżynierska, w ramach działu Automatyzacja pracy z AutoCAD. Funkcje zapisane w modułach są dostępne jak wszystkie inne funkcje Excela, a zgromadzone są w kategorii Zdefiniowane przez użytkownika.

Poniżej zostanie przypomniana jedynie składnia funkcji i kilka najważniejszych instrukcji struktural- nych (instrukcje wyboru, pętle) przydatnych przy definiowaniu funkcji.

Uwagi dotyczące notacji!

1. Do zaznaczania fragmentów kodu opcjonalnych (nie wymaganych) będę używał nawiasów kwa- dratowych [] – oczywiste więc, że te nawiasy nie występują w kodzie skryptu (mam świadomość, że to dopowiedzenie obraża inteligencję Czytelnika – niestety, zdarzają się osoby, które namiętnie te nawiasy przepisują).

2. Słowa kluczowe zaznaczam poprzez pogrubienie.

3. Alternatywa oznaczana jest poprzez kreskę pionową.

Składnia funkcji:

[Public|Private] [Static] Function nazwa_funkcji([lista argumentów]) [As typ]

[instrukcje]

[Exit Function]

nazwa_funkcji = wyrażenie [Exit Function]

[instrukcje]

End Function

Objaśnienia:

Public (opcjonalne) – oznacza, funkcję dostępną dla wszystkich innych procedur i funkcji wszyst- kich aktywnych projektów VBA. Domyślnie ten właśnie zasięg funkcji jest przyjmowany.

Private (opcjonalne) – oznacza funkcję dostępną tylko we własnym module.

Static (opcjonalne) – oznacza, że wartości zmiennych zdefiniowanych w tej funkcji są zachowywa- ne pomiędzy kolejnymi wywołaniami.

nazwa_funkcji – nadawana zgodnie z regułami nadawania nazw zmiennych. W praktyce oznacza to używanie liter i cyfr (ale nie samych cyfr!). Należy unikać nazw mylących (np. A7 zostanie zinter- pretowane jako adres komórki) lub funkcji zaimplementowanych w Excelu (one mają pierwszeń- stwo).

lista argumentów – lista nazw zmiennych przedzielanych przecinkami (można również zadekla- rować w niej typ każdej zmiennej, dodając do każdej kod: As typ). Jeżeli funkcja jest bezargumen- towa (jak np. PI() w Excelu), to lista nie występuje w deklaracji (ale nawiasy są konieczne!).

As typ (opcjonalne ale zalecane) – deklaracja typu zwracanej przez funkcję wartości.

Niektóre typy zmiennych języka VBA

Long liczba całkowita długa od –2147483648 do 2147483647

Single liczba zmiennoprzecinkowa od –3,402823E38 do –1,401298E–45 dla liczb ujemnych oraz od 1,401298E–45 do 3,402823E38 dla liczb dodatnich

Double podobnie jak Single ale dużo większe zakresy (z mantysą od E–324 do E308) Variant domyślny typ zmieniający się w zależności od sposobu przetwarzania

danych

Exit Function (opcjonalne, nie zalecane) – natychmiastowe wymuszenie zakończenia funkcji przed jej formalnym zakończeniem (raczej należy tego unikać).

nazwa_funkcji = wyrażenie – wymagane (!) przynajmniej raz przypisanie zmiennej repre- zentującej funkcję wartości jakiegoś wyrażenia. Należy zwrócić uwagę, że w tym wyrażeniu nazwa_funkcji występuje bez nawiasów!.

Exit Function – formalne zakończenie funkcji.

W kodzie mogą występować też komentarze poprzedzane apostrofem.

Instrukcje

Podstawowym typem instrukcji jest instrukcja przypisania wartości zmiennej:

nazwa_zmiennej = wyrażenie

Składnikiem wyrażenia mogą być stałe, zmienne, operatory (np. +, –, *, /, ^), nawiasy (tylko okrągłe!) i funkcje (zarówno funkcje języka VBA, Excela, jak i własne).

Funkcje VBA i własne wprowadzamy poprzez samą nazwę, ale VBA można wprowadzić też poprzez kod VBA.nazwa_funkcji. Funkcje Excelowskie wprowadzamy przez kod

WorksheetFunction.nazwa_funkcji. Zalecane jest używanie raczej funkcji VBA.

Niektóre funkcje VBA

Cos(x) – wiadomo

Exp(x) – funkcja wykładnicza o podstawie stałej Eulera – e^x Log(x) – logarytm naturalny (!!!) – ln x

Sin(x) – wiadomo

Sqr(x) – pierwiastek kwadratowy Tan(x) – tg x

Poza instrukcjami przypisania w kodzie mogą występować również instrukcje strukturalne (instruk- cje warunkowe i pętle).

Instrukcje warunkowe

Instrukcje warunkowe wykonujemy jeśli wykonanie jakiejś instrukcji jest zależne od spełnienia (lub niespełnienia – instrukcje alternatywne) sprawdzanego warunku.

Instrukcja If

If warunek Then instrukcja_jeśli_warunek_prawdziwy [Else instrukcja_alternatywna]

Jak widać część alternatywna nie musi występować.

Postać blokowa:

If warunek Then

instrukcje_jeśli_warunek_prawdziwy [Else

instrukcje alternatywne]

End If

Proszę zwrócić uwagę na wcięcia – ułatwiają czytanie kodu.

Instrukcje If możemy zagnieżdżać w sobie:

If warunek1 Then

instrukcje_jeśli_warunek1_prawdziwy [ElseIf warunek2 Then

instrukcje_jeśli_warunek2_prawdziwy]

[Else

instrukcje alternatywne]

End If

Instrukcji ElseIf może być więcej (więcej zagnieżdżeń), ale nie jest to zalecane – lepiej użyć (jeśli to możliwe) innej konstrukcji: Select Case. Jeżeli porównujemy ze sobą wartości jakichś wyrażeń, to można użyć właśnie tej instrukcji.

Instrukcja Select Case.

Select Case sprawdzane_wyrażenie Case lista_wyrażeń1

instrukcje [Case lista_wyrażeń2

inne_instrukcje]

[Case Else

instrukcje_alternatywne]

End Select

Prostsza postać jednej instrukcji po Case:

Case lista_wyrażeń1: jedna_instrukcja

Pętle

Pętle są konstrukcjami pozwalającymi wykonywać instrukcję (szereg instrukcji) wielokrotnie. Oczy- wiście musi być jakieś kryterium zakończenia pętli – inaczej dostalibyśmy informatyczne perpetuum mobile.

Pętla For

Pętlę tę stosujemy, jeśli chcemy instrukcje wykonać określoną liczbę razy. Kontrolujemy to poprzez licznik.

For licznik=wartość_początkowa To wartość_końcowa [Step krok]

[instrukcje]

[Exit For]

[instrukcje]

Next [licznik]

Step krok używamy opcjonalnie, jeśli krok jest inny niż 1. Exit For to awaryjne wyjście z pętli (raczej należy unikać).

Jeśli wykonujemy pętlę nieokreśloną liczbę razy, to używamy pętli, w których na początku lub końcu sprawdzamy warunek wyjścia.

Pętla While

Wykonywana jest dopóki sprawdzany warunek jest spełniony.

Do While warunek [instrukcje]

[Exit Do]

[instrukcje]

Loop

Ta pętla sprawdza warunek na początku – może nie być więc ani razu wykonana.

Do

[instrukcje]

[Exit Do]

[instrukcje]

Loop While warunek

Ta pętla sprawdza warunek na końcu – będzie wykonana przynajmniej raz.

Exit Do jest wyjściem awaryjnym.

Pętla Until

Wykonywana jest dopóki sprawdzany warunek nie jest (!!!) spełniony.

Do Until warunek [instrukcje]

[Exit Do]

[instrukcje]

Loop

Ta pętla sprawdza warunek na początku – może nie być więc ani razu wykonana.

Do

[instrukcje]

[Exit Do]

[instrukcje]

Loop Until warunek

Ta pętla sprawdza warunek na końcu – będzie wykonana przynajmniej raz.

Exit Do jest wyjściem awaryjnym.

Tyle powinno nam wystarczyć do napisania kilku prostych funkcji własnych. Mam nadzieję, że mają Państwo świadomość, że język VBA jest o wiele bogatszy niż wynika to z tego krótkiego wprowa- dzenia – zachęcam do jego poznania!

Przykładowe realizacje

Przykłady tu omówione znajdują się w module dołączonym do pliku vba.xslm.

Najprostszy przykład, kiedy warto użyć funkcji własnej, to taki, kiedy wzór analityczny funkcji jest na tyle złożony, że warto zapisać ją w postaci wzoru w języku VBA i wywoływać ją przez nazwę w Excelu. Tym przypadkiem nie będziemy się zajmować.

Inne, bardzo przydatne użycie funkcji własnej, to wtedy, gdy jej wartość obliczana jest w różny sposób, w zależności od wartości argumentu.

Najprostszy przykład to taki, kiedy funkcja jest określona w prawie całej dziedzinie ℝ, za wyjątkiem kilku punktów osobliwych.

Przykładem takiej funkcji jest funkcja

f (x )= sin (x ) x

Funkcja ta jest określona w ℝ\{0}, ale w {0} ma tę właściwość, że w tym punkcie ma obustronną granicę, równą 1, więc istnieje pokusa, by taką funkcję określić na całym ℝ, nadając jej wartość 1 w punkcie {0}. Gdybyśmy chcieli ją obliczyć w tym punkcie za pomocą funkcji Excela, to otrzymali- byśmy błąd dzielenia przez zero.

Jej wartość można by więc określić następującym algorytmem:

1) jeżeli x=0 to wartość funkcji =1;

2) jeżeli x≠0 to wartość funkcji =sin(x)/x

W tym przypadku od razu nasuwa się konstrukcja wyrażenia warunkowego:

Function sinx(x) As Double If x = 0 Then

sinx = 1 Else

sinx = Sin(x) / x End If

End Function

Wykres powyższej funkcji znajduje się na arkuszu sinx. Proszę zwrócić uwagę, że w punkcie 0 wartość funkcji jest obliczana i wynosi 1, tka jak chcieliśmy. Dla porównania zachęcam do obliczenia wartości funkcji w tym punkcie za pomocą zwykłej formuły Excela.

Jako ćwiczenie proszę spróbować utworzyć funkcję

f (x )= x ln(x ) x−1

Funkcja ta jest określona na obszarze ℝ⁺, za wyjątkiem punktu {1}, ze względu na mianownik, ale w tym punkcie ma obustronną granicę, równą 1. Co więcej, również w punkcie {0} ma określoną granicę, równą 0. Zadanie polega na stworzeniu funkcji logx określonej algorytmem:

1) jeżeli x=0 to wartość funkcji =0;

2) jeżeli x=1 to wartość funkcji =1;

3) jeżeli x≠0 i x≠1 to wartość funkcji =x·ln(x)/(x-1).

Podpowiedź – użyć konstrukcji:

If Then

ElseIf Then

Else End If

Innym przykładem, kiedy funkcje własne są użyteczne, są funkcje, których wartości są określane za pomocą szeregu. Najczęściej są to szeregi Taylora i Fouriera.

Szeregi Fouriera służą do opisu funkcji periodycznych. Poniższe funkcje opisują często używane sygnały periodyczne:

a) fala prostokątna:

y= 4 π ∑

i=1

∞

sin(2 π f (2i−1) x)

2i−1

b) fala piłokształtna:

y= 2 π ∑

i =1

∞

(−1)

sin (2 π f i x) 2i−1

c) fala trójkątna:

y= 8 π

∑

i =1

∞

(−1)

sin (2 π f (2i+1)x ) (2i+1)

Wielkość ω=2πf to częstość podstawowa szeregu i jak widać występuje w każdym członie, funkcja zależy więc nie tylko od x ale również od f, które decyduje o częstości fali, bo 2π jest oczywiście stałą. Nie jesteśmy w stanie dodawać kolejne człony w nieskończoność, więc funkcja będzie zależna również od liczby członów n.

Szczegółowo omówię poniżej realizację funkcji fali prostokątnej – pozostałe dwie wyglądają podobnie. Niech funkcja nazywa się fkw. Ponieważ zależy ona od x, f i n, więc jej szkielet będzie wyglądał następująco:

Function fkw(x, f, n) As Double End Function

We wzorze występuje liczba π, której wartość powinna być podana jak najprecyzyjniej. W VBA nie ma takiej funkcji, jak PI() w Excelu, ale możemy skorzystać z funkcji arcus tangens, ponieważ atn(1)= π/4. Zdefiniujemy więc na wstępie stałą:

pi4 = Atn(1)

Warto również uprościć obliczenia poprzez wprowadzenie zmiennej, której wartość będzie równa 2πf:

f2pi = pi4 * 8 * f

Nie można było nazwać stałej 2pif, gdyż nazwy zmiennych i stałych muszą zaczynać się od litery.

Sumowanie określonej liczby kolejnych elementów wykonuje się w kodach skryptów najczęściej za pomocą pętli For, według schematu:

1) najpierw zerujemy zmienną przechowującą sumę: suma = 0

2) następnie wielokrotnie dodajemy do niej człony: suma = suma + człon

Ponieważ ostatecznie wartość sumy będziemy przekazywać do nazwy funkcji, więc od razu użyjemy jej na oznaczenie sumy:

fkw = 0

For i = 1 To n

fkw = fkw + Sin(f2pi * (2 * i - 1) * x) / (2 * i – 1) Next i

Tak otrzymana suma musi być jeszcze pomnożona przez 4/π, co jest akurat równe odwrotności stałej pi4.

fkw = fkw / pi4

Ostatecznie funkcja wygląda nastepująco:

Function fkw(x, f, n) As Double pi4 = Atn(1)

f2pi = pi4 * 8 * f fkw = 0

For i = 1 To n

fkw = fkw + Sin(f2pi * (2 * i - 1) * x) / (2 * i – 1) Next i

fkw = fkw / pi4 End Function

Podobnie jak wyżej fali piłokształtnej została stworzona w module funkcja fpil, a dla fali trójkątnej funkcja ftr. Zachęcam do ich przeanalizowania. Na arkuszu Szeregi Fouriera znajdują się wykresy tych trzech funkcji, obliczone dla zaledwie 5 członów sumy (n=5). Zachęcam do zbadania, jak zmieniają się kształty wykresów przy zmianie liczby członów sumowanych, np. poprzez syste- matyczne podwajanie liczby członów sumowanych (2, 4, 8, 16, 32, 64, 128, 256, 512, 1024).

Na koniec przedstawię, jak obliczać wartość funkcji uwikłanej za pomocą kolejnych przybliżeń.

Algorytm jest następujący:

0) przedstawiamy funkcję uwikłaną w postaci y=f(y,parametry) 1) przyjmujemy wartość początkową y1;

2) przekazujemy wartość y1 do następcy y2;

3) liczymy kolejną wartość y1=f(y2,parametry);

4) sprawdzamy, czy różnica między y2 a y1 jest mniejsza niż zadana dokładność; jeśli nie, to y1 przyjmuje wartość y2 i powtarzamy punkty 2), 3) i 4); jeśli tak, to ostatnia obliczona wartość jest wartością funkcji.

W tym wypadku najlepszą konstrukcją jest pętla Do z warunkiem wyjścia takim, jak podany w p. 4).

Jako przykładu użyję znanego z poprzednich materiałów wzoru Colebrooka-White’a.

1

√ ^{(λ) =−2log} ( ^{Re 2,51} √ ^{(λ ) +}

ks 3,71 D )

Jest to funkcja trzech argumentów: liczby Reynoldsa Re, chropowatości ks oraz średnicy D. Czwar- tym argumentem jest zadana dokładność, ale w tej realizacji zadamy ją z góry wewnątrz funkcji (użytkownik nie będzie miał na nią wpływu). Szkielet funkcji, wraz z zadaną dokładnością, będzie następujący:

Function CW(Re, ks, D) As Double eps = 0.001

End Function

Warto uprościć wzór, przyjmując jako stałe wielkości 2,51/Re oraz ks/(3,71D):

k = ks / (3.71 * D) R = 2.51 / Re

Występujący we wzorze logarytm dziesiętny trzeba zamienić na logarytm naturalny, gdyż funkcja Log() w VBA jest logarytmem naturalnym: log(x)=ln(x)/ln(10). Po przekształceniu do postaci y=f(y,parametry) otrzymujemy.

λ= ln

10 4

1 ln

( k +R /

λ )

We wzorze pojawiła się kolejna wartość stała, którą warto dla uproszczenia przechować za pomoca stałej:

ln10p4 = Log(10) ^ 2 / 4

Pozostaje już tylko stworzyć pętlę z warunkiem wyjścia, jak w podanym algorytmie:

lb1 = 1 Do

lb2 = lb1

lb1 = ln10p4 / Log(k + R / Sqr(lb2)) ^ 2 Loop Until Abs(lb1 – lb2) < eps

a następnie przekazanie wartości ostatniego obliczenia do wartości funkcji:

CW = lb1

Ostatecznie kod funkcji wygląda następująco:

Function CW(Re, ks, D) As Double eps = 0.001

k = ks / (3.71 * D) R = 2.51 / Re

ln10p4 = Log(10) ^ 2 / 4 lb1 = 1

Do

lb2 = lb1

lb1 = ln10p4 / Log(k + R / Sqr(lb2)) ^ 2 Loop Until Abs(lb1 – lb2) < eps

CW = lb1 End Function

Zachęcam do sprawdzenia, czy funkcja ta liczy współczynnik oporów liniowych tak samo, jak w temacie 1. Zachęcam również do spróbowania swoich sił, tworząc funkcję liczącą wartości u w profilu Spaldinga.