Yestok.pl

Kiedy potrzebujemy makr? Pierwsza odpowiedź: wtedy gdy chcemy zautomatyzować pewien proces realizowany w dokumencie. Zwłaszcza gdy jest to proces często wykonywany a nadto złożony z wielu działań. Odpowiedź druga dotyczy programu Calc i brzmi: wtedy gdy potrzebujemy funkcji realizującej nasze specyficzne wymagania.

Umiejętność programowania pozwala nie tylko na usprawnienie obsługi własnego dokumentu, ale wręcz utworzenie aplikacji, dla której dokument jest tylko środowiskiem pracy, a właściwości samego dokumentu nie zostaną w ogóle wykorzystane. Oczywiście jest to możliwość teoretyczna. Nikt rozsądny nie będzie tworzył aplikacji działającej w taki sposób. Makra mają ułatwić i zautomatyzować obsługę konkretnego dokumentu albo udostępnić wszystkim dokumentom możliwości, jakie nie były przewidziane przez autorów oprogramowania.

Do napisania makra potrzebna jest znajomość języka programowania, czyli języka, w jakim można zapisać kod programu. W oprogramowaniu (x)Office można to zrobić w kilku z nich. Są one wymienione w menadżerze makr, są to: Basic, JavaScript, BeanShell i Python. W tym opracowaniu poruszone będą tylko problemy związane z Basicem.

W pierwszym opracowaniu dotyczącym makr napisałem, że każde makro musi zostać zapisane w bibliotece zlokalizowanej wewnątrz wybranego kontenera. Ten proces dotyczy w rzeczywistości wyboru jednego z dwóch kontenerów: kontenera „Moje makra” albo kontenera związanego z dokumentem. Napisałem też, że w obu tych kontenerach istnieje biblioteka o nazwie „Standard”. Istnieje ona nawet wówczas gdy jest pusta. Precyzyjniej, w kontenerze pliku biblioteka „Standard” zostanie utworzona automatycznie podczas zapisywania pierwszego makra.

Aby makro mogło zostać uruchomione, biblioteka je zawierająca musi być załadowana. Podstawową cechą bibliotek „Standard” jest to, że są one ładowane zawsze po uruchomieniu oprogramowania pakietu (x)Office („Standard” w „Moje makra”) i po wczytaniu pliku („Standard” w kontenerze pliku). To powoduje, że makra umieszczone w tych bibliotekach mogą być uruchamiane i wykonywane bez żadnych dodatkowych deklaracji.

Biblioteki „Standard” ze względu na rolę, jaką pełnią w systemie mają kilka ograniczeń. Nie można ich usunąć, zmienić im nazwy ani nałożyć ochrony hasłem.

Użytkownicy zapisują napisane przez siebie makra najczęściej w jednej z tych dwóch bibliotek. Jeśli jednak projekt, jaki realizują, składa się wielu makr, wolą umieścić je w oddzielnej bibliotece. Ułatwia to w konsekwencji prace nad rozwojem, konserwacją czy nadzorem oprogramowania. Ponadto takie dedykowane biblioteki mogą być chronione hasłem. Hasło nie utrudnia wykonywania makr, sprawia jednak, że tylko osoby znające hasło mogą przeglądać i edytować zawartość biblioteki. Makra umieszczone w bibliotece dedykowanej mogą zostać wywołane dopiero po uprzednim załadowaniu takiej biblioteki.

O tym, czy biblioteka jest aktualnie załadowana, informuje postać ikonki reprezentującej bibliotekę w okienkach dialogowych otwieranych podczas akcji związanych z makrami. Np. „Makra Basic” w LibreOffice albo „OpenOffice - Makra Basic” w Apache OpenOffice, ale także we wszystkich tych oknach, w których z jakichś powodów prezentowana jest struktura bibliotek. Ilustracja poniżej przedstawia wspomniane powyżej okna dialogowe w każdym ze wspomnianych programów. Czerwoną ramką otoczony jest symbol biblioteki, która jest załadowana.

Ładowanie bibliotek.

W wielu sytuacjach wymuszanie ładowania bibliotek nie jest konieczne. Choćby wspomniane tutaj biblioteki „Standard”, które są ładowane automatycznie. Ponadto biblioteki są ładowane, bez dodatkowych deklaracji, także w następujących sytuacjach:

• Po rozwinięciu zawartości zamkniętej biblioteki (znak „+” przy jej nazwie) w oknie dialogowym, w którym ta biblioteka jest pokazana (np. w takim jak na rys. 1.). To zdanie po prostu stwierdza, że przy takim działaniu biblioteka zostaje załadowana. Nikt przecież podczas pracy z dokumentem nie będzie najpierw ładował w ten sposób bibliotek.
• Po uruchomieniu makra poleceniem „Narzędzia → Makra → Wykonaj makro” lub „Narzędzia → Makra → Zarządzaj makrami → Basic... → Uruchom”, gdyż aby wykonać to polecenie, należy wskazać bibliotekę (a to już ją załaduje), moduł i makro.
• Przy uruchamianiu makra przypisanego do skrótu klawiaturowego, pozycji w pasku narzędziowym, pozycji w menu, lub innego obiektu, lub zdarzenia w dokumencie, gdyż przygotowanie takiego sposobu uruchomienia wymaga wcześniej wskazania makra do wykonania.

Biblioteka raz załadowana jest dostępna do zamknięcia dokumentu (gdy dotyczy to biblioteki związanej z dokumentem) lub do końca sesji programu biurowego (x)Office (gdy dotyczy to kontenerów „Moje makra” i „Makra LibreOffice/Makra OpenOffice”).

Kiedy zatem należy wymuszać ładowanie bibliotek? Wtedy gdy makro odwołuje się do procedury znajdującej się w innej bibliotece. Za ładowanie biblioteki odpowiada metoda LoadLibrary("nazwa_biblioteki") obiektu BasicLibraries – dla bibliotek zawartych w kontenerze pliku i GlobalScope.BasicLibraries – dla bibliotek znajdujących się w kontenerach systemowych.

Jeżeli własną bibliotekę nazwałem: „Moja”, to jej załadowanie zapewni odpowiednio:

BasicLibraries.LoadLibrary("Moja"), gdy biblioteka ma być załadowana z kontenera pliku i GlobalScope.BasicLibraries.LoadLibrary("Moja"), gdy biblioteka ma być załadowana z kontenera „Moje makra”.

Wspomniałem już o tym, że biblioteki raz załadowane są dostępne stale, więc wielokrotne wykonywanie makra wykonywałoby także wielokrotnie procedurę ładowania bibliotek. Dlatego proces ładowania biblioteki warto zakodować następująco:

With BasicLibraries 	'lub odpowiednio GlobalScope.BasicLibraries
	If Not .IsLibraryLoaded("Moja") Then .LoadLibrary("Moja")
End With

Taki kod powoduje, że żądana biblioteka będzie ładowana tylko wówczas, gdy jeszcze nie była załadowana.

Nagrywanie makr w programach Calc i Writer.

Choć makra mogą zostać dołączone do każdego typu dokumentów pakietu (x)Office, a więc nie tylko Calc lub Writer, ale także Base, Draw, Impress czy Math, to możliwość nagrywania dostępna jest wyłącznie dla programów Calc i Writer. Nagranie makra to w rzeczywistości zarejestrowanie przez procedurę nagrywającą wszystkich czynności wykonanych w dokumencie przez użytkownika i przekształcenia tych czynności w zapis programu w języku Basic.

Ten sposób tworzenia makr przeznaczony jest z jednej strony dla osób, które nie znają języka programowania, lecz chciałyby zautomatyzować niektóre procesy w dokumencie a z drugiej dla tych, którym nie chce się pisać odrębnego kodu w czystym języku. Zarejestrowane fragmenty można bowiem dowolnie włączać do struktury własnego oprogramowania.

Rejestrator makr generuje makra, wykorzystując komponenty technologi UNO (Universal Network Object). Makra są w rezultacie sekwencyjną kolejką wywołań obiektów UNO realizowanych wewnętrznymi mechanizmami (x)Office. Z kolei język programowania Basic, choć stosunkowo łatwy do poznania, to jednak aby mógł korzystać z dostępu do obiektów (x)Office, czyli np. dokumentu tekstowego, arkusza, komórki czy rysunku, musi korzystać ze specjalnego sposobu komunikowania się, nazywanego API (Application Programming Interface). To właśnie nieznajomość API jest największą przeszkodą podczas pisania makr.

Makro utworzone poprzez nagranie w dość istotny sposób różni się wizualnie od programu napisanego przez programistę bezpośrednio w języku Basic i realizującego to samo działanie. Tę różnicę zaprezentuję, pokazując zapis makr wykorzystanych w programie Calc do wstawienia do komórki D17, w pierwszym arkuszu skoroszytu, liczby 217.

Kod napisany przez programistę może wyglądać np. tak:

Sub Programista
	Dim dokument As Object
	Dim arkusz As Object
	Dim komorka As Object
	dokument = ThisComponent
	arkusz = dokument.sheets(0)
	komorka = arkusz.getCellRangeByName("D17")
	komorka.value = 217
End Sub

A przy absolutnym zminimalizowaniu zapisu, tak:

Sub Programista
	ThisComponent.sheets(0).getCellByRange("D17").value=217
End Sub

Ta sama czynność zarejestrowana przez nagranie makra wytworzy taki kod w języku Basic (nazwę procedurze, tutaj „rejestrator”, nada użytkownik podczas zapisywania makra w bibliotece):

sub rejestrator
	rem ----------------------------------------------------------------------
	rem define variables
	dim document   as object
	dim dispatcher as object
	rem ----------------------------------------------------------------------
	rem get access to the document
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	rem ----------------------------------------------------------------------
	dim args1(0) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "Nr"
	args1(0).Value = 1
	dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
	rem ----------------------------------------------------------------------
	dim args2(0) as new com.sun.star.beans.PropertyValue
	args2(0).Name = "ToPoint"
	args2(0).Value = "$D$17"
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
	rem ----------------------------------------------------------------------
	dim args3(0) as new com.sun.star.beans.PropertyValue
	args3(0).Name = "StringName"
	args3(0).Value = "217"
	dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
	rem ----------------------------------------------------------------------
	dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "", 0, Array())
end sub

Oba przykłady wykonają dokładnie to samo. Jeżeli chcemy, aby do wskazanej komórki można było wprowadzać wartość podaną przez użytkownika, należy zmodyfikować nagrane makro. Np. tak: po deklaracji Sub rejestrator dopiszę linijkę kodu:

Następnie w zarejestrowanym makrze zmienię zapis wiersza z: args3(0).Value="217" na: args3(0).Value=x

Tak zmodyfikowana procedura najpierw zażąda podania jakiejś wartości, a następnie podstawi tę wartość do elementu tablicy args3(0).Value. Analogicznie można zmodyfikować pozostałe wartości „.Value” przy innych zmiennych i w ten sposób zdefiniować arkusz i komórkę, w których zapis ma nastąpić. Ten przykład pokazuje, że po nagraniu makra można je sobie przystosować do własnych potrzeb.

Rejestrowanie makr może zakodować tylko takie czynności, jakie sami możemy wykonać w arkuszu. Można sądzić, że to niewiele, jednak ktoś, kto zna – bardziej niż tylko powierzchownie – obsługę programu, może uzyskać naprawdę korzystne rezultaty.

Proces nagrywania rozpoczyna polecenie „Zarejestruj makro...” dostępne w poleceniach „Narzędzia → Makra”. W oprogramowaniu LibreOffice wymagane jest – dodatkowo – ustawienie odpowiedniej opcji w ogólnych parametrach pakietu (Narzędzia → Opcje... → LibreOffice → Zaawansowane), ilustruje to rys. 2. W pakiecie Apache OpenOffice nie ma potrzeby ustawiania takiej opcji.

Myśląc o nagrywaniu makr, należy znać ograniczenia, jakie temu towarzyszą. Przypomnę je tutaj ponownie, cytując dosłownie treść systemu pomocy:

Odniosę się do uwagi dotyczącej zaznaczania. Podczas nagrywania makra w programie Writer zaznaczanie myszką rzeczywiście nie jest rejestrowane. Natomiast w programie Calc zaznaczanie myszką jest traktowane jako wskazanie komórki lub obszaru opisanych adresem bezwzględnym.

Co to oznacza, jeśli chodzi o makro? Gdy zarejestrujemy kliknięcie myszką w komórkę leżącą np. o dwa wiersze poniżej aktualnej komórki aktywnej, zarejestrowane zostanie przejście do tej konkretnej komórki, to znaczy, że gdy kliknięta zostanie np. komórka B8, makro zarejestruje adres $B$8.

Wykonanie tego makra spowoduje uaktywnienie komórki zarejestrowanej przez makro (w tym przykładzie $B$8) bez względu na to, w jakiej części arkusza podczas wywołania tego makra znajduje się komórka aktywna.

Gdy w trakcie rejestracji wymagana komórka zostanie wskazana poprzez np. dwukrotne naciśnięcie klawisza „strzałka w dół”, (w tym przykładzie nastąpiłoby przejście od komórki B6 do B8), zarejestrowany zostanie fakt przeniesienia zaznaczenia komórki o dwa wiersze w dół. Wykonanie takiego makra uaktywni komórkę leżącą o dwa wiersze w dół względem aktualnie aktywnej komórki. Gdy więc to makro zostanie wywołane wtedy, gdy aktywną komórką jest np. D17, to nastąpi uaktywnienie komórki D19, czyli komórki o dwa wiersze poniżej.

Czego jeszcze nie można zrealizować makrami, które są rejestrowane? Nie można wykonać akcji warunkowych, uzależnionych od zawartości komórki lub obszarów arkusza. Mówiąc prościej, nie można np. tą metodą pobrać zawartości wybranej komórki i na tej podstawie wykonać wybranych działań.

Pomimo tych ograniczeń, narzędzie nagrywania może zapewnić, w sporym zakresie, możliwości automatyzowania procesu. Pokażę to w prezentacji poniżej.

Prezentacja dotyczyć będzie nagrania makra w programie Calc. Obserwacja for internetowych wskazuje, że to najczęściej wykorzystywane środowisko wymagające dodatkowych możliwości.

Najpierw jednak krótki opis.

Gdy planujemy utworzenie arkusza, którego wygląd będzie się stale zmieniał, np. poprzez dopisywanie nowych danych w wierszach tego arkusza, a zwłaszcza wtedy, gdy w wierszach tych mają także być wykonane równocześnie jakieś obliczenia, stajemy przed problemem zorganizowania takiej tabeli. Z jednej strony można utworzyć kompletny szkielet takiej tabeli, zawierający przewidywaną ilość wierszy i wszystkie niezbędne formuły. Będzie to jednak tabelka zawierająca mnóstwo zer w wierszach, które jeszcze są puste. Nie jest także do końca pewne, czy zaplanowane rozmiary rzeczywiście są wystarczające. Kłopotliwe jest też wydrukowanie danych, gdyż automatyczne definiowanie zakresu wydruku włącza do niego wszystkie wiersze takiej tabelki. Z drugiej strony można realizować rozwiązanie, w którym każdy nowy wiersz danych będzie za każdym razem dodawany na końcu tabelki. Następnie trzeba będzie w tym nowym wierszu odtworzyć niezbędne formuły i wprowadzić dane. Dzięki takiemu podejściu tabela będzie zawierała tyle wierszy ile faktycznie jest wykorzystanych. Ten właśnie sposób postępowania można zautomatyzować, nagrywając makro, które wykona te czynności za nas.

Tabelka przygotowana do prezentacji wygląda tak:

Drugi wiersz tabeli jest wierszem technicznym, zawierającym wszystkie niezbędne formuły, formatowania i ustawienia. Zakres komórek A2:F2 ma przypisaną nazwę „wiersz”. Wszystkie komórki tego obszaru mają przypisane formatowanie warunkowe, wyświetlające komórki stylem „Bad”, wtedy gdy komórka jest pusta. Ma to zwrócić uwagę osoby wprowadzającej dane na konieczność wypełnienia komórek. Komórka D2 ma przypisaną kontrolę poprawności danych. Może zawierać wpisy tylko z dopuszczalnej listy jednostek miar. Komórki C2 i F2 mają przypisane formatowanie liczb z dwoma miejscami po przecinku a komórka E2 z jednym miejscem po przecinku. W komórce F2 wpisana jest formuła =ZAOKR(ZAOKR(C2;2)*ZAOKR(E2;1);2). Taka formuła może dziwić niektórych czytelników, którzy uznają, że wystarczy wpisać =C2*E2.

Oto wyjaśnienie. Nie dotyczy ono samej procedury, lecz ogólniejszego spojrzenia na problematykę rozwiązań przygotowywanych dla użytkownika, nad którego działaniami nie będziemy mieli bezpośredniego wpływu.

Autor arkusza nie ma kontroli nad jego obsługą. Arkusz może być obsługiwany przez różne osoby, nie zawsze przestrzegające zasad, jakie przyjął autor oprogramowania. Nie ma pewności, że cena zostanie podana jako liczba z dwoma miejscami po przecinku a ilość z jedną cyfrą.

Formatowanie komórek ogranicza ilość wyświetlanych po przecinku cyfr. Sama liczba jest jednak przechowywana w pełnej, wpisanej przez użytkownika, postaci. Konsekwencje podania wartości z większą niż to przewidziano liczbą cyfr po przecinku, może prowadzić do zafałszowania wyniku. Szczególnie może to być widoczne przy obliczeniach dotyczących dużej ilości wierszy. Unaoczni to ilustracja poniżej, pokazująca jak sposób obliczenia wyniku może wpłynąć na zestawienia zbiorcze.

Ilustracja obrazuje mnożenie dwóch liczb, które zostały wpisane tak, jak to widać w kolumnie „wpisane”. Kolumna „sformatowane” pokazuje jak te liczby, po sformatowaniu, zobaczy użytkownik arkusza. Kolejne wiersze pokazują postać wyniku mnożenia tych liczb zależną od sposobu obliczania i formatowania.

Jeśli w bardzo dużej tabeli takie dane wystąpią tysiąckrotnie, to ich wpływ na wynik sumowania będzie już istotny. Widać, że tysiąckrotne zsumowanie wartości, która po sformatowaniu pokazywana jest jako 3,00, wcale nie daje wyniku 3000,00, lecz 2996,21. Gdy jakiś dociekliwy odbiorca zestawienia zechce sprawdzić na kalkulatorze nasze obliczenie, to 2,35×1,3 da mu dokładny wynik 3,055, czyli w zaokrągleniu 3,06, a nie 3,00, jaki widzi w zestawieniu.

Wracając do realizacji projektu, dla komórki F2 wprowadziłem jeszcze jedno formatowanie warunkowe, które wyświetli jej zawartość przy pomocy stylu „Error”. To formatowanie sprawdza, czy gdy wybrano jednostkę miary „para” lub „szt.” – „ilość” jest liczbą całkowitą. Chodzi o uniknięcie sytuacji, gdy ktoś dla tych jednostek miar, które z samej natury takiej nazwy dotyczą liczb całkowitych, wpisze liczbę z ułamkiem dziesiętnym. „Wartość” zostanie obliczona, lecz będzie sygnalizowana zmienionym stylem.

W wykonywalnej wersji arkusza wiersz drugi będzie ukryty. Pogrubiona linia między wierszem drugim a trzecim sygnalizuje miejsce przytwierdzenia wierszy leżących powyżej. To sprawi, że przy długich zestawieniach, podczas przewijania, zawsze będą widoczne kolumny opisowe.

Tabela nie zawiera jeszcze żadnych wprowadzonych danych, dlatego trzeci wiersz tej tabeli jest jednocześnie wierszem ostatnim i zawiera końcową formułę podsumowującą wartość globalną zestawienia. Obliczona jest ona za pomocą funkcji SUMY.CZĘŚCIOWE (w LibreOffice) lub jej odpowiedniku SUMY.POŚREDNIE (w Apache OpenOffice). To z kolei sprawi, że podsumowanie będzie zawsze zawierało wynik dotyczący tylko wierszy odfiltrowanych, a gdy filtrowania nie będzie – wszystkich wierszy.

Pierwsza komórka tego wiersza (A3) ma przypisaną nazwę „Razem”, będzie ona wykorzystana do dotarcia do miejsca wstawiania nowego wiersza. Miejsce wstawiania będzie się bowiem przesuwało w dół tabeli, w miarę dodawania nowych wierszy.

Nowe wiersze będą wstawiane do tabelki bezpośrednio przed wierszem podsumowania.

Nagrane makro zostanie podpięte pod przycisk ze znakiem „+” umieszczony w nagłówku tabeli, w obszarze „Nazwa” (komórka B1).

Oto nagrana prezentacja.

Na co należy zwrócić uwagę podczas nagrywania makr.

• System nagrywania zapisuje wszystkie działania użytkownika, także niestety te przypadkowe i pomyłkowe. Warto przygotować sobie scenariusz postępowania, aby nie pomylić czynności.
• Szczególną uwagę zwrócić na nagrywanie stanów pól wyboru i przycisków opcji. System nagra ich zmiany tylko wówczas gdy efekt ustawień będzie inny niż pierwotny. Tu małe wyjaśnienie na konkretnym przykładzie. Makro ma nagrać przestawienie pola wyboru ochrony komórek na „ustawiony” (to jest element formatowania komórki w zakładce „Ochrona komórek”, opcja „Chronione”). Po rozpoczęciu nagrywania wykonujemy wszystkie kolejne kroki i w okienku dialogowym widzimy, że ta opcja jest już ustawiona. Zakładamy więc następującą strategię: najpierw wyłączymy tę opcję, następnie ją włączymy, potem w nagranym makro znajdziemy ten fragment, który wyłączał opcję i go usuniemy. Ten pomysł nie spełni oczekiwanego rezultatu, gdyż w takiej sytuacji nic nie zostanie nagrane.
• Skróty klawiaturowe są także rejestrowane (np. CTRL+SHIFT+↓, CTRL+1, CTRL+C itp.).
• Komórki albo zakresy w arkuszu, do których chcemy dotrzeć, lecz nie możemy określić ich położenia, musimy nazwać. Wybór komórki z użyciem jej nazwy aktywizuje ją, bez względu na to, gdzie się aktualnie ona znajduje i taki wybór zostanie zarejestrowany.
• Do wskazywania miejsc w dokumencie można wykorzystać nawigatora.

Programowanie w Basicu i API.

Temat programowania jest zbyt obszerny jak na to opracowanie. Wymaga przede wszystkim dobrego podręcznika. Takich podręczników w języku polskim niestety nie ma. Dlatego w tym miejscu podam linki do źródeł omawiających aspekty programowania w języku Basic z wykorzystaniem API. Są w języku angielskim. Pierwsze, to przyjaźnie zorganizowany serwis pod adresem nadzorowanym przez Apache OpenOffice.

Oto on: https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide

Drugim doskonałym źródłem wiedzy jest serwis guru programowania w (x)Office, Andrew Pitonyak’a.

W swoim serwisie utworzył sekcję „Andrew Pitonyak's OpenOffice Macro Information” ( https://www.pitonyak.org/oo.php), w której można znaleźć odpowiedzi na wiele własnych pytań i wątpliwości. Jednak przede wszystkim można pobrać najnowsze wydanie opracowania „OpenOffice.org Macros Explained”, będącego jednym z najczęściej cytowanych i polecanych źródeł. W czasie gdy pisałem to opracowanie dostępne było czwarte wydanie tej książki, pod następującym adresem: https://www.pitonyak.org/OOME_4_0.odt. Warto jednak sprawdzić, czy autor nie dodał kolejnego, poprawionego, wydania.