Excel, Access, VBA ....

Przez ostatnich kilka lat współpracowałam z jednym z portali elearningowych. Publikowałam tam swoje autorskie szkolenia z VBA w Excelu. Niestety, kilka miesięcy temu portal zniknął z sieci, nawet nie udało mi się wyśledzić co się z nim stało. I tym sposobem pozostały mi materiały (nie wszystkie, ale sporo), które w tej chwili są zupełnie niewykorzystywane. Trochę mi ich szkoda - po małych przeróbkach można je wykorzystać, może się komuś przydadzą? A ponieważ większość z tych materiałów to filmiki, na swoim koncie YouTube założyłam kanał, gdzie będę je sukcesywnie publikować.

Na początek kurs "VBA dla początkujących" czyli kurs dla tych, którzy chcieliby zacząć swoją przygodę z VBA. Link jest tu:

VBA dla początkujących

Zapraszam do subskrypcji.

Wyobraźmy sobie arkusz Excela z kodem reagującym na zdarzenia arkusza. W zależności od tego, jaki jest to kod, jego wykonywanie bardzo łatwo może generować kolejne i zupełnie niepotrzebne zdarzenia arkusza.
Załóżmy, że mamy oprogramowane zdarzenie arkusza, które po zmianie wartości w komórce kolumny A w sąsiadującej z nią komórce kolumny B wstawia datę. Przykładowy kod takiej funkcji wygląda tak:

W trakcie wykonywania kodu, po wstawieniu wartości w kolumnie B następuje wykrycie zdarzenia "przy zmianie" i następuje ponowne uruchomienie makra (mimo, że pierwsze nie doszło jeszcze do końca). W tym przypadku akurat może być praktycznie niezauważalne, ale jeżeli takich zmian jest więcej - owszem. W ekstremalnym przypadku może to doprowadzić do zapętlenia kodu.

Chcąc tego uniknąć musimy na czas wykonywania kodu po prostu wyłączyć obsługę wykrywania zdarzeń. Wykorzystujemy do tego właściwość obiektu Application - EnableEvants. Jest to właściwość typu Boolean czyli może przybierać wartość Prawda lub Fałsz. Ustawiamy ją tak:

Application.EnableEvents=False          'wyłączenie obsługi zdarzeń

Application.EnableEvents=True            'włączenie obsługi zdarzeń

Wykorzystując w kodzie makra jw. mamy więc:

I już. Na czas wykonywania kodu obsługa zdarzeń jest wyłączona - wstawienie daty w komórce sąsiadującej nie wywoła w tym przypadku ponownej reakcji na zdarzenie.

Poprzednia notka na blogu poświęcona była dobrym praktykom przy tworzeniu aplikacji Access, tym razem kilka rad jakich zasad trzymać się przy pisaniu kodu VBA (czy to w Accessie czy w Excelu).

Na wszelki wypadek uprzedzam, że są to rady wynikające z moich własnych doświadczeń - stosuję je na co dzień i sprawdzają się. Generalnie jednak uważam, że nie ma jedynie słusznego sposobu tworzenia oprogramowania, choć pewne zasady powodują, że kod jest bardziej optymalny, a aplikacja działa szybciej i wydajniej.

• na początku każdego modułu warto wstawić Option Explicit. Pozwoli to na zabezpieczenie przed literówkami w nazwach zmiennych i procedur;
  
• żadnych polskich literek w nazwach funkcji, procedur czy zmiennych - tak jest naprawdę bezpieczniej i kod nie wysypie się przy uruchomieniu na komputerze z inną wersją językową;
  
• ponieważ słowa kluczowe VBA są ściśle związane z językiem angielskim, nadając własne nazwy (procedur, funkcji, zmiennych) lepiej używać oznaczeń bardziej przypominających słowa polskie. Pozwoli to na łatwe wyłapanie w kodzie tych nazw, które sami zdefiniowaliśmy od tego, co jest zdefiniowane w bibliotekach VBA;
• makra w aplikacjach Access lepiej napisać lub przekonwertować do kodu VBA - działają szybciej;
• w aplikacjach Excel przy nagrywaniu makra najczęściej pojawiającym się obiektem jest Selection - modyfikujmy nagrany kod tak, aby tego uniknąć. Nie tylko spowalnia działanie, ale także często powoduje niepotrzebne miganie ekranu;
• przy bardziej skomplikowanych aplikacjach warto podzielić kod na kilka różnych modułów i nadać im własne nazwy - odszukamy wówczas moduł z funkcjami, kodem wejściowym itp.
• korzystajmy z obsługi błędów - to się zawsze sprawdza;
• kompilujmy kod - od razu wyłapiemy jego błędy.
  

To takie podstawy. Ja wykorzystuję na co dzień i się sprawdzają.

Jak z poziomu kodu VBA (w Excelu lub Accessie) zmienić nazwy plików w określonym folderze? Służy do tego metoda (dość stara, ale ciągle użyteczna) Name .... As ....
Jej konstrukcja jest dość dziwna, liczy się skuteczność.o

Przykład kod z jej wykorzystaniem wygląda tak:

I już, nazwa pliku zapisanego na dysku została zmieniona - nie ma już pliku o nazwie Obrazek, jest za to Kwiatki. Warto zwrócić tu uwagę na to, że nie ulegają zmianie wartości przypisane do zmiennych, a jedynie nazwy plików.
Oczywiście, jeżeli plików jest więcej - robimy to w pętli.

Za pomocą tej metody można zmieniać nie tylko nazwy plików, ale również ich rozszerzenie. Jeżeli mamy na przykład pliki tekstowe z nietypowym rozszerzeniem (pochodzące np. z jakichś innych programów) np. *.abc - można też je zmienić np. na txt. W takim przypadku trzeba wyznaczyć nazwę podstawową pliku (wykorzystując funkcję InstrRev odszukującą położenie ostatniej kropki w nazwie pliku i zastępującą to, co po kropce - rozszerzeniem txt).

Moim zdaniem może mieć naprawdę szerokie zastosowanie.

Tworząc bardziej rozbudowane aplikacje w Excelu czy Accessie, często korzystamy z kilku różnych modułów. Z pewnością pozwala to na uporządkowanie procedur i funkcji. Ponieważ jednak część z nich może być wywoływana z innych modułów, konieczne będzie zadeklarowanie ich jako funkcji/procedur publicznych. Oznacza to jednak, że będą one także widoczne z poziomu skoroszytu - w okienku makr oraz w funkcjach użytkownika, co nie zawsze jest dobrym wyjściem. Gdy  zdecydujemy się na ich "ukrycie" czyli ustawimy jako procedury czy funkcje prywatne - będą one możliwe do wywołania tylko w obrębie modułu, w którym się znajdują.

W takiej sytuacji optymalnym wyjściem jest zadeklarowanie modułu jako prywatnego.

Robimy to poprzez wpisanie na początku modułu słów Option Private Module.
W ten sposób wszystkie funkcje i procedury publiczne w tym module będą możliwe do wywołania z poziomu kodu VBA (czyli wszystkich innych modułów), ale nie będą widoczne z skoroszytu czyli bez dostępu do edytora nie da się ich uruchomić.

Zasady te obowiązują zarówno VBA w Excelu, jak i Access.



 Zarówno funkcje jak i procedury utworzone w kodzie VBA mogą mieć charakter prywatny jak i publiczny. Co to oznacza?

Funkcje (procedury) prywatne są widoczne tylko w obrębie modułu, w którym zostały umieszczone, nie można się do nich odwołać z innych modułów. Nie są także widoczne w okienku makr w skoroszycie czy na liście funkcji zdefiniowanych przez użytkownika.

W kodzie poprzedzone są słowem Private.

Funkcje (procedury) publiczne są dostępne i mogą być wywołane zarówno z innych modułów, jak i z poziomu skoroszytu (lista makr i lista funkcji zdefiniowanych przez użytkownika. 

W kodzie poprzedzone są słowem Public lub w ogóle nie mają oznaczenia - domyślną opcją jest charakter publiczny.

Zasady te obowiązują w VBA zarówno w Excelu , jak i Accessie.



Do najczęściej spotykanych opcji modułów VBA należy Option Explicit. Dotyczy ona modułów ogólnych, arkuszy i skoroszytu w Excelu oraz wszystkich modułów w bazie Access, ma więc charakter bardzo uniwersalny. Co oznacza?

Option Explicit to wymaganie deklaracji wszystkich zmiennych występujących w module. Nie muszą mieć one zadeklarowanych typów, ale poprzedzoną słowem kluczowym Dim nazwę - tak.

Jeżeli któraś ze zmiennych nie będzie zadeklarowana - to przy kompilacji (lub przy uruchomieniu procedury pojawi się komunikat błędu kompilacji i mak0ro nie zostanie wykonane.

Bez Option Explicit - kod skompilowałby się prawidłowo.

Mimo tego - warto stosować tę opcję. Głównie dlatego, że pozwoli na wyłapanie i eliminację błędów już na etapie debugowania, a nie dopiero przy wykonywaniu kodu. Przede wszystkim stanowi skuteczną barierę przed literówkami w kodzie - od razu je wskaże.

Oczywiście sama deklaracja zmiennych (obowiązkowa lub nie) też ma bardzo duże znaczenie, zarówno do kontroli danych jak i oszczędzania pamięci. Warto.

Option Explicit można wpisać ręcznie na początku każdego modułu, ale można też ustawić jako opcję domyślną.
Robimy to w edytorze VBA: Tools --> Options:

W kolejnym okienku zaznaczamy opcję Require Variable Declaration.

Od tej pory każdy moduł w każdym skoroszycie Excela będzie rozpoczynał się właśnie od Option Explicin.

W trakcie pracy w Excelu zdarza się, że na ekranie pojawiają się komunikaty ostrzegawcze. Najczęściej dotyczą one np.nieodwracalności usunięcia danych, łączy itp. Dobrze, że są, gdyż stanowią ochronę przed przypadkowym skasowaniem ważnych elementów aplikacji. Gorzej jednak, gdy komunikaty te pojawiają się w trakcie wykonywania kodu VBA - takie potwierdzanie nie tylko wydłuża czas wykonywania makra, ale dodatkowo jeszcze sprawia, że obsługa staje się uciążliwa.
Takie systemowe komunikaty można wyłączyć na czas wykonywania kodu poprzez wykorzystanie właściwości obiektu Application - DisplayAlerts:

Ustawienie właściwości Application.DisplayAlerts na False powoduje wyłączenie komunikatów systemowych. Po wykonaniu ciągu instrukcji, włączamy je ponownie korzystając z tej samej właściwości ustawionej na True.

Często zdarza się, że w trakcie wykonywania makra czy procedury zapisanej w kodzie VBA, "miga" ekran. Wygląda to mało ciekawie - ciąg instrukcji powinien się wykonywać raczej w tle, bez odświeżania (a tym samym - migania) ekranu po każdym wykonanym kroku.
Można to osiągnąć poprzez wykorzystanie właściwości obiektu Application - ScreenUpdating:

Ustawienie właściwości na False powoduje wyłączenie odświeżania ekranu - zostaje on "zamrożony". Po wykonaniu ciągu instrukcji odświeżamy ekran korzystając z tej samej właściwości ustawionej na True.

Jakoś tak się dziwnie złożyło, że mimo iż często tworzyłam różne i to rozbudowane prezentacje w PowerPoincie, nigdy nie wstawiałam tam kodu VBA. Nie było takiej potrzeby, zawsze wystarczały mi narzędzia systemowe. Z VBA korzystam obficie w Excelu i w Accessie, w PP nigdy. Może dlatego przeżyłam mało przyjemną niespodziankę, gdy musiałam przygotować prezentację z użyciem formantów Active-X? 

Zadanie, przed jakim stanęłam, to zrobienie prezentacji PowerPoint będącej rodzajem testu. Użytkownik na kolejnych slajdach wykonuje ćwiczenia. Do wyboru jest jedna z kilku odpowiedzi - wszystko proste, tylko jedna odpowiedź dobra, brak pól wielokrotnego wyboru. Główną wartością prezentacji były pytanie i prawidłowe odpowiedzi na nie, a nie kwestia oprogramowania. I fajnie, wydawało mi się, ze nie ma żadnego problemu, aby takie coś stworzyć. Jak to jednak w życiu bywa - pojawiły się niespodzianki.
W przeciwieństwie do Excela czy Accessa w PowerPoint nie ma możliwości oprogramowania zdarzeń np. przy wejściu do slajdu itp. Nie da się więc tu przypisać żadnego kodu. Przykre, ale trudno. Gorszym problemem okazało się coś innego. Użytkownik na slajdzie widzi pytanie i kilka możliwych odpowiedzi, każda przypisana do jednej opcji (próbowałam również z przyciskami). Po kliknięciu w opcję - jedno z dwóch niewidocznych pól tekstowych (odpowiedź dobra lub odpowiedź zła)  zmienia swój stan na Visible czyli staje się widoczne w slajdzie. Banalne rozwiązanie, ale wystarczyłoby na potrzeby tej prezentacji. Problem polega jednak na tym, że jest to działanie jednorazowe. Nawet zapisanie prezentacji jako przenośnej powoduje "zapamiętywanie" ostatniego statusu czyli jeżeli raz została wybrana odpowiedź i dany TextBox stał się widoczny, to po ponownym uruchomieniu jest on już widoczny na samym starcie. Z punktu widzenia zadania do wykonania - absurd i nie może tak być. Szukałam trochę w sieci, czas naglił, ale nie znalazłam żadnego rozwiązania problemu.

Poradziłam sobie droga nieco "pod górkę". Wyłączyłam systemowe przechodzenie pomiędzy slajdami, wstawiając na każdym ze slajdów własne przyciski "poprzedni slajd i "kolejny slajd". Kliknięcie w któryś z nich powoduje wpierw uruchomienie makra "resetującego" wszystkie obiekty do stanu pierwotnego, a dopiero w drugim kroku - przejście do innego slajdu. Takie rozwiązanie działa i spełnia swoje zadanie, ale jakoś mam wrażenie, że idę dookoła.
Jak powinnam to zrobić?