Tworzenie wysokiej jakości aplikacji VBA wymaga pracy na wielu polach. Głównie ścierają się tutaj dwie rzeczy: kodowanie i projektowanie. Oznacza to, że dobry VBA Developer potrafi nie tylko pisać wydajne makra, ale też stworzyć intuicyjny i łatwy w obsłudze interfejs. Czego zatem trzeba się nauczyć, aby budować kompletne aplikacje VBA? Czy są jakieś punkty wspólne w dużych projektach Excelowych? Odpowiedź brzmi – tak!
Działanie w makroskali
Jesteśmy przyzwyczajeni do tego, że ucząc się Excela, rozwiązujemy mały problem. Często jest to napisanie formuły, wstawienie wykresu, edycja nagranego makra czy prosta obróbka danych w PQ. To wszystko stanowi tylko malutki wycinek, niewielki procent całej aplikacji. Im dalej w las tym ciemniej. Istnieją problemy, o których nie wiemy, że istnieją, a które NA PEWNO się pojawią.
Ilość danych
Działanie w makroskali to operowanie na dużo większej ilości danych niż do tego przywykliśmy. Przykładowo, w projekcie dla Fundacji, raportem źródłowym był plik CSV z Urzędu Skarbowego, który składał się z 425 000 wierszy.
Okazuje się wówczas, że rozwiązania, które świetnie działają w mikroskali, w ogóle nie sprawdzają się przy większym zbiorze danych. Nie inaczej było tym razem… Aby oczyścić dane w kluczowej kolumnie Cel szczegółowy, musieliśmy dokonać podmiany ok. 8 000 fraz w każdej komórce… Nieskuteczne było PQ, funkcja UDF czy lambda rekurencyjna. Rozwiązaniem najlepszym okazały się tablice VBA.
Duża ilość danych to także duże ryzyko „puchnięcia” plików. Excel zwyczajnie może tego nie „udźwignąć”. W naszym przypadku, chodziło głównie o dodanie wielu kolumn pomocniczych w pliku głównym. Każda taka kolumna to 425 000 nowych, niepustych wierszy.
A, zapomniałem dodać, że niezbędne okazało się przejście na Excela 64-bit, ponieważ wersja 32-bit nie pozwalała nam operować na tak dużych zbiorach danych.
Architektura
Aplikacje EXCEL/VBA to zawsze system naczyń połączonych. Konieczne jest zaplanowanie całej architektury tj. powiązań między plikami. W przypadku omawianej aplikacji, potrzebowaliśmy m.in.:
- pliku głównego do rozszyfrowania wpłat
- mniejszego narzędzia do ręcznego rozpoznawania <1% datków
- generatora raportów PDF i XLSX
- szablonów XLTX pod wymienione raporty
- kilkunastu kwerend PQ na bazie pliku CSV
Obsługa błędów
Każda duża automatyzacja powinna posiadać dobrą obsługę błędów. Dzięki niej, możemy od razu namierzyć problem i pomóc Klientowi. Każdy profesjonalny programista powinien myśleć dalekowzrocznie, a wdrożenie takiego rozwiązania jest przejawem takiej właśnie odpowiedzialności.
W praktyce, często spotykam się z kodem, który nie zawiera nawet podstawowej obsługi błędów, a od tego de facto powinno się zaczynać ciało procedury. Może być to kwestia lenistwa, albo braku wypracowanych standardów pracy w VBA.
Praca równoczesna
Dużo wyzwań pojawia się zwłaszcza w projektach korporacyjnych. Wynika to z faktu, że są one obsługiwane przez kilkadziesiąt osób. Użytkownicy operują zwykle na kopiach skoroszytów, ale często muszą otworzyć inny plik w trybie pełnego dostępu, aby np. coś w nim zarejestrować. Wyzwaniem będzie tu więc równoczesny dostęp.
Należy także „dograć” z IT sprawę uprawnień dostępu pracowników do konkretnych folderów na serwerze Zakładu. Jeśli użytkownik nie może otworzyć pliku w trybie pełnego dostępu, nie zarejestruje w nim zmian, a aplikacja zgłosi błąd.
Pełna automatyzacja
Aplikacje VBA mogą działać samoczynnie przez 24h, bez ingerencji człowieka. Po prostu mają cyklicznie (np. co 15min) pobierać dane, przetwarzać je i „wypluwać” raporty lub inne wizualizacje, które później są w jakiś sposób „konsumowane” np. wyświetlane na dużych monitorach produkcyjnych.
Mój stały Klient miał prośbę, aby dwie takie aplikacje pracowały równocześnie na tym samym komputerze. Zrobiłem to zadanie w oparciu o dwie niezależne instancje Excela.
Projektowanie wrażeń
Jak wspomniałem na wstępie, tworzenie aplikacji VBA to nie tylko pisanie kodu, ale także projektowanie interfejsu. Mam tu na myśli sposób w jaki użytkownik komunikuje się z programem.
Formularze
Sercem i mózgiem każdej profesjonalnej aplikacji VBA są tzw. userforms. To głównie one poprawiają komfort pracy i wpływają istotnie na odbiór aplikacji, a także jej cenę.
Projektując taki interfejs zawsze trzymam się prostej zasady: „Twórz formularze, które do złudzenia imitują wbudowane okna Excela”. Od kilkunastu lat moją inspiracją są okna, które można znaleźć w dodatku ASAP Utilities.
Wstążka
Drugim elementem interfejsu aplikacji Excelowych jest własna wstążka. Zawiera ona pogrupowane kontrolki, które mają przypisane konkretne makra. Chociaż możliwości są tutaj ogromne, moi Klienci chcą rozwiązań maksymalnie prostych w obsłudze. Wynika to z faktu, że użytkownikami aplikacji są często osoby słabo znające Excela, lub nawet rzadko korzystające z komputera (np. pracownicy produkcji). W praktyce wystarczą im zatem duże przyciski.
Ponieważ zaprojektowanie własnej wstążki jest trudne, bo wymaga znajomości XML, lepiej zaopatrzyć się w darmowy dodatek RibbonX Visual Designer.
Arkusze
Następnym elementem, który mocno wpływa na aspekt wizualny naszych aplikacji są arkusze. Zauważam tu duży problem, bo narzędzia Excelowe często są tworzone ad-hoc, w pośpiechu. Mało, która firma posiada własne szablony – brakuje też kursów dotyczących tego, jak projektować estetyczne arkusze. Ja od kilku lat korzystam z szablonu Microsoftu, dzięki czemu w prosty sposób uzyskałem miły dla oka efekt 😉.
Dashboardy
Warto w tym miejscu wspomnieć o interaktywnych kokpitach menedżerskich. Może się zdarzyć, że to właśnie dashboardy będą finalnym celem aplikacji. Trzeba więc wiedzieć jak je tworzyć tzn. jak współpracują ze sobą tabele przestawne, fragmentatory, wykresy i kształty.
Czy warto nauczyć się tworzenia efektownych dashboardów? To zależy! Dla mojego Klienta kluczowy był… minimalizm. Kontrolerzy jakości codziennie analizują błędy produkcyjne na tzw. „daily scrap review” i na każde takie spotkanie muszą wydrukować kilka dashboardów. Postawili więc na oszczędność i pragmatyzm.
W tym miejscu warto wspomnieć o rosnącej popularności narzędzia POWER BI, które zdominowało temat tworzenia interaktywnych kokpitów menedżerskich. W tej chwili jest to must-know każdego poważnego analityka danych.
Procesowanie danych
Przetwarzanie danych od zawsze było istotną częścią automatyzacji opartych na Excelu. Niektórzy wręcz uważają, że automatyzacja ogranicza się do procesu ETL (od słów: Extract → Transform → Load), ale jest to pojęcie znacznie szersze.
Power Query
Pojawienie się Power Query znacznie ułatwiło proces obróbki danych w Excelu. Wcześniej takie przekształcenia były realizowane z poziomu VBA. Trzeba było być doświadczonym programistą, aby wykonywać je samodzielnie. Odkąd w Excelu pojawiło się PQ, nawet laik jest w stanie zaimportować dane z wielu plików, czy ze strony internetowej. Nie trzeba znać składni VBA, rozwiązanie można łatwo wyklikać.
Power Query świetnie odciąża VBA, ale poważnym błędem jest traktowanie go jako alternatywy. Napisałem o tym tutaj.
Kwerendy bardzo mocno ułatwiają życie, ponieważ wystarczy je tylko odświeżyć makrem – nie trzeba ich tworzyć od podstaw. Pod tym względem są trochę podobne do tabel przestawnych.
Ale nie ma róży bez kolców. Korzystanie z PQ jest bardzo intuicyjne, ale tylko w podstawowym zakresie. Już samo budowanie warunków jest znacznie trudniejsze niż w przypadku formuł Excela. Chcąc dokonać bardziej spektakularnych transformacji, musimy nauczyć się języka M. A jego nauka do najłatwiejszych nie należy.
Tablice
Pomimo ogromu zalet, PQ ma jednak swoje wady i w kontekście złożonych automatyzacji – nie jest narzędziem wystarczającym. Aby pracować efektywnie z danymi, należy nauczyć się ich przetwarzania w tablicach VBA.
PQ nigdy nie zwróci wyników w formie tradycyjnej (obiekt komórkowy) – jest to Tabela (przestawna) lub połączenie. Makro ma tę przewagę, że nie tworzy łączy.
W praktyce, bardzo często istnieje potrzeba przetworzenia tablicy do innej postaci, aby wczytać tę nową strukturę do konkretnej formatki (np. faktury VAT, planu lekcji, karty złomowania itp). Co więcej, obiekt Tabela nie współpracuje dobrze z filtrem zaawansowanym, który jest prawdziwą lokomotywą do generowania raportów.
Automatyzacja raportowania
Raportowanie jest zwykle ostatnią fazą nietrywialnych aplikacji VBA. Jego rolą jest utworzenie finalnych plików, które będą w jakiś sposób pokazywane, analizowane i rozpowszechniane.
Pliki PDF
Jednym z najbardziej popularnych makr jest eksport arkusza do pliku PDF. Faktycznie, potrzeba uzyskania raportów w takim formacie jest widoczna. Co więcej, często jest głównym celem działania aplikacji. Napisałem w liczbie mnogiej „raportów”, bo zwykle chodzi o utworzenie w pętli dużej liczby takich plików.
Idealnie do tego zadania nadaje się wspomniany już filtr zaawansowany. Ma trzy potężne zalety – jest niesamowicie szybki, łatwy w oprogramowaniu i świetnie współpracuje z szablonami.
Samo generowanie plików PDF też jest proste. Wystarczy określić prawidłową nazwę i ścieżkę; upewnić się, że docelowy katalog istnieje, a obszar wydruku jest poprawnie zdefiniowany. Potem wystarczy już tylko podać argumenty dla metody ExportAsFixedFormat. Niestety pewną wadą jest fakt, że parametr Quality posiada tylko wartości xlQualityMinimum i xlQualityStandard. W sytuacji, gdy do PDF chcemy wyeksportować nie tylko tekst, ale też grafikę – jakość nie będzie aż tak dobra jak w Excelu.
Wracając do meritum. Większość moich programów posiada opcję masowego generowania plików PDF.
Najbardziej dobitnym przykładem jest aplikacja dla Fundacji. Jej ostateczny cel polega na utworzeniu raportu PDF dla każdego dziecka z listą otrzymanych wpłat. W 2023r. tych plików było ok. 4 000 i zgodnie z zasadą pracy w makroskali, wystąpiły nieoczekiwane problemy (wyciek pamięci RAM), które udało się sprawnie rozwiązać. W przypadku aplikacji dla Szkoły, operator ma możliwość utworzenia bardzo elastycznych grafików zajęć – w skali semestru, miesiąca, tygodnia – dla klas lub nauczycieli. OPTISTAL pozwala z kolei użytkownikowi zapisać do PDF rozkrój poszczególnych belek i prętów. Aplikacja do rejestrowania części uszkodzonych w trakcie montażu (scrapping), eksportuje do PDF pojedynczą kartę, ale dziennie generowanych jest ok. 100 takich plików.
XLSX i inne formaty
Oprócz PDF, drugim najbardziej pożądanym formatem jest XLSX. Ze względu na fakt, że w takich plikach łatwo zmienić dane, Klienci wolą raporty PDF.
Coraz rzadziej pojawiają się prośby o zapis do CSV czy TXT. Zdarzają się natomiast zapytania o makra, które eksportowałyby dokumenty (podatkowe) do formatu XML lub JSON, a następnie wysyłały je przez API na stronę internetową.
Dystrybucja raportów
Jeśli chodzi o rozpowszechnianie raportów, to tylko w dwóch przypadkach Klient chciał, aby pliki były automatycznie wysyłane Outlookiem. Chodziło o aplikacje dla HR (automatyczne rozliczanie delegacji + rejestracja czasu pracy).
Podsumowanie
Aplikacje EXCEL/VBA różnią się od siebie nie tylko logiką biznesową, ale także ilością użytkowników czy częstością korzystania. Rozwiązania korporacyjne zwykle są w użyciu przez cały czas, obsługuje je wiele osób i są one nieco mniej skomplikowane. Programy niekorporacyjne (np. Fundacja, Szkoła) są używane cyklicznie (raz lub kilka razy w roku), są obsługiwane przez jedną lub dwie osoby i często mają kluczowe znaczenie dla funkcjonowania całej organizacji.
Pomimo tej różnorodności – istnieją pewne cechy wspólne takich narzędzi.
- rozwiązują one problem w skali makro
- każde z nich posiada mniej lub bardziej zaawansowany interfejs (oparty na formularzach, wstążce, czy osadzonych kontrolkach)
- istnieje jakiś Input (dane surowe, raporty źródłowe)
- istnieje jakiś Output (docelowe raporty PDF/XLSX, wizualizacje).
Aby stworzyć kompletną aplikację EXCEL/VBA należy rozwijać swoje umiejętności zarówno od strony backendu (optymalizacja kodu), jak i frontendu (projektowanie UI).