„Power Query to największa rewolucja w Excelu od czasu tabel przestawnych!”. „Power Query zastąpiło VBA!” Takie opinie stają się coraz bardziej popularne… Faktycznie PQ to świetne narzędzie, które pozwala nam pobierać dane z różnych źródeł i przekształcać je w łatwy sposób. Czy PQ jest faktycznie alternatywą dla VBA? Moim zdaniem – zdecydowanie NIE.
Power-ofensywa
Nie da się ukryć, że w ostatnich miesiącach Power Query zyskało mocno na popularności. Na dodatek, coraz głośniej słychać fan(atyk)ów tego narzędzia. W zuchwałym tonie wieszczą upadek VBA, traktując PQ jako 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ę do sprawdzenia linka i 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 z plików Excela czy baz Accessa. Obsługuje ono także inne formaty m.in. XML, JSON czy PDF. To jednak nie wszystko – od niedawna możemy w nim odczytywać tabelki ze zdjęć! Przyznam, że nie wierzyłem, że uda się wdrożyć technologię OCR w Excelu. A jednak! Impossible is nothing?
To jednak tyle komplementów, bo generalnie nie zgadzam się z podejściem wielbicieli Power Query. Oczywiście, w wielu obszarach oba narzędzia się pokrywają – co więcej, PQ jest częścią wielu nietrywialnych aplikacji VBA – ale nieprawdą jest, że PQ zastąpiło VBA w 90% zadań, 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 zwią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 właśnie cel Power Query – pobieranie i transformacja danych.
Skąd się więc biorą te wszystkie porównania do VBA? Otóż w aplikacjach klienckich często wykonujemy podobne zadania. Przeważnie mamy jakieś raporty źródłowe (tzw. „surówka”), które musimy przekształcić, aby wyciągnąć z nich potrzebne informacje. Zwykle robimy to dodając makrem kolumny wyliczeniowe i zamieniając formuły na wartości. Gdy mamy już obrobione dane – budujemy dashboardy lub tworzymy w pętli dedykowane raporty.
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 konkretny problem Klienta od przysłowiowej podszewki.
Kto naprawdę ma MOC?
Skoro już wiemy, że VBA dysponuje większą siłą rażenia – 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ż jakieś źródło, na którym operujemy. W VBA zaczynamy totalnie od zera, od pustego arkusza i czystej kartki papieru. Głośno myślimy i szkicujemy architekturę całej aplikacji – w tym powiązań między tabelami.
W dalszej kolejności projektujemy userformy, których celem jest zawsze pobranie informacji od użytkownika. Aby zadbać o wysoką jakość danych, musimy dodać reguły poprawności – głównie dla zdarzeń pól tekstowych. Czasami wystarczą proste warunki – w przypadkach bardziej złożonych 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 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 skrypty optymalizacyjne, które zostały napisane od podstaw (np. pocięcie prętów 1D). Tego typu rozwiązania sprawdzają się nie tylko w budownictwie, ale też w logistyce czy handlu.
Każda aplikacja VBA posiada swoją warstwę biznesową. Inne wyzwania napotkamy tworząc rozbudowany kalkulator usług logistycznych. Inne gdy będziemy budować automat do rozliczania delegacji dla HR. Jeszcze inne, gdy będziemy pracować nad aplikacją, która usprawnia 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 pobierać dane z plików Microsoft Office, ale na tym koniec. Dzięki VBA uzyskujemy dostęp do modelu obiektowego każdej aplikacji z tego pakietu. Jaka korzyść z tego płynie? Ano, możemy uruchomić taką aplikację i wykonać z jej poziomu konkretne zadania.
Najpopularniejszym z nich 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ć całą 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. 1 000 plików PDF z naszą ofertą. Będą się one różnić tylko danymi odbiorcy, które z kolei zostaną odczytane z tabeli Excela. Całość 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.
Biblioteki zewnętrzne
VBA, podobnie jak Excel, posiada bogatą kolekcję wbudowanych funkcji i rozwiązań. Ale siłą VBA jest dostęp do obiektów zewnętrznych. Możemy w łatwy sposób podpiąć pliki dll (Dynamic-Link Library), aby uzyskać dostęp do właściwości i metod obiektów, których nie ma w Excelu. Potrafią one wynieść aplikacje VBA na całkiem inny poziom.
VBA pozwala m.in. 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 sieci i odczytywać dane ze stron internetowych (Web Scraping).
Podsumowanie
Bardzo lubię Power Query i wiem, że są sytuacje, w których to narzędzie jest niezastąpione – np. przy imporcie tabel z PDF`ów czy przy „odpivotowaniu” danych.
Zawsze jednak porównuję ze sobą różne rozwiązania pod kątem skuteczności i szybkości. Dla Klienta są to dwa najważniejsze kryteria, które wpływają na zadowolenie z użytkowania aplikacji. I tutaj nie jest już tak różowo…
Pojawienie się nowych funkcji w Excelu 365 zmieniło grę! Dzięki nim, możemy wyliczać bardzo skomplikowane rzeczy. Możemy wstawiać do arkusza rozbudowane formuły z poziomu VBA, a następnie zamieniać je na wartości. Przeliczenie takiej formuły dla 500 000 wierszy nie powinno trwać dłużej niż 3-4 sekundy. To bardzo dobra alternatywa dla PQ, które nie radzi sobie tak dobrze z przetwarzaniem dużej liczby wierszy.
Drugą sprawą jest intuicyjność. Budowanie formuł w Excelu jest łatwe i przyjemne. W PQ proste rzeczy możemy sobie wyklikać, ale do tych bardziej skomplikowanych potrzebujemy już znajomości języka M, który jest bardzo nieintuicyjny. Podobna analogia nasuwa mi się przy porównaniu VBA z Office Scripts. Uważam, że łatwość pisania makr zadecyduje o tym, że skrypty Office nigdy nie wyprą VBA – chyba, że Microsoft zmusi do tego swoich użytkowników.