Ścieżka wielu użytkowników Excela jest podobna. Najpierw poznajemy coraz dziwniejsze funkcje. Potem zaprzyjaźniamy się z VBA. Potem wykorzystujemy tę wiedzę, aby pomóc innym. A potem próśb o pomoc jest zbyt wiele i chcielibyśmy móc jakoś wysłać fragment naszej wiedzy komuś w prezencie. I do tego służą dodatki w Excelu.

Tworzenie dodatków do Excela – to łatwe!

Jesteś znawcą Excela. Nie, to mało powiedziane. Excela masz w małym palcu. Wiesz o nim wszystko. VLOOKUP czy PIVOT to imiona twoich dzieci. Oczywiście, że znasz VBA, bo na tym poziomie byłby wstyd przed rodzicami, gdyby zabrakło ci tej wiedzy. Makra piszesz z zamkniętymi oczami. Ale tworzenie tylko dla siebie to za mało, a rozsyłanie mailem arkuszy kalkulacyjnych z instrukcją, co gdzie kliknąć, już cię nie bawi. Chcesz wejść na wyższy poziom pomagania ludziom w Excelu. Chcesz wybudować sobie pomnik trwalszy niż ze spiżu.

Albo po prostu masz bardzo fajne makro lub niesamowicie przydatną funkcję swojego autorstwa i nie chcesz każdorazowo uruchamiać skoroszytu, w którym jest ten konkretny kod VBA. W każdym z powyższych przypadków możesz pokusić się o stworzenie dodatku do Excela. Będzie to plik z zapisanym kodem VBA, który uruchomi się w tle przy każdym otwarciu Excela.

Po co tworzyć dodatki do Excela? Aby ułatwić użytkownikom naszego kodu korzystanie z niego. Bez względu na to, jak zaplanujemy działanie naszego dodatku, zainstalowany dodatek uruchomi się automatycznie przy włączeniu Excela. Ponadto będzie on działać w tle, w sposób niewidoczny nawet dla średniozaawansowanych użytkowników. Wszelkie arkusze dodatku będą ukryte, bez możliwości ich wyświetlania. Jeśli nasz kod VBA dokonuje jakichś zmian w pliku dodatku, zapytanie o ich zapisanie nie jest wyświetlane. Możemy dodatkowo zabezpieczyć naszą aplikację hasłem, żeby uniemożliwić zmienianie jej, także przypadkowe. Wszystko to sprawia, że znacząco maleje ryzyko pomyłkowego uszkodzenia naszej pracy przez użytkownika, a tym samym konieczności rozwiązywania problemów lub wystąpienia dyskomfortu przy korzystaniu z naszego kodu.

Dodatek to idealne narzędzie do pomagania innym użytkownikom Excela, a zarazem doskonały sposób korzystania z rozwiązań stworzonych przez innych. Pamiętam, jak stworzyłem dodatek oszczędzający koleżance godzinę pracy. Oboje się cieszyliśmy, choć z różnych powodów. Zobaczmy więc, w jaki sposób można przygotować dodatek w Excelu, z którego będzie mógł skorzystać każdy.

Odpowiednie podejście do tworzenia

Zanim jednak przejdziemy do szczegółów technicznych, podzielę się spostrzeżeniem. Stworzyłem w życiu trochę dodatków i moim zdaniem niezbędne jest dokładne sprawdzenie swojego kodu VBA przed wypuszczeniem gotowego dodatku. Nieważne, czy jest to makro, czy funkcja – przetestujmy je dogłębnie, zgodnie z zasadami sztuki testerskiej. Stwórzmy różne scenariusze, poprośmy użytkowników końcowych o konsultacje, sprawdzajmy nawet najbardziej nieprawdopodobne przypadki. Jeśli coś jest na świecie nieprzewidywalne, to są to właśnie użytkownicy naszego dodatku. Gdy go tworzymy, wydaje nam się, że po prostu przekształcamy pewien algorytm procesu na VBA, ale życie nie daje się tak łatwo ująć w linie kodu.

Dokładne sprawdzenie jest kluczowe, bowiem często po wypuszczeniu z naszych rąk stworzonego dodatku tracimy kontakt z plikiem i jego użytkownikami. Owszem, mogą siedzieć nawet przy sąsiednim biurku, ale w rzeczywistości nie będą już tak bardzo zainteresowani współtworzeniem tego dodatku. Przyjmą założenie, że dostali gotowy i działający produkt. Mogą przekazywać go dalej. Mogą korzystać z niego automatycznie, nie analizując jego działania. A przecież w jakimś skrajnym przypadku nasz dodatek może zmieniać ich dane, powodować błędy, a wszystko to w sposób niezauważalny na pierwszy rzut oka. Dlatego tak ważne jest dołożenie wszelkich starań, aby nasz dodatek był gotowy na każdą ewentualność. Pamiętajmy też, że dodatek będzie się uruchamiał w tle przy każdym włączeniu Excela, warto więc nie przeładowywać go, aby nie spowalniać startu programu. Jeśli musimy umieścić w naszym narzędziu wiele danych, może zamiast dodatku warto zrobić aplikację, czyli specjalnie przygotowany skoroszyt?

Dodatek w trzech prostych krokach

Jak zatem zrobić dodatek? To zależy od tego, co w nim chcemy zawrzeć i na ile skomplikowany jest ten dodatek. Zacznijmy więc od podstaw. Pliki z dodatkami MS Excel mają rozszerzenie xlam – wystarczy więc po prostu zapisać nasz plik jako taki i dodatek będzie gotowy. W praktyce bowiem warto przedtem zadbać o kilka kwestii. I to pomijając kwestię testowania. Załóżmy, że stworzyliśmy kod idealny, przetestowaliśmy go na różne sposoby. Warto pomyśleć o lepszym opisaniu naszego projektu oraz zabezpieczeniu go przed przypadkowymi albo celowymi zmianami przez użytkowników.

W edytorze VBA wybieramy z menu Tools opcję VBAProject Properties. W nowym oknie, które się pojawiło, na karcie Protection zaznaczamy Lock project for viewing (Rysunek 1) oraz dwukrotnie podajemy hasło w odpowiednich polach. Tak unikamy modyfikacji pliku oraz zawartego w nim kodu.

Aby dodatek miał nazwę i opis widoczne dla przyszłych użytkowników, przejdźmy do sekcji Informacje na karcie Plik. Z menu Właściwości wybierzmy Właściwości zaawansowane. W oknie dotyczącym naszego pliku, na karcie Podsumowanie, umieśćmy nazwę naszego dodatku – w polu Tytuł – i jego krótki opis, w polu Komentarze (Rysunek 2).

Pamiętajmy też o tym, że użytkownik naszego dodatku nie będzie widział żadnego arkusza, jaki jest w naszym skoroszycie. Dlatego zadbajmy o to, aby w naszych arkuszach nie było danych lub informacji, które są niezbędne dla użytkownika. Oczywiście możemy stworzyć makro, które będzie kopiować dane z arkuszy w dodatku do arkuszy w pliku użytkownika, ale musi być to zrobione bardzo ostrożnie. Należy w kodzie makra wyraźnie rozróżnić arkusz źródłowy i docelowy, a także zadbać, aby przenoszone dane nie nadpisały jakichś informacji użytkownika.

Mamy więc kod zabezpieczony przed zmianami, opisany w oknie właściwości i przetestowany. Zapisujemy go jako plik o rozszerzeniu xlam i... to wszystko. Gotowe. Oczywiście to jest najprostszy dodatek i nie w każdym przypadku możemy sobie pozwolić na zakończenie pracy tutaj. Ale jeśli nasz dodatek zawiera tylko kod dodatkowej funkcji – to w zasadzie wszystko, co musimy zrobić. Zauważmy, że przy wyborze rozszerzenia xlam domyślnie wskazywany jest folder do przechowywania dodatków – jeśli chcemy korzystać z naszej pracy, warto zapisać plik właśnie tam. Pozostaje tylko zainstalować taki dodatek.

Instalacja dodatku

W tym celu na karcie Deweloper wybieramy polecenie Dodatki programu Excel. W wyświetlonym oknie widzimy wszystkie już zainstalowane lub dostępne do uruchomienia dodatki. Jeśli zapisaliśmy dodatek w domyślnej lokalizacji, powinien być na liście. Wystarczy go zaznaczyć i kliknąć OK (Rysunek 3). Jeśli na liście nie ma nowego pliku, za pomocą opcji Przeglądaj możemy go zlokalizować. Zwracam uwagę, że domyślnie poszukiwanie zaczynamy w folderze z dodatkami. Dodatkami można też zarządzać przez wybranie na karcie Plik polecenia Opcje, a następnie wybranie sekcji Dodatki. Zobaczymy aktywne i nieaktywne dodatki Excela. Aby móc dodać kolejny, należy kliknąć przycisk Przejdź, dzięki czemu wyświetli się standardowe okno dodatków. Użytkownik po zainstalowaniu takiego prostego dodatku będzie mógł bezproblemowo korzystać ze stworzonej przez nas funkcji w swoich skoroszytach poprzez wpisywanie jej w komórce lub z poziomu okna Wstawianie funkcji, po wybraniu w rozwijanym menu kategorii Zdefiniowane przez użytkownika. Podobnie jeśli stworzyliśmy makro, które uruchamia się automatycznie, na przykład w momencie zamykania lub otwierania Excela – tu również nie trzeba niczego więcej, aby nasz dodatek mógł działać w tle.

Co jednak w przypadku, gdy stworzyliśmy dodatek z makrem, które musi być jakoś uruchomione? Tu również mamy możliwość skorzystania z prostego rozwiązania, o ile będziemy samodzielnie korzystać ze stworzonego przez nas dodatku lub możemy pomóc przy jego instalacji wszystkim korzystającym z niego użytkownikom. Wystarczy wtedy umieścić ikonę wywołującą dany kod lub formularz na pasku szybkiego dostępu. Aby to zrobić, po zainstalowaniu dodatku wybieramy z rozwijanego menu w pasku szybkiego dostępu opcję Więcej poleceń. Następnie z rozwijanego menu Wybierz polecenia z: wybieramy Makra – pojawi się lista dostępnych makr. Wystarczy przenieść nasze makro z dodatku do okna po prawej stronie i gotowe. Możemy uruchamiać nasze makro przyciskiem szybkiego dostępu.

Zmieniamy interfejs Excela

A co w sytuacji, gdy stworzony przez nas dodatek jest nieco bardziej skomplikowany? Składa się na niego więcej poleceń, być może niektóre z nich warto byłoby pogrupować – w takim przypadku dodawanie ich do paska szybkiego dostępu mogłoby w całości zapchać to przydatne narzędzie. Poza tym dodawanie pojedynczo wszystkich makr jest czasochłonne. A jeśli jeszcze mielibyśmy dopilnować – osobiście lub posługując się instrukcją – naszych użytkowników, aby dodali wskazane przez nas makra, to brzmi to jak przepis na kłopoty, chaos i niezadowolonych użytkowników. Tu trzeba zadziałać inaczej i stworzyć własną kartę na wstążce, zawierającą wszystkie niezbędne ikony.

Tu pozwolę sobie na chwilę wspomnień z dawnego szlaku bojowego twórców dodatków. Przed 2007 r. tworzenie własnych ikon dla dodatków było proste i sprowadzało się do ich narysowania. Potem jednak wymagało to dłubania w kodzie pliku Excela, czyli rozpakowaniu go za pomocą aplikacji do kompresji plików, a następnie dodaniu plików z rysunkami ikon i edycji jego struktury XML. Miało to posmak bycia hakerem, ale jednocześnie istniało ryzyko popełnienia błędu. Zwłaszcza jeśli ktoś nie znał składni XML. Na szczęście te czasy już minęły i teraz mamy aplikacje, robiące za nas całą tę brudną robotę.

Do tworzenia własnej karty na wstążce użyjemy narzędzia Custom UI Editor – jest ono darmowe i do pobrania ze strony bettersolutions.com/vba/ribbon/custom-ui-editor.htm. Po jego rozpakowaniu, przy pierwszej próbie uruchomienia, może pojawić się monit o zainstalowanie .NET Framework 3.5 – należy się na to zgodzić i cierpliwie poczekać, aż system zainstaluje wszystkie niezbędne składniki. W dalszej części artykułu będę opierać się na aplikacji Office RibbonX Editor, która bazuje na Custom UI Editor – różnice między nimi są kosmetyczne, natomiast pobranie jej jest nieco bardziej skomplikowane (ale wyjaśnione na wspomnianej stronie). Warto też podkreślić, że możemy wykorzystać te aplikacje nie tylko do modyfikowania wstążki w dodatku, ale także w zwykłym skoroszycie. Można nawet zaprojektować dla swojego skoroszytu w całości nową wstążkę, tworząc w ten sposób elegancką aplikację.

Po uruchomieniu widzimy okno jak na Rysunku 4. Aby móc edytować wstążkę w naszym pliku, należy go otworzyć w aplikacji za pomocą polecenia Open. Nazwa naszego pliku pojawi się w panelu po lewej stronie. Custom UI Editor pozwala nam na dodanie do Excela pliku XML, który normalnie wstawialibyśmy ręcznie, rozpakowując nasz skoroszyt. W tym celu z menu Insert wybieramy odpowiednią opcję, zależnie od naszej wersji Excela. Ponieważ działam na wersji 2019, wybrałem Office 2010+ Custom UI Part (Rysunek 5) – jest to opcja właściwa dla wszystkich wersji od 2010 wzwyż. W panelu po lewej stronie pojawiła się pozycja oznaczająca plik XML – jest on jednak domyślnie pusty. Aby wstawić kod, generujący nową kartę wstążki, zaznaczmy ten nowy plik i ponownie w menu Insert wybieramy Sample XML, a następnie Custom Tab (Rysunek 6). Ta opcja pozwala na najbardziej elastyczne zmienianie wstążki.

W panelu po prawej stronie pojawił się kod (Rysunek 7), który będziemy musieli edytować. Domyślnie dodana jest jedna karta o nazwie Custom Tab i jednym przycisku. Przyjrzyjmy się składni naszej wstążki. Custom UI to root element naszego pliku XML, w którym zawierają się następujące elementy:

  • ribbon: odpowiada za generowanie wstążki,
  • tabs: zbiór kart na wstążce,
  • tab: konkretna, jedna karta,
  • group: grupa przycisków na karcie,
  • button: przycisk na karcie, przypisany do danej grupy.

Najważniejsze zmiany kodu XML

Jeśli chcemy, aby wstążka składała się tylko z naszych kart, w elemencie ribbon musimy umieścić atrybut startFromScratch=”true”. W przypadku dodatku wydaje się to jednak zbędne, nie chcemy przecież, aby zniknęła cała wstążka w Excelu. Tab to kolejny element, który powinniśmy zmienić. Atrybut id może pozostać niezmieniony, bowiem w przypadku prostych modyfikacji nie będzie on używany. Zmieńmy wartość atrybutu label, nadając mu taką nazwę, jaka powinna wyświetlać się na karcie wstążki. Następnie w elemencie group możemy zmienić wartość atrybutu label na nazwę grupy naszych ikon, ale możemy go też usunąć. W takim wypadku nasza grupa ikon nie będzie w żaden sposób nazwana.

Ostatnia zmiana, jaka jest konieczna, to element button. Przyjrzyjmy się jego atrybutom. Id warto zmienić, choćby po to, aby mieć porządek w kodzie naszego dodatku. Label to nazwa, jaka będzie wyświetlana pod przyciskiem, więc koniecznie należy ją zmienić. ImageMso to nazwa pliku z obrazem ikony z wbudowanej biblioteki obrazów Microsoft. Za chwilę zmienimy tę ikonkę. Size to rozmiar naszej ikony: dostępne opcje to large i normal. Z kolei onAction to odwołanie do naszego makra – więc jako wartość naszego atrybutu powinna być podana nazwa, występująca w kodzie po słowie Sub. Jednocześnie w kodzie VBA, w nawiasie po nazwie naszego makra, należy dodać słowa control As IRibbonControl. Możemy wygenerować początek makr dla naszych przycisków za pomocą przycisku Generate Callbacks w UI Editorze. Po dodaniu tego argumentu w makrze, nie będzie ono już widoczne w oknie makr Excela.

Aby zmienić obraz ikony klikamy przycisk Insert Icons i wybieramy rysunek naszego przycisku – w formacie png, dzięki czemu możemy skorzystać z przezroczystości tła. Zadbajmy o przyjazną nazwę tego pliku – jeśli nie zrobiliśmy tego wcześniej, możemy kliknąć w lewym panelu na danym obrazku prawym przyciskiem i wybrać Change ID. Następnie w elemencie button, dla którego chcemy użyć danej ikony, kasujemy atrybut imageMso, a zamiast niego używamy atrybutu image, z wartością równą nazwie naszej ikony. W ten sam sposób możemy dodać i edytować kolejne przyciski – czy to w ramach tej, czy kolejnej grupy – a nawet nowe karty na wstążce. Zawsze przed zapisaniem warto kliknąć przycisk Validate, aby mieć pewność co do poprawności kodu XML. Przykładowy kod po opisanych powyżej zmianach przedstawiam na Rysunku 8, a stworzoną w jego wyniku wstążkę na Rysunku 9.

A to dopiero początek przygody...

Nie są to, oczywiście, jedyne możliwości modyfikacji wstążki. Poza przyciskami na karcie można dodawać rozwijane menu (element dropDown), pola wyboru (element checkBox) czy przyciski z dodanym menu (element splitButton). Każdy z tych elementów ma swoje atrybuty, pozwalające stworzyć elegancką i funkcjonalną kartę wstążki. Jest ich jednak sporo, a ich przegląd to zupełnie inny temat. Pamiętajmy jednak o nich, tworząc nasze dodatki. W ten sposób możemy uczynić je bardziej przyjaznymi dla użytkownika końcowego, pozwalając mu wybierać odpowiednie makra według potrzeb. Zachęcam do eksperymentowania z tworzeniem dodatków, choćby na własne potrzeby. To najlepszy sposób, aby nie tylko rozwijać swoją znajomość VBA, ale przede wszystkim pomagać innym w szybszym i efektywniejszym wykonywaniu ich pracy.

Możesz zobaczyć ten artykuł, jak i wiele innych w naszym portalu Controlling 24. Wystarczy, że klikniesz tutaj.

Ulubione Drukuj

Zobacz również

Tylko on-line nr 27/2021

Wykresy wodospadowe w praktyce

Wykresy są często nieodłącznym narzędziem wykorzystywanym przy różnego rodzaju raportach. Ich zadaniem jest zarówno przekazać pewne merytoryczne dane, jak również przyciągnąć uwagę odbiorcy. Jako że większość z nas jest wzrokowcami, to dobrze stworzony wykres pozwoli zapamiętać najważniejsze dane czy różne zależności.

Czytaj więcej

Pętle w Excelu

Pętle w Excelu

Język VBA (ang. Visual Basic for Applications) jest językiem programowania obsługującym aplikacje pakietu Microsoft Office, do których należy m.in. Excel. Celem pracy w VBA jest automatyzacja zadań wykonywanych na dokumentach za pomocą makropoleceń. Jednym z podstawowych narzędzi dostępnych w VBA są pętle.

Czytaj więcej

Przejdź do

Partnerzy

Reklama