Jak nie zostać małpą w pracy
VBA w pakiecie Office
Czy nie zdarzyło się Państwu pracować jak przysłowiowa małpka? Przygotowując dzienny raport, do którego jest dziesięć stron instrukcji. Albo ręcznie rozsyłając dane do regionalnych sprzedawców. Często w takich pracach 95 proc. nakładu pracy nie jest w żaden sposób związana z „merytoryką”, tylko z banalną, powtarzalną „klikologią”.
Typowy scenariusz. Pani Ania codziennie przygotowuje raport sprzedaży. Aby go wykonać musi ściągnąć dane z systemu A, potem z systemu B, resztę danych podlinkować z plików tekstowych C. Niestety formaty dat nie zgadzają się, zatem parę razy trzeba użyć CTRL+H, potem jeszcze zamiana kropek na przecinki, aby się sumowało, wycięcie wierszy nagłówkowych, dostawienie paru kolumn, podpięcie słowników, skasowanie błędów w komórkach bez danych i za godzinę może będzie gotowe do wysyłki. Brzmi znajomo? Niestety tak wygląda rzeczywistość wielu działów polskich firm.
Drugim problemem, który występuje równie często jest wydajność. Nie mam na myśli sprzętu, ale nasze ludzkie ograniczenia. Kiedy pojawiają się kolejne raporty do opanowania, nowe punkty sprzedaży, kolejne grupy produktów do analizy, obróbka danych bez automatyzacji może okazać się niemożliwa do wykonania w zadanym czasie.
Jak możemy sobie pomóc?
Rozwiązaniem może być VBA, czyli Visual Basic for Applications. VBA nie jest wynalazkiem sezonu 2011, ani też żadną kosmiczną technologią. To po prostu rozszerzenie pakietu Office o możliwość programowania, które Nowoczesne Technologie dostępne było już w wersji 2000. VBA jest uproszczoną wersją języka Visual Basic, stosowaną do automatyzacji dokumentów pakietu Office.
Co można automatyzować?
Tak naprawdę możemy automatyzować każdą czynność składającą się z sekwencji kilku kroków. Choćby formatowanie tabel, zamianę symboli, formatów, scalanie i transformację danych. Do tak prostych automatyzacji nie musimy nawet zaglądać w kod. Po prostu można skorzystać z rejestratora makr, który nagra każdy nasz krok, a potem odtworzy na żądanie.
Do bardziej złożonych zadań, logikę trzeba oprogramować w języku VBA. Opanowanie podstaw składni języka zajmuje zwykle dwa dni szkoleniowe, po kolejnych dwóch dniach ćwiczeń można rozwiązać typowe problemy z uzgadnianiem list, obróbką danych z plików tekstowych, scalaniem, konsolidacją. VBA pozwala także na pisanie własnych funkcji użytkownika. Zamiast formułek, które nierzadko zajmują pięć linijek i mają na początku osiem nawiasów, można napisać swoją funkcję, której wywołanie będzie dziecinnie proste, a cała logika pozostanie po stronie VBA. Przyjrzyjmy się zatem bliżej kilku scenariuszom.
Przykład 1 – Niepozorny metr sześcienny. Jeden z moich kursantów w firmie CTS zgłosił kiedyś taki oto problem. Dane numeryczne, które wprowadzał w Excelu były opatrzone jednostką,m.in. symbolem m3, np. 137 m3. Aby wykonać to ręcznie trzeba zaznaczyć „3”, przejść do odpowiedniego menu, wybrać opcję czcionki i tam poszukać opcji indeks górny. W sumie nic skomplikowanego, prawda? Ale jeśli takich danych jest do wprowadzenia co najmniej
30 dzienne, to już wieje nudą.
W ramach rozwiązania zaproponowałem makro, które uruchamia się automatycznie po zatwierdzeniu danejkomórki. Czyli wprowadzamy dane i po prostu przechodzimy dalej, w tym momencie makro uruchamia się samoistnie i przeszukuje zawartość komórki na obecność symbolu m3 – jeśli go znajdzie zamienia 3 na 3 w indeksie górnym. Kod tego makra to nie więcej jak 15 linijek, a o problemie właściwego formatu można zapomnieć – całość działa w pełni automatycznie bez ingerencji użytkownika.
Przykład 2 – Raportowanie kosztów telefonii komórkowej. Kolejny problem, z jakim spotkałem się w firmie CTS, był związany z raportowaniem kosztów telefonii komórkowej dla grupy ok. 50 pracowników. Każda osoba mogła posiadać kilka kart SIM, a całkowity rachunek był złożony z 20-25 składowych (abonament, roaming, GPRS, MMS, SMS, połączenia, pakiety biznesowe, itd.). Dane były importowane do arkuszy excela, każdy miesiąc w nowym arkuszu. Niestety operator GSM regularnie zmieniał układ kolumn w taki sposób, że prosta konsolidacja nie wchodziła w grę. Jak w takim gąszczu sprawdzić, ile kosztowały połączenia roamingowe danego pracownika w ostatnim kwartale?
W ramach rozwiązania zaproponowałem dość rozbudową funkcję użytkownika, która po podaniu nazwiska i rodzaju kosztu liczyła sumaryczny koszt pracownika, przeszukując wszystkie arkusze, wszystkie kolumny i wszystkie wiersze, tak, aby uwzględnić posiadane karty SIM.
Raport kosztów, który ręcznie przygotowywany był kilka godzin, można było wywołać dla dowolnej liczby miesięcy, pracowników i grup kosztów, a czas przygotowania zamykałby się w kilku sekundach.
Przykład 3 – Szablon umów w MS Word. Kolejne rozwiązanie dotyczy Worda. Problemem w pewnej Kancelarii okazała się nie tyle monotonia prac, co wydajność. Duża liczba dokumentów o objętości 100-150 stron, bardzo napięteterminy i wewnętrzny standard, który powinien być przestrzegany. Do tego dokumenty przygotowywane były przez prawników, którzy koncentrowali
się tylko na treści, a żmudne formatowanie zostawiali sekretarkom. Założenie zapewne słuszne, ale efekt był taki, że Panie sekretarki nie były w stanie sformatować chaotycznie pisanych dokumentów w czasie, w jakim życzyliby sobie tego prawnicy.
Wspólnie z klientem ustaliliśmy, że rozwiązaniem są szablony Ms Word przygotowane tak, aby formatowanie możliwe było tylko w określonym zakresie. Zdefiniowany własny pasek narzędziowy z opcjami pozwalającymi na proste formatowanie tekstu, list, tabel, wstawianie obrazów z automatycznym formatowaniem, wstawianie całych sekcji CV, zmiana koloru w całym dokumencie i wiele innych. Szablon pouruchomieniu blokował w Wordzie wszystko co zakazane. Zamiast wielu standardowych pasków narzędzi, wyświetlał się jeden rozbudowany pasek ze wszystkimi opcjami potrzebnymi do przygotowania umów wg wewnętrznych wytycznych firmy. Style do tekstu podstawowego, cytatów, nagłówków, gotowe listy, sformatowane tabele, menu do wstawiania sekcji CV.
Z tak opracowanym szablonem przygotowanie umów stało się dużo efektywniejsze. Po pierwsze, szablon zawiera łtypowe elementy umowy, które co najwyżej wymagały aktualizacji. Po drugie, zaprojektowano pasek narzędziowy, który zawierał tylko akceptowane w firmie style formatowania. A VBA umożliwiło takie opcje, jak: automatyczne wstawianie obrazów i ich formatowanie, zmiana koloru w całym dokumencie, formatowanie tabel jednym kliknięciem czy wstawianie całych sekcji powtarzalnego tekstu. Trzymanie firmowego standardu przestało być koszmarem, a prawnicy z czystym sumieniem mogli skoncentrować się na zawartości merytorycznej.
Podsumowanie
Powyżej przedstawiłem trzy przykłady użycia VBA do automatyzacji pakietu Office. Jak widać automatyzacja może być realizowana na bardzo zróżnicowanym poziomie. Można zacząć od kreatora i prostych makr automatyzujących powtarzalne kroki, które wykonujemy wielokrotnie w codziennej pracy. Można też zbudować niebanalną logikę obsługującą zaawansowaną konsolidację danych z operacjami na plikach czy mailingiem włącznie.
Czy VBA to recepta na wszystko? Na pewno nie, ale jeśli w naszej pracy wyciskamy z Office’a siódme poty, to z VBA możemy wycisnąć jeszcze więcej. Nie dajmy się zamienić w małpy – większość zadań można automatyzować.
Autor – Trener w firmie CTS, wykładowca WSZiM w Sochaczewie, specjalista w zakresie baz danych programowania, a także tworzenia dynamicznych witryn internetowych i projektowania aplikacji komputerowych
CTS – Centrum Technik Sieciowych
cts@cts.com.pl
www.cts.com.pl
tel. 22 838-19-08
22 838-52-70
Drugim problemem, który występuje równie często jest wydajność. Nie mam na myśli sprzętu, ale nasze ludzkie ograniczenia. Kiedy pojawiają się kolejne raporty do opanowania, nowe punkty sprzedaży, kolejne grupy produktów do analizy, obróbka danych bez automatyzacji może okazać się niemożliwa do wykonania w zadanym czasie.
Jak możemy sobie pomóc?
Rozwiązaniem może być VBA, czyli Visual Basic for Applications. VBA nie jest wynalazkiem sezonu 2011, ani też żadną kosmiczną technologią. To po prostu rozszerzenie pakietu Office o możliwość programowania, które Nowoczesne Technologie dostępne było już w wersji 2000. VBA jest uproszczoną wersją języka Visual Basic, stosowaną do automatyzacji dokumentów pakietu Office.
Co można automatyzować?
Tak naprawdę możemy automatyzować każdą czynność składającą się z sekwencji kilku kroków. Choćby formatowanie tabel, zamianę symboli, formatów, scalanie i transformację danych. Do tak prostych automatyzacji nie musimy nawet zaglądać w kod. Po prostu można skorzystać z rejestratora makr, który nagra każdy nasz krok, a potem odtworzy na żądanie.
Do bardziej złożonych zadań, logikę trzeba oprogramować w języku VBA. Opanowanie podstaw składni języka zajmuje zwykle dwa dni szkoleniowe, po kolejnych dwóch dniach ćwiczeń można rozwiązać typowe problemy z uzgadnianiem list, obróbką danych z plików tekstowych, scalaniem, konsolidacją. VBA pozwala także na pisanie własnych funkcji użytkownika. Zamiast formułek, które nierzadko zajmują pięć linijek i mają na początku osiem nawiasów, można napisać swoją funkcję, której wywołanie będzie dziecinnie proste, a cała logika pozostanie po stronie VBA. Przyjrzyjmy się zatem bliżej kilku scenariuszom.
Przykład 1 – Niepozorny metr sześcienny. Jeden z moich kursantów w firmie CTS zgłosił kiedyś taki oto problem. Dane numeryczne, które wprowadzał w Excelu były opatrzone jednostką,m.in. symbolem m3, np. 137 m3. Aby wykonać to ręcznie trzeba zaznaczyć „3”, przejść do odpowiedniego menu, wybrać opcję czcionki i tam poszukać opcji indeks górny. W sumie nic skomplikowanego, prawda? Ale jeśli takich danych jest do wprowadzenia co najmniej
30 dzienne, to już wieje nudą.
W ramach rozwiązania zaproponowałem makro, które uruchamia się automatycznie po zatwierdzeniu danejkomórki. Czyli wprowadzamy dane i po prostu przechodzimy dalej, w tym momencie makro uruchamia się samoistnie i przeszukuje zawartość komórki na obecność symbolu m3 – jeśli go znajdzie zamienia 3 na 3 w indeksie górnym. Kod tego makra to nie więcej jak 15 linijek, a o problemie właściwego formatu można zapomnieć – całość działa w pełni automatycznie bez ingerencji użytkownika.
Przykład 2 – Raportowanie kosztów telefonii komórkowej. Kolejny problem, z jakim spotkałem się w firmie CTS, był związany z raportowaniem kosztów telefonii komórkowej dla grupy ok. 50 pracowników. Każda osoba mogła posiadać kilka kart SIM, a całkowity rachunek był złożony z 20-25 składowych (abonament, roaming, GPRS, MMS, SMS, połączenia, pakiety biznesowe, itd.). Dane były importowane do arkuszy excela, każdy miesiąc w nowym arkuszu. Niestety operator GSM regularnie zmieniał układ kolumn w taki sposób, że prosta konsolidacja nie wchodziła w grę. Jak w takim gąszczu sprawdzić, ile kosztowały połączenia roamingowe danego pracownika w ostatnim kwartale?
W ramach rozwiązania zaproponowałem dość rozbudową funkcję użytkownika, która po podaniu nazwiska i rodzaju kosztu liczyła sumaryczny koszt pracownika, przeszukując wszystkie arkusze, wszystkie kolumny i wszystkie wiersze, tak, aby uwzględnić posiadane karty SIM.
Raport kosztów, który ręcznie przygotowywany był kilka godzin, można było wywołać dla dowolnej liczby miesięcy, pracowników i grup kosztów, a czas przygotowania zamykałby się w kilku sekundach.
Przykład 3 – Szablon umów w MS Word. Kolejne rozwiązanie dotyczy Worda. Problemem w pewnej Kancelarii okazała się nie tyle monotonia prac, co wydajność. Duża liczba dokumentów o objętości 100-150 stron, bardzo napięteterminy i wewnętrzny standard, który powinien być przestrzegany. Do tego dokumenty przygotowywane były przez prawników, którzy koncentrowali
się tylko na treści, a żmudne formatowanie zostawiali sekretarkom. Założenie zapewne słuszne, ale efekt był taki, że Panie sekretarki nie były w stanie sformatować chaotycznie pisanych dokumentów w czasie, w jakim życzyliby sobie tego prawnicy.
Wspólnie z klientem ustaliliśmy, że rozwiązaniem są szablony Ms Word przygotowane tak, aby formatowanie możliwe było tylko w określonym zakresie. Zdefiniowany własny pasek narzędziowy z opcjami pozwalającymi na proste formatowanie tekstu, list, tabel, wstawianie obrazów z automatycznym formatowaniem, wstawianie całych sekcji CV, zmiana koloru w całym dokumencie i wiele innych. Szablon pouruchomieniu blokował w Wordzie wszystko co zakazane. Zamiast wielu standardowych pasków narzędzi, wyświetlał się jeden rozbudowany pasek ze wszystkimi opcjami potrzebnymi do przygotowania umów wg wewnętrznych wytycznych firmy. Style do tekstu podstawowego, cytatów, nagłówków, gotowe listy, sformatowane tabele, menu do wstawiania sekcji CV.
Z tak opracowanym szablonem przygotowanie umów stało się dużo efektywniejsze. Po pierwsze, szablon zawiera łtypowe elementy umowy, które co najwyżej wymagały aktualizacji. Po drugie, zaprojektowano pasek narzędziowy, który zawierał tylko akceptowane w firmie style formatowania. A VBA umożliwiło takie opcje, jak: automatyczne wstawianie obrazów i ich formatowanie, zmiana koloru w całym dokumencie, formatowanie tabel jednym kliknięciem czy wstawianie całych sekcji powtarzalnego tekstu. Trzymanie firmowego standardu przestało być koszmarem, a prawnicy z czystym sumieniem mogli skoncentrować się na zawartości merytorycznej.
Podsumowanie
Powyżej przedstawiłem trzy przykłady użycia VBA do automatyzacji pakietu Office. Jak widać automatyzacja może być realizowana na bardzo zróżnicowanym poziomie. Można zacząć od kreatora i prostych makr automatyzujących powtarzalne kroki, które wykonujemy wielokrotnie w codziennej pracy. Można też zbudować niebanalną logikę obsługującą zaawansowaną konsolidację danych z operacjami na plikach czy mailingiem włącznie.
Czy VBA to recepta na wszystko? Na pewno nie, ale jeśli w naszej pracy wyciskamy z Office’a siódme poty, to z VBA możemy wycisnąć jeszcze więcej. Nie dajmy się zamienić w małpy – większość zadań można automatyzować.
Autor – Trener w firmie CTS, wykładowca WSZiM w Sochaczewie, specjalista w zakresie baz danych programowania, a także tworzenia dynamicznych witryn internetowych i projektowania aplikacji komputerowychCTS – Centrum Technik Sieciowych
cts@cts.com.pl
www.cts.com.pl
tel. 22 838-19-08
22 838-52-70
|
nr 9(113)2011  |
|
