Walczymy z Makrami w Excelu Część 3

O kim fantazjują kobiety? Oczywiście o mężczyznach piszących makra w Excelu . „Smart is a new sexy”, jak mawiają ;) Zamiast iść na siłownię, czy solarium, znów poszalejemy z Excelem i VBA .

Podsumujmy to,czego nauczyliśmy się do tej pory. W pierwszej części zapoznaliśmy się z edytorem makr. Powiedzieliśmy kilka słów o obiektach i nauczyliśmy się edytować nagrane makra. Później w części drugiej przeszliśmy na poziom programowania i poznaliśmy podstawy języka VB(A/S). Powinniśmy więc umieć już pisać małe makra, składające się z jednego modułu, które mogą już wykonywać całkiem skomplikowane rzeczy. Możesz być nieco zaskoczony, słysząc, że możesz już dość dużo zrobić. Wiesz jak obrobić nagrane makro i wykorzystać obiekty , które znajdziesz w wygenerowanym kodzie. Możesz spróbować „zaplątać” je w „if‑y” i „for-y”. To powinno dać ci możliwość rozwoju – poznawania nowych obiektów itd. Dziś spróbujemy bardziej zagłębić się w arkana programowania, a na koniec napiszemy bardzo prosty projekt praktyczny.

Zatem trochę już umiesz, a skoro nadal to czytasz, to pewnie chciałbyś umieć więcej ( lub jesteś masochistą lubiącym czytać mało interesujące rzeczy) . To po tym rozwlekłym jak kadencja rządu wstępie pogadamy sobie o funkcjach , procedurach, tablicach i obiektach

Funkcje :

Jeśli kiedykolwiek programowałeś w dowolnym właściwie języku programowania, to nie potrzebujesz tu wyjaśnienia. Zaczniemy zatem od składni:

Function jakasfunkcja(a, b)
	' tresc funkcji
End Function

Wywołanie funkcji nie zwracającej wartości może wyglądać w następujący sposób:

Sub makro()
    jakasfunkcja "Ala ma ", "kota"
End Sub

Jeśli natomiast chcemy żeby funkcja zwróciła wartość to zapiszemy to następująco:

Function jakasfunkcja(a, b)
	' tresc funkcji
	jakasfunkcja= „zwracana wartość”  ' tak zwracamy wartość funkcji
End Function

W tym momencie wywołanie:

Sub makro()
    wynik = jakasfunkcja("Ala ma ", "kota")
	msgbox wynik 
End Sub

Zwróć uwagę na nawiasy. Wywołanie makra wyświetli nam okienko z napisem „zwracana wartość”

Przetestuj czy tak jest. (myślałeś że tym razem nie będziesz musiał uruchomić Excela? ;) )

W języku VBA mamy dostępny całkiem pokaźny zestaw dostępnych funkcji. Wujek google na pewno pomoże przetestować Ci ich działanie. Funkcje zadeklarowane jako publiczne mogą być używane bezpośrednio w formułach Excela, podobnie jak funkcje wbudowane ( np. suma, czy suma,jeżeli) . Jest to bardzo przydatne , gdyż pozwala w prosty sposób rozszerzać możliwości arkusza kalkulacyjnego. Możliwe jest także działanie odwrotne. To znaczy wywoływanie funkcji Excela w kodzie VB. Przykładowo :

MsgBox WorksheetFunction.Sum(Range("a1:b2"))

Wyświetli wynik funkcji suma na komórkach od a1 do b12 aktywnego arkusza Procedury:

Procedury nie różnią się zbytnio od funkcji. W niektórych językach programowania całkowicie zrezygnowano z pojęcia procedur, na rzecz funkcji nie zwracających wartości. Procedury zaczynamy słowem kluczowym SUB, a kończymy END SUB. To już wiesz. Już pisałeś procedury. To co nas interesuje pod kątem Excela to to, że procedury możemy wywoływać jako makra, możemy je także przypisać do akcji przycisków i innych obiektów. Aby procedury przestały być widoczne używamy słowa private przed słowem kluczowym SUB.

Procedury mogą być także przypisane do zdarzeń aplikacji, ale o tym w następnej częsći.

Do tej pory pisałeś makra w jednej procedurze. Jednak kiedy będziesz pisał dłuższe programy, okaże się ,że jest to mało wydajne. W wielu miejscach zapewne powtórzyłbyś identyczne, lub prawie identyczne fragmenty kodu. Tu właśnie wykorzystujemy funkcje i procedury. Dzięki temu nasz program staje się bardziej przejrzysty, a także łatwiejszy w modyfikacji.

Tablice: Kolejną ważną częścią praktycznie każdego języka programowania są tablice. Najpierw przykład , a później objaśnienie. :

Sub test()
    tablica = Array("cukier", "kawa", "herbata", "sól", "proszek", "olej")
    For x = 0 To UBound(tablica) - 1
        MsgBox tablica(x)
    Next x
End Sub    

Pewnie domyślasz się, jaki będzie wynik działania tego makra(Ba! Pewnie myślisz nawet,że nie potrzebnie wspominam o tak prostych rzeczach ), ale dla porządku objaśnię. Zadeklarowaliśmy tablicę za pomocą linijki :

    tablica = Array("cukier", "kawa", "herbata", "sól", "proszek", "olej")

następnie, w pętli pokazaliśmy kolejne elementy tej tablicy w okienku. Co mamy ciekawego w tym krótkim makrze? Mamy pierwszy sposób deklaracji tablicy czyli Array(element1, element2....) Drugi sposób deklaracji to :

Dim tablica(5)
    tablica(0) = "cukier": tablica(1) = "kawa": tablica(2) = "herbata": tablica(3) = "sól": tablica(4) = "proszek": tablica(5) = "olej"

dla utrudnienia ( a także dla skrócenia listingu) umieściłem instrukcje w jednej linijce. Możemy tak zrobić - wykorzystujemy do tego celu dwukropek( zastępuje przejście do nowej linii) . Zwróć uwagę że elementów w tablicy jest 6, a zadeklarowałem tablice z liczbą 5, czyli o 1 mniej niż elementów. To dlatego,że tablice indeksowane są od zera.

Powinieneś przyzwyczaić się do indeksowania od zera. Można w prawdzie wykorzystać dyrektywę Option Base 1 , która sprawi ze tablice, będą „liczone” od jedynki,ale nie polecam takiego rozwiązania. Jest to moim zdaniem nie eleganckie i sprzeczne z ogólnymi zasadami „programowania” w większości języków. Kolejną ciekawa rzecz w tym makrze jest sposób odwoływania się do elementów tablicy. Podajemy nazwę tablicy i w nawiasie okrągłym podajemy numer elementu licząc od zera. W nawiasie OKRĄGŁYM, a nie kwadratowym , co jest charakterystyczne dla VBA.

Ciekawa jest także pętla for, w której wykorzystaliśmy funkcję UBOUND podającą liczbę elementów w tablicy. Pętle moglibyśmy zapisać nieco inaczej. Oto zmienione makro :

Sub test()
Dim tablica(5)
    tablica(0) = "cukier": tablica(1) = "kawa": tablica(2) = "herbata": tablica(3) = "sól": tablica(4) = "proszek": tablica(5) = "olej"
    For Each element In tablica
        MsgBox element
    Next element
End Sub

To druga forma pętli for. Zmienna o nazwie element za każdym razem przyjmuje kolejną wartość z tablicy. Jest to bardzo przydatne. Możemy nie tylko iterować (przechodzić po kolejnych elementach) po tablicach , ale także po innych kolekcjach. Poniższa pętla przechodzi po wszystkich arkuszach kasując wszystko w komórkach.

For Each wk In Worksheets
    Cells.Clear
Next wk

Obiekty: Obiekty wykorzystywałeś od początku zabawy, czyli od części pierwszej tego cyklu. Wiesz już na pewno, że do metod i pól obiektów odwołujesz się poprzez operator kropki. Czym są obiekty? Zobaczmy to na przykładzie obiektu Application

Sub t2()
For x = 0 To 10000
    Application.StatusBar = x
Next x
Application.StatusBar = False
Application.Calculate
Application.Calculation = xlCalculationManual
End Sub

W tym przykładzie odwołujemy się kilkukrotnie do obiektu Application. W pętli pokazujemy wartość zmiennej x w pasku statusu, po zakończeniu pętli przywracamy działanie paska. Następnie wywołujemy przeliczenie arkuszy – działanie jest adekwatne do naciśnięcia F9 na klawiaturze podczas zwykłej pracy. Następnie włączamy ręczne przeliczanie ( można zrobić to ręcznie w ustawieniach.

Jak widzisz odwołaliśmy się do pól i metod obiektu. Myślę że już czujesz czym jest obiekt. Wypowiedzenie tego wprost mogłoby tylko zaciemnić obraz. Lepiej jeśli na razie pozostanie to pojęciem intuicyjnym.

Obiekt Application jest zawsze dostępny. Niektóre musimy sami tworzyć. Kolejny przykład:

Sub test3()
Dim wb As Workbook ' linijka nie obowiązkowa
Set wb = Workbooks.Add
wb.Close (False)
End Sub

W odróżnieniu do zmiennych , aby przypisać wartość do zmiennej obiektowej musimy użyć słowa SET. W przypadku tego prostego makra metoda workbooks.add zwraca nam nowy obiekt . Jak widzisz w pierwszej linijce zadeklarowałem zmienną wb jako obiekt „klasy” workbook. Zrobiłem tak nie dlatego że tak trzeba, tylko po to żeby ułatwić sobie życie. Jeśli edytor „wie” jakiego typu jest obiekt, to po wciśnięciu kropki rozwinie nam dostępne opcje. Nie stanie się tak jeśli wyrzucisz z makra pierwszą linijkę. Podając typ obiektu, dzięki rozwinięciu metod unikniemy wielu literówek.

Musimy poznać jeden bardzo ważny obiekt FilesystemObject . Pozwala on nam na operacje na systemie plików. I jest punktem wyjściowym podstawowych operacji wejścia/wyjścia na plikach. Brzmi strasznie, ale jak zobaczysz wcale takie nie jest. Nie mamy metody , która zwraca nam taki obiekt. Musimy utworzyć go tradycyjnie.

W języku VB Script wyglądałoby to następująco:

Set fso = CreateObject("Scripting.FileSystemObject")

i nic nie stoi na przeszkodzie,żeby takiej właśnie składni użyć. Jednak wtedy nici z naszych podpowiedzi od edytora. Może prościej by było tak:

Dim fso As Scripting.FileSystemObject

spróbuj proszę tak zadeklarować nowy obiekt w edytorze. Działa? Tak? A Ja myślę że oszukujesz ;) Sprawdź to jeszcze raz.

Nie działa, ponieważ Excel nie ma dostępu do biblioteki w której zawarte są informacje na temat obiektu FileSystemObject. Możemy to prosto naprawić. W edytorze wybierz menu Tools i pozycje references . Odnajdź pozycję Microsoft Scripting Runtime i ją zaznacz. Teraz nasze makra ( w tym zeszycie) będą miały dostęp do wszystkich obiektów z biblioteki scripting runtime, w tym do FileSystemObject. Teraz Działa :) dopiszmy kolejną linijkę:

set fso = new FileSystemObject

To jest standardowa droga tworzenia nowych obiektów. W pierwszej linijce deklaruję że taki obiekt będzie, a w drugiej nadaje mu „wartość”. Oczywiście możemy tworzyć kilka obiektów tego samego typu. Myślę,że sam powinieneś zapoznać się z obiektem FileSystemObject i kilkoma innymi. Poszukaj troszkę w internecie. Sprawdź się. Spróbuj np. napisać makro, które do excela będzie wrzucać nazwy plików mp3 i ich rozmiar. Myślę, ze dasz radę. Nie nauczysz się niczego jeśli nie będziesz próbował.

Dla oderwania od teorii , na zakończenie, zrobimy coś praktycznego. Załóżmy,ze pracujesz w korporacji i twój szef kazał ci przygotować pewien raport..Mówiłeś, że jest on niepotrzebny, ale szef uparł się, że wszyscy będą z niego korzystać. Musiałeś go zrobić. Raport został umieszczony na zasobie sieciowym, skąd wszyscy mogą go pobrać i korzystać. Teraz w prosty sposób sprawdzimy ile osób faktycznie korzysta z twojego raportu(UWAGA! MOŻE DZIAŁAĆ DEMOTYWUJĄCO! ) .

Praktyka wykorzystania obiektów

Pomysł jest bardzo prosty. Przy otwieraniu raportu, będziemy zapisywać informacje o użytkowniku aplikacji , dacie i godzinie otwarcia raportu. Ponieważ plik może zostać skopiowany na komputer służbowy, nie możemy zapisywać tego bezpośrednio w pliku. Wszyscy otwierają plik w sieci firmowej i mają dostęp do pewnego zasobu sieciowego, co umożliwia nam wykorzystanie w tym celu pliku. Najpierw utworzymy plik na zasobie. W przykładzie będzie to :

//192.168.1.100/sciezkadopliku/plik.txt

konstrukcja pliku będzie wyglądać następująco: użytkownik;data;godzina możesz zapisać to w pierwszej linijce pliku. Teraz napiszemy makro, które będzie zapisywać informacje do pliku. Ponieważ nie chcemy, aby makro było dostępne po wciśnięciu alt+F8 (lepiej,żeby szef tego nie widział) , będzie to funkcja.

Const plik = "//192.168.1.100/sciezkadopliku/plik.txt"
Function logowanie()
On Error GoTo errorhandler
    Dim fso As FileSystemObject
    Dim file As file
    Dim tex As TextStream
    
    Set fso = New FileSystemObject
        
    If fso.FileExists(plik) Then        
            Set file = fso.GetFile(plik)
            If Not file.Attributes = ReadOnly Then
                Set tex = fso.OpenTextFile(plik, ForAppending)
                    tex.WriteLine (Application.UserName & ";" & Date & ";" & Time())
                    tex.Close
            End If
            
    End If
Exit Function
errorhandler:
    ' w przypadku błędu udajemy,że nic się nie stało
End Function

jeśli znasz język angielski to przeczytanie tego makra nie sprawi ci problemów. Omówmy krótko działanie. Będziemy wykorzystywać trzy obiekty. FileSystemObject, File ,oraz Textstream. Pierwszy już znasz, drugi to obiekt pliku, a trzeci to strumień tekstowy. Na początku widzisz także linijkę on error go to errorhandler. Jest to typowa dla vba obsługa błędów. W prawdzie w tym makrze sami zadbaliśmy o to, by błędy się nie pojawiły, ale i tak zostawiliśmy tą linijkę. On erro goto , w momencie wystąpienia błędu spowoduje przeskoczenie do etykiety, którą widzisz pod koniec funkcji. Przed nią stoi exit function ( zakończ funkcję). Gdyby tego tam nie było, to program wykonałby także to co znajduje się za etykietą ( w tym wypadku nic tam nie ma) . Jak widzisz najpierw sprawdziliśmy, czy plik istnieje, później czy możemy do niego pisać, a następnie otwarliśmy go w trybie dopisywania, dopisaliśmy linijkę i zamknęliśmy plik. Jeśli makro nie działa, prawdopodobnie nie dodałeś referencji do Microsoft Scripting runtime

Prześledź działanie makra i zobacz, że faktycznie dopisywana jest linijka. Ostatnią rzeczą jaką musimy zrobić jest sprawienie,żeby makro uruchamiało się automatycznie przy otwarciu zeszytu. Kliknij dwukrotnie na obiekt ten_skoroszyt w oknie eksploratora projektu po lewej stronie. W nowo otwartym oknie znajduje się pole wyboru z napisem (General) . Po wybraniu Workbook powinien zostać wygenerowany kod :

Private Sub Workbook_Open()

End Sub

Dokładniejsze omówienie zdarzeń znajdzie się w następnej części, bo dziś już i tak zbytnio się rozpisałem :) Dziś wpiszemy w środek po prostu nazwę naszej funkcji.

Private Sub Workbook_Open()
logowanie
End Sub

Zapisz zmiany i zamknij zeszyt. Przetestuj działanie naszego makra. Dziś zrobiliśmy pierwszy praktyczny projekt. Będziemy zawsze wiedzieć kiedy i kto(osobiście zapisuje także login domenowy, bo nazwę w aplikacji można zmieniać) otwierał nasz raport.

Jak obiecałem, kończymy na dziś. Postaram się, żeby kolejna część była krótsza :) Mam nadzieję, że Cię nie zraziłem. Wiem, że dość szybko omówiłem zagadnienia, i nie wyczerpałem żadnego tematu, myślę jednak że masz już jakiś pogląd na to jak wygląda pisanie makr w Excelu, a jeśli temat Cię zainteresował, to poszukasz lepszych źródeł informacji niż kilka wpisów na blogu. Gorąco zachęcam do rozwijania się, czytania książek, kursów, ale przede wszystkim do praktyki. Już dziś zapraszam także do kolejnej części, gdzie powiemy sobie troszkę o zdarzeniach. Pozdrawiam!