Uwaga: przeglądasz tę stronę na urządzeniu o niewielkim ekranie (szerokość < 640px). Niektóre zamieszczone w artykule ilustracje i animacje mogą stać się nieczytelne po dopasowaniu ich do rozdzielczości tego ekranu.

Yestok.pl

Jerzy Moruś

© Wszystkie prawa zastrzeżone. Wykorzystanie całości serwisu lub jego fragmentów bez pisemnej zgody autora zabronione.

Własne funkcje w Calc.



Opracowanie powstało w oparciu o wersję programu istniejącą w trakcie jego pisania, Libre Office: 6.4.6, Apache OpenOffice: 4.1.7.


Zdarza się, że podczas tworzenia tabel w arkuszu kalkulacyjnym przydałaby się dodatkowa funkcja usprawniająca obliczenia. Choć wydaje się to dziwne, bo w arkuszu kalkulacyjnym autorzy zdefiniowali około 500 funkcji, to jednak taka sytuacja może się pojawić. Dotyczy to na ogół obliczeń na indywidualne potrzeby, niedających się wykonać z użyciem funkcji wbudowanych do arkusza, obliczeń o bardzo złożonym algorytmie albo obliczeń na ogólnym poziomie, o których nie pomyśleli autorzy programu.

Dwie pierwsze sytuacje związane są zwykle ze specyficznym sposobem obliczeń, dotyczącym jakiegoś zagadnienia lub algorytmów obowiązujących np. w korporacji. Przykładem ostatniej wspomnianej sytuacji są funkcje, które mogą być przydatne każdemu użytkownikowi. Należą tu np. funkcje NUMBERTEXT() i MONEYTEXT(), które napisał węgierski programista László Németh. O funkcjach tych napisałem w opracowaniu „Rozszerzanie możliwości pakietu (x)Office”.

Funkcja to w gruncie rzeczy makro, które jest wywołane poprzez swoją nazwę. Tak jak makra może być napisana w jednym z dostępnych języków programowania. To opracowanie dotyczy programowania w Basicu.

Własne funkcje są wywoływane w taki sam sposób jak funkcje wbudowane do programu Calc, różnica polega na tym, że te funkcje nie znajdą się na liście dostępnych funkcji w kreatorze funkcji, ich nazwy nie są automatycznie rozpoznawane, a podczas ich wpisywania nie pojawią się charakterystyczne „dymki” z podpowiedziami. Użytkownik musi wiedzieć, że taka funkcja jest dostępna i znać sposób jej wywołania. W tym miejscu muszę niejako zaprzeczyć temu, co napisałem powyżej. Istnieje bowiem możliwość przekształcenia własnej funkcji w typ funkcji wbudowanej, która będą się zachowywać tak jak podstawowe funkcje Calc. Jest to jednak metoda wymagająca zaawansowanej wiedzy, wykraczającej poza zakres tego artykułu. Tą metodą László Németh przygotował swoje funkcje tak, że po wgraniu ich do systemu zachowują się tak, jak funkcje wbudowane. Zainteresowani tym tematem mogą poczytać o tym np. tutaj: http://wiki.services.openoffice.org/wiki/SimpleCalcAddIn.

Napisanie funkcji wydaje się łatwiejsze niż napisanie makra obsługującego działania arkusza kalkulacyjnego. W większości przypadków nie jest potrzebna znajomość API i zadanie można zrealizować w czystym języku Basic.

Na potrzeby tworzenia kodu w języku Basic, w pakietach (x)Office dostępny jest edytor tego języka. Treść tworzonego oprogramowania może być dzięki niemu bezpośrednio umieszczana w wybranych bibliotekach i modułach. Edytor ten może zostać otworzony w dowolnym programie pakietu, a nawet w samej powłoce (x)Office. W tym artykule będę go otwierał z wnętrza programu Calc. Makra mogą być dodane do biblioteki w kontenerze pliku tylko wtedy, gdy ten plik jest otwarty.

Przypomnę w tym miejscu, że w języku Basic parametry funkcji zawsze oddziela się przecinkiem, znakiem separatora części ułamkowej w liczbie jest kropka (nie przecinek), a stałe logiczne mają wartość TRUE i FALSE.

Tworzenie funkcji.

Formalnie funkcja musi być zorganizowana wg poniższego schematu:

FUNCTION MojaNazwa[(parametr1 [As typ zmiennej],
                     parametr2 [As typ zmiennej], …)] [As typ ]
…
Algorytm obliczeń.
…
MojaNazwa=wynik obliczeń
END FUNCTION

Pierwszy wiersz to dyrektywa FUNCTION, po której wpisuje się nazwę nadaną funkcji. Nazwa musi zaczynać się literą, a oprócz liter może zawierać także cyfry i znak podkreślnika „_”. W polskiej lokalizacji pakietów (x)Office nazwy niektórych funkcji wbudowanych zawierają litery charakterystyczne dla języka polskiego oraz kropki rozdzielające nazwę. Tych znaków nie można jednak wykorzystywać w nazwach funkcji własnych. Wielkość liter w nazwie nie ma znaczenia, po wywołaniu funkcji, jej nazwa w arkuszu i tak będzie prezentowana wielkimi literami. Po nazwie, w nawiasach, można zadeklarować listę parametrów przekazywanych do funkcji. Funkcja może nie mieć parametrów, lecz sytuacja taka choć możliwa, jest raczej rzadka. Po nazwie każdego parametru może wystąpić określenie jego typu. Sama funkcja, jako całość, też może mieć przypisaną deklarację typu.

Dyrektywa END FUNCTION kończy treść procedury. Wszystkie wiersze kodu zapisane między tymi dwoma dyrektywami nazywamy ciałem funkcji. W ciele funkcji przynajmniej raz musi wystąpić podstawienie wyniku obliczeń pod nazwę funkcji.

Wykonywanie funkcji kończy się po osiągnięciu dyrektywy END FUNCTION albo, wcześniej, po wykonaniu instrukcji EXIT FUNCTION.

Wywołanie tak napisanej funkcji w arkuszu odbywa się np. tak:

=MojaNazwa(parametr1;parametr2; …)

Po nazwie funkcji zawsze muszą wystąpić nawiasy okrągłe, nawet jeżeli napisana funkcja nie wymaga parametrów albo pomijamy ich podanie. Wywołanie funkcji ma wówczas postać:

=MojaNazwa()

Własną funkcję rzadko tworzymy tylko do osobistego użytku. Raczej tworzymy ją z założeniem, że będzie dostępna szerszemu gronu użytkowników. Dlatego budując funkcję, musimy zabezpieczyć ją przed niewłaściwym użyciem. Może nim być błędne wywołanie, podstawienie niewłaściwych wartości, czy złe parametry.

Wygenerowanie błędu przetwarzania w kodzie procedury powoduje otworzenie edytora języka Basic, wyświetlenie komunikatu o błędzie oraz wskazanie w edytorze instrukcji, która ten błąd wygenerowała. Zwykły użytkownik może być zaskoczony tym zdarzeniem i nie będzie wiedział co dalej z tym robić. Dlatego to autor kodu powinien zapewnić poprawne działanie algorytmu nawet w przypadku niewłaściwego użycia.

Rozważę to na przykładzie funkcji, która na podstawie numeru PESEL ma zwrócić informację czy taka osoba to „Pani” czy „Pan”.

Oczywiście trzeba w związku z tym znać budowę tego numeru. Składa się on z 11 cyfr. Sześć pierwszych określa datę urodzenia. Następne cztery są przypisane do osoby, a jedenasta jest cyfrą kontrolną. Przedostatnia cyfra tego kodu określa płeć osoby, parzysta oznacza kobietę a nieparzysta mężczyznę. Uwzględniając tylko tę ostatnią informację, można napisać następującą funkcję:

Function PaniPan(pesel)
PaniPan=Iif(CInt(Left(Right(pesel,2),1)) MOD 2,"Pan","Pani")
End Function

Sam algorytm obliczeń zależy tylko od autora kodu. Równie dobrze funkcja mogłaby wyglądać tak:

Function PaniPan(pesel)
If Cint(Mid(pesel,10,1)) Mod 2=1 Then
	PaniPan="Pan"
Else
	PaniPan="Pani"
End If
End Function

Taka funkcja napisana wyłącznie na własne potrzeby może być wystarczająca. Korzystam z niej tylko osobiście i jako autor wiem np. dlaczego „coś nie wychodzi”.

Jeśli funkcję udostępnię użytkownikom, mogę się spodziewać – prędzej czy później – zawiadomień, że nie wszystko dobrze się liczy. Algorytm jest prawidłowy, więc skąd te uwagi?

Co mogło pójść nie tak?

Brak parametru.

Nieuważny użytkownik wywołał funkcję, nie podając parametru, np. tak =PANIPAN(). Spowoduje to wystąpienie następującego komunikatu o błedzie:

Przykładowy błąd uruchomieniowy makra.
Rys.1: Błąd z powodu praku parametru.

By zabezpieczyć się przed takim zdarzeniem, muszę zmodyfikować kod funkcji. Są dwie metody rozwiązujące ten problem. Pierwsza to przewidzenie tego, że parametr nie zostanie podany. W związku z tym definicja parametru w dyrektywie FUNCTION zostanie poprzedzony klauzulą OPTIONAL. Oznacza ona, że wymieniony po OPTIONAL parametr może zostać pominięty podczas wywołania. Funkcja zatem zaczynałaby się takim zapisem:

Function PaniPan(Optional pesel)

Zastosowanie klauzuli OPTIONAL oznacza, że system nie zgłosi błędu wywołania funkcji z powodu braku parametru. Jeśli jednak w ciele funkcji odwołam się do nieistniejącego parametru, wygeneruję błąd wykonania. Jako autor muszę zatem utworzyć fragment kodu odpowiadający za sprawdzenie, czy parametr został pominięty i co wtedy zrobić. Basic ma funkcję IsMissing(nazwa parametru). Funkcja ta zwraca wartość True, gdy parametr o podanej nazwie nie wystąpił. Używając tej funkcji, mogę zadecydować, jak ma się zachować proces przetwarzania podprogramu, gdy parametr zostanie pominięty. Zmodyfikowana funkcja mogłaby mieć taką postać:

Function PaniPan(Optional pesel)
If IsMissing(pesel) Then
	PaniPan="Brak parametru"
	Exit Function
End If
PaniPan=Iif(CInt(Left(Right(pesel,2),1)) MOD 2,"Pan","Pani")
End Function

Tutaj, jeśli instrukcja If zinterpretuje brak parametru, funkcja ma zwrócić tekst „Brak parametru” oraz się zakończyć. Gdy parametr jest podany, wykona się następna instrukcja.

Druga metoda to „przechwycenie” błędu wykonania, wynikającego z braku parametru i wykonanie działań zaradczych. Realizuje to instrukcja On Error GoTo etykieta. Wystąpienie tej instrukcji w kodzie powoduje włączenie „nasłuchu”, czy podczas wykonywania dalszej części makrokodu nie wystąpił jakiś błąd wykonania. Jeśli taki błąd wystąpi, sterowanie programem zostanie przekazane do instrukcji znajdującej się po podanej etykiecie. Taki sposób wykorzystania tego narzędzia prowadzi do utworzenia funkcji jak poniżej:

Function PaniPan(pesel)
On Local Error GoTo Brakparametru
PaniPan=Iif(CInt(Left(Right(pesel,2),1)) MOD 2,"Pan","Pani")
Exit Function
Brakparametru:
PaniPan="Brak parametru"
End Function

W powyższej procedurze wykorzystałem tę instrukcję w wersji On Local Error, która oznacza, że „nasłuchiwanie” błędu ma wystąpić tylko w tej procedurze.

W tym przypadku niepodanie parametru spowoduje skok do instrukcji po etykiecie Brakparametru.

Jeśli parametr został podany, wykona się zasadnicza procedura, a funkcja zostanie zakończona instrukcją Exit Function.

Niewłaściwy parametr.

Użytkownik nie wskaże adresu pojedynczej komórki w arkuszu i jako parametr funkcji poda zakres komórek. Jest to sytuacja błędna i łatwa do odkrycia. Wystarczy użyć funkcji IsArray(parametr). Odpowiedni fragment kodu to:

If IsArryay(pesel) Then
	PaniPan="Zakres komórek"
	Exit Function
End If

Użytkownik wprowadził więcej parametrów.

To jest akurat sytuacja w ogóle niekłopotliwa i nie musimy się nią martwić. Jeśli użytkownik, wywołując funkcję, podał więcej parametrów, niż wynika to z definicji funkcji, system nie zgłosi żadnego błędu. Funkcja wykorzysta tylko tyle początkowych parametrów, ile wynika z jej definicji.

Przekazany parametr nie jest numerem PESEL.

Użytkownik może wywołać funkcję, wskazując jako parametr dowolny rodzaj wartości. Muszę zatem sprawdzić, czy przekazany parametr spełnia kryteria kodu PESEL. Niektóre z tych kryteriów to wymogi formalne, inne wynikają z logiki systemu, któremu ta funkcja ma służyć.

Czyli, czy ma dokładnie 11 znaków, czy te znaki są cyframi, czy zakodowana data to prawdziwa data i czy cyfra kontrolna jest zgodna? To przykład kryteriów formalnych. Czy data urodzenia zawarta w PESELU nie jest późniejsza niż data dnia, w którym wpisujemy ten numer? To kryterium logiczne, ma nas ochronić przed wpisaniem kogoś, kto się jeszcze nie urodził choćby PESEL spełniał wszystkie wymogi formalne.

Dwa pierwsze kryteria zweryfikuję instrukcją IF

If Len(pesel)<>11 Or Not IsNumeric(pesel) Then
	PaniPan="To nie jest PESEL"
	Exit Function
End If

Sprawdzenie daty jest bardziej złożone. W PESELU dwie pierwsze cyfry określają dwie ostatnie cyfry roku urodzenia. Oznacza to, że taka sama para cyfr występuje w każdym stuleciu. Aby rozróżnić stulecia, twórcy systemu PESEL zastosowali specyficzny sposób zapisu numeru miesiąca urodzenia. Dla osób urodzonych w latach 1900 do 1999 wpisywany jest numer miesiąca urodzenia. Dla urodzonych w latach 2000 – 2999 do numeru miesiąca dodaje się 20, dla urodzonych w latach 2100 – 2199 liczbę 40 a w latach 2200 – 2299 liczbę 60. Liczba 80 jest dodawana dla osób urodzonych w latach 1800 – 1899. Kiedy powstawał ten system kodowania, żyli jeszcze ludzie urodzeni w końcu XIX wieku, stąd dopuszczalne daty z latami 18xx.

Przekształcę zapis daty do postaci RRRR-MM-DD, taki zapis pozwala na użycie go jako parametru funkcji DateValue(). Jeżeli parametr nie odpowiada dacie. np. 31 kwietnia albo 29 lutego w roku nieprzestępnym, to funkcja wygeneruje błąd wykonania. Do obsłużenia tego błędu wykorzystam poznaną już instrukcję ON LOCAL ERRO GOTO etykieta. Gdy zakodowane cyfry odpowiadają prawdziwej dacie, sprawdzę, czy jest ona starsza o co najmniej 15 lat od daty dzisiejszej. Ten algorytm wygląda tak:

pesel=Format(pesel,"00000000000")
i=Int(CInt(Mid(pesel,3,1))/2)
lata=Array(19,20,21,22,18)
r=lata(i)&Left(pesel,2)&"-"&Format((CInt(Mid(pesel,3,2))-i*20),"00")_
		&"-"&Mid(pesel,5,2)
On Local Error GoTo niedata
If DateDiff("yyyy",DateValue(r),Date)<15 Then
	PaniPan="Osoba za młoda"
	Exit Function
End If
PaniPan="Data O.K."
Exit Function
niedata:
PaniPan="zła data"
End Function

W powyższym kodzie wykorzystałem funkcję DateValue() z parametrem daty wynikającym z cyfr numeru PESEL. Jeżeli parametr nie reprezentuje prawidłowej daty, funkcja, w momencie jej wywołania, wygeneruje błąd, a wtedy sterowanie zostanie przekazane do etykiety „niedata”. Instrukcja IF sprawdza, czy wynik funkcji DateDiff() obliczającej różnicę w latach pomiędzy datą dzisiejszą a wynikającą z numeru PESEL jest mniejszy od 15. Przyjąłem tym samym, że dopuszczalny jest PESEL tylko osoby mającej więcej niż 14 lat.

Instrukcja PaniPan="Data O.K." reprezentuje punkt, w którym parametr pesel przeszedł pomyślnie wszystkie poprzednie sprawdzenia.

Przekazany kod nie jest PESELEM.

Pozostaje jeszcze sprawdzenie, czy przekazany kod może zostać uznany za właściwy PESEL. W tym celu obliczona zostanie cyfra kontrolna i porównana z ostatnią cyfrą kodu. Cyfra kontrolna obliczona jest jako reszta z dzielenia przez 10 sumy iloczynów współczynników wagowych przypisanych do pozycji każdej z dziesięciu cyfr i cyfry znajdującej się na tej pozycji. Współczynniki wagowe, wymieniając je od lewej do prawej, to liczby: 9, 7, 3, 1, 9, 7, 3, 1, 9, 7.

Obliczenie i sprawdzenie cyfry kontrolnej zrealizuje taki algorytm:

wagi=Array(9, 7, 3, 1, 9, 7, 3, 1, 9, 7)
suma=0
For i=1 to 10
	suma=suma+wagi(i-1)*CInt(Mid(pesel,i,1))
Next
If CInt(Right(pesel,1)<>suma Mod 10 Then
	PaniPan="Cyfra kontrolna"
	Exit Function
End If

Po obliczeniu sumy sprawdzam, czy reszta z dzielenia tej sumy przez 10 jest taka sama jak cyfra kontrolna PESEL-u, czyli ostatnia cyfra tego kodu.

Postać końcowa funkcji.

Uwzględniając wszystkie dotychczasowe elementy ostateczna postać tej funkcji to:

Function PaniPan(Optional pesel)
If IsMissing(pesel) Then
	PaniPan="Brak parametru"
	Exit Function
End If
If IsArray(pesel) Then
	PaniPan="Zakres komórek"
	Exit Function
End If
If Len(pesel)<>11 Or Not IsNumeric(pesel) Then
	PaniPan="To nie jest PESEL"
	Exit Function
End If
pesel=Format(pesel,"00000000000")
i=Int(Cint(Mid(pesel,3,1))/2)
lata=Array(19,20,21,22,18)
r=lata(i)&Left(pesel,2)&"-"&Format((CInt(Mid(pesel,3,2))-i*20),"00")_
		&"-"&Mid(pesel,5,2)
On Local Error GoTo niedata
‘DateValue(r)
If DateDiff("yyyy",DateValue(r),Date)<15 Then
	PaniPan="Osoba za młoda"
	Exit Function
End If
wagi=Array(9, 7, 3, 1, 9, 7, 3, 1, 9, 7)
suma=0
For i=1 to 10
suma=suma+wagi(i-1)*CInt(Mid(pesel,i,1))
'Print i, wagi(i-1),suma
Next
If CInt(Right(pesel,1))<>suma Mod 10 Then
	PaniPan="Cyfra kontrolna"
	Exit Function
End If
PaniPan=Iif(CInt(Left(Right(pesel,2),1)) MOD 2,"Pan","Pani")
Exit Function
niedata:
PaniPan="Zła data"
End Function

Warto zwrócić uwagę na to, że trzywierszowy kod generujący wynik został rozbudowany do 37 wierszy po to, by zweryfikować poprawność danych. Nadal trzeba też pamiętać, że nie wiemy, czy wpisany numer PESEL faktycznie należy do jakiejś osoby. To można sprawdzić tylko wówczas, gdy mamy dostęp do rzeczywistej bazy danych. Funkcja podała wynik po sprawdzeniu, czy taki zestaw cyfr faktycznie może odpowiadać prawdziwemu peselowi.

Ochrona funkcji przed wykonaniem niedozwolonego działania jest ważna. Trzeba bowiem pamiętać, że komunikaty o błędzie są generowane dla każdego wystąpienia funkcji w arkuszu. Jeśli użytkownik wstawił funkcję do 100 komórek, to ewentualne komunikaty o błędach mogą pojawić się w skrajnej sytuacji nawet 100 razy.

Parametry funkcji.

Funkcje w Calcu należy zawsze wywoływać z nawiasami wyznaczającymi parametry. Czyli, jeśli nie podajemy żadnych parametrów albo funkcja nie wymaga parametrów, to jej wywołanie i tak musi zostać zapisane tak: MojaFunkcja().

Jeśli funkcja wymaga kilku parametrów i wszystkie one mają klauzulę OPTIONAL, to jako parametry niewystępujące zostaną potraktowane tylko pominięte ostatnie parametry. Każdy parametr, który nie został podany, ale jest zasygnalizowany separatorem parametrów, jest przekazany do funkcji jako wartość 0. Przykład. Niech funkcja MojaFunkcja wymaga 5 parametrów.

Wywołanie jej w postaci =MojaFunkcja(a;b;c;d;e) przekazuje jej wszystkie parametry. Wywołanie w postaci =MojaFunkcja(a;b;c) przekazuje jej trzy parametry, czwarty i piąty są „IsMissing”.

Postać =MojaFunkcja(a;;;d) przekazuje cztery parametry, dwa zdefiniowane („a” i „d”), dwie wartości zerowe odpowiadające parametrom „b” i „c” a ostatni parametr „e” ma status „IsMissing”.

Wspomniałem już, że można podać więcej parametrów, niż wymaga tego funkcja, więc zapis =MojaFunkcja(;;;;;) przekaże do funkcji pięć zerowych wartości, bo w zapisie zasygnalizowałem występowanie wszystkich parametrów. Po piątym postawiłem separator parametrów, czyli go zasygnalizowałem.

Parametry są zawsze przekazywane jako wartości, to oznacza, że w funkcji nie ma możliwości zidentyfikowania komórek, które są źródłem parametru. Wywołanie =MojaFunkcja(A1) przekaże do funkcji tylko wartość znajdującą się w komórce A1.

Jeśli przekazanym parametrem jest zakres komórek, to w ciele funkcji jest on reprezentowany zawsze jako dwuwymiarowa tablica. Do określenia rozmiaru takiej tablicy należy wykorzystać funkcję UBound(par,1) do określenia górnego indeksu pierwszego wymiaru tablicy i UBound(par,2) do określenia górnego indeksu drugiego wymiaru. Pierwszy wymiar określa wiersze, drugi kolumny. Pierwszym indeksem każdego wymiaru tablicy przekazanej przez wskazanie zakresu komórek jest 1 i nie zależy to od stanu instrukcji OPTION BASE. Oczywiście można skorzystać z funkcji LBound() do określenia dolnej granicy każdego wymiaru. W tym stanie rzeczy funkcje UBound informują jednocześnie o liczbie wierszy i kolumn przekazanego zakresu. Użycie tych instrukcji w stosunku do elementu niebędącego tablicą generuje błąd wykonania. Dlatego trzeba najpierw sprawdzić, czy parametr jest tablicą. Służy do tego funkcja IsArray(par), która zwraca True, gdy par jest tablicą i False, gdy nie jest.

Przykład działania na tablicy danych prezentuje funkcja poniżej. Funkcja o nazwie Suma_podzielnych ma dwa parametry. Pierwszym jest zakres komórek, które wezmą udział w obliczeniach, drugim jest liczba podzielnika. Funkcja ma policzyć sumę wszystkich wartości, które dzielą się bez reszty przez podany podzielnik.

Function suma_podzielnych(Optional zakres,Optional dzielnik)
	If IsMissing(zakres) Then Stop
	If IsMissing(dzielnik) Or dzielnik=0 Then dzielnik=1
	If IsArray(dzielnik) Then Stop
	suma_podzielnych=0
	If Not IsArray(zakres) Then w=Iif(IsNumeric(zakres),zakres,0)_
				: GoSub Oblicz : Exit Function
	For i=1 To UBound(zakres,1)
		For j=1 To UBound(zakres,2)
			w=IIf(IsNumeric(zakres(i,j)),zakres(i,j),0)
			GoSub Oblicz
		Next j
	Next i
	Exit Function
Oblicz:
	Suma_podzielnych=suma_podzielnych-w*(Abs(Frac(w/dzielnik))<=1.E-10)
Return
End Function

Choć funkcja miała tylko pokazać jak wykorzystać dostęp do elementów zakresu przekazanego funkcji, to napiszę kilka słów objaśnienia. Pierwsza instrukcja przerywa wykonanie funkcji, gdy nie podano zakresu. Podobnie funkcja zostanie przerwana, gdy parametr dzielnika zostanie podany jako zakres komórek. Jeśli dzielnik nie został podany albo podany jako wartość 0, przyjęta zostanie wartość 1. Zabezpiecza to funkcję przed przypadkiem dzielenia przez 0. Taka wartość tego parametru oznacza także, że sumowane będą wszystkie wartości podanego zakresu. Jeżeli parametr zakres został podany jako adres pojedynczej komórki, funkcja przeanalizuje zawartość tylko tej komórki. Wykorzystałem pomocniczą zmienną „w”, przyjmującą wartość analizowanego parametru, gdy jest on liczbą lub 0, gdy jest ciągiem tekstowym. Następnie wywołany jest wewnętrzny podprogram zaczynający się etykietą „Oblicz”. Podprogram sumuje przekazane wartości zmiennej „w”, jeśli ta wartość dzieli się bez reszty przez podzielnik. Wyrażenie logiczne w instrukcji sprawdza, czy część ułamkowa z dzielenia przez podzielnik jest mniejsza od wartości 1.E-10. Dlaczego nie wtedy gdy wynosi 0? Dlatego, że przekazywane z arkusza liczby są typu Double. I niektóre działania nie są dokładne. Podam przykład, podzielenie liczby 48 przez liczbę 0,6 daje wynik 80. Jednak wynik tego działania w Basicu ma część ułamkową i wynosi ona 1,4210854715202E-14. Jest to wartość bardzo mała, jednak różna od 0. Zdziwienie czytelnika wzbudzi zapewne znak minusa w formule sumowania liczb. Znak ten został użyty, gdyż w Basicu, w instrukcji typu „liczba×wynik wyrażenia logicznego”, wartość FALSE jest traktowana jako 0, a wartość TRUE jako -1. Jest to odmienna sytuacja niż w arkuszu kalkulacyjnym, w którym TRUE (w lokalizacji polskiej PRAWDA) jest traktowane jak +1.

W końcu obie pętle podstawiają pod zmienną „w” kolejne elementy tablicy przekazanej jako parametr i także wykorzystują ten sam podprogram.

Określanie typu zmiennych.

Dane w arkuszu kalkulacyjnym są przechowywane jako ciągi tekstowe albo wartości liczbowe, te ostatnie w terminologii Basica są typem Double. Arkusz nie rozróżnia zatem typów Integer, Long, Single, Currency, Date czy Boolean. Jeżeli w dyrektywie FUNCTION określono typ parametru, to przekazana wartość zostanie przekonwertowana na zadany typ i przekazana do ciała funkcji. Może to także być powodem błędu. Np. niech funkcja MojaFunkcja ma taką dyrektywę:
Function MojaFunkcja (x As Integer). Użycie tej funkcji w arkuszu w postaci =MojaFunkcja(A1) nie spowoduje błędu wykonania, dopóki wartość w komórce A1 będzie znajdowała się w zakresie od -32768 do 32767, bo taki jest dopuszczalny zakres wartości zmiennej tego typu. Jeśli liczba w komórce A1 będzie spoza tego zakresu, wywołany zostanie błąd wykonania. Jeśli zakres, w którym znajduje się liczba, będzie prawidłowy, a liczba będzie zawierała część ułamkową, to zostanie ona zaokrąglona do liczby całkowitej, zgodnie z zasadami zaokrąglania i dopiero wówczas przekazana funkcji.

Analogicznie ma się rzecz z określeniem typu funkcji. Wynik funkcji zostanie przekonwertowany na typ wskazany w dyrektywie funkcji i zwrócony do arkusza. Gdy wspomniana powyżej funkcja zostanie zadeklarowana np. tak: Function MojaFunkcja (x As Integer) As String, wówczas wynik funkcji zostanie zwrócony do arkusza jako ciąg tekstowy.

Biblioteki.

Makra, jak wspomniałem w innym opracowaniu, muszą zostać zapisane w bibliotece. Każda biblioteka składa się z modułów, a makra zapisywane są właśnie w modułach. Moduł może zawierać wiele makr, ograniczeniem jest tu łączna długość tekstu w module, która nie może przekroczyć 64kB. Każda biblioteka może mieć 16 000 modułów. W jednym module nie mogą występować makra o takiej samej nazwie. Użytkownik może każdemu modułowi nadać indywidualną nazwę.

Funkcje zapisane w bibliotekach „Standard” są dostępne przez cały czas, ponieważ te biblioteki są zawsze załadowane. Biblioteka „Standard” z kontenera „Moje makra” jest ładowana już w momencie użycia pakietu (x)Office. Biblioteka „Standard” z kontenera pliku jest ładowana w momencie otwarcia pliku. Przy wywołaniu funkcji pierwszym analizowanym kontenerem jest kontener pliku, po nim kontener „Moje makra”. Jeśli funkcje o takich samych nazwach znajdują się w różnych modułach, to do wykonania pobrana zostanie funkcja z pierwszego napotkanego modułu.

Do makr zapisanych w tych bibliotekach mają dostęp wszyscy użytkownicy oprogramowania. To oznacza, że będą oni mogli zmieniać i usuwać makra oraz dopisywać nowe. Nie zawsze jako autorzy chcemy do tego dopuścić. Makro zmodyfikowane w sposób nieautoryzowany może nie zwracać dobrych wyników lub komunikować o błędach wykonania. Uwagi i pretensje użytkowników będą jednak kierowane do autora. Dlatego dobrym rozwiązaniem jest stworzenie oddzielnej biblioteki przechowującej makra autorskie a dodatkowo włączenie ochrony tej biblioteki hasłem. Edytować taką bibliotekę będą mogły tylko osoby znające hasło i nie ma to wpływu na wykonywanie makroprogramów.

Umieszczenie własnych funkcji w oddzielnej bibliotece rodzi jednak problem wykonywania tych funkcji. Biblioteki te nie są przecież załadowane. Problem ten rozwiązuje pewien trick.

Niech istnieje biblioteka o nazwie „Moja”. W tej bibliotece chcę umieścić napisaną przez siebie funkcję MojaFunkcja(a,b,c).Przenoszę zatem funkcję do nowej biblioteki i zmieniam jej nazwę (i wystąpienia tej nazwy w ciele funkcji) na inną. Sam stosuję zasadę, że jeśli użytkownik ma znać moją funkcję pod nazwą „MojaFunkcja(a,b,c)”, to w bibliotece „Moja” tą inną nazwą jest „MojaFunkcja_”. Należy oczywiście pamiętać o tym, żeby zmienić wszystkie wystąpienia nazwy w ciele funkcji. Następnie w bibliotece „Standard” umieszczam nową funkcję, która ma nazwę, jaką zna użytkownik. Funkcja ta ma tylko kilka linijek kodu i wygląda tak:

Function MojaFunkcja(a,b,c)
With BasicLibraries 	'lub odpowiednio GlobalScope.BasicLibraries
If Not .IsLibraryLoaded("Moja") Then .LoadLibrary("Moja")
End With
MojaFunkcja=MojaFunkcja_(a,b,c)
End Function

Ponieważ funkcja jest umieszczona w bibliotece „Standard”, można ją od razu wywołać. Sama funkcja sprawdza tylko, czy niezbędna biblioteka jest załadowana i jeśli nie jest, to ją ładuje, po czym wywołuje właściwy kod funkcji wykonawczej.

Instrukcja GlobalScope.BasicLibraries powinna być użyta wtedy, gdy biblioteka „Moja” została utworzona w kontenerze „Moje makra”.

Prezentacja poniżej przedstawia, przykład postępowania podczas tworzenia opisanej w tym opracowaniu funkcji PaniPan, analizującej PESEL. Edytor Basic-a był wywoływany z programu Calc. Pokazałem włączanie funkcji do kontenera pliku, ale takie samo postępowanie można zrealizować przy zapisie funkcji do kontenera „Moje makra”.



Informacje pomocnicze dla użytkowników Apache OpenOffice i LibreOffice w systemie MacOs

System operacyjny Windows/Linux System operacyjny MacOs
Kliknięcie prawym przyciskiem myszki Control + kliknięcie
Klawisz CTRL (w skrótach klawiaturowych) Klawisz ⌘
F5 Nawigator Shift + ⌘ + F5
F11 Style i formatowanie ⌘ + T