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.

view sourceprint?

1	Sub VBS_Test()

2	MsgBox "Wywołanie z poziomu VBS o godzinie: " & Time

3

End Sub

4

5	Sub VBS_Test_Arg(Parametr)

6	MsgBox "Wywołanie z poziomu VBS z parametrem: " & Parametr

7

End Sub

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.

view sourceprint?

01	'tworzymy zmienną reprezentującą aplikacje Excela

02

dim EXL

03	'uruchamiamy instancję Ecxcela

04	set EXL = CreateObject("Excel.Application")

05	'opcjonalnie wyświetlamy aplikację-

06	'krok nie jest wymagany jeżeli makro ma działać w tle

07	EXL.Visible = true

08

09	'otwieramy skoroszyt z uprzednio utworzonymi makrami

10	EXL.Workbooks.Open "c:\PROJEKT VBA\SZKOLENIA\VBS_Test_Arg.xlsm"

11	'wywołujemy pierwsze makro bez parametru

12	EXL.Run "VBS_Test"

13	'wywołujemy drugie makro z przekazaniem parametrem

14	EXL.Run "VBS_Test_Arg", "Tekst do przekazania"

15

16	'opcjonalnie wyłączamy aplikację i niszczymy naszą zmienną

17

EXL.Quit

18	Set EXL = Nothing

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.

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

Kontynuując opis języka VBScript chciałbym spojrzeć na wybrane podobieństwa i różnice pomiędzy tym językiem a VBA.

Zacznijmy może od zasadniczej różnicy- VBS nie działa w zamkniętym środowisku powiązanym z określoną aplikacją. Obszarem jego funkcjonowania jest, w naszym przypadku, system Windows, a podstawowym edytorem będzie zapewne zwykły notatnik. Pliki z kodem VBS są plikami tekstowymi, wykonywalnymi (z rozszerzeniem VBS). Język jest na tyle uniwersalny, że działa w środowisku Windows XP, Windows 7 czy też Windows 8.

Warto też zwrócić uwagę na wybrane cechy języka VBS, a w szczególności: problem ze stosowaniem polskich liter w nazwach własnych, brak profesjonalnego edytora, który ułatwiałby edycję kodu, dowolność stosowania wielkich i małych liter w procesie edycji skryptu.

A teraz zwróćmy uwagę na podobieństwa:

1. Każdy plik VBS tworzy niejako procedurę główną w ramach której mogą funkcjonować opcjonalnie podprogramy (Sub...End Sub) oraz funkcje (Function...End Function). Obie struktury wywołujemy zgodnie z technikami obowiązującymi w VBA.

2. Zmienne deklarujemy tymi samymi instrukcjami jak w języku VBA: Dim, Public, Private. Co ważne, nie deklarujemy typu zmiennej choć w procesie kompilacji każda zmienna jest przypisywana do określonego typu jaki znamy z VBA (Integer, Long, String, itp). Deklaracja zmiennych nie jest obowiązkowa, chyba że zastosujemy polecenie Option Explicit.

3. Możemy tworzyć tablice korzystając z funkcji Array oraz tablice dynamiczne. Funkcja ReDim funkcjonuje wg standardów znanych ze środowiska VBA.

4. Do dyspozycji mamy dziesiątki funkcji matematycznych, konwersji typów zmiennych, konwersji tekstu, daty, itp. W zdecydowanej większości są to funkcje znane ze środowiska VBA.

5. Istnieje możliwość stworzenia referencji do bibliotek innych aplikacji i wykonywanie operacji na strukturze obiektowej danej aplikacji. Do punktu tego zaliczę więc interakcję z aplikacjami pakietu Microsoft Office, możliwość zarządzania plikami dzięki bibliotece Microsoft Scripting Runtime, itp.

6. Do dyspozycji mamy kilka rodzai pętli (For...Next, For Each...Next, Do...Loop), instrukcje warunkowe (If...End If, Select Case...End Select).

7. W zakresie zaawansowanych technik możemy wykorzystać własne klasy, technikę pracy z wyrażeniami regularnymi RegEx czy też słownikiem Dictionary.

Wszystkich zainteresowanych szerszym zastosowaniem języka VBScript, w tym składnią oraz zestawem instrukcji i funkcji odsyłam do szeregu informacji znajdujących się w sieci. W szczególności zaś polecam następujące linki:
VBScript by MSDN
Microsoft Script Center

W następnym artykule zaprezentuję technikę, która pozwala na wywołanie makra stworzonego w VBA z poziomu języka VBScript. Dodatkowo działanie to wyzwolimy z poziomu Menadżera Zadań systemu Windows.

Na koniec chciałbym zademonstrować zestaw przykładowych instrukcji i działań jakie można wykonać z pomocą języka VBScript. Poniższy kod w całości proszę skopiować i zapisać w dowolnym pliku tekstowym z rozszerzeniem VBS. Następnie proszę uruchomić utworzony plik.

view sourceprint?

01	'proszę zapisać cały skrypt w pliku *.vbs

02	'następnie proszę zapisać i uruchomić

03	msgbox "Witam w przykładowym skrypcie VBScript"

04

05

'funkcja

06	function Test(a, b, h)

07	Test = 1/2 * (a+b) * h

08	end function

09

10	'podprogram subprocedura

11	sub Obliczenie_Pole_trapezu

12	'przykłady deklaracji zmiennych

13

dim A

14

dim B

15

dim C

16	A = 2:b = 2

17	'pobranie danych od użytkownika

18	c = inputbox("Podaj wysokość trapeza dla podstawy 2 i 2")

19	msgbox Test(a, b, c)

20	'pobranie informacji o typie zmiennej A

21	msgbox Typename(a)

22

End sub

23

24	'podprogram ze parametrem wejściowym

25	sub Praca_z_tekstem(Tekst)

26	msgbox "Tekst z wielkiej litery: " & ucase(tekst)

27

end sub

28

29	'wywołanie funkcji

30	msgbox Test(2,3,4)

31	'wywołanie sub-procedur

32	obliczenie_pole_trapezu

33	'wywołanie sub-procedury z parametrem

34	praca_z_tekstem "to jest tekst przykładowy"

35	'wywołanie sub-procedury (znajdującej się poniżej)

36	procedura_z_petla

37

38	'tablica dynamiczna

39

dim TBL()

40	Redim TBL(10)

41	TBL(0) = 100

42	TBL(1) = 200

43	'zwrócenie elementów tablicy

44	MsgBox Tbl(0) & " " & Tbl(1)

45

46	sub Procedura_z_Petla

47	dim i, txt

48	'prosta pętla For...Next

49	for i=1 to 10

50	txt = txt & chr(10) & i

51

next

52

53	msgbox txt

54

end sub

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

Zacznę od prostego i jednoznacznego określenia: VBScript (VBS) to nie to samo co VBA, to dwa różne języki pomimo tego, iż mają ze sobą bardzo wiele wspólnego. Do wspólnych elementów należy zaliczyć szczególnie szereg elementów składni, instrukcji, pętli, a większość instrukcji VBScript znaleźć można jednocześnie w VBA. Należy wyraźnie jednak rozróżniać oba języki.

Niniejszy wpis poświęcony zostanie teorii i definicji dot. języka VBS oraz zaprezentowany zostanie pierwszy przykładowy kod tego języka. Kolejny post w temacie dotyczyć będzie zagadnień związanych z instrukcjami VBS, podobieństwem i różnicami w relacji do VBA, stworzymy bardziej rozbudowaną procedurę VBS. W trzeciej części zaprezentuję techniki wiązania VBS z VBA- dokładnie w tym, a nie przeciwnym kierunku. Pokażę jak z poziomu VBS można wywołać makro stworzone w Excelu oraz jak uruchomić makro VBA o określonej godzinie wykorzystując systemowego Menadżera Zadań, VBS i VBA.

VBS (Microsoft Visual Basic Scripting Edition) to jezyk dość stary. Został stworzony do współpracy z technologią ASP jako alternatywa dla języka JavaScript i jak wiadomo w tym zakresie nie przyjął się na większą skalę. VBS wykorzystywany jest przez formularze aplikacji Microsoft Outlook także w najnowszych wersjach. Poza tym wspomaga działania na plikach i folderach będąc zintegrowany z biblioteką Windows Script Hosting (dość powszechnie używaną z poziomu języka VBA). Warto pamiętać również, że VBScript nie jest obecnie rozwijany w postaci samodzielnego języka, a jedynie jako element języka ASP.

W jaki sposób tworzymy skrypt języka VBS? Wystarczy kilka zaledwie kroków aby skonstruować samowykonywalny plik wykonujący prosty skrypt i wywoływany z poziomu systemu Windows.

1. Tworzymy plik tekstowy z rozszerzeniem vbs, np. QuickTest.vbs

2. Przechodzimy do edycji pliku i dodajemy następujących kilka linii kodu:

view sourceprint?

01	'wariant 1 okna z komunikatem

02	msgbox "Hello World!"

03

04	'wariant 2 okna z komunikatem

05	msgbox "10 razy 10 to razem " & 10*10

06

07	'wykorzytanie zmiennych i funkcji czasu

08

dim czas

09	czas = Now

10	msgbox "Aktualna data i godzina to: " & czas

3. Zapisujemy i zamykamy utworzony plik VBS

4. Uruchamiamy plik przez dwukrotne kliknięcie lub wpisanie nazwy pliku w pasku poleceń. Poniżej pierwsze dwa okna, które ukarzą się w wyniku działania skryptu.

Na koniec kilka szybkich spostrzeżeń dot. zaprezentowanego kodu. Proszę zwrócić uwagę, że VBScript:

a) pozwala na interakcję w środowisku Windows wykonaną przez wyświetlenie okien
b) umożliwia wykonywanie obliczeń
c) pracuje ze zmiennymi w sposób podobny do VBA
d) posiada zestaw funkcji  znanych ze środowiska VBA.

Mam nadzieję, że to zachęci czytelników do zapoznania się z kolejnym wpisem dot. VBScript, który zostanie opublikowany już za kilka dni.