Święta tuż, tuż więc proponuję Wam dzisiaj oderwanie się nieco od poważnych zagadnień i naukę makr poprzez zabawę w świąteczne dekoracje. Nasze wyzwanie to narysować w Excelu choinkę z życzeniami którą prześlemy do naszych zapracowanych koleżanek i kolegów.
Generalnie makra w aplikacjach Microsoft Office umożliwiają automatyczne wykonywanie zaprogramowanych wcześniej czynności bez udziału użytkownika. Wykorzystywanym w nich językiem programowania jest Visual Basic for Automation natomiast najważniejszą informacją dla początkującego adepta sztuki automatyzacji jest fakt, iż Excel posiada umiejętność nagrywania wykonywanych przez nas czynności i późniejszego ich wielokrotnego odtwarzania po ewentualnym zmodyfikowaniu. Tak więc w najprostszym przypadku możecie włączyć rejestrowanie makr narysować sobie choinkę kolorując wybrane komórki w Excelu i zapisać stworzone automatycznie przez program makro.
My też tak rozpoczniemy ale przy tej okazji pokażę wam kilka podstawowych funkcji VBA których użycie w przyszłości może wam zaoszczędzić mnóstwo czasu.
Na początek zapiszmy sobie nasz plik w formacie który umożliwia przechowywanie makr czyli .xlsm. W tym celu wybieramy Plik, Zapisz jako lub Zapisz kopię jeżeli plik jest już zapisany, Nazwa pliku i wybieramy Zapisz jako typ „Skoroszyt programu Excel z obsługą makr (*.xlsm)”
Teraz możemy przejść do nagrywania naszego makra.
W tym celu w menu Widok uruchomimy Makra i Zarejestruj makro…
Następnie wybieramy nazwę naszego makra np. Choinka
I robimy wszystko co nam się podoba, aby narysować choinkę
Mnie na przykład wyszła taka
Teraz zatrzymujemy rejestrację makra
I możemy go sobie oglądnąć przez Widok, Makra, Wyświetl makra
Oraz wybór wprowadzonej przez nas nazwy
Zobaczycie coś podobnego do mojego obrazka
To co w nim jest najważniejsze to
„Sub Choinka ()
End Sub”
W ten sposób definiujemy w Excelu procedury gdzie Choinka () określna nazwę procedury a słowa Sub i End Sub jej początek i koniec. Kluczową kwestią jest również informacja że w VBA każdy tekst poprzedzony znakiem pojedynczego górnego apostrofu „’” jest komentarzem który nie jest traktowany jako kod programu. Używajcie komentarzy wszędzie gdzie się da aby w przyszłości zrozumieć co dany kod miał robić i uprościć sobie jego ewentualne poprawki.
Wracając do naszego arkusza możecie teraz przypisać skrót klawiszowy do zarejestrowanej procedury Choinka() przez Widok, Makra, Wyświetl makra, Opcje
Następnie możecie skasować swoją choinkę i wciskając wybraną kombinację klawiszy odtworzyć ją sobie jeszcze raz i tyle razy ile wam się podoba.
Brawo macie swoje pierwsze makro !
Teraz poprawimy nieco wygląd naszej choinki wykorzystując większy zakres dostępnych w VBA funkcji.
W szczególności zwróćcie uwagę na poniższe funkcje gdyż będziemy z nich korzystać w przyszłości bardzo często:
“Cells(W,K) =A” nadaje wartość A komórce o wierszu W i kolumnie K
Cells(1,2)=3 oznacza przypisanie komórce B1 wartości 3
„Cells(W,K).Select” zaznacza komórkę o wierszu W i kolumnie K
Cells(4,5).Select oznacza zaznaczenie komórki E4
“Range(Cells(W1,K1),Cells(W2,K2)).Select” zaznacza obszar którego rogami są komórki W1 K1 oraz W2 K2 (gdzie W i K to numery wierszy)
Range(Cells(6,7),Cells(8,9)).Select oznacza zaznaczenie obszaru którego rogami są komórki G6 oraz I8
„For Z=X to Y
Next” powtarza zawarte pomiędzy liniami For oraz Next linie kodu zwiększając za każdym razem zmienną Z o jeden od wartości X aż do Y.
Czyli np. For i=1 to 10
Cells(i,1)=i
Next oznacza że i po kolei przyjmuje wartości 1,2,3,4 …. 10 wpisując te wartości w komórki A1,A2, A3, A4, ….A10
Tyle z teorii na dzisiaj resztę musicie na razie przyjąć na wiarę lub oprzeć na dokładnych opisach przy każdej linijce poniższego kodu
Sub Choinka2()
‘Przygotowanie arkusza
Cells.Select
Selection.Delete Shift:=xlUp
Columns(“A:HL”).Select
Selection.ColumnWidth = 0.1 Rows(“1:500”).Select
Selection.RowHeight = 1 ActiveWindow.DisplayGridlines = False
Napis
Range(Cells(501, 1), Cells(501, 225)).Select
Selection.Font.Name = “Amasis MT Pro Black”
Selection.Font.Color = RGB(255, 0, 0)
Selection.Interior.Color = RGB(255, 255, 0)
Selection.RowHeight = 35
Selection.Font.Size = 20
Cells(501, 1) = “Wesołych Świąt i Szczęśliwego Nowego Roku !!!”
‘Rysujemy podłogę
Range(Cells(489, 1), Cells(500, 225)).Select
Selection.Interior.Color = RGB(90, 60, 0)
‘Rysujemy pień
For Wiersz = 461 To 488
Range(Cells(Wiersz, 100), Cells(Wiersz, 120)).Select
Selection.Interior.Color = RGB(120, 60, 0)
Next
‘Rysujemy choinkę
For Wiersz = 10 To 460
Range(Cells(Wiersz, 110 – (Wiersz – 10) / 5), Cells(Wiersz, 110 + (Wiersz – 10) / 5)).Select
Selection.Interior.Color = RGB(0,200,80)
Next
End Sub
‘zaznaczenie całego arkusza
‘skasowanie wszystkich komórek
‘zaznaczenie 220 pierwszych kolumn
‘ustawienie szerokości zaznaczonych kolumn na 0,1
‘zaznaczenie 500 pierwszych wierszy
‘ustalenie wysokości zaznaczonych wierszy na 1
‘ukrycie siatki dla komórek
‘zaznaczenie wiersza 501
‘wybranie czcionki dla napisu
‘wybranie koloru czcionki dla napisu
‘wybranie koloru tła dla napisu
‘ustawienie wysokości wiersza
‘ustawienie wielkości czcionki
‘wprowadzenie napisu do komórki A501
‘wybieramy obszar podłogi
‘ustalamy kolor podłogi
‘dla Wierszy od 461 do 488
‘zaznaczamy kolumny od 100 do 120
‘i kolorujemy na wybrany kolor
‘następny wiersz
‘dla Wierszy od 10 do 460
‘zaznaczamy coraz szerszy zakres komórek
‘i kolorujemy na zielono
‘następny wiersz
Którego uruchomienie da nam już całkiem przyzwoitą choinkę wraz z życzeniami.
Jeżeli Wasza choinka jest za szeroka lub za wysoka zmieńcie eksperymentalnie parametry w linijkach
Selection.ColumnWidth = 0.1
Selection.RowHeight = 1
tak aby dopasować wygląd drzewka do Waszego ekranu.
Teraz tak jak na początku możecie zarejestrować swoje makro kolorując drzewko w miejscach które wam się podobają a następnie dokopiować zarejestrowany kod do powyższego makra wklejając go przed linijką „End Sub”. Pamiętajcie aby nie kopiować ze swojego makra linijki „Sub” oraz „End Sub”
Jak pewnie zauważycie dorysowanie fajnych ozdób na choince jest czasochłonną sprawą a przecież całym sensem niniejszej serii jest upraszczanie sobie życia i oszczędzanie czasu spędzonego przy komputerze. Dlatego zaproponuję wam na koniec jeszcze jedno makro które nieco zmodyfikuje naszą choinkę aby znowu bardziej przypominała oryginał i automatycznie doda jej światełka i bańki.
Sub Choinka3()
‘Przygotowanie arkusza
Cells.Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Interior.Color = RGB(0, 0, 0)
Columns(“A:HL”).Select
Selection.ColumnWidth = 0.1
Rows(“1:500”).Select
Selection.RowHeight = 1
ActiveWindow.DisplayGridlines = False
‘Napis
Range(Cells(501, 1), Cells(501, 225)).Select
Selection.Font.Name = “Amasis MT Pro Black”
Selection.Font.Color = RGB(255, 0, 0)
Selection.Interior.Color = RGB(255, 255, 0)
Selection.RowHeight = 35
Selection.Font.Size = 20
Cells(501, 1) = “Wesołych Świąt i Szczęśliwego Nowego Roku !!!”
‘Rysujemy podłogę
Range(Cells(489, 1), Cells(500, 225)).Select
Selection.Interior.Color = RGB(90, 60, 0)
‘Rysujemy pień
For Wiersz = 461 To 488
Range(Cells(Wiersz, 100), Cells(Wiersz, 120)).Select
Selection.Interior.Color = RGB(120, 60, 0)
Next
‘Rysujemy choinkę
L = 0
Piętro = 0
WKS = 0
For i = 1 To 450
L = L + 1
If L = 50 + Piętro * 10 Then
WKS = Int(L / 4) + Piętro * 10
L = 0
Piętro = Piętro + 1
End If
Korekta = Int(i / 4 * 1.5) – WKS
Range(Cells(10 + i, 110 – Korekta), Cells(10 + i, 110 + Korekta)).Select
Selection.Interior.Color = RGB(0, 176, 80)
Next
‘Definiujemy Wzorce
Światełko = “OOOOOOXOOOOOOOOXOOOOXOOOOXOOOOXOOOXOOOXOOOOOOXOOXOOXOOOOOOOOXXXXXOOOOOOOOOXXXXXXOOOOXXXXXXXXXXXXXOOOOXXXXXXXOOOOOOOOXXXXXOOOOOOOOXOOXOOXOOOOOOXOOOXOOOXOOOOXOOOOXOOOOXOOOOOOOOXOOOOOOOOOOOOOOOOOOOOO”
Bańka = “OOOOOOOXOOOOOOOOOOOOOXOOOOOOOOOOOXXXXXOOOOOOOOOXXXXXOOOOOOOOXXXXXXXOOOOOOOXXXXXXXOOOOOOXXXXXXXXXOOOOOXXXXXXXXXOOOOOOXXXXXXXOOOOOOOXXXXXXXOOOOOOOOXXXXXOOOOOOOOOXXXXXOOOOOOOOOOOXOOOOOOOOOOOOOXOOOOOO”
‘Umieszczamy światełka
For i = 1 To 6
For j = 1 To i
Y = Int(Rnd() * 28) + i *Rnd()* 2
X = 6 – Int(Rnd() * 12)
For L = 0 To 13
For P = 1 To 14
If Mid(Światełko, P + 14 * L,1) = “X” Then
Cells(-35 + i * 45 + i * i * 4 + L + Y, 110 – (i – 1) * 12 + (j – 1) * 24 – 7 + P + X).Select
Selection.Interior.Color = RGB(255, 255, 0)
End If
Next
Next
Next
Next
‘Umieszczamy bańki
For i = 1 To 5
For j = 1 To i * 2
If j = 1 Or j = i * 2 Then
Y = 0
X = 0 Else
Y = 9 – Int(Rnd() * 18)
X = 9 – Int(Rnd() * 18)
End If
For L = 0 To 13
For P = 1 To 14
If Mid(Bańka, P + 14 * L, 1) = “X” Then
Cells(30 + i * 65 + i * i * 3 + L + Y, 100 – i * 20 + j * 20 – 7 + P + X).Select
Selection.Interior.Color = RGB(255, 0, 0)
End If
Next
Next
Next
Next
End Sub
‘zaznaczenie całego arkusza
‘skasowanie wszystkich komórek
‘zaznaczenie całego arkusza
‘czarne tło
‘zaznaczenie 220 pierwszych kolumn
‘ustawienie szerokości zaznaczonych kolumn na 0,1
‘zaznaczenie 500 pierwszych wierszy
‘ustalenie wysokości zaznaczonych wierszy na 1
‘ukrycie siatki dla komórek
‘zaznaczenie wiersza 501
‘wybranie czcionki dla napisu
‘wybranie koloru czcionki dla napisu
‘wybranie koloru tła dla napisu
‘ustawienie wysokości wiersza
‘ustawienie wielkości czcionki
‘wprowadzenie napisu do komórki A501
‘wybieramy obszar podłogi
‘ustalamy kolor podłogi
‘dla Wierszy od 461 do 488
‘zaznaczamy kolumny od 100 do 120
‘i kolorujemy na wybrany kolor
‘następny wiersz
‘zerujemy zmienną liczenia linijek
‘zerujemy zmienną piętra choinki
‘zerujemy zmienną wstępnej korekty szerokości
‘dla 450 linijek pionowych
‘liczymy kolejne linijki
‘jeżeli mamy 50 linijkę + 10 linijek razy numer kolejnego piętra choinki
‘wyliczamy wstępną korektę szerokości
‘zerujemy licznik linijek
‘zwiększamy numer piętra choinki o jeden
‘koniec jeżeli
‘wyliczamy korektę szerokości dla danej linijki
‘obszar do zaznaczenia w danej linijce
‘kolorujemy choinkę
‘następny wiersz
Światełko = “OOOOOOXOOOOOOOOXOOOOXOOOOXOOOOXOOOXOOOXOOOOOOXOOXOOXOOOOOOOOXXXXXOOOOOOOOOXXXXXXOOOOXXXXXXXXXXXXXOOOOXXXXXXXOOOOOOOOXXXXXOOOOOOOOXOOXOOXOOOOOOXOOOXOOOXOOOOXOOOOXOOOOXOOOOOOOOXOOOOOOOOOOOOOOOOOOOOO”
Bańka = “OOOOOOOXOOOOOOOOOOOOOXOOOOOOOOOOOXXXXXOOOOOOOOOXXXXXOOOOOOOOXXXXXXXOOOOOOOXXXXXXXOOOOOOXXXXXXXXXOOOOOXXXXXXXXXOOOOOOXXXXXXXOOOOOOOXXXXXXXOOOOOOOOXXXXXOOOOOOOOOXXXXXOOOOOOOOOOOXOOOOOOOOOOOOOXOOOOOO”
‘w sześciu rzędach
‘w każdym kolejnym rzędzie o jedno więcej
‘losowo rozsunięte w pionie
‘losowo rozsunięte w poziomie
’14 kolejnych linijek wzorca
’14 pixeli pojedynczej linii wzorca
‘jeżeli we wzorcu dla danej linijki i pixela w linijce jest X
‘położenie pixela
‘kolorujemy pixel na żółty
‘koniec jeżeli
‘następne P (pixel wzorca)
‘następne L (linijka wzorca)
‘następne j (światełko)
‘następne i (rządek światełek) ‘w pięciu rzędach
‘w każdym kolejnym rzędzie o 2 więcej
‘ jeżeli pierwsza i ostatnia bańka
‘nie przesuwamy w pionie
‘nie przesuwamy w poziomie
‘a dla pozostałych baniek
‘przesuwamy w pionie losowo
‘przesuwamy w poziomie losowo
‘koniec jeżeli
’14 kolejnych linijek wzorca
’14 pixeli pojedynczej linii wzorca
‘jeżeli we wzorcu dla danej linijki i pixela w linijce jest X
‘położenie pixela
‘kolorujemy pixel na czerwono
‘koniec jeżeli
‘następne P (pixel wzorca)
‘następne L (linijka wzorca)
‘następne j (bańka)
‘następne i (rządek baniek)
Uzyskany efekt oglądnijcie sami. Dodanie losowego wyboru miejsca umieszczania ozdób powoduje że za każdym razem drzewko wygląda nieco inaczej.
Na koniec zdradzę Wam co zrobić aby wasze choinki rysowały się automatycznie po uruchomieniu arkusza i wyrażeniu zgody na użycie makr. Będzie to miła niespodzianka dla kogoś komu wyślecie swój arkusz z życzeniami.
W edytorze kodu z prawej strony w układzie arkuszy wybierzcie „Ten skoroszyt”
Następie przełączcie widok na „Workbook”
I wybierzcie procedurę „Open”
W tym miejscu umieszczamy kod który ma się uruchamiać podczas otwierania naszego arkusza. Pamiętajcie że pozostawienie tu swojego lub co gorsza obcego kodu może powodować dziwne działanie arkusza lub w skrajnych przypadkach przejęcia kontroli nad waszym komputerem więc nigdy nie zezwalajcie na uruchomienie makr w plikach z niewiadomych źródeł.
Teraz wklejcie zawartość swojego makra (bez linijek „Sub „i „End Sub”) pomiędzy linijki „Private… ” oraz „End Sub”. Koniecznie skasujcie choinkę z arkusza jeżeli ja tam macie i zapiszcie plik. Jeżeli wszystko poszło dobrze choinka zacznie się rysować automatycznie po otwarciu pliku.
Zachęcam do tworzenia własnych choinek w Excelu i przysyłania ich kodu (w plikach tekstowych lub w Wordzie aby nie były traktowane jako złośliwe oprogramowanie) na adres Konrad.Pogodz@itch.pl. Wasze choinki upiększą naszą firmową stronę.
Dzięki darmowym materiałom szkoleniowym będziesz mógł przećwiczyć nabytą wiedzę i sprawdzić ją w praktyce. Pobierz i ćwicz natychmiast.
