ZAŁOŻENIA i ZAKRES TUTORIALI VBA KLIKNIJ by (roz)winąć

Na niniejszej podstronie postaram się przedstawić wypracowane przeze mnie przepisy na wykonanie własnego programu ewidencyjnego pracującego pod Excelem oraz pliku wzbogaconego makropoleceniami.
W wymienionej literaturze można znaleźć omówienie zasad programowania w języku VBA. Na stronach internetowych znalazłem również bardzo dobre kursy obsługi arkusza kalkulacyjnego oraz pisania i edycji makropoleceń. Ze względu na wysoką, moim zdaniem, wartość merytoryczną wymienionych prac nie zamierzam ich powtarzać. Jednak w trakcie swojej działalności zawodowej nie spotkałem się z ramowymi zaleceniami jak napisać program, który był mi potrzebny. Do metodyki, podobnie jak do wymienionych na podstronie 'programy od kuchni' wniosków dochodziłem więc sam. Chciałbym przedstawić ramowy zakres wykonania programu oraz pliku z makropoleceniami.
Przyjąłem założenie, że czytelnik:
- nieźle zna arkusz kalkulacyjny,
- jest w stanie zapoznać się z wbudowaną funkcją programu (np. 'Licz.jeżeli ()' 'Złącz teksty ()' itp.),
- orientuje się w typach zmiennych VBA, pętlach itp. lub jest gotów informacje te uzupełnić podczas czytania poszczególnych części tutoriala,
- a przede wszystkim - chce napisać program,
Przewiduję osobny tutorial dla pliku oraz osobny dla programu. Jako przykłady wybrałem:
- plik 'Rejestr zakupów'
- program 'Okna ewidencja'
Pierwszy plik wybrałem ze względu na jego dość ciekawy pod względem programowym zakres, obejmujący między innymi: przygotowanie tabel, obsługę zdarzeń arkuszy roboczych, wprowadzenie formantów 'ComboBox' (lista rozwijana) oraz CommandButton (przycisk poleceń), sortowanie zakresu arkusza z poziomu VBA oraz przygotowanie prostych formularzy do wprowadzania zmian.
Drugi przykład tj. program 'Okna ewidencja' wybrałem ze względu na dość klarowny układ programu: z tabeli głównej pobierane są dane do zdefiniowanych tabel wydruków nieruchomości oraz tabeli wejść. Większość funkcji programu można zrealizować w samym Excelu bez wsparcia VBA. Programowanie służy uproszczeniu obsługi i tym samym zwiększeniu wydajności. W ten sposób wszystkie funkcje programu obsługiwane są z przygotowanych wcześniej formularzy. Poza tym jest to mój pierwszy program i mam do niego duży sentyment ;)
Przed rozpoczęciem 'kursu' przydatne jest zapoznanie się z możliwościami przykładów na ich własnych podstronach 'Rejestr zakupów' oraz 'Okna ewidencja'. Oprócz opisu przeznaczenia i możliwości programów zawarte są tam również instrukcje obsługi. Całość materiałów jest również dostępna w zakładce 'Pliki'.
W miarę potrzeby w poszczególnych częściach tutoriali widoczne będą zrzuty ekranów, a na zakończenie plik roboczy (do ściągnięcia).

PODSTAWOWE NARZĘDZIE - MENU I EDYTOR VBA w EXCELU KLIKNIJ by (roz)winąć

Standardowo MS Excel ma włączony wysoki stopień ochrony przed makropoleceniami tzn. przy uruchamianiu pliku z makrami VBA, zablokowane zostaną dodatki programowe bez informowania użytkownika o tym przykrym fakcie ;) Na początek zmienimy poziom zabezpieczeń:
- MS Excel 2003 - Pasek poleceń 'NARZĘDZIA' / MAKRA / ZABEZPIECZENIA / POZIOM ŚREDNI
- MS Excel 2007
Na rysunku widać pasek menu VBA w arkuszu Excela z aktywnym przyciskiem edytora.
Teraz uruchomimy edytor VBA:
- MS Excel 2003 - Pasek poleceń 'NARZĘDZIA' / DOSTOSUJ / PASKI NARZĘDZI / VISUAL BASIC
lub prościej - PPM na menu głównym programu, zaznaczamy pasek menu VBA i gotowe.
- MS Excel 2007 - Karta 'Developer', grupa 'Kod',
Jeśli karta 'Developer' nie jest dostępna to użyj: 'PRZYCISK MICROSOFT OFFICE' / OPCJE PROGRAMU EXCEL / POPULARNE / NAJCZĘŚCIEJ UŻYWANE OPCJE W PRACY Z PROGRAMEM EXCEL / POLE WYBORU 'POKAŻ KARTĘ DEVELOPER NA WSTĄŻCE'

Edytor Visual Basica ma w zasadzie identyczny wygląd dla w/w wersji Excela. Poszczególne jego elementy będę omawiał wraz z ich wprowadzaniem.
Na początek uruchomimy te najpotrzebniejsze:
- menu VIEW / CODE [klawisz F7] - zakładka w której będziemy wprowadzać polecenia poszczególnych procedur,
- menu VIEW / PROJECT EXPLORER [klawisz Ctrl + R] - podgląd składników pliku Excela z poziomu edytora VBA,
- menu VIEW / PROPERTIES WINDOW [klawisz F4] - podgląd właściwości poszczególnych składników pliku, formularzy itp.,
Od razu, żeby nie umknęło, wprowadzamy polecenie wymuszenia deklaracji zmiennej: okno CODE w zakładce [General] / [Declarations] wpisujemy 'Option Explicit' - wymuszenie deklaracji zmiennej. Uzasadnienie patrz tutaj,
Warto jeszcze zajrzeć do menu 'Insert' - to w nim, już niedługo, wstawimy swój pierwszy formularz i pierwszy moduł. Na tym etapie powinno być widoczne okno jak na załączonym rysunku.
Po powrocie do arkusza Excela z menu VBA można jeszcze uruchomić przybornik formantów. Identyczną ikoną z górnego paska można uruchomić tryb projektowania na poziomie edytora. Z poziomu arkusza Excela warto jeszcze uruchomić pasek narzędzi 'Formularze'.
Teraz mamy arkusz Excela przygotowany do pracy z wykorzystaniem VBA. Dalsza cześć tutoriali podzielona jest na część dotyczącą pliku 'Rejestr zakupów' oraz programu 'Okna ewidencja'. Czytać je można w dowolnej kolejności. Zakres tematyczny obydwu przykładów w swoich istotnych częściach nie pokrywa się.

Uwaga ogólna

Przypominam, że przed rozpoczęciem 'kursu' przydatne jest zapoznanie się z założeniami wykonania i możliwościami przykładów na ich własnych podstronach 'Rejestr zakupów' oraz 'Okna ewidencja'. Oprócz opisu przeznaczenia i możliwości programów zawarte są tam również instrukcje obsługi. Całość materiałów jest również dostępna w zakładce 'Pliki'.

Spis lekcji - Rejestr zakupów

Spis lekcji - Okna Ewidencja

Do góry podstrony Do strony głównej

Rejestr zakupów 1

Rysujemy i opisujemy tabelę w pierwszym arkuszu. Szerokości kolumn i wysokości wierszy dobieramy doświadczalnie tak, by po prostu dobrze wyglądały na wydruku. Tworzona tabela powinna zawierać wiekszą ilość wierszy niż jest możliwa do wykorzystania (ułatwi to późniejszy wydruk). W pierwszej tabeli jedyne formuły to sumy w wierszach 104 i 106, kolumnie Q oraz iloczyn w komórce M105. Co ważne nie stosujemy komórek zespolonych - stwarzają problemy przy operacjach z poziomu VBA. Zmieniamy nazwę arkusza roboczego na nazwę kolejnego miesiąca i kopiujemy dla całego roku.
Tworzymy jeszcze dwa arkusze o nazwach "Wydruk" oraz "Wydruk " (druga nazwa ze spacją), gdzie umieszczamy dwie identyczne jak w arkuszach miesięcznych tabele. Obydwa arkusze będą służyć do wydruku. W pierwszym będą usuwane wiersze, a drugi zachowa zawsze czystą, nie naruszoną tabelę do skopiowania. Dla arkusza "Wydruk" ustawiamy obszar wydruku, zmniejszamy marginesy, zmieniamy układ strony na poziomy, centrujemy itd. Warto ustawić powtarzanie na wydruku górnych wierszy (od 1 do 5) i niestandardową stopkę. Ponieważ dwie ostatnie funkcje nie są często używane przypomnę, że:
- MENU PLIK / USTAWIENIA STRONY / ZAKŁADKI: NAGŁÓWEK/STOPKA I ARKUSZ
przy czym jeżeli to samo menu uruchomimy z poziomu podglądu wydruku to zakładka ARKUSZ będzie nieaktywna (!) O powodach, dla których na tym etapie ustawiamy wszystkie opcje wydruku można poczytać tutaj.
Plik kończący pierwszą lekcję w postaci archiwum samorozpakowującego można ściągnąć stąd (90,2kB).

Do góry podstrony Do spisu treści Do strony głównej

Rejestr zakupów 2

Jednym z podstawowych zadań pliku jest automatyzacja wprowadzania nazw kontrahentów. Ze względu na ten cel stworzymy jeszcze jeden, ściśle roboczy, arkusz o nazwie 'Firmy'. W poszczególnych kolumnach osobno wprowadzamy: nazwę firmy, ulicę i miasto będące jej siedzibą oraz numer NIP. Ze względu na fakt, że w planowanej liście rozwijanej musimy mieć podgląd pełnej nazwy firma łącznie z adresem, to w dodatkowej kolumnie wprowadzamy funkcją 'Złacz.tekst' połączenie wcześniejszych kolumn.
Uwaga: arkusz ten będzie w dalszym etapie rozszerzany, ponieważ jak się okaże jego zakres jest niewystarczający.
W przykładowym pliku (93,4kB) wprowadziłem tylko kilka firm.
Na tym etapie prac możemy już przystąpić do definiowania pierwszych procedur VBA. W następnej lekcji zmniejszymy widoczność arkuszy roboczych (aktualnie jest ich już piętnaście), skojarzymy to działanie z obsługą zdarzeń dla arkuszy oraz zadbamy by przy otwieraniu naszego programu Excel reagując na klawisz 'Enter' aktywował komórki w poziomie, a nie w pionie (ale tylko dla tego pliku).

Do góry podstrony Do spisu treści Do strony głównej

Okna ewidencja 1

Program ma przede wszystkim pozwolić na szybkie określenie jakie okna są w danym lokalu mieszkalnym, jakie ewentualne działania zostały na nich wykonane oraz z jaką datą. Główną tabelą programu będzie więc prosta dwuwymiarowa tablica dla której wyróżnikiem wyszukiwania będzie adres lokalu. W kolejnych kolumnach określimy rodzaje zestawów okiennych oraz każdemu z nich przyporządkujemy datę. Ilość kolumn będzie równa podwojonej ilości niepowtarzalnych zestawów okiennych np. dla okien o symbolu O-39 przewidziano sześć kolumn.
Wyjaśnienie: Okna tego typu mogą występować po stronie wschodniej lub zachodniej lokalu oraz w kuchni mieszkań M-2, co daje trzy możliwości, a ponadto do każdej z nich należy przyporządkować datę. Podanie tylko symbolu okna nie określa w sposób jednoznaczny jego położenia w lokalu. W przypadku zestawu balkonowego wystarczy jedna kolumna (oczywiście dodatkowo druga z datą) ze względu na fakt, że w każdym z lokali osiedla występuje tylko jeden balkon danego typu (w lokalach dwubalkonowych obydwa zestawy różnią sie wymiarami, a tym samym symbolem).
W ten sposób powstanie tabela, w której do każdego lokalu przyporządkowano kilkanaście kolumn z symbolami okien wraz z kolumnami dat. Teraz wystarczy w wierszu każdego lokalu zaznaczyć powtarzalnym symbolem (np. 'X') zestawy które w tym mieszkaniu nie występują. Tak konstruowana tabela bedzie najważniejszym elementem programu. Z niej pobierane bedą dane do wszystkich pozostałych tabel i wydruków.
W przykładowym pliku (118kB) można zapoznać się z układem tabeli głównej. W przykładzie uwzględnione zostały tylko wejścia 1-13 tj. dwie nieruchomości.

Do góry podstrony Do spisu treści Do strony głównej

Okna ewidencja 2

Wprowadzamy trzy kolejne arkusze: "zbiorcze_N1", "zbiorcze_N2" oraz "Cennik". W pierwszych dwóch arkuszach odpowiednio dla pierwszej (wejścia 11,12,13) i drugiej (wejścia od 1 do 10) nieruchomości opracowujemy tabele, które jednocześnie bedą podstawą wydruku uproszczonego (tabela mniejsza) i wydruku pełnego (tabela większa). Wykorzystywane są formuły: 'Licz.jeżeli' oraz 'Licz.puste'. Jako zakres formuł podajemy odpowiednie kolumny z tabeli głównej.
Formuła w postaci: 'LICZ.JEŻELI(zakres,"<>X")' podliczy ze wskazanego zakresu (odpowiednia ilość wierszy w kolumnie) wszystkie pozycje, w których nie ma wpisanego symbolu 'X' - czyli wszystkie istniejące okna, niezależnie od tego czy zostały zrealizowane czy też nie. Suma takich formuł dla wszystkich kolumn (w przypadku okien przetargowych z odpowiednim współczynnikiem - wyjaśnienie w instrukcji oraz na wydrukach) da nam ilość wszystkich okien w nieruchomości. Oczywiście suma wszystkich kolumn jednego rodzaju okien da nam ilość stolarki o tym symbolu w nieruchomości. Dla przypomnienia: symbol 'X' wstawiony został w tych wszystkich miejscach, gdzie okna danego rodzaju na adresie nie występowały.
Formuła w postaci: 'LICZ.PUSTE(zakres)' podliczy z danego zakresu wszystkie puste komórki. Jeżeli podamy ten sam zakres, co w przypadku formuł 'LICZ.JEŻELI', a wynik podamy jako różnicę obydwu wartości to uzyskamy ilość wszystkich okien z zadanego zakresu, które istnieją (brak 'X'), a jednocześnie nie są zrealizowane (brak jakiegokolwiek symbolu w komórce).
Formuły obliczające procenty oraz odpowiednie iloczyny ilości i kosztów okien pozwalają przygotować tabelę zawierającą wszystki niezbędne do planowania dane w odniesieniu do nieruchomości. Pracę uzupełniamy o przygotowanie wydruku - analogicznie do lekcji 1 rejestru zakupów. W przykładowym pliku (131kB) można zapoznać sie z układem omawianych tabel.
Do pełni szczęścia brakuje jeszcze danych dla wejść i te uzyskamy w następnej lekcji.

Do góry podstrony Do spisu treści Do strony głównej

Okna ewidencja 3

Na poprzedniej lekcji zastosowaliśmy formułę 'LICZ.JEŻELI' dla zadanego zakresu i kryteriów, by uzyskać ilość okien w nieruchomościach tj. grupach wejść od 1-10 (nieruchomość 2) i od 11 do 13 (nieruchomość 1). Jeżeli było możliwe uzyskanie takich wartości to - stosując tą samą formułę - możemy również uzyskać ilości poszczególnych typów okien dla pojedynczych wejść. W ten sposób uzyskane zostały wartości w komórkach AE46-AS97 nowego arkusza 'Wydruk_wg_wejść'. Sumując poszczególne kolumny uzyskamy wartości dla zakresu wejść od 1 do 13. Celem naszym jest jednak uzyskanie wartości tylko dla wybranych wejść z tego zakresu. Możemy je uzyskać jeżeli każdą wartość z zakresu AE46-AS97 pomnożymy przez 0 lub przez 1 w zależności od tego czy dane wejście nas interesuje, czy też nie. W ten sposób uzyskaliśmy wartości w obszarze BF46-BT97. Wartości współczynników 0 lub 1 wynikają z podliczenia jednokomórkowych zakresów. Zakresy zawarte są w tabeli B40-Y74, w której w opisanych numerami wejść komórkach, możemy postawić znak 'X' lub pozostawić puste pole. Wartości z tej tabeli roboczej przepisywane są do identycznej w układzie tabeli B3-Y37. Tabela ta będzie celem wydruku. Tak więc pozostaje jedynie w odpowiednich komórkach postawić 'X' i arkusze same podliczą ilości okien poszczególnych typów tylko dla zaznaczonych wejść. W ten sposób zrealizowane zostało zamierzenie, by program podliczał ilości okien poszczególnych typów dla dowolnej kombinacji wejść.
Uwaga: Jest bardzo ważne, by arkusze dla nieruchomości i wejść były od siebie niezależne tzn. wartości formuł w nich zawartych były pobierane bezpośrednio z arkusza 'Główny'. W ten sposób pod koniec prac będziemy mogli sprawdzić, czy nie mamy błędów w formułach - dla tego samego zakresu wejść program powinien podać te same wartości w każdej pozycji obydwu arkuszy. Możliwość, że dwukrotnie popełnimy ten sam błąd (dla obydwu arkuszy) jest niewielka.
Na tym etapie widzimy, że podstawowe funkcje programu są spełnione:
- mamy dostęp do poszczególnych adresów,
- mamy dostęp do podsumowań dla nieruchomości,
- mamy dostęp do podsumowań dla dowolnej kombinacji wejść.
Program jednak jest bardzo uciążliwy w obsłudze. Dalsze prace będą zmierzały do nadania mu wygodniejszej i bardziej starannej formy z wykorzystaniem procedur VBA.
Załączam plik (151kB) zawierający omawiany w tej lekcji materiał.

Do góry podstrony Do spisu treści Do strony głównej

Okna ewidencja 4

Po ciężkiej pracy związanej z ostatnią lekcją teraz coś nieco lżejszego - rysowanie menu. Tworzymy dwa nowe arkusze: 'OKNA EWIDENCJA' i 'OKNA EWIDENCJA ' (drugi arkusz ma tą samą nazwę tylko ze spacją na końcu). Dwa ponieważ w dalszym toku prac przygotujemy zmianę rozdzielczości.
W arkuszu rysujemy korzystając z paska narzędzi 'Rysowanie'. W swoim menu wykorzystałem linie, pola tekstowe, wstawiony plik graficzny, prostokąty wypełnione kolorem itp. Elementy każdego z 'przycisków' należy pogrupować, ponieważ to do nich będą przypisywane makropolecenia. Jeżeli wszystkie elementy stanowiące przycisk nie będą dokładnie pogrupowane użytkownik będzie zmuszony 'celować' w odpowiedni zakres arkusza. Narysowane przeze mnie menu widać na stronie głównej i podstronie programu, ale załączam również plik (181kB) zawierający omawiany w tej lekcji materiał.
Ponieważ ćwiczenie było nieco lżejsze polecam zapoznać się z uwagami wstępnymi z podstrony 'programy od kuchni' - być może inny rodzaj menu będzie lepiej spełniał oczekiwania czytelnika.
W następnej lekcji utworzymy pierwszy formularz wyszukujący zadany adres i wyświetlający znalezione wartości ;)

Do góry podstrony Do spisu treści Do strony głównej