“Power Query jest największą rewolucją w Excelu od czasu tabel przestawnych” – takie opinie są coraz powszechniejsze. Faktycznie, jest to świetne narzędzie, które pozwala pobierać i przekształcać dane z różnych źródeł. Dodatkowo praca z PQ jest po prostu wygodna – graficzny interfejs pozwala łatwo reorganizować dane, bez pisania złożonych zapytań SQL. Czy PQ możemy jednak postrzegać jako alternatywę dla VBA? Moim zdaniem – zdecydowanie NIE.
Power-Ofensywa…
Narzędzia Power ciągle zyskują na popularności – trudno z tym faktem w ogóle dyskutować. Na dodatek, coraz głośniej słychać fanów Power Query. W zuchwałym tonie wieszczą upadek VBA, traktując PQ jak zmiennika, który przejmie pałeczkę w tym nierównym wyścigu. Zapominają jednak, że VBA to kot mający wiele żyć…
Niedawno na LinkedIn przetoczyła się dość burzliwa dyskusja na ten temat. Zachęcam Cię ogólnie do zapoznania się z argumentami obu stron.
Doceniam ogromny potencjał Power Query i to jak dynamicznie się rozwija. To nie tylko narzędzie do pobierania danych ze skoroszytów Excela czy baz Accessa. Obsługuje też formaty XML, JSON czy PDF. Od niedawna możemy w Power Query odczytywać dane z plików graficznych. WOW! Przyznam, że nie wierzyłem, że uda się z sukcesem wdrożyć technologię OCR w Excelu. A jednak! – Impossible is nothing?
To jednak tyle pochlebstw, bo generalnie nie zgadzam się z podejściem wielbicieli Power Query. Oczywiście, w wielu obszarach oba narzędzia się pokrywają, ale nieprawdą jest jakby PQ zastąpiło VBA w 90% – co najwyżej jest to 30%.
Co Łączy PQ z VBA?
Już sama nazwa Power Query sugeruje, że mamy do czynienia z narzędziem, które jest w jakiś sposób powiązane z bazami danych. Query znaczy tyle co kwerenda lub zapytanie. Pierwsze słowo powinno nam się skojarzyć z kwerendami Access, drugie zaś z językiem zapytań SQL. I nie jest to przypadek. Taki jest cel Power Query – pobieranie i transformacja danych.
Skąd się więc biorą te wszystkie porównania do VBA? Otóż w aplikacjach często wykonujemy podobne zadania. Przeważnie mamy jakieś raporty źródłowe (tzw. „surówka”), z których musimy wyciągnąć potrzebne informacje. Zwykle robimy to dodając makrem kolumny wyliczeniowe i zamieniając formuły na wartości. Mając przygotowane dane, budujemy dashboardy lub tworzymy w pętli dedykowane raporty, które często wysyłane są mailem do zainteresowanych.
Jak widać, większość tych zadań możemy wykonać dzięki Power Query. Opisana sytuacja jest jednak dużym uproszczeniem, bowiem pomija całą logikę biznesową, która stanowi sedno w aplikacjach VBA. Pobranie i obróbka danych jest tylko częścią nietrywialnej automatyzacji, w której rozwiązujemy problem Klienta od przysłowiowej podszewki.
Kto Naprawdę Ma Moc?
Skoro już wiemy, że VBA dysponuje większym zasięgiem – zastanówmy się, co możemy zrobić dzięki makrom, czego nie wykonamy w Power Query…
Formularze Użytkownika
W PQ pobieramy i przekształcamy dane. Mamy więc już jakiegoś gotowca, na którym operujemy. Mamy bazę danych lub pojedynczą tabelę i zwykle chcemy ją najpierw trochę obrobić. Następnie chcemy ją zaimportować do Excela w całości lub części albo utworzyć na jej podstawie tabelę przestawną.
W VBA zaczynamy totalnie od zera, od pustego arkusza i czystej kartki papieru. Głośno myślimy i szkicujemy układ każdej tabeli. Następnie określamy powiązania między nimi.
W dalszej kolejności projektujemy userformy, których celem jest zawsze pobranie informacji od użytkownika. Aby zadbać o wysoką jakość danych, musimy utworzyć reguły poprawności – głównie dla zdarzeń pól tekstowych. Czasami wystarczą proste warunki – w bardziej złożonych przypadkach (np. walidacja adresu @) możemy użyć tzw. wyrażeń regularnych. Musimy też pomyśleć o ochronie tych danych, pozwalając jednocześnie na ich edycję np. z poziomu administratora.
Algorytmy Biznesowe
W VBA rozwiązujemy konkretne problemy biznesowe naszych Klientów. Dzięki elastyczności tego środowiska, możemy stworzyć narzędzie „uszyte na miarę”. To moim zdaniem największy atut VBA.
Często rozwiązujemy problem, pisząc konkretny algorytm. Prym wiodą tu zwłaszcza algorytmy optymalizacyjne, które działają w oparciu o Solvera lub zostały napisane od podstaw (np. pocięcie prętów 1D). Tego typu rozwiązania sprawdzają się przede wszystkim w logistyce i handlu (zarówno w dziale zakupów jak i sprzedaży).
Każda aplikacja VBA posiada swoją logikę biznesową. Inne wyzwania napotkamy tworząc kalkulator usług logistycznych; inne gdy będziemy budować automat do rozliczania delegacji; a jeszcze inne, gdy będziemy pracować nad aplikacją usprawniającą pracę pomiędzy magazynem, a produkcją. Jak widać, Power Query nam tu nie pomoże – na pewno nie przyda się w pisaniu algorytmów.
Automatyzacja Office
Power Query pozwala pobrać dane z plików Office, ale na tym koniec. Dzięki VBA uzyskujemy dostęp do modelu obiektowego każdej aplikacji z pakietu Office. Jaka korzyść z tego płynie? Ano, możemy uruchomić taką aplikację z poziomu Excela i wykonać z jej poziomu konkretne taski.
Najpopularniejszym zadaniem jest automatyczna wysyłka maili z Outlooka. Jest to bardzo proste do wykonania z poziomu kodu VBA, zwłaszcza gdy korzystamy z szablonów wiadomości. Ale dzięki makrom, możemy kompleksowo sterować naszą skrzynką, zarządzać kontaktami czy kalendarzem.
Dzięki Wordowi możemy stworzyć np. coś na wzór korespondencji seryjnej. Możemy szybko wygenerować np. 10 000 plików PDF z naszą ofertą. Mogą się one różnić tylko danymi odbiorcy, które zostaną wczytane z tabeli Excela. Całość oczywiście możemy zautomatyzować wysyłając te pliki Outlookiem.
Możemy także tworzyć prezentacje Power Point. Jest to bardzo efektowne, ale dość rzadko stosowane w praktyce. Wynika w dużej mierze z tego, że model obiektowy Power Pointa jest chyba najtrudniejszy ze wszystkich aplikacji Office.
Budowanie Robotów
VBA, podobnie jak Excel, posiada bogatą kolekcję wbudowanych funkcji. Ale siłą VBA jest dostęp do obiektów zewnętrznych. Możemy w bardzo łatwy sposób podpiąć pliki dll (Dynamic-Link Library), aby uzyskać dostęp do właściwości i metod obiektów. Mogą one nie mieć z Excelem zbyt wiele wspólnego, ale potrafią wynieść aplikacje VBA w całkiem inny wymiar.
VBA pozwala także na wygodną pracę na plikach i folderach. Możemy tworzyć nowe pliki i katalogi jak również usuwać już istniejące. Możemy je kopiować, wklejać, zmieniać im nazwy, a nawet kompresować czy wysyłać do chmury.
Możemy otwierać nie tylko pliki, ale dowolne programy zainstalowane na komputerze. Możemy także ściągać pliki z internetu i odczytywać dane ze stron internetowych (Web Scraping).
Dzięki temu wszystkiemu, jesteśmy w stanie budować roboty o zbliżonych możliwościach do tego, co oferują platformy RPA.
Podsumowanie
Bardzo lubię Power Query i wiem, że są sytuacje, w których to narzędzie jest niezastąpione – np. przy imporcie tabel z plików PDF lub “odpivotowaniu” danych.
Gdy mam jednak wybór – zawsze używam VBA. Dlaczego? Wyjaśnię to na przykładzie odczytu danych z pliku tekstowego.
Po pierwsze, brak łącz. Importując dane z takiego pliku wolę go zaczytać do Excela zamiast tworzyć kwerendę z połączeniem. Wynika to z faktu, że tworząc narzędzie dla Klienta, pracuję w dwóch środowiskach. Jeżeli Klient zmieni lokalizację pliku, wystarczy tylko, że podmienię wartość stałej w kodzie VBA (zwykle Klient potrafi zrobić to sam). W przypadku Power Query jest to trochę trudniejsze. Trzeba przejść do edycji zapytania i zmienić źródło.
A co z argumentem, że w PQ jest to łatwiejsze do zrobienia? Korzystam z gotowych “cegiełek kodu”, więc edycja takiego makra to kwestia kilkunastu minut..
Po drugie, szybkość. Import danych z kilkuset plików tekstowych trwa tyle co mrugnięcie okiem. Optymalny kod VBA działa błyskawicznie. Szybkość Power Query pozostawia wiele do życzenia, nawet w kontekście porównania z archaicznym (1995r.!) narzędziem Microsoft Query.
Po trzecie, formuły. Wiele raportów pochodzi z różnych systemów informatycznych np. SAP. Takie raporty bardzo często zawierają zbędne wiersze lub inne “śmieci”. Mając dobrze opanowane funkcje Excela, usunięcie tych danych staje się przyjemnością i formalnością. Tworzenie skomplikowanych formuł z poziomu Power Query nie jest takie proste i często wymaga przynajmniej podstawowej znajomości języka M.
