Wydajność i efektywność 1/2- Makrooptymalizacja

Zagadnienia dot. optymalizacji i efektywności programów w VBA podzielone zostaną na dwie części zgodnie z przyjętą gdzieniegdzie systematyką:

1. makrooptymalizacja dot. zagadnień szerszych, dużych, właściwego doboru narzędzi, techniki, rozwiązania czy algorytmu,
2. mikrooptymalizacja dot. szeregu drobnych detali w kodzie, które w całościowym ujęciu mają istotny wpływ na szybkość wykonywania procedury.

Muszę jednak od razu poczynić zastrzeżenia zanim przejdę do prezentacji krótkich opisów w/w technik. Optymalizacja jest tematem 'żywym', co jakiś czas zdarza się, że pojawiają się nowe sugestie, tak więc nie należy traktować niniejszych postów jako zamkniętą listę działań optymalizacyjnych. I drugie zastrzeżenie- niektóre z technik można uznać za sporne, a więc takie, które nie w każdym przypadku przekładają się na widoczną poprawę efektywności.

Na początek kilka zagadnień dot. makrooptymalizacji:

1. W aspekcie pracy z pętlami, tablicami i kolekcjami:

• unikać zagnieżdżonych pętli, stosować techniki wyjścia z pętli (Exit For, Exit Do)
• w procesach porównywania posortować porównywane kolekcje/tablice
• w pracy z kolekcjami stosować pętle For Each...Next
• w pracy z tablicami stosować pętle For i...Next
• w pracy z tablicami dynamicznymi stosować płynną redefinicję wymiaru

2. W pracy z obiektami:

• unikać aktywowania (.Activate) i zaznaczanie (.Select)
• wyłączać odświeżanie ekranu (Application.ScreenUpdating = False)
• wyłączać (gdy tylko jest to bezpieczne) standardowe okna potwierdzeń (Application.DisplayAllerts = False)
• wyłączać przeliczanie formuł, szczególnie gdy kod VBA ingeruje w strukturę wartości i formuł w skoroszycie (Application.Calculation = xlManual)

3. Inne aspekty:

• unikać przesadnego stosowania zmiennych publicznych
• gdy możliwe i dopuszczalne zerować wartości zmiennych publicznych instrukcją End
• nie tworzyć własnych funkcji jeżeli podobna funkcja istniej w zbiorze funkcji VBA lub WorksheetFunction

A na koniec chyba najważniejsza z technik optymalizacji- nie zapominać o A w VBA... innymi słowy, jeżeli można coś wykonać z wykorzystaniem dostępnych, gotowych narzędzi w aplikacji to zróbmy to w miejsce tworzenia nowego kodu VBA.

VBScript vs. VBA w teorii i przykładach (3/3)

Trzeci wpis poświęcony pracy z językiem skryptowym VBScript dotyczyć będzie analizy konkretnego przykładu. Zacznijmy więc od opisu samego problemu...

W czasie prowadzonych szkoleń z zakresu VBA dla Excela czasem słyszę pytanie dot. możliwości uruchomienia określonego makra o określonej godzinie. Jedną z technik pozwalającą na wykonanie takiej operacji jest wykorzystanie instrukcji Application.OnTime. Warunek- aplikacja Excela musi pozostać uruchomiona do godziny, o której makro ma być uruchomione. Co jednak zrobić w sytuacji gdy Excel jest wyłączony? Co zrobić jeżeli chcemy uruchomić makro VBA dla aplikacji MS Word lub MS PowerPoint w sytuacji, gdy obie te aplikacje nie dysponują instrukcją Application.OnTime? W każdym z tych wariantów możemy sięgnąć po systemowego Menadżera Zadań i skrypt VBS. Prześledźmy kolejne niezbędne działania.

Krok 1. Utwórzmy testowy plik XLSM z dwoma prostymi makrami, które umożliwią nam wykonanie testu także w wariancie z przekazaniem parametru do wywoływanego makra.


Krok 2. Tworzymy skrypt VBS i zapisujemy go w pliku z takim właśnie rozszerzeniem. Poniżej przykładowy kod wraz z komentarzami wyjaśniającymi poszczególne operację. Proszę zwrócić uwagę na olbrzymie podobieństwo do języka VBA.

Krok 3. Pozostało nam już tylko powiązać nasz skrypt z godziną wywołania wykorzystując do tego celu systemowego Menadżera Zadań.

• uruchamiamy Menadżera Zadań z Menu >> Programy >> Akcesoria >>Narzędzia systemowe >> Harmonogram zadań (dla Windows 7)
• tworzymy nowe zadanie wskazując jako akcję do wykonania nasz utworzony plik VBS (poniżej odpowiednie okno prezentujące ten krok) i ustawiamy wszystkie niezbędne parametry zgodnie z kreatorem tworzenia zadań
  (operacja ta jest relatywnie łatwa i intuicyjna więc nie będę poświęcał jej więcej czasu)

O określonej godzinie, zgodnie z parametrami ustawionymi w systemowym Harmonogramie Zadań, uruchomiony zostanie skrypt VBS. W wyniku jego działania otwarta zostanie nowa instancja Excela i wywołane zostaną dwa makra. Po zakończeniu pracy makr zakończony zostanie także skrypt VBA w wyniku czego Excel zostanie zamknięty.