Metodyka 4TG - Visual Basic for Excel

 WPROWADZENIE

Celem metodyki 4TG jest budowa rozwiązań o cechach: bezpieczeństwo, czytelność i elastyczność. Dlatego, do budowy rozwiązań, wykorzystuje się tylko te elementy MS Excel oraz Visual Basic, które w prosty sposób realizują typowe funkcjonalności, jednocześnie oznacza to wykorzystanie jak najmniejszej liczby elementów Visual Basic for Excel (dotyczy to również MS Excel), ale tak, aby można było zrealizować wszystkie potrzebne funkcjonalności. Powodem takiego postępowania jest fakt, że kod napisany w Visual Basic czyta się i analizuje wiele razy, dlatego należy napisać go tak, aby łatwo było interpretować. Dzięki temu jest nie tylko czytelniej, ale i szybciej, łatwiej oraz bezpieczniej.

Metodyka 4TG zawiera też schematy postępowania i rozwiązania standardowe typowych problemów występujących w organizacjach gospodarczych. Oznacza to, że rozwiązania rachunkowości, zarządzania oraz informatyczne zostały zaimplementowane w MS Excel w postaci rozwiązań standardowych.
Schematy postępowania określają sposób projektowania i budowy oraz możliwości modyfikacji rozwiązań w zależności od zidentyfikowanych potrzeb. Rozwiązania standardowe są zrealizowane za pomocą dwóch narzędzi: MS Excel (łącznie ze wzorami) oraz Visual Basic for Excel.
Jedną z zasad metodyki 4TG jest: wszystko co można, należy zrealizować za pomocą wzorów w MS Excel; Visual Basic for Excel należy użyć wtedy, kiedy nie da się rozwiązać danego problemu za pomocą wzorów (na przykład transmisja), albo wtedy, kiedy to, co zostało zbudowane za pomocą wzorów działa zbyt długo.
Ważna jest czytelność rozwiązania - wzory zdecydowanie łatwiej zrozumieć niż kod w Visual Basic.

Wybrane standardowe rozwiązania oraz sposoby postępowania znajdują się też w książce "Metodyka 4TG na tle metodologii projektowania systemów informatycznych”.

 WARSZTATY: METODYKA 4TG - VISUAL BASIC FOR EXCEL

Dwa poziomy warsztatów Visual Basic for Excel:

* Na pierwszym: Metodyka 4TG - Visual Basic (część 1)
(wcześniej: Visual Basic for Excel, warsztaty dla tych, którzy chcą się nauczyć programować w VB)

uczymy:
- istotnych elementów języka (w tym: elementy potrzebne do operacji na komórkach, arkuszach i skoroszytach)
- projektowania rozwiązań (ZOB. PRZYKŁAD PONIŻEJ)
- sposobów analizy problemu w celu zapisania ich w kodzie języka Visual Basic for Excel,
- operacji na zdarzeniach wykorzystując praktyczne zastosowanie form użytkownika – przedostatni dzień zajęć
- operacji na plikach tekstowych (odczyt, interpretacja wartości, zapis) – ostatni dzień zajęć

wymagamy od uczestników szkolenia:
- posługiwania się MS Excel,
- umiejętności budowy strumienia informacji za pomocą wzorów,
- doświadczenia (przynajmniej niewielkiego) przy tworzeniu rozwiązań.

Pobierz zgłoszenie Pobierz szczegółową ofertę



* Na drugim: Metodyka 4TG - Visual Basic (część 2)
(wcześniej: Intelligence Programming Visual Basic for Excel)

uczymy:
- budowy rozwiązań, które wspomagają różne typowe procesy w firmie (na przykład: wspomagają zarządzania plikami, odczytują/zapisują informacje do plików w różnej formie, wspomagają pracę MS Query, realizują symulację, …)

wymagamy od uczestników szkolenia:
- praktyki w używaniu Visual Basic for Excel + wskazane uczestnictwo w części pierwszej

Pobierz zgłoszenie Pobierz szczegółową ofertę

 PRZYKŁAD

PROJEKTOWANIE I BUDOWA ROZWIĄZANIA ZREALIZOWANEGO ZA POMOCĄ VISUAL BASIC FOR EXCEL

  Słowo wstępne

Rejestr jest najczęściej używaną strukturą danych…, szczegółowe zasady budowy i użytkowania rejestru również zostały opisane w wymienionej wcześniej książce: Metodyka 4TG..., jak też są prezentowane na zajęciach z Projektowania modeli w MS Excel wg metodyki 4TG
… jedną z czynności przy użytkowaniu rejestru jest dopisanie nowej pozycji. Często, przy takiej operacji, należy zrealizować funkcję kontroli danych.
Jednym z narzędzi realizujących prewencję (niedopuszczenie do wprowadzania niewłaściwych danych) jest wybór z listy. Nie zawsze można zastosować taki sposób. Czasem trzeba sprawdzić możliwość rejestracji nowej pozycji za pomocą wzorów.
To znaczy, że projektując arkusz do wprowadzania danych należy zastosować listy wyboru tam, gdzie jest to możliwe, a następnie, za pomocą wzorów, sprawdzić związki między wprowadzonymi danymi. Wynik sprawdzania uzyskany w jednej komórce (PRAWDA/FAŁSZ) ma wpływ na rejestrację nowej pozycji w arkuszu - rejestrze. W niniejszym przykładzie jest to komórka H6 (zobacz Rysunek nr 2).

Rysunek nr 1. Rejestracja (w arkuszu REJESTR) danych wprowadzonych w arkuszu: ArkuszWE.

  Definicja problemu

Problem: Budowa narzędzia do rejestrowania danych w arkuszu: REJESTR.

Przykład, który ilustruje problem, to rejestracja danych w Excelu dotycząca operacji na koncie bankowym.
Do komórki C2 wprowadzana jest data, a do komórki C3 (lub C4) kwota wpłaty (lub wypłaty). Po wprowadzeniu danych wynik wzoru w komóce H6 jest równy PRAWDA, co oznacza, że możliwa jest rejestracja danych, ponieważ dane są poprawnie wprowadzone (zob. Rysunek nr 2).

Na Rysunku nr 2 zostały zamieszczone wzory, które kontrolują poprawność wprowadzenia danych (komórki kolumn E:H). Wzory w ramach kolumn E:H są takie same (z wyjątkiem zawartości komórki H6, komórka E2 jest pusta). Wynik wzoru w komórce H6 jest sprawdzeniem, czy wszystkie dane są poprawnie wpisane.

Na rysunku nr 2 zostało również przedstawione dynamiczne wprowadzanie danych (+ działanie wzorów).


Rysunek nr 2. Arkusz WE (ArkuszWE), w którym w komórkach C2:C4 użytkownik wprowadza dane dotyczące transakcji w banku.

  Analiza problemu

W arkuszu REJESTR, oprócz rejestru, zostaną wykorzystane dwa pierwsze wiersze arkusza. W drugim wierszu będą wzory, według których przeprowadzane zostaną obliczenia w całej kolumnie rejestru. Pierwszy wiersz przeznaczony jest na wskaźniki, których celem jest wskazanie, czy po skopiowaniu, w danej kolumnie, wzory mają zostać zamienione na wartości, czy mają, w danej kolumnie, pozostać wzorami. Oznacza to, że chcąc zautomatyzować pracę wspomagania rejestrowania danych w rejestrze zaprojektowane zostały struktury danych:
- rejestr,
- wzorcowy wiersz rejestru (ze wzorami i formatami komórek) - drugi wiersz arkusza,
- wskaźniki (komórki niepuste), które wskazują, że w rejestrze będą przechowywane wartości, a nie wzory - pierwszy wiersz arkusza.

Użytkownik rejestrując dane może użyć w komórce C2 arkusza ArkuszWE funkcję DZIŚ(), która daje w wyniku numer dzisiejszego dnia. I tak, na przykład, jeżeli użytkownik wprowadzałby dane dwa dni po transakcji to mógłby, w komórce C2, napisać: =DZIŚ()-2.

Rejestracja danych w arkuszu: REJESTR może być realizowana wtedy, kiedy wynikiem wzoru w komórce H6 (arkusza WE) będzie PRAWDA.

W drugim wierszu arkusza z rejestrem znajdują się wzory (zob. Rysunek nr 3):
- w kolumnie A (wzór na liczbę porządkową); wzór zawiera funkcjonalność, która uniemożliwia wypisanie we wzorcowym wierszu numeru pozycji,
- w kolumnie B (wzór – odwołanie do daty w arkuszu: arkuszWE),
- w kolumnie C (wzór – odwołanie do wpłaty w arkuszu: arkuszWE),
- w kolumnie D (wzór – odwołanie do wypłaty w arkuszu: arkuszWE),
- w kolumnie E (wzór wyliczający stan).
W rejestrze w kolumnie A będą wzory (nie zamienione na wartości), w kolejnych trzech kolumnach przechowywane będą wartości – świadczą o tym wskaźniki (1) w pierwszym wierszu arkusza: REJESTR, a w kolumnie E wzory.


Rysunek nr 3. Arkusz: REJESTR. W drugim wierszu są odwołania do arkusza: ArkuszWE.

  Projekt algorytmu

Podstawą projektowania jest podzielenie problemu na części (Rysunek nr 4).
Niniejszy problem zostanie podzielony na dwa mniejsze problemy (zob. część I na Rysunku nr 4):
- kopiowanie drugiego wiersza do wiersza wstawionego na końcu rejestru (na końcu rejestru trzeba wstawić wolny wiersz, ponieważ użyte zakresy we wzorach powinny automatycznie zwiększyć się tak, aby wprowadzona, nowa pozycja, była automatycznie uwzględniana w innych obliczeniach),
- zamiana skopiowanych wzorów na wartości, ale tylko we wskazanych, za pomocą wskaźników z pierwszego wiersza, kolumnach.


Rysunek nr 4. Podział problemu na części (metoda M. Jacksona).

Aby skopiować drugi, wzorcowy, wiersz na koniec rejestru (zob. część II na Rysunku nr 4) należy:
- znaleźć ostatni wiersz w rejestrze,
- dostawić nowy wiersz na końcu rejestru, tak aby we wszystkich wzorach, w których wykorzystywany jest zakres rejestru, automatycznie zmienił się zasięg zakresu,
- skopiować drugi wiersz, na koniec rejestru.

Aby wykonać zamianę wzorów na wartości (zob. część III na Rysunku nr 4) należy:
- znaleźć ostatnią kolumnę ze wzorami; w praktyce jest to kolumna z ostatnim wskaźnikiem (niepustą komórką w pierwszym wierszu), ponieważ wtedy, w każdej następnej kolumnie wzory nie mają być zamieniane na wartości,
- zamienić wzory na wartości w skopiowanym wierszu w wybranych kolumnach (od pierwszej do ostatniej – znalezionej w poprzednim punkcie).

Analiza na tym poziomie powinna wykazać, że cztery pierwsze czynności można zrealizować nagrywając je i poprawiając.
Ostatnia czynność: zamiana wzorów na wartości wymaga dalszej analizy.
I tak, należy zamienić wiele wzorów na wartości począwszy od kolumny pierwszej do ostatniej znalezionej (patrz punkt: znalezienie ostatniej kolumny ze wzorami). Ponadto niektóre ze wzorów należy zamienić na wartości, a niektóre nie. Dlatego czynność: zamiany wzorów na wartości należy zmodyfikować na: wielokrotna zamiana wzoru na wartości. Należy pamiętać również, że nie każdy wzór ma zostać zamieniony na wartość (zob. część IV na Rysunku nr 4).

Gwiazdka w prostokącie, przy opisie czynności: zamiana wzoru na wartość oznacza, że czynność będzie wykonywana wielokrotnie (w tym przypadku, dla każdej kolumny).
Należałoby również zwrócić uwagę, że wstawienie gwiazdki wymusza zapis w liczbie pojedynczej (gwiazdka oznacza, że dana czynność będzie wykonywana wielokrotnie – w iteracji). W niniejszym przykładzie w ramach iteracji jest selekcja (kolejny prostokąt z kółeczkiem). Oznacza to, że w niektórych kolumnach czynność będzie wykonywana, a w niektórych nie.
Kółeczko w prostokącie oznacza, że czynność zostanie wykonana wtedy, kiedy warunek będzie spełniony (w tym przypadku warunek to: komórka niepusta w pierwszym wierszu).

  Testowanie projektu

Testowanie projektu polega na śledzeniu wykonywania kolejnych poziomów czynności zamieszczonych na schemacie M. Jacksona. Wykonanie czynności na ostatnich poziomach skutkuje ich realizacją (na Rysunku nr 5 jest zapisem kolejnych zrealizowanych czynności).


Rysunek nr 5. Testowanie projektu.

  Kodowanie projektu

Wraz z kodem programu wprowadzane są komentarze, po to, aby można było łatwo analizować napisany wcześniej program. Na Rysunku nr 6 pokazana jest kolejnosć kodowania (w przypadku tłumaczenia projektu na kod programu).


Rysunek nr 6. Kodowanie projektu.

  Wykonanie programu

Rysunek nr 7. Testowanie (wykonanie) kodu programu.

  PODSUMOWANIE

Uczymy nie tylko elementów MS Excel, czy Visual Basic for Excel, ale przede wszystkim sposobu definiowania problemu, schematów postępowania przy projektowaniu (przedstawieniu problemu w czytelnej formie), standardów rozwiązań (zob. przykład powyżej).
Celem wszystkich zajęć jest dostarczenie sposobów rozwiązań określonej jakości, a nie nauczenie kolejnych elementów MS Excel. Tu ważne jest, że w metodyce nie wykorzystuje się wszelkich elementów MS Excel, ale tylko te, za pomocą których można zrealizować rozwiązania określonej jakości (bezpieczne, czytelne i elastyczne).

Na wszystkich prowadzonych przez nas warsztatach uczymy elementów metodyki 4TG, standardów rozwiązań, schematów postępowania, oraz oduczamy stosowania tych elementów MS Excel, czy Visual Basic For Excel, które obniżają jakość (czyli są mało czytelne, mało bezpieczne czy nieelastczne).


Powyżej została przedstawiona budowa jednego ze standardów rozwiązań w metodyce 4TG – narządzia do wspomagania rejestrowania danych, które wspomaga funkcjonalność: zarządzanie rejestrem (czyli najczęściej wykorzystywaną strukturą danych).

Makro wykorzystujące opisany standard może być uruchomione z poziomu arkusza: ArkuszWE i powinno zawierać:
- sprawdzenie, czy wszystkie dane są poprawnie wpisane (wtedy, kiedy komórka H6 w arkuszu: ArkuszWE będzie miała wartość PRAWDA, zob. Rysunek nr 2), w takim przypadku należy:
- zmienić aktywny arkusz na arkusz z rejestrem,
- wywołać procedurę: NowaPozycja,
- zmienić aktywny arkusz na ArkuszWE (powrót do arkusza: ArkuszWE),
- wyczyścić dane wejściowe (zakres komórek C2:C4) – po to, aby przypadkowo, nie zarejestrować transakcji dwa razy.




Rysunek nr 8. Makro wykorzystujące standard do wprowadzania pozycji do rejestru.

Uwaga, makro zamieszczone na Rysunku nr 8 nie jest elastyczne, służy jedynie do zwrócenia uwagi na potrzebę stosowania jednego ze standardów rozwiązań: procedury NowaPozycja.